I’m more than a little disappointed at people not being serious about information security. One of my customers asked me to help load data from a school system into an apex application I designed for some years back. The excel spreadsheet received from the school system has teacher names and social security numbers. The excel file was not encrypted in any way shape or form. I’ll be contacting the school system on Monday to resolve this issue. If it’s not resolved in a short period of time, I’ll be sending the data to their local news paper. This has got to stop.
Chatting with a friend this morning, we were talking about todo list and being overwhelmed by everything that needs to get done. After sharing with her mindfulness meditation, that helps me keep the “chattering monkeys” at bay and has improved my focus. I mentioned that I’ve gotten away from todo list and now write out the outcome I want from today. Coming up with an outcome for the day can be quite difficult; but once you get the hang of it; the outcome for today will be self evident.
Back on 3/1 I needed to bring this practice back into focus and wrote this note to myself.
In my outcome for the day, I also establish a performance goal for the day. This performance goal can be related to my physical performance while exercising and/or my performance around people. An example of this would be even though my chair is very comfortable, I will do twenty minutes on the rowing machine and life weight for thirty minutes. It will also include what distance will I do in those twenty minutes, and what weights / sets will I do in my exercise.
My professional outcomes for the day, may be; Catch the problem in production, before the customer catches it. This way, the customer is not surprised when there is an issue. They get an FYI instead of, them sending my team an email saying there is a problem.
The beauty of this is, when you’ve defined the outcome, what you need to do to get that outcome should become self-evident.
Just a short post, if you design, develop, maintain, or administer applications, you need to read this document. The Ten Most Critical Web Application Security Risks.
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>
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;
I have been seeing this database architecture for over thirty years and it’s high time we stopped using it. Before I go too far, let me tell you I get it, you have pressure to get the application out the door and working in a defined timeframe. I still design and develop systems and the pressure to take shortcuts can be great. This short cut is a security killer.
So what have we been doing wrong for all these decades? Put all of the database objects and application code into once schema. This is just a bad idea all around. All it takes is one security bug and the bad guy owns your database. You might as well, put pretty gold wrapping paper with a bow around it and write the bad guy a gift card. If you come to any of my talks, I’ll be happy to demonstrate owning a database, including all your source code and all your data in just a couple of easy commands. But because this is not intended on being a lesson in hacking a database, I wont go into it here.
There is an architecture, that will drastically improve the security of your database. By segmenting your application code from your data and use an API to access the data. Oracle 12c has several PL/SQL enhancements that will make your code much more secure. Oracle 12c PL/SQL now allows you to assign roles to packages, procedures and functions (But you should only be using packages). PL/SQL also now allows you to white list what can execute code. For years, we granted execute to a user, but now you can define what PL/SQL package can call another PL/SQL package using the accessible by clause. We are going to leverage these new features along with authid to define a trusted path that is controllable, fast and secure.
My next several post will move through this architecture, and explaining how to implement it effectively.
My 2017 speaking schedule is starting out with a bang.
My first stop will be in Moscow Russia where I am trying to arrange a short speaking engagement in conjunction with the Russia Oracle Users Group. Hopefully we can arrange something. I’ll be there on Tuesday January 24th and departing on Wednesday January 25th. If we can’t get a speaking engagement put together, feel free to drop me an email email@example.com and we can meet for dinner / drinks the evening of the 24th. Please put “European Security Tour Moscow” on the subject line so your email does not get buried under the other 500+ emails I get every day.
On Wednesday evening January 25th, I will be in London to meetup with the folks at UKOUG. If you like to join us that evening, contact Martin Widlake his twitter handle is @MDWidlake. We will be drinking beer and discussing many aspects of Oracle, including how to integrate beer into your Oracle Presentation, Martin is an expert on that.
On Thursday January 26th, I will be in Paris for the French Oracle Users Group meetup. I will be presenting a combination of Holistic Database Security and Secure PL/SQL coding practices. Once I have a URL and details on time / location I will update the post.
From Paris, I will be off to Helsinki Finland to present Holistic Database Security on 30 January with Technopolis and Oracle Users Group Finland. This is a free event. The url is
From Helsinki I’m heading north to Rovaniemi and doing a private event for Finland National Bureau of Investigation. Then it’s back south to Helsinki for another engagement for a database security conference on February 2nd. I do not have the URL for the database security conference yet. As soon as I have it, I will update this post.
Now if that were not enough, once I get back to Baltimore, I will have about enough time to do a load of laundry then will be heading to Denver for RMOUG where I will be presenting Holistic Database Security and PL/SQL Secure Coding Practices.