Top priority: Transparent Database Encryption
Published on: Author: Bastiaan Bak Category: IT development and operationsThe 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:
NAME TYPE VALUE ------------------------------------ ----------- ----------- 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:
SQL> SELECT tablespace_name, encrypted FROM dba_tablespaces; TABLESPACE_NAME ENC ------------------------------ --- SYSTEM NO SYSAUX NO TEMP NO USERS NO SP55260604_IAS_TEMP NO SP55260604_STB YES SP55260604_WLS YES SP55260604_MDS YES SP55260604_IAS_IAU YES SP55260604_IAS_OPSS YES
As well as any new tablespaces created:
SQL> CREATE tablespace TEST1 datafile SIZE 1M; Tablespace created. SQL> SELECT tablespace_name, encrypted FROM dba_tablespaces WHERE tablespace_name = 'TEST1'; TABLESPACE_NAME ENC ------------------------------ --- TEST1 YES
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:
SQL> SELECT street_address, city FROM locations 2 WHERE city = 'Seattle'; STREET_ADDRESS CITY ---------------------------------------- -------- 2004 Charade Rd Seattle
This data is readable from the unencrypted data file as follows:
[oracle@datafile]$ strings o1_mf_users_ckh2m31w_.dbf | grep -i Seattle Seattle
But, the data is not readable from the encrypted data file:
[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:
SQL> ALTER TABLE locations MODIFY (CITY encrypt); TABLE altered. SQL> DESC locations Name TYPE ------------------------------- -------------------- LOCATION_ID NUMBER(4) STREET_ADDRESS VARCHAR2(40) POSTAL_CODE VARCHAR2(12) CITY NOT NULL VARCHAR2(30) ENCRYPT STATE_PROVINCE VARCHAR2(25) COUNTRY_ID CHAR(2)
But the data is still readable from the data file:
[oracle@duck datafile]$ strings o1_mf_users_ckh2m31w_.dbf | grep -i Seattle Seattle
How come?
Well, existing data is not encrypted. Only new data is encrypted.
SQL> INSERT INTO locations VALUES (4000, 'De Bruyn Koppsstraat 9','2288 EC','Rijswijk','ZH','NL'); 1 ROW created. SQL> commit; Commit complete. SQL> SELECT street_address, city FROM locations WHERE city = 'Rijswijk'; STREET_ADDRESS CITY ---------------------------------------- ------------------------------ De Bruyn Koppsstraat 9 Rijswijk
This new data is not readable from the data file:
[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.
ENCRYPTION_WALLET_LOCATION= (SOURCE=(METHOD=FILE)(METHOD_DATA= (DIRECTORY=/u01/app/oracle/product/12.1.0/dbhome_1/keystore) ) )
Connected as SYSDBA or SYSKM to the root container we can now create the keystore:
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE ' /u01/app/oracle/product/12.1.0/dbhome_1/keystore/' IDENTIFIED BY "MyPasswd" ;
The wallet file is now created in the keystore directory:
ls -l /u01/app/oracle/product/12.1.0/dbhome_1/keystore -rw-r--r--. 1 oracle oinstall 2408 Jun 8 13:09 ewallet.p12
Then we open the wallet:
SQL>ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "MyPasswd" CONTAINER=ALL ; keystore altered.
The wallet is open, but holds no masterkey:
SELECT WRL_PARAMETER, STATUS, WALLET_TYPE FROM V$ENCRYPTION_WALLET; WRL_PARAMETER -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- STATUS WALLET_TYPE ------------------------------ -------------------- /u01/app/oracle/product/12.1.0/dbhome_1/keystore/ OPEN_NO_MASTER_KEY PASSWORD
Add masterkey
So we also have to add a masterkey. This can be done for each container individually, or for all containers at once:
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:
ls -l /u01/app/oracle/product/12.1.0/dbhome_1/keystore -rw-r--r--. 1 oracle oinstall 2408 Jun 8 13:09 ewallet_2016060811093137_masterkey-backup.p12 -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:
CREATE tablespace test_encrypt datafile SIZE 5M ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);
The usage of the encrypted tablespace from SQL is transparent:
CREATE TABLE test_encrypt tablespace test_encrypt AS SELECT 'I see you now' c1 FROM dual ; TABLE created. SELECT * FROM test_encrypt ; C1 ------------- I see you now
When looking in the datafile, all data is encrypted so can no longer be read:
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:
SQL> CONNECT system/passwd @pdb1 ERROR: ORA-28365: wallet IS NOT OPEN
So, we first have to open the wallet from the root container:
SQL> conn / AS sysdba Connected. SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "MyPasswd" CONTAINER=ALL ; keystore altered. SQL> CONNECT system/passwd@pdb1 Connected. SQL>
This can be solved by creating an auto login keystore. This can be done when connected to the root container.
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.