Five things that make me just want to scream. #Infosec

When evaluating the security of a database, and/or the security of an application, there are a few things that will make me start pounding my head against my desk. What prompted this? A customer asked me help evaluate a system that is about to go through an IG Audit and they want to be ahead of the curve. The good part is I was able to come up with a lockdown procedure that is specific to this customer and their needs. The bad part is, I keep seeing these same mistakes over and over again. So, lets sit down and go through five of the mistakes that I keep seeing over and over again.

  1. A COTS application required any of the following privileges to get installed or operating. I’m sorry guys, if you have not figured out what permissions your application really needs, then it’s not ready to be put on the market. I was able to do a privilege analysis and find out what this application really needed.
    1. DBA – I have a hard time justifying granting DBA to an application to be installed or operate.
    2. SELECT / CREATE / UPDATE / DROP ANY <FILL IN YOUR TYPE>. Hey, this is another privilege that just makes me shake my head. Because the ANY any privilege allows the grantee to operate on ANY object in the database, this just tells me you have not thought it through. Many databases have multiple applications running in an instance. Why would your application need to access schemas that are not part of your applications?
      1. Oh and as a side note, why did the application need both DBA and all these ANY privileges?
    3. Okay there are a whole bunch of privileges that should never be granted to an application, these are just a few.
  2. Not cleaning up users that have left. Please tell me why you need to keep a user in the system who left two years ago. If someone can give me a good excuse, I’m willing to listen, but I got to tell you, in thirty years in this business, I have not heard any good reason to keep a user in the system.
    1. Go ahead, drop those users, including the test, sit, and dev instances. They don’t need to be there.
  3. Connecting as the application to do your job. I see this so often that it really makes me wonder about the technical ability of the people doing the job. Please tell me why you need to connect to the application to do your work? If there are permissions that you need that are missing, we can resolve that.
  4. Mixing Data and Code in the same schema. Why do you need to do this? You really should separate your data and code. All you need is one little sql injection bug for a bad guy to find, then she owns your database. Oh, and it’s highly likely you have a sql injection bug, you just don’t know it yet. We have a plan to separate the code from the data, it’ll take a while, but this should keep the auditors happy.
  5. Writing queries in production. I get it, it happens; you are supporting your users and the user needs something from the database quick. There is not a report available that answers the users question. You connect to the production instance through sql plus, sqlcl, sql developer or some other tool. You then type your query, hit enter and production slams on the breaks. Yea’ I have lost count of the number of times I’ve seen this, you forgot to join those two really big tables and just got a cartesian product. This happened while I was sitting there. You have a test instance, please use it before running in production.

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.