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?

August 13, 2018: NOTE UPDATE TO POST THIS IS SPECIFIC TO Oracle 12.1 and bellow. Oracle 12.2 and above, you can change an unencrypted tablespace to an encrypted tablespace.

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.

 

set pagesize 1000
set linesize 132
col owner format a30
col name format a30
select d.owner,
d.name,
s.tablespace_name,
t.encrypted
from dba_dependencies d,
dba_segments s,
dba_tablespaces t
where d.owner = s.owner
and d.name = s.segment_name
and s.tablespace_name = t.tablespace_name
and referenced_name IN (
SELECT segment_name
FROM dba_segments
WHERE tablespace_name IN
(SELECT tablespace_name
FROM dba_tablespaces
WHERE tablespace_name = upper('&&tbs')))
UNION
SELECT i.owner,
i.index_name,
i.tablespace_name,
dd.ENCRYPTED
FROM dba_indexes i,
dba_tablespaces dd
WHERE i.tablespace_name = dd.tablespace_name
AND table_name IN (
SELECT segment_name
FROM dba_segments
WHERE tablespace_name IN
(SELECT tablespace_name
FROM dba_tablespaces
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. UPDATE, in Oracle 12.2 and above you can change an unencrypted tablespace into an encrypted tablespace.

CREATE TABLESPACE sensitive_dat 
DATAFILE '/opt/oracle/oradata/DEV/datafile/sensitive_data01.dbf' size 1024M
ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);
CREATE TABLESPACE sensitive_idx
DATAFILE '/opt/oracle/oradata/DEV/datafile/sensitive_idx01.dbf' size 1024M
ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);

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.youtub

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.

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

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.

set heading off 
set pagesize 1000
set linesize 132
set serveroutput on
col object_name format a24
col object_type format a24
col doctype format a10
col userhost format a40
col os_username format a15
col username format a15
col terminal format a15
spool $HOME/session_audit.txt
select 'login failures' from dual;
select os_username,
username,
userhost,
terminal,
to_char(timestamp, 'dd-mon-rr hh24:mi')
from dba_audit_session
where returncode != 0
and trunc(timestamp) >= trunc(sysdate-1)
and username != 'DUMMY'
order by timestamp
/
select 'logins yesterday' from dual;
select os_username,
username,
userhost,
count(*)
from dba_audit_session
where trunc(TIMESTAMP) >= trunc(sysdate-1)
and username != 'DUMMY'
and action_name != 'LOGOFF BY CLEANUP'
group by os_username,
username,
userhost
order by os_username,
username
/
select 'logins last 31 days' from dual;
select os_username,
username,
userhost,
count(*)
from dba_audit_session
where trunc(TIMESTAMP) >= trunc(sysdate-31)
and username != 'DUMMY'
and action_name != 'LOGOFF BY CLEANUP'
group by os_username,
username,
userhost
order by os_username,
username
/

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.

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

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.

3b) If you have not already done so, you can reverse engineer your 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.