Code Based Access Control, #securecoding #oracle #plsql #MultipleSchemas

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.

Secure Coding Part 1

Secure Coding Part 2

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

#Oracle #Privilege analysis #QuickTip

Here is a quick tip on Oracle privilege analysis. Frequently I want to find out all of the ways a user can get to an object for any privilege. DBA_TAB_PRIVS and DBA_ROLE_PRIVS are the two views I go to. I want to also see all the privileges that are granted on any object. This is good for starting at the user tracking privileges to the object, it’s also good for starting at an object and walking back to the user.

This query does a pivot on the users and roles to get the path to the object and what privileges are associated with that path.


SELECT OWNER,
TYPE,
TABLE_NAME,
GRANTEE_FROM,
GRANTEE_TO,
"'SELECT'" SEL,
"'UPDATE'" UPD,
"'INSERT'" INS,
"'DELETE'" DEL,
"'EXECUTE'" EXE,
"'FLASHBACK'" FLSH,
"'ON COMMIT REFRESH'" OCR,
"'ALTER'" ALTR,
"'DEQUEUE'" DEQ,
"'INHERIT PRIVILEGES'" IPRV,
"'DEBUG'" DBG,
"'QUERY REWRITE'" QR,
"'USE'" US,
"'READ'" RD,
"'WRITE'" WT,
"'INDEX'" IDX,
"'REFERENCES'" REF
FROM
(SELECT R.GRANTEE "GRANTEE_TO",
T.GRANTEE GRANTEE_FROM,
T.GRANTABLE,
T.owner,
T.table_name,
T.TYPE,
T.PRIVILEGE
FROM DBA_TAB_PRIVS T,
DBA_ROLE_PRIVS R
WHERE T.GRANTEE = R.GRANTED_ROLE (+)
AND t.grantee != 'SYS'
AND t.grantee != 'SYSTEM'
AND R.GRANTEE != 'SYS'
AND R.GRANTEE != 'SYSTEM'
)
PIVOT (COUNT(PRIVILEGE)
FOR PRIVILEGE IN ('SELECT',
'UPDATE',
'INSERT',
'DELETE',
'EXECUTE',
'FLASHBACK',
'ON COMMIT REFRESH',
'ALTER',
'DEQUEUE',
'INHERIT PRIVILEGES',
'DEBUG',
'QUERY REWRITE',
'USE',
'READ',
'WRITE',
'INDEX',
'REFERENCES'))
ORDER BY TABLE_NAME;

@Oracle 12.2.0.1 Cool new features to improve security. Part 1 Enhanced Whitelists PL/SQL

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;

Reference: http://docs.oracle.com/database/122/LNPLS/ACCESSIBLE-BY-clause.htm#LNPLS-GUID-9720619C-9862-4123-96E7-3E85F240FF36

PL/SQL Security Coding Practices. Introduction to a better architecture part 2

For this post, we are going to focus on definers rights and invokers rights. Most developers already know about these privilege modifiers, but sadly I rarely see these being used at customer sites.

Key to understanding how to secure your code is understanding definers and invokers along with inherit privileges, the accessible by clause along with a few other things. We are going to use these privilege modifiers to help implement a trusted path to your data. Here is the Oracle documentation on Definers Rights and Invokers Rights. Managing Security for Definer’s Rights and Invoker’s Rights.

Definers rights and invokers rights are pretty easy to understand. Lets start with a simple example. If a package, procedure or function (You should always use packages) is created using definers rights, then the code will execute with the privileges that are giving to the owner of the package. If the package is created with invokers rights then the package will execute with the privileges of the invoker (user who executed the code).

We are going to create an application user usr1 and an application code schema app.  Once we have done that we will grant select on hr.employees to the app user.

1 SQL> create user usr1 identified by usr1; 2 User USR1 created. 3 SQL> grant create session to usr1; 4 Grant succeeded. 5 SQL> create user app identified by app; 6 User APP created. 7 SQL> grant select on hr.employees to app; 8 Grant succeeded.

Lets create a package with a function that does one simple thing; return the number of employees in the hr.employees table that have a salary that is greater than or equal to the passed parameter. Once we have this package we are going to grant execute on the package to the user usr1.

1 create or replace package app.emp_api 2 authid definer -- this is the default, but it's nice 3 -- to be specific 4 as 5 function fEmpCount(pAmt IN NUMBER) return number; 6 end; 7 / 8 9 create or replace package body app.emp_api as 10 function fEmpCount(pAmt IN NUMBER) return number is 11 x number; -- just a dumb variable to hold the count. 12 begin 13 select count(*) 14 into x 15 from hr.employees 16 where salary >= pAmt; 17 return x; 18 exception when others then 19 sys.dbms_output.put_line(sqlerrm); 20 return -1; 21 end; 22 end; 23 / 24 Package Body EMP_API compiled 25 SQL> grant execute on app.emp_api to usr1; 26 Grant succeeded. 27

Now, when usr1 executes the app.emp_api.fEmpCount it will work fine. We don’t need to grant permissions to usr1 to access the employees table, because the package executes with the permissions of the user app, that does have select on hr.employees. Using this scheme, we have effectively locked the user into using the application.

1 SQL> conn usr1/usr1@demo1 2 Connected. 3 SQL> set serveroutput on 4 SQL> declare 5 x number; 6 begin 7 x := app.emp_api.fEmpCount(pAmt => 100); 8 sys.dbms_output.put_line(to_char(x)); 9 end; 10 / 11 107 12 PL/SQL procedure successfully completed. 13

