Top priority: Transparent Database Encryption

Top priority: Transparent Database Encryption

Published on: Category: IT development and operations

The cloud computing industry is becoming bigger every year. Oracle offers databases as a service (DBaas). In the latest Oracle database release 12C, the focus is on new features for the cloud. For Oracle, security in the cloud is a top priority. No one will move their valuable data to the cloud if there are any doubts about security.

On the other hand, the cloud also needs to be easy to use. That’s why security options need to be transparent after the initial setup.

Transparent data encryption

Moving large data into the cloud can only be done if it is secure. In the Oracle Cloud, security is enabled by default. One of the advanced security features is Transparent Data Encryption (TDE).

In the Oracle Cloud, there is a new parameter configured on the Oracle 12c databases with DBaaS:

  1. NAME TYPE VALUE
  2. ------------------------------------ ----------- -----------
  3. encrypt_new_tablespaces string CLOUD_ONLY

Non system tablespaces created in the cloud are automatically encrypted. This includes the tablespaces created by JaaS (weblogic) in the cloud:

  1. SQL> SELECT tablespace_name, encrypted FROM dba_tablespaces;
  2.  
  3. TABLESPACE_NAME ENC
  4. ------------------------------ ---
  5. SYSTEM NO
  6. SYSAUX NO
  7. TEMP NO
  8. USERS NO
  9. SP55260604_IAS_TEMP NO
  10. SP55260604_STB YES
  11. SP55260604_WLS YES
  12. SP55260604_MDS YES
  13. SP55260604_IAS_IAU YES
  14. SP55260604_IAS_OPSS YES

As well as any new tablespaces created:

  1. SQL> CREATE tablespace TEST1 datafile SIZE 1M;
  2. Tablespace created.
  3.  
  4. SQL> SELECT tablespace_name, encrypted FROM dba_tablespaces
  5. WHERE tablespace_name = 'TEST1';
  6.  
  7. TABLESPACE_NAME ENC
  8. ------------------------------ ---
  9. TEST1 YES
  10.  

How effective is Tablespace Encryption?

The example below demonstrates encryption of data created in a table. First on a non-encrypted tablespace, and then on an encrypted tablespace.

Example data in table:

  1. SQL> SELECT street_address, city FROM locations
  2. 2 WHERE city = 'Seattle';
  3.  
  4. STREET_ADDRESS CITY
  5. ---------------------------------------- --------
  6. 2004 Charade Rd Seattle

This data is readable from the unencrypted data file as follows:

  1. [oracle@datafile]$ strings o1_mf_users_ckh2m31w_.dbf | grep -i Seattle
  2. Seattle

But, the data is not readable from the encrypted data file:

  1. [oracle@ datafile]$ strings o1_mf_test1_cmforz3d_.dbf | grep -i Seattle

Column encryption

An existing tablespace cannot be encrypted. A workaround is to encrypt columns with sensitive data. Note: column encryption consumes more overhead than tablespace encryption.

In the example below, a column, located on the non encrypted tablespace, is encrypted:

  1. SQL> ALTER TABLE locations MODIFY (CITY encrypt);
  2.  
  3. TABLE altered.
  4.  
  5. SQL> DESC locations
  6. Name TYPE
  7. ------------------------------- --------------------
  8. LOCATION_ID NUMBER(4)
  9. STREET_ADDRESS VARCHAR2(40)
  10. POSTAL_CODE VARCHAR2(12)
  11. CITY NOT NULL VARCHAR2(30) ENCRYPT
  12. STATE_PROVINCE VARCHAR2(25)
  13. COUNTRY_ID CHAR(2)

But the data is still readable from the data file:

  1. [oracle@duck datafile]$ strings o1_mf_users_ckh2m31w_.dbf | grep -i Seattle
  2. Seattle
  3.  

How come? 

Well, existing data is not encrypted. Only new data is encrypted.

  1. SQL> INSERT INTO locations VALUES (4000, 'De Bruyn Koppsstraat 9','2288 EC','Rijswijk','ZH','NL');
  2.  
  3. 1 ROW created.
  4.  
  5. SQL> commit;
  6. Commit complete.
  7.  
  8. SQL> SELECT street_address, city FROM locations WHERE city = 'Rijswijk';
  9.  
  10. STREET_ADDRESS CITY
  11. ---------------------------------------- ------------------------------
  12. De Bruyn Koppsstraat 9 Rijswijk
  13.  

This new data is not readable from the data file:

  1. [oracle@ datafile]$ strings o1_mf_users_ckh2m31w_.dbf | grep -i Rijswijk

How is encryption configured?

In the Oracle cloud, encryption is already configured.  Here’s how it can be configured for an onsite Oracle 12 container database.

For encryption we have to create a key, and the key has to be stored in a wallet.  The terms keystore and wallet are interchangeable for Oracle databases, the Oracle Wallet is an implementation of a keystore. There are also other implementations like the hardware keystore.

