Pat Shuff's Blog

  • PaaS
    June 14, 2016

database options - advanced security

Advanced Security and Transparent Data Encryption (TDE) stops would-be attackers from bypassing the database and reading sensitive information from storage by enforcing data-at-rest encryption in the database layer. Earlier when we talked about partitioning and compression we talked about tablespace files and how to manage them. If these files are stored in clear text, anyone who can access our file system could theoretically read the dbf file and do a clear text search. If, for example, we have a credit card number "1234 5678 9012 3456". We could find this string with a strings, grep, or od statement (octal dump to show text data). If we don't know the credit card number we can just do a dump on the data and see the values stored in the database. With enough trial and error we can figure out what the structure of the database might be and correlate names to credit card information. By default, all data stored in the cloud has TDE enabled. This is done in the $ORACLE_HOME/network/admin/sqlnet.ora file. If you look at this this file, you will see that the ENCRYPTION_WALLET_LOCATION is defined as well as the SQLNET.ENCRYPTION_TYPES_SERVER is defined. When the database is created, a wallet is generated based on your ssh keys allowing you to access your data. In the database that we created we have the wallet file location in /u01/app/oracle/admin/ORCL/tde_wallet. There is a file called cwallet.sso that is enabled anytime someone connects to the database through port 1521 or through the sqlplus command. If we rename this file to something else, we can connect to the database and create clear text files. Note that it is not recommended that you do this but we are doing this to highlight first the differences between Iaas and PaaS as well as the need for data encryption in the cloud. With a database installation on top of compute as is done with EC2, Azure Compute, and Oracle Compute, we have to enable TDE, configure the wallet, configure the cwallet.sso. With PaaS, this is all done for you and you can manage the keystore and rotate key access.

Note that it is not recommended that you execute these commands. They will desecure your database and allow for clear text storage and transmission of data across the internet. We are doing this as an example.

cat $ORACLE_HOME/network/admin/sqlnet.ora
cd /u01/app/oracle/admin/ORCL/tde_wallet

This should allow you to see the cwallet.so file which enables automatic wallet connection upon login. If we want to change encryption we can first look at the parameter encrypt_new_tablespaces and see that it is set to CLOUD_ONLY which encrypts everything. We want to change this to DDL which says that we only encrypt if we tell it to. We first want to create a tablespace and a banking user with encryption turned on. This is done with
sqlplus / as sysdba
alter session set container=PDB1;
drop tablespace banking including contents and datafiles;
create tablespace banking datafile '/u02/app/oracle/oradata/ORCL/PDB1/banking.dbf' size 20m;

We then create a banking user as well as a paas_dba user. One is used to create an encrypted table and the other is to create a clear text table
drop user banking;
create user banking identified by "PaasBundle_1" default tablespace banking;
grant create session to banking;
grant unlimited tablespace to banking;
drop user paas_dba;
create user paas_dba identified by "PaasBundle_1";
grant create session to paas_dba;
grant dba to paas_dba;

We now connect to the PDB1 as the banking user and create a table in our encrypted tablespace

connect banking/PaasBundle_1@PDB1
create table banking_customers (first_name varchar(20), last_name varchar(20), ccn varchar(20)) tablespace banking;
insert into banking_customers values('Mike','Anderson','5421-5424-1451-5340');
insert into banking_customers values('Jon','Hewell','5325-8942-5653-0031');
alter system flush bufffer_cache;
select ccn from banking_customers;

This should create a table with two entries. The table will be encrypted and stored in the banking.dbf file. If we do a string search from this file we will not find the credit card number starting with 5421. Now that we have an encrypted table created we need to reconnect to the database and disable encryption on new tables. To do this we change the parameter from CLOUD_ONLY to DDL as sysdba.
sqlplus / as sysdba
show parameter encrypt_new_tablespaces;
alter system set encrypt_new_tablespaces=DDL SCOPE=BOTH;

We can now create a new tablespace and the contents are only encrypted if we pass in the encrypt statement with the create statement. The tablespace will not be encrypted by default. We do this operation as paas_dba who has dba rights to create a tablespace and table.
connect paas_dba/PaasBundle_1@PDB1;
drop tablespace bankingCLEAR including contents and datafiles;
create tablespace bankingCLEAR datafile '/u02/app/oracle/oradata/ORCL/PDB1/bankingCLEAR.dbf' size 20m;
create table banking_Clearcustomers tablespace bankingCLEAR as select * from banking_customers;
select ccn from banking_Clearcustomers;

We should get back two entries from both select statements and see two credit card numbers. We can then exit sqlplus and look at the banking.dbf and bankingCLEAR.dbf files to see if we can lift credit cards. By executing the
strings bankingCLEAR.dbf | grep 5421
strings banking.dbf | grep 5421

we see that we get the credit card number from the bankingCLEAR.dbf file. The data is inserted clear text. It is important to remember that all data should be encrypted in motion and at rest. We need to change the parameter back to CLOUD_ONLY for the encrypt_new_tablespaces moving forward. By default we connect to the data using the encryption wallet. We can disable this as well as turning off default encryption.

In summary, we have looked at what it takes to encrypt data at rest in the cloud. By default it is turned on and we don't have to do anything with platform as a service. If we are using infrastructure as a service we need to purchase the Advanced Security option, turn on encrypting tablespaces bu changing a parameter, enable the wallet to our login, and install keys for the wallet. Platform as a service provides levels typically above and beyond what most customers have in their data center. The recent credit card loss that happened at Target a few years ago happened because they owned the Advanced Security option for the database but did not turn on the feature. An outside consultant (working on something non-it related) got access to a shared storage and pulled the dbf files onto a USB drive. They were able to get thousands of credit cards from the data center costing the CIO and IT staff their jobs. Today we learned how to turn off TDE in the cloud to illustrate what it takes to turn it on in your data center and we looked at the simplicity of pulling data from a dbf file if we know the data we are looking for. We could just as easily have just looked for number patterns and peoples names and correlated the two as valid credit card numbers.

I would like to thank the GSE team at Oracle for doing 90% of the work on this blog. I liberally hijacked the demo scripts and most of the content from demo.oracle.com, PaaS - Data Management (Solutions Demo) by Brijesh Karmakar. The GSE team creates demo environments and scripts for specific products. This demo is an Oracle internal demo and can be requested from your pre-sales consultant in your area. I took the demo code from the Database Vault and Encryption_demo_script written on 08-Jun-2016. I have to admit that looking for demo scripts on demo.oracle.com and searching for TDE was very opportune given that I wrote this blog on the 9th and it was published on the 8th.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.