So that works just fine, now lets try it when we use invokers rights, we get table or view does not exists.

1 SQL> conn rlockard@demo1 2 Password? (**********?) *********** 3 Connected. 4 SQL> create or replace package app.emp_api 5 authid current_user -- all we need to do is change the 6 -- package spec 7 8 as 9 function fEmpCount(pAmt IN NUMBER) return number; 10 end; 11 / 12 Package EMP_API compiled 13 SQL> conn usr1/usr1@demo1 14 Connected. 15 SQL> set serveroutput on 16 SQL> declare 17 2 x number; 18 3 begin 19 4 x := app.emp_api.fEmpCount(pAmt => 100); 20 5 sys.dbms_output.put_line(to_char(x)); 21 6 end; 22 7 / 23 ORA-00942: table or view does not exist 24 -1 25

Because the code executed with the same rights as the invoker (usr1) we get the ora-00942: table or view does not exists. For this to work we need to grant select on hr.employees to usr1.

1 SQL> grant select on hr.employees to usr1; 2 3 Grant succeeded. 4 5 SQL> conn usr1/usr1@demo1 6 Connected. 7 SQL> set serveroutput on 8 SQL> declare 9 x number; 10 begin 11 x := app.emp_api.fEmpCount(pAmt => 100); 12 sys.dbms_output.put_line(to_char(x)); 13 end; 14 / 15 107 16 17 18 PL/SQL procedure successfully completed. 19

Now we have that down stay tuned for the next post on inherit privileges, because when usr1 has powerful privileges, when we use invokers rights the inherits those powerful privileges.

PL/SQL Security Coding Practices. Introduction to a better architecture part 1.

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.

The power and security configuration of using an API (1)

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.

The power and security configuration of using an API

#ORACLE PL/SQL Secure Coding Practices #INFOSEC – Please tell me how your database system is designed @bgoug will get this presentation first

The more you tell me, the more ways I can find I can find to attack your system. All I need is one little sql injection bug and trust me, it is most likely there, you just don’t know it yet.

execute process_row(’EMPLOYEES where 1=2 union select owner, name, text from all_source order by owner, name, line –’);

Problem #1 for you, Opportunity #1 for bad guys. Guess what, all of your source code just leaked out from your database.

Problem #2 for you, Opportunity #2 for bad guys. Not putting your your code into packages. If you put your pl/sql into a procedure or a function, I can extract your code from all_source, learn about your system and tailor my attack.

What do you need to do? Put your code into packages. If the code is in a package, the only thing I can get is the package specification.

Problem #2 for you, Opportunity #2 for bad guys. Comments in your package specification. Hey, I’ve been <humor> smacking junior developers with a boat paddle </humor> for years about not commenting their code. The good part is they eventually get it and put in comments. The bad part is comments are being put into the package specification. Some comments are quite verbose and <humor> I really like that</humor>. You are telling the bad guys everything they need to know to exploit your system.

What you need to do? Move all of your comments to the top of the package body and use inline comments in the package body. Again, when I extract your source code, if it’s in a package then I can only get the package specification.

Here is a sample of one of my packages specifications. You are not going to derive too much information from this except maybe what calls it.

CREATE OR REPLACE PACKAGE TARGETAPP.REPORTTARGET

AUTHID current_user 

ACCESSIBLE BY (TARGETAPP.PROCESSTARGET)

AS

-- constant declarations

sVersion CONSTANT VARCHAR2(10) := '20161026.1';

FUNCTION MAIN(arg1 IN VARCHAR2, arg2 IN NUMBER) RETURN NUMBER;

FUNCTION WHAT_VERSION RETURN VARCHAR2;

END;

When I put on my penetration testing hat, all of your source code and comments make my job much easier. I learn exactly how your system is designed and coded and that lets me find all kinds of ways to exploit your system. <humor>Please don’t make my pen testing work too easy, customers will start thinking they are paying me too much money.</humor> And please for goodness sake, make the bad guys life harder; because if you do, they will likely move on to an easier target.

Turn off the #http #listener in #Oracle #STIG

Locking down a database (applying STIGs) you need to check to see if the listener is running http. If you don’t need the http service, turn it off. Turning off http will reduce the attack surface.

Step 1) Is http running?
[oracle@vbgeneric db_1]$ lsnrctl stat | grep HTTP
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vbgeneric)(PORT=8081))(Presentation=HTTP)(Session=RAW))
[oracle@vbgeneric db_1]$

Step 2) Turn off http
RLOCKARD@orcl> select version from v$instance;
VERSION
-----------------
12.1.0.2.0

RLOCKARD@orcl12c> sho parameter dispatchers

NAME TYPE VALUE
———————————— ———– ——————————
dispatchers string (PROTOCOL=TCP)
max_dispatchers integer

RLOCKARD@orcl12c> exec dbms_xdb.sethttpport(0);
PL/SQL procedure successfully completed.

RLOCKARD@orcl12c> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SYS@orcl12c> sho parameter dispatchers

NAME TYPE VALUE
———————————— ———– ——————————
dispatchers string (PROTOCOL=TCP)
max_dispatchers integer
SYS@orcl12c>

[oracle@vbgeneric db_1]$ lsnrctl stop

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 15-SEP-2016 09:25:29

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
The command completed successfully
[oracle@vbgeneric db_1]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 15-SEP-2016 09:25:34

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Starting /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait…

