Oraclewizard.com, Inc is a Veteran Owned HUBZONE Certified Small Business, Principle owner Robert Lockard is an Oracle ACE Director and Winner of the 2015 Oracle Developers Choice Award for Database Design.
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
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.
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.REPORTTARGETAUTHID current_user ACCESSIBLE BY (TARGETAPP.PROCESSTARGET)AS-- constant declarationssVersionCONSTANT 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.
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.
1SQL>selecttextfrom all_source where owner =userand name ='PARSE_STRING';
2procedure parse_string(p_string varchar2) AS 3CURSOR col_cur IS 4selectdistinct (instr(p_string||',',',',1,level)) loc
6 CONNECT BYLEVEL<= LENGTH(REPLACE(p_string,','))+1 7ORDERBY1;
8 l_col varchar2(65);
9BEGIN10for col_rec in col_cur
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);
161714 rows selected.
1819SQL>createuser u2 identified by MY##56SecurePassword;
2223SQL>grantcreate session to u2;
2627SQL>grantexecuteon parse_string to u2;
3031SQL> conn u2@orcl32Enter password:
34SQL>selecttextfrom all_source where owner ='RLOCKARD'352and name ='PARSE_STRING';
36procedure parse_string(p_string varchar2) AS37CURSOR col_cur IS38selectdistinct (instr(p_string||',',',',1,level)) loc
40 CONNECT BYLEVEL<= LENGTH(REPLACE(p_string,','))+141ORDERBY1;
42 l_col varchar2(65);
43BEGIN44for col_rec in col_cur
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);
505114 rows selected.
Now when we put this into a package, the only thing I can extract from it is the package specification.
1SQL>grantexecuteon utility to u2;
2 3Grant succeeded.
4 5SQL> conn u2@orcl 6Enter password:
8SQL>selecttextfrom all_source where owner ='RLOCKARD' 9and name ='UTILITY';
10211TEXT12--------------------------------------------------------------------------------13package utility AS14procedure parse_string(p_string varchar2);
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.
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.
1SQL>createorreplace package rlockard.cust_api AS 2function update_customers_credit_limit(pID innumber, pCredit innumber) returnnumber;
4/ 5Package created.
6 7SQL>createorreplace package body rlockard.cust_api AS 8 9function update_customers_credit_limit(pID innumber, pCredit innumber) returnnumberis10 retVal number;
11begin12update customers set credit = pCredit where id = pId;
14 exception when no_data_found
15then16 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);
20return retVal *-1; -- we are flipping the sign to indacate it's an error to caller.21end;
23/24Package body created.
I am going to grant execute to the user U2 that we create earlier and test this.
1SQL>grantexecuteon rlockard.cust_api to u2;
2 3Grant succeeded.
4 5SQL> conn u2@orcl 6Enter password:
8SQL>declare 92 x number;
103begin114 x:=rlockard.cust_api.update_customers_credit_limit(pId =>1770, pCredit =>1000000);
136/1415PL/SQL procedure successfully completed.
1617SQL>select credit from rlockard.customers where id =1770;
18select credit from rlockard.customers where id =177019*20ERROR at line 1:
21ORA-00942: tableorview does not exist
222324SQL> conn rlockard@orcl25Enter password:
27SQL>select credit from rlockard.customers where id =1770;
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.
1SQL>createorreplace package rlockard.cust_api
2AUTHID CURRENT_USER 3AS 4function update_customers_credit_limit(pID innumber, pCredit innumber) returnnumber;
6/ 723456 8Package created.
91011SQL> conn u2@orcl12Enter password:
14SQL>declare15 x number;
16begin17 x := rlockard.cust_api.update_customers_credit_limit(pId =>1770, pCredit =>2500000);
18exception when others then19 sys.dbms_output.put_line(sqlerrm);
21/22ORA-00942: tableorview does not exist
2324SQL> conn rlockard@orcl25Enter password:
27SQL>select credit from customers where id =1770;
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.
1CREATE ROLE update_customers;
2 3grant update_customers to rlockard;
4 5GRANTSELECT 6ON customers
8 9GRANT update_customers TO PACKAGE cust_api;
1011declare12 x number;
13begin14 x := rlockard.cust_api.update_customers_credit_limit(pId =>1770, pCredit =>2500000);
15exception when others then16 sys.dbms_output.put_line(sqlerrm);
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.
1SQL>createorreplace package public_package AS 2procedure update_customers(pId innumber,
3 pColumn invarchar2,
4 pValue invarchar2);
6/ 7 8Package created.
910SQL>createorreplace package body public_package as11procedure update_customers(pId innumber,
12 pColumn invarchar2,
13 pValue invarchar2) IS14 x number; -- we know it's a function that returs a number.15begin-- this is simplistic to demo accessable_by16if pColumn ='CREDIT'then17 x := rlockard.cust_api.update_customers_credit_limit(pId => pId, pCredit => pValue);
21/2223Package body created.
2425SQL>createorreplace package rlockard.cust_api
26accessible by (public_package) AS27function update_customers_credit_limit(pID innumber, pCredit innumber) returnnumber;
3233SQL>3435SQL>declare36 x number;
37begin38 x := rlockard.cust_api.update_customers_credit_limit(pId =>1770, pCredit =>2500000);
39exception when others then40 sys.dbms_output.put_line(sqlerrm);
42/43x := rlockard.cust_api.update_customers_credit_limit(pId =>1770, pCredit =>2500000);
44*45ERROR at line 4:
46ORA-06550: line 4, column8:
47PLS-00904: insufficient privilege to access object CUST_API
48ORA-06550: line 4, column3:
49PL/SQL: Statement ignored
505152SQL>begin53 public_package.update_customers(pId =>1771, pColumn =>'CREDIT', pValue =>'200');
55/5657PL/SQL procedure successfully completed.
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.
2 (p_email logins.email%type DEFAULTNULL,
3 p_password logins.password%type DEFAULTNULL)
4AS 5 6 STMT CONSTANT VARCHAR2(4000) := 7'SELECT email
8 FROM logins
9 WHERE email = '''|| p_email ||10''' AND password = '''|| p_password ||'''';
1112 l_result logins.email%type;
13BEGIN1415 dbms_output.put_line ('SQL STMT: '|| STMT);
1617EXECUTE IMMEDIATE STMT INTO l_result;
1819 dbms_output.put_line ('Logon succeeded.');
2021EXCEPTION WHEN OTHERS THEN22null; -- OH NO HE DID NOT 23END 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.
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.
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.
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.
$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.
I’m keep seeing this common mistake; The application schema was granted DBA privileges. Here is the problem, when a sql injection bug is found, then all DBA commands are available to the attacker.
The truth is, granting DBA to an application schema is the lazy way to get your application the privileges it requires to operate. Heck, I’m still seeing COTS applications that in the install guide say GRANT DBA TO . COTS applications require DBA privileges are poorly designed.
To fix this. Audit that app user to see what are it’s actually doing.
where username = ‘&USER’
group by obj_name,
Use the results of the query to derive what privileges are actually needed.
Last week one of my customers called me into a meeting to discuss moving a critical application to the cloud. This application is very sensitive to the customer and the data it holds is very sensitive to my customers customer. The results of this meeting turned into a list of questions forwarded the customers executive staff and also a set of questions for the cloud vendor.
This Cloud vendor is providing a COTS solution storing personally identifiable information tax and other very sensitive information. Because of this a number of the questions focus on the protection of PII and the destruction of unneeded copies of data.
I have redacted customer and vendor information from this list of questions, these questions may serve as a baseline for your organization to come up with questions for your Cloud vendors. Point of note the answers to these questions will more likely than not cause follow-up questions.
Here is the list of questions for the customers executive staff to address.
As <REDACTED> moves towards cloud based computing solutions, <REDACTED> must consider the following to create standards for all cloud based systems going forward:
Will <REDACTED> require TLS on day 1? If not, vendor must have a plan and a deadline to get off SSL and on to TLS?
Will <REDACTED> require DISA STIG standards (Fed DOD standard) for all off site cloud data?
Will <REDACTED> require PENetration testing and at what frequency (Federal standard is 1 year)?
What level of data destruction is required for <REDACTED>’s secure/PII data being stored on a cloud based system controlled by non-<REDACTED> vendors?
Will <REDACTED> hold AES256 as the minimum encryption standard for cloud based systems?
Will <REDACTED> require 3DES minimum 168 bits?
Will <REDACTED> require a minimum of 7 wipes for secure/PII data stored on cloud based systems?
Will <REDACTED> require in sales contract with stated frequencies, independent audits to ensure <REDACTED>’s stated audit, encryption and data destruction plans are in effect and compliant?
Will <REDACTED> require internal <REDACTED> audits and/or legislative audits be performed on <REDACTED> systems?
The sales contract must state the “break up” plan for all <REDACTED> data including the delivery back to <REDACTED>, the destruction of the data on vendor systems and the certification that all data has been destroyed according to the <REDACTED> standards. Independent audit to verify results.
Will <REDACTED> require all data stay within the United States, with no data ever leaving the US?
What will <REDACTED> require regarding the vetting standard for cloud vendor trusted inside employees?
What will <REDACTED> require regarding liability insurance in the event of a security incident?
As <REDACTED> data is highly sensitive and contains a great deal of PII for each firm, the following are questions to be answered:
1. Regarding the destruction of sensitive/PII data on <REDACTED> systems, how will you destroy unnecessary copies of data and ensure the necessary copies are encrypted and secure?
2. Is the use of AES256 and 3DES encryption consistent throughout <REDACTED> enterprise as referenced on page 10 of the Security Management Plan? How many bits are used for 3DES?
3. Initial Source Data/Document Load files (via sftp per <REDACTED> docs): Controls/Audit – <REDACTED> should know exactly who touched the load files and for what purpose via audit reports.
4. Additionally, after migration is complete, <REDACTED> to certify (via independent audit) that all source data has been destroyed and no ghost data remains on servers or work stations.
5. Cross boarder – will the data leave the United States for any reason at any time?
6. What analytics software packages are in use to monitor account activity for our <REDACTED> employees as well as <REDACTED> trusted inside employees? How will audit reports be delivered to <REDACTED>?
7. What does “in compliance with Cyber Security Standard” refer to as mentioned on page 6 of the <REDACTED> Security Management Plan? Is this a subset or superset of NIST?
8. On page 7 of the <REDACTED> Security Management Plan in reference to Export Servers under System Architecture, how is the use of these Export Servers audited and after the export is no longer required, how will you certify that the data has been destroyed? If used, can an unencrypted copy of the export be made?
9. Will all backups be encrypted with 3DES and at what bit level? How will <REDACTED> certify the destruction of old backups?
10. What is the plan for <REDACTED>’s system using TLS?
11. Does <REDACTED> harden sqlserver to DISA STIG standards? If not, is it a superset or a subset?
12. What is the end of contract plan for all <REDACTED> data including the delivery back to <REDACTED>, the destruction of the data on <REDACTED> systems and the certification that all data has been destroyed according to the <REDACTED> standards.
13. In the event of a security incident, does <REDACTED> have liability insurance to cover associated losses?
14. How are your trusted inside employees are vetted (DBA’s, System Admins, Network Admins, etc)?
15. If you perform PEN testing, what is the frequency of the testing and will <REDACTED> get a redacted copy of the results of each test?
This is the demo code for encrypting data where there is an existing index. We are starting with a table customers_tst that is in the unencrypted tablespace dat.
start with dropping the old test objects.
create two small tablespaces small_idx and dat.
create the customers_tst table as a subset of customers.
create an index on customers_tst(ssn)
alter the table customers_tst to add encryption to ssn and cc_nbr. Because ssn has an index we are not using salt.
We then alter the index to rebuild. Because Oracle marks block as free and does not erase them, the old index still exists.
We check for ghost data in small_indx.dbf and dat.dbf.
We confirm that there is ghost data and then drop the index.
Once we drop the tablespace, we can then turn our attention on shredding the ghost data.
2DROP MATERIALIZED VIEW customer_sales;
3DROP TABLESPACE small_idx INCLUDING CONTENTS;
4DROP TABLESPACE dat INCLUDING CONTENTS;
5-- rm /opt/oracle/oradata/DEV/datafile/small_idx.dbf 6-- rm /opt/oracle/oradata/DEV/datafile/dat.dbf 7CREATE TABLESPACE small_idx DATAFILE '/opt/oracle/oradata/DEV/datafile/small_idx.dbf' SIZE 10M;
8CREATE TABLESPACE dat DATAFILE '/opt/oracle/oradata/DEV/datafile/dat.dbf' SIZE 10M;
9 10-- create a test table from customers. 11CREATETABLE customers_tst
13as (select* 14from customers
15where rownum <=1000);
16 17-- we are going to build an index on SSN 18CREATEINDEX customers_ssn_idx ON customers_tst(ssn) TABLESPACE small_idx;
19 20ALTERTABLE customers_tst MODIFY
21 (ssn encrypt USING 'AES256' NO SALT,
22 cc_nbr encrypt USING 'AES256');
23 24-- now lets do an index rebuild and test for ghost data 25ALTERINDEX customers_ssn_idx REBUILD;
26 27-- in root container run flush the buffer cache 28 29-- in shell run strings /opt/oracle/oradata/DEV/datafile/small_idx.dbf 30-- in shell run strings /opt/oracle/oradata/DEV/datafile/dat.dbf 31 32SELECT*FROM customers_tst
33where ssn ='347631761';
34 35dropindex customers_ssn_idx;
36 37DROP TABLESPACE small_idx;
38-- in the shell shread the datafile 39-- in the shell shred /opt/oracle/oradata/DEV/datafile/small_idx.dbf 40-- in the shell rm /opt/oracle/oradata/DEV/datafile/small_idx.dbf 41-- we are going to build an index on region so support FK to regions. 42create tablespace small_idx datafile '/opt/oracle/oradata/DEV/datafile/small_idx.dbf' size 10M;
43 44-- a small tablespce to hold a materialized view. 45CREATE TABLESPACE dat
46DATAFILE '/opt/oracle/oradata/DEV/datafile/dat.dbf' SIZE 10M;
47 48-- now lets recreate our test data and encrypt it. 49 50CREATETABLE customers_tst
52as (select* 53from customers
54where rownum <=1000);
55 56ALTERTABLE customers_tst MODIFY
57 (ssn encrypt USING 'AES256' NO SALT,
58 cc_nbr encrypt USING 'AES256');
59 60-- we are going to build an index on SSN 61CREATEINDEX customers_ssn_idx ON customers_tst(ssn) TABLESPACE small_idx;
62 63 64CREATE MATERIALIZED VIEW CUSTOMER_SALES
68USING INDEX 69REFRESH
70START WITH SYSDATE NEXT SYSDATE +1/24 71COMPLETE
73USING DEFAULTROLLBACK SEGMENT
74DISABLE QUERY REWRITE AS 75SELECT 76 c.fname,
81 c.cc_nbr, -- cc_nbr is sensitive and encrypted. 82 c.ssn, -- ssn is sensitive and encrypted. 83 s.price,
86FROM customers_tst c,
87 sales_tst s,
88 products p
89where c.id = s.cust_id
90and s.product_id = p.id;
91 92CREATEINDEX CUSTOMER_SALES_IDX ON CUSTOMER_SALES (SSN) TABLESPACE small_idx;
93CREATEINDEX CUSTOMER_SALES_IDX2 ON CUSTOMER_SALES (cc_nbr) tablespace small_idx;
94 95--DROP MATERIALIZED VIEW customer_sales; 96 97-- lets check for sensitive data in the datafiles. 98-- in shell strings /opt/oracle/oradata/DEV/datafile/dat.dbf 99-- in shell strings /opt/oracle/oradata/DEV/datafile/small_idx.dbf100DROP MATERIALIZED VIEW customer_sales;
101DROP TABLESPACE small_idx INCLUDING CONTENTS;
102DROP TABLESPACE dat INCLUDING CONTENTS;
103-- in shell shred /opt/oracle/oradata/DEV/datafile/dat.dbf104-- in shell shred /opt/oracle/oradata/DEV/datafile/small_idx.dbf105-- in shell rm /opt/oracle/oradata/DEV/datafile/dat.dbf106-- in shell rm /opt/oracle/oradata/DEV/datafile/small_idx.dbf107108CREATE TABLESPACE small_idx DATAFILE '/opt/oracle/oradata/DEV/datafile/small_idx.dbf' SIZE 10M;
109CREATE TABLESPACE dat DATAFILE '/opt/oracle/oradata/DEV/datafile/dat.dbf' SIZE 10M;
120 CC_NBR ENCRYPT USING 'AES256',
122 SSN ENCRYPT USING 'AES256' NO SALT)
127where rownum <=1000);
128129130-- we are going to build an index on SSN131CREATEINDEX customers_ssn_idx ON customers_tst(ssn) TABLESPACE small_idx;
132133134-- we are going to recreate the materialized view, this time135-- we will add the encrypt clause.136137CREATE MATERIALIZED VIEW CUSTOMER_SALES
143 cc_nbr encrypt USING '3DES168',
144 ssn encrypt USING '3DES168' NO SALT, -- because ssn has an index we will not use salt.145 price,
147 product_name )
161FROM customers_tst c,
162 sales_tst s,
163 products p
164WHERE c.ID = s.cust_id
165and s.product_id = p.id);
166167CREATEINDEX CUSTOMER_SALES_IDX ON CUSTOMER_SALES (SSN) tablespace small_idx;
168CREATEINDEX CUSTOMER_SALES_IDX2 ON CUSTOMER_SALES (ZIP) TABLESPACE small_idx;
169170-- in shell strings /opt/oracle/oradata/DEV/datafile/dat.dbf171-- in shell strings /opt/oracle/oradata/DEV/datafile/small_idx.dbf
I have spent the past several weeks interviewing potential Senior Oracle DBAs. Two made the cut. Why did they make the cut? Most people did not make the cut. Why did they not make the cut? Who got hired? Why did we hire her?
There are some simple things you can do to put yourself out in front of the crowd.
If your resume is thin. (your finishing up at the University and you’ve been waiting tables) Hey, I get it, I was there to back when I chiseled my first resume onto stone with iron and a stone hammer. Indicate you have been at the university, what you studied, and hey, maybe put on there what you are most interested in. Defensively put some of your class projects on there, describing them (not more than one paragraph.) Also break down your skills and how you feel you fall on the beginner – expert scale for each skill. See my CV / Resume http://oraclewizard.com/Oraclewizard/cv-resume/ for an idea on skills.
If you have 10 years experience at 10 different places that tells me you will most likely quit or be fired in a year. Job hopping is not impressive. Now that said, if you are working in a consulting capacity (contract to contract) that is pretty normal. Please indicate that on your resume.
Putting a PhD in public policy from a diploma mill will not impress me. We may invite you to the phone interview just to figure out who you are. But then you better perform like a PhD.
If you put <name your technology> Expert on your resume, you better be able to back that up in the interview. I will ask hard questions on the things you say you are an expert on.
BS your way through the answer. It’s okay to not know the answer. These questions are designed to learn how you think. What I’m looking to hear if you do not know the answer is to say “I don’t know the answer, this is how I would go about finding out the answer.” It’s okay to think out loud, that gives us an idea on how you solve problems. And NO saying I would open an SR for every problem you have is not the right way to solve a problem.
Once you have answered the question, indicate you are ready for the next question. Don’t ramble on, please, I just start hearing “bla bla bla.”
OMG, we did an interview today where we were wondering if the person suffered from depression. Okay, you have been out of work, you feel bad about yourself, get over it. We need someone who will come in and be ready to work. So please get yourself pumped up, be happy to talk to us, be professional. Even if it’s the ninth phone interview you did this week and everyone said no. You need to convince me and my coworkers you are ready to come in and get the job done.
There is a difference between, confidence and condescending. One gentleman was quite condescending about our current environment. Then rolled his eyes when we talked about how we are starting a consolidation effort. (this is what we were interviewing him for)
Who did we invite back for a face to face?
She showed energy: the young lady spent five minutes explaining to us her history and was proud of her work. (we were wondering if she had one too many espressos) She then followed up the interview with a thank you email addressed to each one of us and summarized why she is a good fit for our team. She showed us that she believes in herself. She missed a couple of questions but indicated she did not know the answer off the top of her head and talked through the logic. Hey you get a lot of points with me if you show me you can think.
He said, “I have not worked with TDE so I can’t answer those questions; so I would to the Oracle Documentation and look it up.” He showed us that he knows what he does not know.
That’s pretty much it. Once you start talking, you have about seven seconds to get my attention. Show your are competent, show that you know what you don’t know and for peats sake, show some energy. Show us that you believe in yourself. We want to hire you. We want you to succeed.
Update: We hired the young lady who showed up with a fire in her belly. Her energy was contagious, and a good fit for our team.
Last week I was at Oracle Cloud World working at the ODTUG booth. This gave me the opportunity to talk to a lot of people who are seriously looking at moving their environment to the cloud. While chatting with these people, I started to pull together some thoughts on the security issues that come with moving to the cloud. Many of those issues are the same for hosting your own database applications. There are several issues with moving to the cloud and if you don’t address them it can become dark and stormy.
What security questions do you need to address prior to moving to the cloud? Note: many of these issues also applies to hosting your own databases! This subject is complex and I’m just touching on some of the issues. If you don’t do your due diligence you will get burned.
Will and How will your data be encrypted? First off, all of your data should be encrypted by default. I am also of the <OPINION> cloud provider should not even offer to store your information unencrypted. </OPINION>. With the advent of hardware encryption modules, encryption performance is a non-issue.
There are a couple of options on encrypting your data, both have strengths and both have weaknesses. First of the easiest encryption option to implement is tablespace encryption. This option is used to encrypt all of your data stored in the tablespaces. The down side is the data is unencrypted in the SGA.
The other option is column encryption. This requires a bit of work upfront to setup. You are going to need to identify the atomic pieces of data that need to be encrypted then go through your indexing scheme to make sure you have not put indexes on columns that are encrypted with salt, and you don’t have foreign key constraints on columns that are encrypted. The upside of column encryption is the data stays encrypted in the SGA.
Will your backups be encrypted? Again, the answer must be yes and this is where it gets a bit tricky. RMAN backups are block level copies of the data files, so if your data is encrypted, your backups will be encrypted. However, if someone runs a datapump export of your data to refresh a lower environment and they do not specify encryption in the options, then your data will be saved unencrypted. The cloud provider must audit for this event and if it does happen, then you need to be informed and the cloud provider must make every effort to find and destroy that datapump file and any copies that have been made of it. You notice I used the word destroy as apposed to delete. Well there is good reason for that, if you delete a file there is still ghost data that can be recovered. So that or those file(s) will need to destroyed by a utility such as Linux shred.
The trusted insider attack surface has changed. <OPINION> It is safe to assume Oracle and other Tier 1 cloud providers will vet their system administrators. </OPINION> However; people change, that is just a fact of life. I frequently use the example of Edward Snowden. Prior to his leaking NSA documents he had gone through polygraph examinations, and his entire background put under a microscope, then he changed.
How will your cloud provider protect you against their trusted insider? The concept is easy, wall off your data from being seen by the system administrator. I’ve been a DBA for decades and can tell you with complete honesty, the DBA or SA does not need access to your data in order to do their job. <OPINION> Oracle has a great product Database Vault that is designed to wall off your data from the SAs. Any cloud solution should include the implementation of Database Vault. </OPINION>
Your cloud provider must provide a proven tool that protects your information from trusted insiders at the cloud provider.
Your cloud provider must also provide an integrated audit solution that tracks all audit events and allows you to report on audit events. Oracle Audit Vault comes with BI. You can use caned reports and customize those reports for your requirements.
Can you make customization’s to the security? Oracle Real Application Security (RAS) gives you, Redaction, Virtual Private Databases and audit on all connections. A full discussion of RAS is beyond the scope of this paper.
<OPIONION> At the very least, you should be able to implement, Virtual Private Databases, and Redaction to protect your data from the normal use of you applications. </OPINION> (I say normal use of you applications. Using different tools and grants it is possible to bypass these features.)
Will the cloud provider implement and configure Database Firewall. Database Firewall is a good tool to defend against sql injection attacks. It takes a lot of work to properly configure it especially if you are using a custom application. Will the cloud provider be responsible for the configuration of database firewall?
How are you going to get your data back if you decide to break up with your cloud provider?
If your cloud provider is using Oracle 12C multitennant an encryption key is generated with the container database and that is used to decrypt the encryption key for the pluggable database. I’m not going to dive too deep into this. The cloud provider can unplug your database and provide you with a set of keys to decrypt your data.
Then the worst happens, there is a data breach. You need to know, how will your cloud provider make you whole. The truth is, your customers will be upset with you and maybe your cloud provider.
End the end, you are the steward of your customers data and with that stewardship comes responsibilities.
Privacy & Cookies Policy
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.