Terug naar overzicht

Top priority: Transparent Database Encryption

Gepubliceerd: 13 juni 2016 Auteur: Bastiaan Bak Categorie: Beheerdiensten

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:

		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.

Over auteur Bastiaan Bak

DBA met ruim 15 jaar ervaring. Ervaring in verschillende branches, met verschillende modules. Onder meer: Oracle Database, Oracle RAC, Oracle EBS en PL/SQL.

Meer posts van Bastiaan Bak

Reacties