TNSLSNR for Linux: Version 12.1.0.2.0 – Production
System parameter file is /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/vbgeneric/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
Notice it’s gone
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 – Production
Start Date 15-SEP-2016 09:25:34
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service orcl12c
Listener Parameter File /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/vbgeneric/listener/alert/log.xml
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
Services Summary…
Service “orcl12c” has 1 instance(s).
Instance “orcl12c”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully

That was easy.

Four things a developer can do now to improve their applications #infosec posture.

Lets face it, we have deadlines to meet and millions of lines of code in production. I get it, I’ve been a working PL/SQL developer off and on for over 20 years. If we get into the habit of using some of the security features in the language along with some practices, we can improve the security of you code. So, lets get into it.

1) Use packages. Steve Feuerstein http://www.oracle.com/technetwork/issue-archive/index-087690.html has been saying for years to move those functions and procedures to packages and there is good security reasons to do that. If you have a SQL Injection bug in your application, I can get to ALL_SOURCE and read your code and if I can get to your code, I can find other exploits.

So we can read the code in Functions and Procedures.

1 SQL> select text from all_source where owner = user and name = 'PARSE_STRING'; 2 procedure parse_string(p_string varchar2) AS 3 CURSOR col_cur IS 4 select distinct (instr(p_string||',',',',1,level)) loc 5 FROM dual 6 CONNECT BY LEVEL <= LENGTH(REPLACE(p_string,','))+1 7 ORDER BY 1; 8 l_col varchar2(65); 9 BEGIN 10 for col_rec in col_cur 11 loop 12 l_col := substr(p_string, col_rec.loc, instr(p_string, col_rec.loc+1)); 13 dbms_output.put_line(l_col || 'col pos ' || col_rec.loc); 14 end loop; 15 end; 16 17 14 rows selected. 18 19 SQL> create user u2 identified by MY##56SecurePassword; 20 21 User created. 22 23 SQL> grant create session to u2; 24 25 Grant succeeded. 26 27 SQL> grant execute on parse_string to u2; 28 29 Grant succeeded. 30 31 SQL> conn u2@orcl 32 Enter password: 33 Connected. 34 SQL> select text from all_source where owner = 'RLOCKARD' 35 2 and name = 'PARSE_STRING'; 36 procedure parse_string(p_string varchar2) AS 37 CURSOR col_cur IS 38 select distinct (instr(p_string||',',',',1,level)) loc 39 FROM dual 40 CONNECT BY LEVEL <= LENGTH(REPLACE(p_string,','))+1 41 ORDER BY 1; 42 l_col varchar2(65); 43 BEGIN 44 for col_rec in col_cur 45 loop 46 l_col := substr(p_string, col_rec.loc, instr(p_string, col_rec.loc+1)); 47 dbms_output.put_line(l_col || 'col pos ' || col_rec.loc); 48 end loop; 49 end; 50 51 14 rows selected. 52 53 SQL> 54

Now when we put this into a package, the only thing I can extract from it is the package specification.

First lets put it into a package.

1 SQL> sho user 2 USER is "RLOCKARD" 3 SQL> create or replace package rlockard.utility AS 4 procedure parse_string(p_string varchar2); 5 end; 6 / 7 8 Package created. 9 10 SQL> create or replace package body rlockard.utility AS 11 12 procedure parse_string(p_string varchar2) IS 13 CURSOR col_cur IS 14 select distinct (instr(p_string||',',',',1,level)) loc 15 FROM dual 16 CONNECT BY LEVEL <= LENGTH(REPLACE(p_string,','))+1 17 ORDER BY 1; 18 l_col varchar2(65); 19 BEGIN 20 for col_rec in col_cur 21 loop 22 l_col := substr(p_string, col_rec.loc, instr(p_string, col_rec.loc+1)); 23 sys.dbms_output.put_line(l_col || 'col pos ' || col_rec.loc); 24 end loop; 25 end; 26 end; 27 / 28 29 Package body created. 30 31 SQL>

Now, lets test to see what we can see.

1 SQL> grant execute on utility to u2; 2 3 Grant succeeded. 4 5 SQL> conn u2@orcl 6 Enter password: 7 Connected. 8 SQL> select text from all_source where owner = 'RLOCKARD' 9 and name = 'UTILITY'; 10 2 11 TEXT 12 -------------------------------------------------------------------------------- 13 package utility AS 14 procedure parse_string(p_string varchar2); 15 end; 16

As we can see, now you only get the package specification. This is really more that I would like to get out, but it’s much better than getting all your source code.

2) Split up your packages into smaller packages based on function. I normally split them up by UTILITY, SENSITIVE and NON SENSITIVE. If there are functions / procedures against sensitive tables those will go into the sensitive packages. You can further break down you sensitive packages. ie: CUSTOMER_API_PKG that would be your interface into your customers table.

Here is a good post by Steve Feuerstein on breaking packages down and keeping them compatible with existing code. http://www.oracle.com/technetwork/issue-archive/2015/15-jan/o15plsql-2398996.html

3) Limit the execution rights to a package and what a user can do with a package.

3a) We have been granting execute to packages for decades now. Then Oracle 11g gave us Invoker and Definer rights. When you create a package and don’t specify invoker or definer rights, the package is created with definer rights as the default. That’s all well and good, but let’s think this through. If I execute a package with definer rights and that package updates the customers table, even thought I don’t have update on the customers table, the package will work.

