I had the pleasure of chatting with Peter Wahl, Oracle Principle Product Manager for Encryption and Secrets. He gives a great talk on Transparent Data Encryption and Oracle Key Vault.
It’s been a year long process now the book is finally been released. There are a few things I would have written different and a few other subjects I would have liked to cover. Perhaps that will come in my next book or future posts.
In this book we cover Secure Coding, setting up Encryption, and audit. We also dive deep into performing privilege analysis.
Between ongoing data breaches and emerging technologies constantly coming out, you need to ask the question. “Is my information secure?” On October 11th we will be hosting a security day with Oracle Corporation in Reston Virginia.
When: October 11, 2019
Where: 1910 Oracle Way, Reston, VA, 20190
Executive level presentations on: Cybersecurity Challenges, 5G Security, Machine Learning and AI Security, Blockchain, Cloud Security, and Cybersecurity Maturity Model Certification (CMMC)
We will be having executive level and technical discussions on the information security challenges we will be facing from emerging technology. All talks are geared to Executives and Senior Technical Leads.
8:00 – 8:30 – Arrival and Registration
8:30 – 9:00 – Morning snack (pastries), Welcome, and Introductions
|9:00 – 11:30 – Executive Sessions|
|9:30-10:00||Governance and Compliance|
|10:30-11:00||Emerging Tech Security|
|11:30-12:30 – Lunch|
|12:30 to 2:55 – Track Sessions Track 1: Cloud Security|
|1:20-2:05||Cloud Access Security|
|2:10-2:55||Mitigating Threats (Insider, lateral movement, exfiltration, spoofing, etc)|
|Track 2: Data Security|
|12:30-1:15||Database Application Development Tools Security (SQL Developer)|
|1:20-2:05||Maximum Security Architecture (MSA) – Data Architecture|
|2:10-2:55||Data Encryption/Key Management|
|Track 3: Governance and Compliance|
|Track 4: Emerging Technology Security|
|12:30-1:15||AI/ML, Augmented Reality Security|
|2:10-2:55||IOT/Connected Device Security|
|Track 5: 5G/Communications Security – 12:30 to 2:55|
|2:10-2:55||Session Border Control, Hardware Security|
Closing Discussion/Q&A with Experts – 3:00-3:30
Thank you to our Primary Sponsor is Oracle Corporation for helping put on this event.
Critical Oracle Database flaw needs to be patched. The patch is in the July 2018 CPU patch.
The exploit is in the Oracle Java VM. Read: https://nvd.nist.gov/vuln/detail/CVE-2018-3110
This is an easily exploited flaw, that allows a user with low level privileges ( connect with network access via Oracle Net) can completely hijack the Oracle database.
Affected versions 188.8.131.52, 184.108.40.206, 220.127.116.11, 18
Patch Information: http://www.oracle.com/technetwork/security-advisory/cpujul2018-4258247.html
Grab a cup of coffee or a cup of tea. This is not a short post; There is a lot to explain, and many point are repeated. You need to understand all the in’s and out’s of CBAC. However; once you have an understanding, CBAC really quite easy to impalement.
A lot about Code Based Access Control is not intuitive. In fact a lot of people I talk to are confused about what CBAC is and what CBAC is not. The basic premise of CBAC is that a subprogram of a package can only execute only using the privileges that have been granted to the package through a role.
NOTE: What I’m explaining is a multi-schema model. I’m intentionally keeping the model simple to show just the CBAC aspect of a secure architecture. This model is using three schemas, hr that holds data and hr_api that is going to hold packages that will access the data, and hr_decls that holds common types that are used across schemas. Also note, this is not the full secure architecture. There are a number of elements of my secure architecture I am leaving out to focus on CBAC.
Before we go too far, we need to understand some subtleties about roles. In order to grant a role to a package, the role must first be granted to the owner of the package with either admin option, delegate option, or you must connect as sys to grant the role to the package and the owner of the package must have created the role. Yea’ that’s a bit to digest, so let’s examine the three different types of role grants that must be made.
Three different ways to grant a role to a package.
#1 --As a user with create role privileges and the ability to grant -- the required privileges to the role. ie: DBA conn rob_dba@demo create role <role>; grant <privilege> on <object owner>.<object name> to <role>; grant <role> to <package owner> with admin option; conn <package owner>@<instance> grant <role> to package <package name>;
#1 GRANTING THE ROLE TO THE PACKAGE OWNER WITH ADMIN OPTION: Granting the package owner the role with admin option works, but the package owner can grant the role to other schemas. Using a user with create role privilege and the ability to grant the required privileges on the underlying objects (I normally use my dba account rob_dba). This is an unnecessary security risk. Like I said, the package owner can now grant the role to other schemas, thereby making the trusted path weaker. I’m sure there are use cases for granting the package owner the role with admin option; however that is a corner case and should be justified, not the norm and quite frankly, I can not think of a use case that would justify using this method.
#2 -- as package owner, create the role.the package owner must have -- create role privileges. conn <package owner>@<instance> create role <role>; conn sys@<instance> as sysdba grant <role> to package <package owner>.<package name>;
#2 USING SYS TO GRANT THE ROLE TO A PACKAGE: The package owner must have the create role privilege and the package owner must have created the role. In this method, we are creating the role as the package owner and connecting as sys to grant the role to the package. I hope there is no need to explain what a bad idea it is to use the sys account. There is a huge security issue, doing work as sys.
#3 --As a user with create role privileges and the ability to grant -- the required privileges to the role. ie: DBA conn rob_dba@demo create role <role>; grant <privilege> on <object owner>.<object name> to <role>; grant <role> to <package owner> with delegate option; conn <package owner>@<instance> grant <role> to package <package name>;
#3 GRANTING THE ROLE TO THE PACKAGE OWNER WITH DELEGATE OPTION: This is the preferred method to grant a role to a package. It appears the same as #1; however we are granting the package owner the role with delegate option. Using this method, we can grant the package the required role, but the package owner can not grant the role outside of it’s schema. This is the least amount of privileges needed to execute the task.
Remember, we want to operate with the least amount of privilege and still be able to do our job.
The hr_decls schema:
My hr_decls schema is used to define common types that will be referenced between schemas. This is not required for using CBAC, however it makes referencing common types between schemas much easier and makes maintenance simpler. Once this package is created, execute is granted to a role (exec_emp_decl_role) and that role is granted to any users that need to use it and not compile code against it. Mainly this grant is for testing purposes. Note: PL/SQL grant execute through a role or grant execute directly? If you are executing a pl/sql package using an anonymous block, you can pick up the privilege through a role. If you are compiling a pl/sql package that references a pl/sql package in a different schema, you must grant execute on the package directly.
conn rob_dba@demo create role exec_emp_decl_role; conn hr_api@demo -- create the decls package. This package is used to create types -- that are used between schemas. create or replace package hr_decls.emp_decl authid current_user as cursor emp_cur is select * from hr.employees; subtype st_emp is emp_cur%rowtype; type tt_emp is table of st_emp index by pls_integer; end emp_decl; / -- usr1 (our test user) required this role to reference the package -- through an anonymous block grant execute on exec_emp_decl_role to usr1; -- hr_api requires a direct grant on the package hr_decls.emp_decl grant execute on hr_decls.emp_decl to hr_api;
create package hr_api.emp_select_pkg authid current_user as
Hey, we are granting a role to the package, why do we need to grant privileges on the underlying objects directly to the package owner? In order to compile your package, you need to grant privileges on the underlying object to the executing schema.
If you use authid definer (that is the default authid. So if you don’t specify current_user or definer, the package is created with definer’s rights.), the package will pick up the privileges from the schema it resides in. If we grant a lot of privileges to the schema the code resides in, or heaven forbid, the code resides in the schema with the data, and there is a flaw in your code; the hacker owns your database.
By using authid current_user, the package inherits the privileges of the user executing the code. If the user does not have any privileges on the underlying data objects then subprogram in a package can only execute with the privileges granted to the package through a role.
Now let’s start setting this up from the beginning. Connecting as a dba, or another appropriate user that has the proper privileges, we need to create a role, grant privileges to that role, and grant that role to hr_api with admin option.
We are going to create the role hr_emp_sel_role. Then we are going to grant to the role select on hr.employees.
create role hr_emp_sel_role; grant select on hr.employees to hr_emp_sel_role;
Once we have these roles and grants let’s grant it to hr_api with delegate option.
grant hr_emp_sel_role to hr_api with delegate option;
Here is a niggle; you need to get your package to compile. The schema, in this case hr_api needs to be granted select on the underlying data. So, we are going to grant select on hr.employees to hr_api. I don’t really like granting privileges directly to a schema, but it’s needed for the code to compile. Using this grant, the code that selects against hr.employees can compile. But remember, the code we are writing will be authid current_user. We now have the underlying roles with the appropriate grants to start building our application.
grant select on hr.employees to hr_api;
There is one more role you need. This is the role that will be granted execute on the api package, that will inturn be granted to the user.
create role EXEC_EMP_SEL_API_ROLE;
Here is the rolls and all the grants we have done so far.
conn rob_dba@demo create role hr_emp_sel_role; grant select on hr.employees to hr_emp_sel_role; grant hr_emp_sel_role to hr_api with delegate option; grant select on hr.employees to hr_api; create role EXEC_EMP_SEL_API_ROLE; grant execute on hr_decls.emp_decl to hr_api; grant EXEC_EMP_SEL_API_ROLE to usr1;
grant execute on hr_decls.emp_decl to exec_emp_decl_role; grant exec_emp_decl_role to usr1;
Now we need to connect as hr_api and start building the application. In my environment, hr_api is granted create procedure through a password protected role. We are creating one function in the package, sel_hr_emp_phone that returns an employees information based on the phone number.
conn hr_api@demo SET role hr_api_admin_role identified by My#Supper7Secret#Password2; -- note authid current_user. create or replace package hr_api.sel_emp_phone_api authid current_user as function sel_hr_emp_phone(p_phone hr.employees.phone_number%type) return hr_decls.emp_decl.tt_emp; end sel_emp_phone_api; /
grant hr_emp_sel_role to package sel_emp_phone_api;
We’ve flipped on the privilege the package needs. So sel_emp_phone_api can only execute with the privileges granted to it from the role, or privileges inherited from the user executing the package. (you must understand, the package can still inherit more privileges from the user.) Let’s finish off by filling in the package details.
create or replace package body hr_api.sel_emp_phone_api as function sel_hr_emp_phone(p_phone hr.employees.phone_number%type) return hr_decls.emp_decl.tt_emp is ltt_emp hr_decls.emp_decl.tt_emp; begin select * bulk collect into ltt_emp from hr.employees where phone_number = p_phone; return ltt_emp; exception when no_data_found then -- <fixme> insert error handler. raise_application_error(-20000,''); End sel_hr_emp_phone; end sel_emp_phone_api; /
We’ve got all the objects we need together, now we need two more grants to run this. We need to grant execute on hr_api.sel_emp_phone_api package to the exec_emp_sel_api_role and we need to grant the role to a user to execute the api. I have a test user usr1 that is used to test various security configurations. Here is the the setup that has already been done.This test user is granted create session, exec_emp_sel_api_role and exec_emp_decl_role.
conn rob_dba@demo create user usr1 identified by AHotRedKotenok; grant create session to usr1; create role exec_emp_decl_role; grant execute on hr_api.sel_emp_phone_api to exec_emp_sel_api_role; -- so the user can execute the api through an anonymous block. grant exec_emp_sel_api_role to usr1; -- so the user can reference the hr_decls.emp_decl package from an -- anonymous block. grant execute on hr_decls.emp_decl to exec_emp_decl_role; grant exec_emp_decl_role to usr1;
Now we can test this out. Connect as usr1, and call the function. Let’s see if we get anything back.
conn usr1@demo declare ltt_emp hr_decls.emp_decl.tt_emp; begin ltt_emp := hr_api.sel_emp_phone_api.sel_hr_emp_phone( p_phone => '+1.800.555.1212'); end; /
Let’s revisit a customer who was about to go through an IG Audit. There is one finding that always seems to come up. Default accounts with default passwords. I don’t care if the accounts are expired and locked, if the user still has the default password set, then it’s a finding. Now you can go through all the default accounts and change the password if you want. I’m personally fond of having a script that does the work for me.
This is the script I use to go through and change all the default passwords. Now, once I’m done with this, I go back on set any necessary default accounts such as DBSNMP to a known password. Why, I find it easier to lock all the doors then methodically move through and unlock only the doors that are necessary.
First we are going to need a function that returns an impossible password. Well, in all honesty, it’s not impossible to crack, (that is because given enough horsepower any password can be eventually cracked.) This function will do one thing, return a 30 character randomly generated password of any printable character. There are a couple characters you can’t put in a password, so when we encounter those, we are going to replace them with an integer between 0 – 9.
Next we are going to go through all the default accounts and change the password.
<CODE> DECLARE -- get the list of users with default passwords. CURSOR users_with_defpwd_cur IS SELECT username FROM sys.dba_users_with_defpwd; stmt VARCHAR2(2000); -- the base sql statement passwd VARCHAR2(32); -- the impossible_password. FUNCTION impossible_password RETURN VARCHAR2 AS -- will create a 30 character password wrapped in double quotes. passwd VARCHAR2(32); -- this is the password we are returning. -- we need 32 characters because we are -- wrapping the password in double quotes. p_invalid_char_3 VARCHAR2(1) := '"'; -- invalid password character 3 is '"' p_invalid_char_4 VARCHAR2(1) := ';'; -- invalid password character 4 is ';' BEGIN passwd := SYS.dbms_random.STRING('p',30); -- get 30 printable characters. -- find all the invalid characters and replace them with a random integer -- between 0 and 9. passwd := REPLACE(passwd, p_invalid_char_3, ceil(SYS.dbms_random.VALUE(-1,9))); passwd := REPLACE(passwd, p_invalid_char_4, ceil(SYS.dbms_random.VALUE(-1,9))); -- before we pass back the password, we need to put a double quote -- on either side of it. This is because sometime we are going to -- get a strange character that will cause oracle to cough up a hairball. passwd := '"' || passwd || '"'; RETURN passwd; END; -- main procedure. BEGIN FOR users_with_defpwd_rec IN users_with_defpwd_cur LOOP passwd := impossible_password; stmt := 'alter user ' || users_with_defpwd_rec.username || ' identified by ' || passwd; EXECUTE IMMEDIATE stmt; END LOOP; EXCEPTION WHEN OTHERS THEN sys.dbms_output.put_line(sqlerrm); sys.dbms_output.put_line(stmt); END; / </CODE>
When evaluating the security of a database, and/or the security of an application, there are a few things that will make me start pounding my head against my desk. What prompted this? A customer asked me help evaluate a system that is about to go through an IG Audit and they want to be ahead of the curve. The good part is I was able to come up with a lockdown procedure that is specific to this customer and their needs. The bad part is, I keep seeing these same mistakes over and over again. So, lets sit down and go through five of the mistakes that I keep seeing over and over again.
- A COTS application required any of the following privileges to get installed or operating. I’m sorry guys, if you have not figured out what permissions your application really needs, then it’s not ready to be put on the market. I was able to do a privilege analysis and find out what this application really needed.
- DBA – I have a hard time justifying granting DBA to an application to be installed or operate.
- SELECT / CREATE / UPDATE / DROP ANY <FILL IN YOUR TYPE>. Hey, this is another privilege that just makes me shake my head. Because the ANY any privilege allows the grantee to operate on ANY object in the database, this just tells me you have not thought it through. Many databases have multiple applications running in an instance. Why would your application need to access schemas that are not part of your applications?
- Oh and as a side note, why did the application need both DBA and all these ANY privileges?
- Okay there are a whole bunch of privileges that should never be granted to an application, these are just a few.
- Not cleaning up users that have left. Please tell me why you need to keep a user in the system who left two years ago. If someone can give me a good excuse, I’m willing to listen, but I got to tell you, in thirty years in this business, I have not heard any good reason to keep a user in the system.
- Go ahead, drop those users, including the test, sit, and dev instances. They don’t need to be there.
- Connecting as the application to do your job. I see this so often that it really makes me wonder about the technical ability of the people doing the job. Please tell me why you need to connect to the application to do your work? If there are permissions that you need that are missing, we can resolve that.
- Mixing Data and Code in the same schema. Why do you need to do this? You really should separate your data and code. All you need is one little sql injection bug for a bad guy to find, then she owns your database. Oh, and it’s highly likely you have a sql injection bug, you just don’t know it yet. We have a plan to separate the code from the data, it’ll take a while, but this should keep the auditors happy.
- Writing queries in production. I get it, it happens; you are supporting your users and the user needs something from the database quick. There is not a report available that answers the users question. You connect to the production instance through sql plus, sqlcl, sql developer or some other tool. You then type your query, hit enter and production slams on the breaks. Yea’ I have lost count of the number of times I’ve seen this, you forgot to join those two really big tables and just got a cartesian product. A junior developer just walked up to me and asked the question, why is production so slow. My gut reaction was to chew him out. In the end, I did not chew him out, and I gave him an explanation of what happened (forgetting to put in a where clause) and gave him a stern warning about writing code in production. You have a test instance, please use it before running in production.
You can download the full code from gethub.com CBAC Simple
This example depends on the HR Demo schema being loaded. We will be building on this example over the next several months to present a full blown application that includes many of the secure coding features I have been talking about.
We need to do better. I keep walking into customers, where I keep hearing: “It’s harder to do.” “We have deadlines to make.” “We will address that sql injection flaw after we go production; because we are behind schedule.” Now guys, I get it! I have been down in the trenches for about thirty years coding, designing, and administering some sensitive information systems. (They are all sensitive in my view.) We really need to get on top of this, now.
First lets go back and see where we started this discussion. In part 1 and part 2, I introduced you to the better Architecture of using multiple schemas then we discussed definers rights and invokers rights.
Lets start: the first part of the code we are going to be doing some clean up. This way we can run the code again and again without getting error messages.
1 -- clean up before we start. 2 declare 3 procedure Drop_User(pUser in varchar2) is 4 User_Doesnt_Exist exception; 5 pragma Exception_Init(User_Doesnt_Exist, -01918); 6 begin 7 execute immediate 'drop user '||pUser||' cascade'; 8 exception when User_Doesnt_Exist then null; 9 end Drop_User; 10 begin 11 Drop_User('hr_api'); 12 Drop_User('usr1'); 13 end; 14 / 15 16 declare 17 Role_Doesnt_Exist exception; 18 pragma exception_init(Role_Doesnt_Exist, -01919); 19 begin 20 execute immediate 'drop role hr_emp_select_role'; 21 execute immediate 'drop role hr_backup_role'; 22 execute immediate 'drop role api_admin_role'; 23 exception when Role_Doesnt_Exist then null; 24 end; 25 -- done cleaning up. 26 27
Now lets get the two schemas we will be using in this example.
Line 3: we are creating a HR_API schema that will hold all the code to access data objects in the HR schema.
Line 5: we are creating the USR1 user that will make calls to code in the HR_API schema. (In a future post, I’ll be adding in HR_CODE schema that will hold the business logic. But for now, we are keeping it simple to demo CBAC.
1 -- this is going to be my api schema that will 2 -- have access to the hr objects. 3 create user hr_api identified by x; 4 -- this will be my executing user. 5 create user usr1 identified by x; 6
Now lets get to the roles we are going to need.
Line 3 we are creating the role HR_EMP_SELECT_ROLE. This role will have select on hr.employees. We will be granting this role to the api package that accesses the hr.employees table.
Line 6 we are creating the HR_BACKUP_ROLE. This role will be granted create any table, and then the role will be granted to the api package that does the backup. I really don’t like granting create any table, but for this purpose it is required to create a table in a different schema.
Line 8 is the API_ADMIN_ROLE. This role will have the create procedure privilege and will be assigned to the HR_API schema.
1 -- the hr_emp_select_role will have select in hr.ermployees. 2 create role hr_emp_select_role; 3 -- the hr_backup_role has create any table privilege. I really don't 4 -- like that, but that is what the role needs to create a table in 5 -- a diffrent schema. 6 create role hr_backup_role; 7 -- the api_admin_role has create procedure privilege. 8 create role api_admin_role; 9
.Now lets get into the grants we will need.
Lines 4 – 6 we are granting create session to usr1. At this time, this is the only privilege this user will need. Once we have the API package compiled, we will grant execute on that package to usr1
Lines 10 – 12 we are granting create procedure to the API_ADMIN_ROLE. This will be needed for HR_API to create a package in it’s schema.
Lines 16 – 18 we are granting select on hr.employees to the HR_EMP_SELECT_ROLE. This role will be granted to the HR_API schema with delegate option. I’ll discuss more about that later.
Lines 20 – 21 we are granting select on hr.employees to the HR_API schema. I really don’t like this, but it’s needed in order to compile the package.
Lines 26 – 28 we are granting create session to the HR_API schema. This will be needed to so HR_API can connect and create the required objects with only the permissions needed.
Line 32 we are granting create any table to the HR_BACKUP_ROLE. I really don’t like granting create any. This role will be granted to the API package that does some dynamic sql. I really need to think about tightening this down a bit. But for this iteration, we are going to use this to create a backup table in the hr schema.
Lines 36 – 39 here we are granting the HR_EMP_SELECT_ROLE and HR_BACKUP_ROLE to the HR_API schema with delegate option. This is important, you need to use either the DELEGATE OPTION or the ADMIN OPTION in order to be able to grant the roles to the HR_API packages.
Lines 41 – 43 and finally on our grants, we are granting the API_ADMIN_ROLE to the HR_API schema so HR_API can create the required packages.
1 -- the user usr1 will only need create session. after we've created 2 -- the package in the hr_api schema, we will grant execute on the 3 -- package to usr1. 4 grant 5 create session 6 to usr1; 7 -- 8 -- the api_admin_role will need the create procedure privilege. 9 -- this will be granted to hr_api. 10 grant 11 create procedure 12 to api_admin_role; 13 -- 14 -- this will give the hr_emp_select role the privilege 15 -- it needs to execute. 16 grant 17 select on hr.employees 18 to hr_emp_select_role; 19 -- 20 -- this will be needed to compile the code in the api schema. 21 grant 22 select 23 on hr.employees to hr_api; 24 -- 25 -- we are going to revoke create session after we are done. 26 grant 27 create session 28 to hr_api; 29 -- 30 -- the hr_bacup_role is used to demenstrate 31 -- using dynamic sql. 32 grant create any table to hr_backup_role; 33 -- 34 -- hr_api needs the roles with delegate option (or admin option) 35 -- to be able to grant the role to a package. 36 grant 37 hr_emp_select_role, 38 hr_backup_role 39 to hr_api with delegate option; 40 -- 41 grant 42 api_admin_role 43 to hr_api; 44 -- 45
The final step to setup our users and roles is to alter the user HR_API so there are no default roles. This is going to be done, because in the next installment we will be looking at creating password protected roles.
1 -- during normal operating, the hr_api schema does not 2 -- need any privileges. 3 alter user hr_api 4 default role none; 5
Now lets get into building our HR_API schema. It starts simple enough,
Line 1 connect to hr_api
Line 4 We set the roles we will need to create the api package, and the roles that will be granted to the api package.
1 -- connect as hr_api 2 conn hr_api/x@orcl 3 4 SET role hr_emp_select_role, api_admin_role, hr_backup_role; 5
Lines 1 – 8 we are creating a simple package that does two things. One get the phone number of a person based on first and last name. The second package is used to make a backup of the hr.employees table.
Line 12 Now that we have the package specification, we can grant the role HR_EMP_SELECT_ROLE and HR_BACKUP_ROLE to the hr_api.pgk_emp_select package.
1 create or replace package hr_api.pkg_emp_select 2 authid current_user AS 3 PROCEDURE pGetPhone(pFname IN VARCHAR2, 4 pLname IN VARCHAR2, 5 pPhone OUT VARCHAR2); 6 PROCEDURE pBackupEmp; 7 END; 8 / 9 10 -- we are going to grant the hr_emp_select_role 11 -- to pkg_emp_select 12 GRANT hr_emp_select_role, hr_backup_role to package pkg_emp_select; 13
Lines 3 – 24 don’t do anything very interesting, this procedure will get a phone number based on first and last name. My exception handler is really quite simple and does not do much. We’ll address that in a later post, because as you know, returning errors to the user is not the best practice from a infosec point of view.
Lines 30 – 39 is using dynamic sql to create a backup of the hr.employees table. We granted HR_BACKUP_ROLE to the package, this way, the package can create a table in the hr schema. I’m going to have to rethink this example, I don’t like it all that much because granting CREATE ANY TABLE is not really a good practice.
1 CREATE OR REPLACE PACKAGE BODY hr_api.pkg_emp_select AS 2 3 PROCEDURE pGetPhone(pFname IN VARCHAR2, 4 pLname IN VARCHAR2, 5 pPhone OUT VARCHAR2) IS 6 x INTEGER; 7 BEGIN 8 BEGIN 9 SELECT phone_number 10 INTO pPhone 11 FROM hr.employees 12 WHERE first_name = pFname 13 AND last_name = pLname; 14 EXCEPTION WHEN no_data_found then 15 pPhone := 'xxx'; 16 WHEN too_many_rows THEN 17 pPhone := 'yyy'; 18 WHEN others THEN 19 -- we can add in the help desk error handler later, again this 20 -- is just to demo granting roles to packages. 21 sys.dbms_output.put_line('pGetPhone raised an exception ' || sqlerrm); 22 END; 23 -- 24 END pGetPhone; 25 -- 26 -- this is a very simple procedure, create a backup table using execute 27 -- immediate. (dynamic sql) the only way this procedure is going to work 28 -- is if the package has create any table privilege to be able to 29 -- create a table in another schema. 30 PROCEDURE pBackupEmp IS 31 -- This is the date string 20170805 32 dt VARCHAR2(8); 33 BEGIN 34 dt := to_char(sysdate,'rrrrmmdd'); 35 execute immediate 'create table hr.employees' ||dt|| ' as select * from hr.employees'; 36 sys.dbms_output.put_line('create table success'); 37 exception when others then 38 sys.dbms_output.put_line('create table error ' || sqlerrm); 39 END pBackupEmp; 40 end pkg_emp_select; 41 / 42
Now that we have all that, lets’ test it.
1 conn usr1/x@orcl 2 set serveroutput on 3 declare 4 -- to hold the phone number, because we can't reference 5 -- hr.employees we can not use phone_number%type. 6 lPhone VARCHAR2(20); 7 begin 8 sys.dbms_output.put_line('testing cbac select on emp'); 9 hr_api.pkg_emp_select.pGetPhone(pFname => 'Jose Manuel', 10 pLname => 'Urman', 11 pPhone => lPhone); 12 sys.dbms_output.put_line(lPhone); 13 end; 14 / 15 16 -- if you run this twice on the same day you are going to get 17 -- a ORA-00955 error. this is because the package creates 18 -- a backup of the hr.employees table by appending the date 19 -- to the table name. So, if you are going to run it twice 20 -- you need to drop or rename the backup table. 21 begin 22 sys.dbms_output.put_line('testing dynamic sql'); 23 hr_api.pkg_emp_select.pBackupEmp; 24 sys.dbms_output.put_line('done'); 25 exception when others then 26 sys.dbms_output.put_line('daaaa ' || sqlerrm); 27 end; 28 / 29
The GDPR Right to access can get a bit complicated as it covers a few things that provide some challenges for us.
What is the purpose of the processing? Lets face it, we process data on people for a number of reasons.
First we are going to use the example of an online storefront. When a person places an order, a number of things happen with that person’s data. 1) Is there payment information accurate? 2) Do they have a store credit? 3) Does that person get a discount? 4) The parts that were ordered need to be shipped. 5) Store a history of the persons order, so the next time they want to order something, the system can make recommendations based on past orders.
If the shipping is being done by a third party such as Fedex or UPS, the person’s data is going to be transmitted to the shipping company. So now a person’s data is being held at both the online vendor and the shipping company.
What is the category of the data being processed? For the online storefront, we have payment, order and shipping information. For the shipping company, there is shipping and value information.
The recipients or categories of recipient to whom the personal data have been or will be disclosed, in particular recipients in third countries or international organisations. So in this case, let’s say that the package was shipped by FEDEX and let’s assume FEDEX data processing is done in the United States. (NOTE: this is an assumption, not a statement of fact.) Anyway the person’s shipping information has now left the EU for the United States of America. From the perspective of the online store vendor, the shipper will need to address Article 44 of the GDPR. I’ll get to Article 44 later.
Where possible, the envisaged period for which the personal data will be stored, or, if not possible, the criteria used to determine that period. So, how long will the data be stored? Certain types need to be kept for a defined period of time. ie. Financial information may need to kept for five or seven years that is defined by either applicable law or regulation. Other types of information may be kept for very short periods of time. I once worked on a system where the data was only resident for thirty days. This system packaged up the data and sent it to downstream system for further processing. Once the data was sent downstream, it was no longer needed. But, once the data was sent downstream, we would need to track what downstream systems received the personal data.
The existence of the right to request from the controller rectification or erasure of personal data or restriction of processing of personal data concerning the data subject or to object to such processing. You should be seeing a pattern here. It is going to be critical that we identify all PII (Personally Identifiable Information) in our systems. If a person identifies information we hold that is inaccurate, there needs to be a process in place to correct the information. If a person wants their information removed from our system, there needs a process in place to remove the information without corrupting or compromising the integrity of the system. Reference the Right to be forgotten and Article 17 of GDPR.
The right to lodge a complaint with a supervisory authority. We need to get into the definition of “Supervisory Authority.” I’ll address that in a later post. What kind of complaints can we expect? And what is the process to resolve those complaints? We need to spend time developing the process to address data complaints.
Where the personal data are not collected from the data subject, any available information as to their source. We feed downstream systems, and to be honest, there are a number of companies that sell our personal data. Say your company purchases mailing and phone list from a series of companies, you are now going to need to track the source of that data, so when the question arises, and it will; you can answer the question. What was the source of the data?
More to come.
In Oracle 12.1 the ACCESSIBLE BY clause was introduced to the PL/SQL language. This gives the developer the ability mark a package, procedure, function, or type with what was allowed to call it. 12.2 gives us fine grained control over what can the specific functions and procedures in a package.
Here is what 12.1 gave us. As you can see in this example the package getEmpInfo and EmpMaint can both call the package emp_api. I love it, now we have a way to limit what can call a piece of code. But wait, in 12.2 it gets even better, look at example for 12.2
1 CREATE OR REPLACE PACKAGE emp_api 2 ACCESSIBLE BY (getEmpInfo, EmpMaint) 3 AUTHID CURRENT_USER AS 4 FUNCTION fGetEmpPhone( pFname IN VARCHAR2, 5 pLname IN VARCHAR2) 6 RETURN VARCHAR2; 7 8 FUNCTION fGetEmpManager(pEmployeeId IN NUMBER) RETURN NUMBER; 9 10 FUNCTION fInsEmp(pFirstName IN VARCHAR2, 11 pLastName IN VARCHAR2, 12 pEmail IN VARCHAR2, 13 pPhoneNumber IN VARCHAR2, 14 pHireDate IN DATE, 15 pJobId IN NUMBER, 16 pSalary IN NUMBER, 17 pCommissionPct IN NUMBER, 18 pManagerId IN NUMBER, 19 pDempartmentId IN NUMBER) 20 RETURN BOOLEAN; 21 22 FUNCTION fDelEmp(pEmployeeId IN NUMBER) 23 RETURN BOOLEAN; 24 25 FUNCTION fUpdateEmp(pEmployeeId IN NUMBER, 26 pFirstName IN VARCHAR2, 27 pLastName IN VARCHAR2, 28 pEmail IN VARCHAR2, 29 pPhoneNumber IN VARCHAR2, 30 pHireDate IN DATE, 31 pJobId IN NUMBER, 32 pSalary IN NUMBER, 33 pCommissionPct IN NUMBER, 34 pManagerId IN NUMBER, 35 pDempartmentId IN NUMBER) 36 RETURN BOOLEAN; 37 38 END;
In 12.2 we now have fine grained control over what can call the individual functions and procedures in our package. In the emp_api package the package getEmpInfo can call the functions fGetEmpPhone and fGetEmpManager. The package EmpMaint can call the functions, fDelEmp, fInsEmp, and fUpdateEmp. Now we have fine grained control over what can call the functions and procedures in a specific package.
1 create or replace PACKAGE emp_api 2 AUTHID CURRENT_USER 3 AS 4 FUNCTION fGetEmpPhone(pFname IN VARCHAR2, 5 pLname IN VARCHAR2) 6 RETURN VARCHAR2 ACCESSIBLE BY (PACKAGE getEmpInfo); 7 8 FUNCTION fGetEmpManager(pEmployeeId IN NUMBER) 9 RETURN NUMBER ACCESSIBLE BY (PACKAGE getEmpInfo); 10 11 FUNCTION fInsEmp(pFirstName IN VARCHAR2, 12 pLastName IN VARCHAR2, 13 pEmail IN VARCHAR2, 14 pPhoneNumber IN VARCHAR2, 15 pHireDate IN DATE, 16 pJobId IN NUMBER, 17 pSalary IN NUMBER, 18 pCommissionPct IN NUMBER, 19 pManagerId IN NUMBER, 20 pDempartmentId IN NUMBER) 21 RETURN BOOLEAN ACCESSIBLE BY (PACKAGE EmpMaint); 22 23 FUNCTION fDelEmp(pEmployeeId IN NUMBER) 24 RETURN BOOLEAN ACCESSIBLE BY (PACKAGE EmpMaint); 25 26 FUNCTION fUpdateEmp(pEmployeeId IN NUMBER, 27 pFirstName IN VARCHAR2, 28 pLastName IN VARCHAR2, 29 pEmail IN VARCHAR2, 30 pPhoneNumber IN VARCHAR2, 31 pHireDate IN DATE, 32 pJobId IN NUMBER, 33 pSalary IN NUMBER, 34 pCommissionPct IN NUMBER, 35 pManagerId IN NUMBER, 36 pDempartmentId IN NUMBER) 37 RETURN BOOLEAN ACCESSIBLE BY (PACKAGE EmpMaint); 38 39 END;