First you have to add the location of the keystore to the sqlnet.ora.

  1. ENCRYPTION_WALLET_LOCATION=
  2. (SOURCE=(METHOD=FILE)(METHOD_DATA=
  3. (DIRECTORY=/u01/app/oracle/product/12.1.0/dbhome_1/keystore)
  4. )
  5. )

Connected as SYSDBA or SYSKM to the root container we can now create the keystore:

  1. ADMINISTER KEY MANAGEMENT CREATE KEYSTORE
  2. ' /u01/app/oracle/product/12.1.0/dbhome_1/keystore/' IDENTIFIED BY "MyPasswd" ;

The wallet file is now created in the keystore directory:

  1. ls -l /u01/app/oracle/product/12.1.0/dbhome_1/keystore
  2. -rw-r--r--. 1 oracle oinstall 2408 Jun 8 13:09 ewallet.p12

Then we open the wallet:

  1. SQL>ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "MyPasswd" CONTAINER=ALL ;
  2. keystore altered.

The wallet is open, but holds no masterkey:

  1. SELECT WRL_PARAMETER, STATUS, WALLET_TYPE FROM V$ENCRYPTION_WALLET;
  2. WRL_PARAMETER
  3. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  4. STATUS WALLET_TYPE
  5. ------------------------------ --------------------
  6. /u01/app/oracle/product/12.1.0/dbhome_1/keystore/
  7. OPEN_NO_MASTER_KEY PASSWORD
  8.  

Add masterkey

So we also have to add a masterkey. This can be done for each container individually, or for all containers at once:

  1. ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "MyPasswd" WITH BACKUP USING 'masterkey-backup' CONTAINER=ALL;

A backup file for the wallet is created in the keystore location:

  1. ls -l /u01/app/oracle/product/12.1.0/dbhome_1/keystore
  2. -rw-r--r--. 1 oracle oinstall 2408 Jun 8 13:09 ewallet_2016060811093137_masterkey-backup.p12
  3. -rw-r--r--. 1 oracle oinstall 5328 Jun 8 13:09 ewallet.p12

The encryption can now be used. We can create an encrypted tablespace:

  1. CREATE tablespace test_encrypt
  2. datafile SIZE 5M
  3. ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);

The usage of the encrypted tablespace from SQL is transparent:

  1. CREATE TABLE test_encrypt
  2. tablespace test_encrypt
  3. AS SELECT 'I see you now' c1
  4. FROM dual ;
  5.  
  6. TABLE created.
  7.  
  8. SELECT * FROM test_encrypt ;
  9. C1
  10. -------------
  11. I see you now

When looking in the datafile, all data is encrypted so can no longer be read:

  1. strings /u01/oradata/CDB1/34C344078B9D6327E0535C6EA8C033E4/datafile/o1_mf_test_enc_colsfc00_.dbf | grep now

There is however a problem after the container database is restarted. If the pluggable database is opened, the wallet isn’t opened automatically. If we don’t open the wallet we get this error when connecting to the pluggable database:

  1. SQL> CONNECT system/passwd @pdb1
  2. ERROR:
  3. ORA-28365: wallet IS NOT OPEN

So, we first have to open the wallet from the root container:

  1. SQL> conn / AS sysdba
  2. Connected.
  3. SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "MyPasswd" CONTAINER=ALL ;
  4. keystore altered.
  5.  
  6. SQL> CONNECT system/passwd@pdb1
  7. Connected.
  8. SQL>

This can be solved by creating an auto login keystore. This can be done when connected to the root container.

  1. ADMINISTER KEY MANAGEMENT CREATE LOCAL AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u01/app/oracle/product/12.1.0/dbhome_1/keystore/' IDENTIFIED BY “MyPasswd” ;

This statements creates a file cwallet.sso in the keystore location.

After restarting the container database, and opening the pluggable databases, the keystore will now be opened in all PDB’s.

Types of keystores

For the Oracle database we can use different types of software keystores:

  • Password based keystores: must be opened using a password to access any data.
  • Auto login keystores: they are opened when the database instance is restarted. This is a transparent solution.
  • 'Local Auto Login' is the same as 'Auto Login', but the keystores cannot be  opened on another system then the one they are created on. This is an extra security, when the complete backup is stolen it won’t work if it’s restored on another system.
  • Hardware keystores.

Transparant usage

Oracle database 12c offers options to encrypt all data when moving your database to the cloud. Even if your complete database is stolen, the data can’t be read when looking in the datafiles, and the database can’t be opened if you don’t know the password of the keystore.

The usage is transparent, and the encryption is enabled default in the Oracle Cloud.

Bastiaan Bak
About the author Bastiaan Bak

DBA with over 15 years of experience. Experience in various branches, with several modules. Including: Oracle database, Oracle RAC, Oracle EBS and PL/SQL.

More posts by Bastiaan Bak
Comments
Reply