1 SQL> create or replace package rlockard.cust_api AS 2 function update_customers_credit_limit(pID in number, pCredit in number) return number; 3 end; 4 / 5 Package created. 6 7 SQL> create or replace package body rlockard.cust_api AS 8 9 function update_customers_credit_limit(pID in number, pCredit in number) return number is 10 retVal number; 11 begin 12 update customers set credit = pCredit where id = pId; 13 return 1; 14 exception when no_data_found 15 then 16 retVal := helpdesk.utility.log_error(pPkg => $$PLSQL_UNIT, pLine => $$PLSQL_LINE, 17 pParm => 'pID = ' || to_char(iID) || 18 ' pAmount= ' || to_char(pCredit), 19 pErr => sqlcode); 20 return retVal * -1; -- we are flipping the sign to indacate it's an error to caller. 21 end; 22 end; 23 / 24 Package body created. 25 SQL> 26

I am going to grant execute to the user U2 that we create earlier and test this.

1 SQL> grant execute on rlockard.cust_api to u2; 2 3 Grant succeeded. 4 5 SQL> conn u2@orcl 6 Enter password: 7 Connected. 8 SQL> declare 9 2 x number; 10 3 begin 11 4 x:=rlockard.cust_api.update_customers_credit_limit(pId => 1770, pCredit => 1000000); 12 5 end; 13 6 / 14 15 PL/SQL procedure successfully completed. 16 17 SQL> select credit from rlockard.customers where id = 1770; 18 select credit from rlockard.customers where id = 1770 19 * 20 ERROR at line 1: 21 ORA-00942: table or view does not exist 22 23 24 SQL> conn rlockard@orcl 25 Enter password: 26 Connected. 27 SQL> select credit from rlockard.customers where id = 1770; 28 29 CREDIT 30 ---------- 31 1000000 32 33 SQL> 34

Did you expect that to happen?  How are we going to tighten this down. We are going to set the package to use invokers rights. By adding AUTHID CURRENT_USER to the package specification, the package executes with U2’s rights. U2 does not have any rights on the customers table, the package fails with ORA-00942: table or view does not exists.

1 SQL> create or replace package rlockard.cust_api 2 AUTHID CURRENT_USER 3 AS 4 function update_customers_credit_limit(pID in number, pCredit in number) return number; 5 end; 6 / 7 2 3 4 5 6 8 Package created. 9 10 11 SQL> conn u2@orcl 12 Enter password: 13 Connected. 14 SQL> declare 15 x number; 16 begin 17 x := rlockard.cust_api.update_customers_credit_limit(pId => 1770, pCredit => 2500000); 18 exception when others then 19 sys.dbms_output.put_line(sqlerrm); 20 end; 21 / 22 ORA-00942: table or view does not exist 23 24 SQL> conn rlockard@orcl 25 Enter password: 26 Connected. 27 SQL> select credit from customers where id = 1770; 28 29 CREDIT 30 ---------- 31 1000000 32 SQL> 33

3b) In Oracle 12c we were given the ability to grant roles to packages. (procedures and functions too, but you should be using packages) Now, when we have sensitive tables in another schema, we can create a role that a package needs and grant that role to a package.

1 CREATE ROLE update_customers; 2 3 grant update_customers to rlockard; 4 5 GRANT SELECT 6 ON customers 7 TO update_customers; 8 9 GRANT update_customers TO PACKAGE cust_api; 10 11 declare 12 x number; 13 begin 14 x := rlockard.cust_api.update_customers_credit_limit(pId => 1770, pCredit => 2500000); 15 exception when others then 16 sys.dbms_output.put_line(sqlerrm); 17 end; 18 /

3c) Oracle 12c also gave us the accessible by clause. This creates a white list of the packages that can call a package. This way you are narrowing down the ways a package can get called, creating a trusted path to your secure data. So here the public package can call the private package, but if anything else tries to call it a PLS-00904 error will be raised.

accessable_by

1 SQL> create or replace package public_package AS 2 procedure update_customers(pId in number, 3 pColumn in varchar2, 4 pValue in varchar2); 5 end; 6 / 7 8 Package created. 9 10 SQL> create or replace package body public_package as 11 procedure update_customers(pId in number, 12 pColumn in varchar2, 13 pValue in varchar2) IS 14 x number; -- we know it's a function that returs a number. 15 begin -- this is simplistic to demo accessable_by 16 if pColumn = 'CREDIT' then 17 x := rlockard.cust_api.update_customers_credit_limit(pId => pId, pCredit => pValue); 18 end if; 19 end; 20 end; 21 / 22 23 Package body created. 24 25 SQL> create or replace package rlockard.cust_api 26 accessible by (public_package) AS 27 function update_customers_credit_limit(pID in number, pCredit in number) return number; 28 end; 29 / 30 31 Package created. 32 33 SQL> 34 35 SQL> declare 36 x number; 37 begin 38 x := rlockard.cust_api.update_customers_credit_limit(pId => 1770, pCredit => 2500000); 39 exception when others then 40 sys.dbms_output.put_line(sqlerrm); 41 end; 42 / 43 x := rlockard.cust_api.update_customers_credit_limit(pId => 1770, pCredit => 2500000); 44 * 45 ERROR at line 4: 46 ORA-06550: line 4, column 8: 47 PLS-00904: insufficient privilege to access object CUST_API 48 ORA-06550: line 4, column 3: 49 PL/SQL: Statement ignored 50 51 52 SQL> begin 53 public_package.update_customers(pId => 1771, pColumn => 'CREDIT', pValue => '200'); 54 end; 55 / 56 57 PL/SQL procedure successfully completed. 58 59 SQL> 60

But when we call it from the package in the accessible by clause, then it works fine. Again, we are limiting the paths to get to the sensitive information.

4a) We are getting down to the meat of what every shop should be doing. Reviewing code. You should be looking for dynamic code that is concatenating variables together. This is a painfully bad piece of code with a major SQL Injection bug.

1 create or replace PROCEDURE UserLogin 2 (p_email logins.email%type DEFAULT NULL, 3 p_password logins.password%type DEFAULT NULL) 4 AS 5 6 STMT CONSTANT VARCHAR2(4000) := 7 'SELECT email 8 FROM logins 9 WHERE email = ''' || p_email || 10 ''' AND password = ''' || p_password || ''''; 11 12 l_result logins.email%type; 13 BEGIN 14 15 dbms_output.put_line ('SQL STMT: ' || STMT); 16 17 EXECUTE IMMEDIATE STMT INTO l_result; 18 19 dbms_output.put_line ('Logon succeeded.'); 20 21 EXCEPTION WHEN OTHERS THEN 22 null; -- OH NO HE DID NOT 23 END UserLogin;

How would I fix this. Well, lets’ change the dynamic SQL and put in some bind variables. We can still do a lot more with this code, but this fixes the SQL Injection bug and a couple other issues.

1 create or replace function UserLogin2 2 (p_email logins.email%TYPE DEFAULT NULL, 3 p_password logins.PASSWORD%TYPE DEFAULT NULL) 4 RETURN NUMBER AS 5 kount number; -- a dumb variable to hold a count 6 BEGIN 7 8 SELECT count(*) 9 INTO kount 10 FROM logins 11 WHERE email = p_email 12 and password = p_password; 13 14 IF kount = 1 THEN 15 sys.dbms_output.put_line ('Logon succeeded.'); 16 RETURN kount; 17 ELSE 18 return -1; 19 end if; 20 21 END UserLogin2;

Now you will find I love code reviews. Frequently we learn a way to do something and because it works, we continue doing it. Heck, I loved cursor for loops until I learned better in a code review. Code reviews should be approached as learning opportunities. You are going to learn a lot more tricks reading other peoples code and you may catch something that will improve the security of your system.

So in review the steps you can do now to improve the security posture of your applications are: Control the rights to executing code. Put everything in packages. Split up your packages. Do code reviews.

Four things a DBA can do now to improve their #infosec posture?

1) When we start talking about securing information, the first thing that always seems to come up is encryption. Everyone has heard about it, but some don’t really understand just what encryption is protecting. When we are discussing Transparent Data Encryption (TDE) we are discussing data at rest. The attack vectors we are protecting from is a bad actor gaining access to the physical hardware.

1a) Now, the easiest and fastest way to implement TDE is to encrypt tablespaces and move the sensitive data into the encrypted tablespace. You need to be careful here, just because you identified the tables that are sensitive, what about objects that are dependent on the table?  (Indexes, Materialized Views, etc).  Each of these sensitive objects need to be moved into encrypted tablespaces.

Find dependent objects.

1 set pagesize 1000 2 set linesize 132 3 col owner format a30 4 col name format a30 5 SELECT d.owner, 6 d.NAME, 7 s.tablespace_name, 8 t.ENCRYPTED 9 FROM dba_dependencies d, 10 dba_segments s, 11 dba_tablespaces t 12 WHERE d.owner = s.owner 13 AND d.NAME = s.segment_name 14 and s.tablespace_name = t.tablespace_name 15 and referenced_name IN ( 16 SELECT segment_name 17 FROM dba_segments 18 WHERE tablespace_name IN 19 (SELECT tablespace_name 20 FROM dba_tablespaces 21 WHERE tablespace_name = upper('&&tbs'))) 22 UNION 23 SELECT i.owner, 24 i.index_name, 25 i.tablespace_name, 26 dd.ENCRYPTED 27 FROM dba_indexes i, 28 dba_tablespaces dd 29 WHERE i.tablespace_name = dd.tablespace_name 30 AND table_name IN ( 31 SELECT segment_name 32 FROM dba_segments 33 WHERE tablespace_name IN 34 (SELECT tablespace_name 35 FROM dba_tablespaces 36 WHERE tablespace_name = upper('&&tbs')));

You can not change an unencrypted tablespace into an encrypted tablespace, so you are going to need to first create the encrypted tablespace.

1 CREATE TABLESPACE sensitive_dat 2 DATAFILE '/opt/oracle/oradata/DEV/datafile/sensitive_data01.dbf' size 1024M 3 ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT); 4 5 CREATE TABLESPACE sensitive_idx 6 DATAFILE '/opt/oracle/oradata/DEV/datafile/sensitive_idx01.dbf' size 1024M 7 ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT); 8

Now that we have an encrypted tablespace, we need to start moving all the sensitive data into it. It’s important to know, that to prevent ghost data you we are going to need to move everything out of the tablespace and into a new tablespace. I normally use alter table move, but you can also use dbms_redefination and create table as select.  Use the report from dependent objects to make sure you have everything out of the tablespace.  Once you have everything out, drop the tablespace then use a utility like shred to over right the data file(s) with random data. Once you have done that, you can safely delete the data file(s).

Here is a link to my demo on moving data to an encrypted tablespace. This demo assumes the base table is already in the encrypted tablespace, now we need to move indexes and materialized views.

https://www.youtube.com/watch?v=pZrdCZ09uiA

TDE also offers column encryption, the analysis required to properly implement column based encrypted is time consuming. So for now we are going to pass over column encryption.

1b) SQLNet encryption. Information that moves through the network is subject to various attacks including man in the middle, replay and modification attacks. With these data can be leaked, corrupted, or even replayed. So we use sqlnet encryption and integrity to protect our data from leaking, replays and modification. You are going to user net manager to setup. Make an encryption or integrity method either Accepted, Requested, Rejected or Required. You can read more on these in the Oracle Documentation.

https://docs.oracle.com/database/121/DBSEG/asoconfg.htm#DBSEG020

$ORACLE_HOME/bin/netmgr Open local –> profile then select network security and click on the encryption tab. Select the encryption algorithms you need and then enter 256 characters in the encryption seed block.

Oracle DB Developer VM_1

Select an integrity method. Remember MD5 has several weaknesses. SHA has become the defacto standard.

Oracle DB Developer VM

2) Audit your users and environment. I’ve have heard this one time and time again, “It’s not my job. It’s the auditors job.” The fact remains many breaches exists for weeks, months and even years before they are discovered. Than when a breach is discovered, the auditor request audit logs. We need to do better!. I get audit reports every morning and review them before I do anything else. So what do you want to audit?

2a) Audit login failures. Login failures can be a sign someone is trying to gain access to your system. If you start seeing login failures, investigate. Is the issue user training or is there something else going on.

2b) Audit logins from yesterday. Why you are going to be looking at os_username / username / userhost to see if people are logging in from multiple workstations. This could be an indicator of a username/password being shared. Another reason I do this audit is to check os_username / username. Is the user using their proper account. I have issues in the past where a user was using the application login to do their normal work. This audit showed this and allowed us to correct the situation.

2c) Audit logins for the past 31 days. This gives you a 30,000 foot picture on how often users are connecting and are then disconnecting at the end of the day.

1 set heading off 2 set pagesize 1000 3 set linesize 132 4 set serveroutput on 5 col object_name format a24 6 col object_type format a24 7 col doctype format a10 8 col userhost format a40 9 col os_username format a15 10 col username format a15 11 col terminal format a15 12 13 spool $HOME/session_audit.txt 14 15 select 'login failures' from dual; 16 17 select os_username, username, userhost, terminal, to_char(timestamp, 'dd-mon-rr hh24:mi') 18 from dba_audit_session 19 where returncode != 0 20 and trunc(timestamp) >= trunc(sysdate-1) 21 and username != 'DUMMY' 22 order by timestamp 23 / 24 25 select 'logins yesterday' from dual; 26 27 select os_username, username, userhost, count(*) 28 from dba_audit_session 29 where trunc(TIMESTAMP) >= trunc(sysdate-1) 30 and username != 'DUMMY' 31 and action_name != 'LOGOFF BY CLEANUP' 32 group by os_username, username, userhost 33 order by os_username, username 34 / 35 36 select 'logins last 31 days' from dual; 37 38 select os_username, username, userhost, count(*) 39 from dba_audit_session 40 where trunc(TIMESTAMP) >= trunc(sysdate-31) 41 and username != 'DUMMY' 42 and action_name != 'LOGOFF BY CLEANUP' 43 group by os_username, username, userhost 44 order by os_username, username 45 / 46

2d) Audit changes to any database objects. This is a simple query you can start with. You are checking objects based on last_ddl_time and created.

1 select 'changed / created objects last 24 hours' from dual; 2 3 select owner, object_type, object_name 4 from dba_objects 5 where (trunc(created) >= trunc(sysdate-1) 6 or trunc(last_ddl_time) >= trunc(sysdate-1)) 7 order by owner, object_type, object_name; 8

2e) Use a product like tripwire to check for any changes to ORACLE_HOME. You can also roll your own but getting a checksum of all the files in ORACLE_HOME, than doing the check everyday to see if a file has changed. (there are some files you will want to filter because they change in the normal course of operations)

3) Identify the sensitive data in your database. How can you know what to protect if you don’t know what is sensitive? When you identify what is sensitive, it’s easier to track that data through your enterprise to limit access to the data.

Now there is something most people know but don’t realize they know. You can have a simple piece of data, that by itself is not sensitive, but when combine it with other data that’s not sensitive and now you  have sensitive data. IE: I’m Robert, that by itself is not vary valuable. Combine that with my zip code that is not very sensitive and you have narrowed down the universe of Roberts’. Next add that I drive a Ford F150 and a BMW R1150RS, now you have uniquely identified me.

3a) Here is a Google spreadsheet with common sensitive column names.  Common Sensitive names

NOTE 1: this list of common names is not inclusive. If you like, feel free to send me other sensitive column names and I will add them to the sheet.

Note 2: I am working on putting this into an Oracle database that you can query locally. I’ll let you know when it’s available.

3b) If you have not already done so, you can reverse engineer you application scheme into Oracle SQL Developer Data Modeler. SQL Developer Data Modeler has the ability to mark and report columns that are sensitive.

Heli made a blog entry on how to mark and report on sensitive data in SQL Developer Data Modeler. Sensitive Data From Heli

4) Create trusted paths to your sensitive data. Or at the very least, limit the number of paths to get to your sensitive data. Now that you have a list of sensitive data, document where that data is getting accessed by. You can use unified_audit_trail to get hosts names and users accessing the data. Once you have validated how the data is getting accessed and from where and by who you can setup Virtual Private Database and redaction to limit the paths to get to the sensitive data.

Here is a simple example, if people who are authorized to access the data are in a specific subnet, then you can check the subnet and use the VPD policy to append where 1=2 onto the where clause to anyone querying the data from outside that subnet. You can also use authentication method in this check. Say the data is so sensitive you only want people to access it if they connected using RADIUS. If someone connected using anything else, again you would append where 1=2 onto the where clause to return nothing. This important thing to remember is, Your VPD policy can be anything you can code in PL/SQL.

Lets start this with setting up some support objects in the security schema.

Setup a table of ip_addresses and if the user is granted access to credit card number, social security number and if they user has customer access.

1 -- Create these objects under the security schema. 2 -- the table ip_addresses will be used in a login trigger for the context to populate. 3 -- the ccnbr_access defults to 'N' for no access. To grant access populate the ccnbr_access column 4 -- with 'Y'. ssn_access column default to 'N' for no access. To grant access to ssn populate the 5 -- ssn_access column with 'Y'. The column customer_access defaults to 'N' if this is 'N' then 6 -- where 1=2 will be appended to the where clause so the query returns nothing. If customers_access is 7 -- 'Y' then the stores table will be used to build the where clause for the customers table. 8 create table security.ip_addresses (id int generated by default as identity, 9 ip_address varchar2(16) not null, 10 ccnbr_access varchar2(1) default 'N' not null, 11 ssn_access varchar2(1) default 'N' not null, 12 customer_access varchar2(1) default 'N' not null); 13 14 -- create a unique index on ip_addresses. 15 create unique index ip_addresses_uidx on ip_addresses(ip_address); 16 -- we are going to insert a row that states localhost can access credit card 17 -- numbers and social security numbers. In this example, it would not be 18 -- a good idea on a policy level to grant a user both credit card number 19 -- and social security number. 20 insert into ip_addresses (ip_address, ccnbr_access, ssn_access, customer_access) values ('127.0.0.1', 'Y','Y','Y'); 21 22 commit; 23

We are going to create a couple of roles where to support CONTEXT, VPD and REDACTION.

1 -- the role ccnbr_access will be used by the redaction policy 2 create role ccnbr_access; 3 -- the ssn_access role will be used by the redaction policy 4 create role ssn_access; 5 -- the customers_access role will be used by the VPD policy. 6 create role customers_access; 7 -- note, app_user01 will not work here. The user will be connecting 8 -- with password authentication, therefore; for testing purposes you 9 -- can change the VPD code to authenticate with a password. Otherwise 10 -- you will need to create a cert for this user and install it. 11 create user app_user01 12 identified by My12SecurePassword; 13 14 create user app_user02 15 identified by My12SecurePassword; 16 17 grant ccnbr_access, customers_access to app_user01; 18 grant create session to app_user01, app_user02; 19

To start with Virtual Private Database you want to create a context. This will be used in a login trigger to get information about the user and their connection.

1 -- create a context 2 -- the customers_ctx context will have three names. 3 -- ccnbr, ssn and customers. 4 -- this relys on 1) the user connecting from the approate ip_address. 5 -- 2) the user having the correct roles. in order for the user to 6 -- access any of the customer informantion the user must have the 7 -- customer access role. If the user does not connect to the database 8 -- from an approved ip address and the user does not have the correct 9 -- role then where 1=2 will be appended onto the where clause. 10 -- if the user has the customer_access role and the user has the 11 -- ccnbr_access role, and the customer connects using ssl then the 12 -- user will be able to access ccnbr. If not, ccnbr will be redacted. 13 CREATE OR REPLACE CONTEXT customers_ctx USING cust_control_pkg; 14

Now, lets create a package to populate the customrs_ctx CONTEXT.

1 -- create the package to support the conext. 2 create or replace package cust_control_pkg as 3 -- in this example there is only one procedure we are 4 -- going to expose. Thats set_context. This procedure 5 -- will set all the context for customer security 6 procedure set_context; 7 end; 8 / 9 10 -- The package has three functions. 1) check the user has access to ccnbr, 11 -- 2) check the user has access to ssn 12 create or replace package body cust_control_pkg as 13 -- check to see if the ip address is allowed to access credit card numbers 14 -- and the user has the ccnbr_access role. 15 function has_ccnbr return boolean 16 is 17 cnt number; -- a variable to hold the count 18 begin 19 --the user must be connecting from an approved ip address and 20 -- have the ssn_access role and authenticate using SSL. 21 select count(*) 22 into cnt 23 from ip_addresses 24 where ip_address = sys_context('userenv','ip_address') 25 and sys_context('sys_session_roles','ccnbr_access') = 'TRUE' 26 and sys_context('userenv','AUTHENTICATION_METHOD') = 'RADIUS' 27 and ccnbr_access = 'Y'; 28 if cnt > 0 then 29 return true; 30 else 31 return false; 32 end if; 33 end has_ccnbr; 34 35 -- check to see if ip address can access ssn 36 function has_ssn return boolean 37 is 38 cnt number; -- to hold the count 39 begin 40 -- the user must be connecting from an approved ip address and 41 -- have the ssn_access role and authenticate using RADIUS. 42 -- for this demo, change RADIUS to PASSWORD 43 select count(*) 44 into cnt 45 from ip_addresses 46 where ip_address = sys_context('userenv','ip_address') 47 and sys_context('sys_session_roles','ssn_access') = 'TRUE' 48 and sys_context('userenv','AUTHENTICATION_METHOD') = 'PASSWORD' 49 and ssn_access = 'Y'; 50 51 if cnt > 0 then 52 return true; 53 else 54 return false; 55 end if; 56 end has_ssn; 57 58 function has_customer return boolean 59 is 60 cnt number; -- to hold the count 61 begin 62 -- the user must be connecting from an approved ip address and 63 -- have the ssn_access role and authenticate using RADIUS. 64 -- for this demo, change RADIUS to PASSWORD 65 select count(*) 66 into cnt 67 from ip_addresses 68 where ip_address = sys_context('userenv','ip_address') 69 and sys_context('sys_session_roles','customers') = 'TRUE' 70 and sys_context('userenv','AUTHENTICATION_METHOD') = 'PASSWORD' 71 and customer_access = 'Y'; 72 73 if cnt > 0 then 74 return true; 75 else 76 return false; 77 end if; 78 end has_customer; 79 80 procedure set_context is 81 begin 82 if has_ccnbr then 83 sys.dbms_session.set_context('customers_ctx', 'ccnbr', 'Y'); 84 else 85 sys.dbms_session.set_context('customers_ctx', 'ccnbr', 'N'); 86 end if; 87 if has_ssn then 88 sys.dbms_session.set_context('customers_ctx', 'ssn', 'Y'); 89 else 90 sys.dbms_session.set_context('customers_ctx', 'ssn', 'N'); 91 end if; 92 if has_customer then 93 sys.dbms_session.set_context('customers_ctx', 'customers', 'Y'); 94 else 95 sys.dbms_session.set_context('customers_ctx', 'customers', 'N'); 96 end if; 97 end set_context; 98 end; 99 / 100

Now we are going to create a login trigger to populate the context when to user connects.

1 -- create the logon trigger to populate the context 2 CREATE OR REPLACE EDITIONABLE TRIGGER "SECURITY"."SET_VPD_CTX_TRIG" AFTER LOGON ON DATABASE 3 BEGIN 4 -- setup the context for access to customers 5 security.cust_control_pkg.set_context; 6 -- setup other context for sensitive information 7 EXCEPTION when others then 8 -- we need a way to trap the error. If the logon trigger is broken 9 -- then users will not be able to connect 10 utility.pkg_log.log_error('Logon trigger raised exception ' || sqlerrm); 11 END; 12 / 13 ALTER TRIGGER "SECURITY"."SET_VPD_CTX_TRIG" ENABLE; 14

Now we are going to create a Virtual Private Database policy on the customers table.

1 -- create a virtual private database policy. 2 BEGIN 3 DBMS_RLS.ADD_POLICY ( 4 object_schema => 'rlockard', 5 object_name => 'customers', 6 policy_name => 'cust_policy', 7 function_schema => 'security', 8 policy_function => 'cust_vpd', 9 statement_types => 'select, insert, update, delete' 10 ); 11 END; 12 / 13

 

And a function that returns the where clause for the policy.

1 -- create the function to append to the where clause 2 CREATE OR REPLACE FUNCTION cust_vpd (object_schema IN VARCHAR2,object_name VARCHAR2) 3 RETURN VARCHAR2 IS 4 return_val VARCHAR2(4000); 5 BEGIN 6 7 IF sys_context('customers_ctx','customers') = 'N' 8 THEN 9 return_val := '1=2'; -- don't return anything 10 ELSE 11 return_val := '1=1'; -- return everything, we can make this 12 -- a lot more fisicated, but for this 13 -- purpose we will stick to where 1=1 14 END IF; 15 return return_val; 16 END; 17 / 18

To tighten this down a bit more lets add redaction to credit card number and social security number.

1 -- create the redaction policy for SSN and CCNBR 2 -- the expression must evaluate to true for the column to be 3 -- redacted and you can only use sys_context in the expression. 4 declare 5 begin 6 dbms_redact.add_policy ( 7 object_schema => 'RLOCKARD', 8 object_name => 'CUSTOMERS', 9 policy_name => 'SENSITIVE_CUST_DATA', 10 expression => 'sys_context(''customers_ctx'', ''SSN'') = ''N''', 11 column_name => 'SSN', 12 function_type => dbms_redact.full 13 ); 14 end; 15 / 16 17 -- alter the policy to add credit card number 18 BEGIN 19 DBMS_REDACT.ALTER_POLICY ( 20 object_schema => 'RLOCKARD', 21 object_name => 'CUSTOMERS', 22 policy_name => 'SENSITIVE_CUST_DATA', 23 column_name => 'CCNBR', 24 action => DBMS_REDACT.ADD_COLUMN, 25 function_type => DBMS_REDACT.PARTIAL, 26 function_parameters => DBMS_REDACT.REDACT_CCN16_F12, 27 expression => 'sys_context(''customers_ctx'', ''CCNBR'') = ''N''' 28 ); 29 END; 30 / 31

 

Trusted paths don’t only include using Virtual Private Databases and Redaction. There are a number of ways to narrow down the paths to get to sensitive data on the developers side of the house. I will cover those in my next blog post.

So in summary, your four steps are Encrypt, Audit, Find your sensitive data and Limit the access paths to your sensitive data by creating trusted paths.