Oracle Advanced Transparent Data Encryption (TDE) in Oracle 11g Database Release 2

Oracle Advanced Transparent Data Encryption (TDE) is used for encrypting sensitive data on storage data for Oracle database. The encryption is totally transparent for application. TDE was introduced in 10G enabling encryption of a columns in 11G it was enhanced with encryption for tablespaces.

Definitions

  • Master encryption key – encrypts other encryption keys table key, tablespace key
  • Unified master key – extra key generated with the first re-key operation, easy for change/regeneration
  • Table key – used to encrypt columns in a table. Introduced in 10G. The key is stored in data dictionary and is encrypted by master encryption key.
  • Tablespace key – used to encrypt a tablespace. The key is stored in each datafile of encrypted tablespace and is encrypted by master encryption key.
  • Wallet – A PKCS#12 file outside of the database that stores master key. The file is encrypted based on password-based encryption defined in PKCS#5.
  • Advanced Encryption Standard (AES) – symmetric cipher algorithm provides three key lengths: 256, 192 and 128 bits.

Wallet setup

Before encryption can be used wallet must be created to store password for encrypting master key. Wallet itself is encrypted file that can be stored in one of two locations:

  • $ORACLE_BASE/admin/{DB_NAME|DB_UNIQUE_NAME}/wallet – default directory for wallet

To create wallet execute following commands. I present it for database instance name ORA11G.

First you need to create directory to store wallet

mkdir $ORACLE_BASE/admin/ORA11G/wallet

connect as user SYS and set master key password for wallet

sqlplus / as sysdba
SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "master password";

System altered.

current status of the wallet can be found in view V$ENCRYPTION_WALLET

SELECT wrl_type, wrl_parameter, status 
  FROM v$encryption_wallet;

WRL_TYPE WRL_PARAMETER                         STATUS
-------- ------------------------------------- ------
file     /ora01/app/oracle/admin/ORA11G/wallet OPEN

check file creation and reduce initial file permission that are determined by ‘umask’ for ‘oracle’ user

chmod 600 $ORACLE_BASE/admin/ORA11G/wallet/ewallet.p12
[oracle@oel6 wallet]$ ls -la
total 12
drwxr-xr-x. 2 oracle oinstall 4096 Sep  1 16:26 .
drwxr-x---. 7 oracle oinstall 4096 Sep  1 16:24 ..
-rw-------. 1 oracle oinstall 2848 Sep  1 16:26 ewallet.p12
  • ENCRYPTION_WALLET_LOCATION parameter in sqlnet.ora that defines where wallet is situated. It’s much better option then first one.

create directory in /etc/ directory as user root

cd /etc
mkdir -p ORACLE/WALLETS/ORA11G
chown oracle:oinstall ORACLE
chmod -R 700 ORACLE

modify sqlnet.ora and add following entry

ENCRYPTION_WALLET_LOCATION =
(SOURCE = (METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /etc/ORACLE/WALLETS/$ORACLE_UNQNAME/)))

create wallet file and master key

sqlplus / as sysdba
SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "master key";

Wallet change master key

To change password execute following command

ALTER SYSTEM SET ENCRYPTION KEY "oracle" IDENTIFIED BY "master key";

Wallet open close

To close wallet execute following command

ALTER SYSTEM SET WALLET CLOSE IDENTIFIED BY "oracle";

To open wallet execute following command

ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY "oracle";

Wallet protection and backup

Initial file permission usually are too high and are determined by ‘umask’ for ‘oracle’ user. It’s recommended to decrease it.

chmod 600 ewallet.p12

To avoid accidental deletion of wallet file it’s recommended to protect it. Any delete, write operation (by root, other users) will fail .

chattr +i ewallet.p12

in case of re-key operation, the “immutable” bit must be reset

chattr -i ewallet.p12

Always backup the wallet associated with the master key

  • immediately after it’s created
  • whenever the master key is changed
  • before changing the wallet password

Encryption levels

There are two levels of encryption that can be used

  • column
  • tablespace

both methods has pros and cons

Column TDE Tablespace TDE
location of data to encrypt is known location of data to encrypt is unknown
less than 5% of all columns needs to be encrypted most of columns needs to be encrypted
indexes over columns are b-tree indexes are functional indexes
range scans are not performed over encrypted columns range scans are very popular over encrypted data
increase storage by 1 to 52 bytes per encrypted value no storage increase acceptable
performance depends on how often encrypted data are selected, updated, size of encrypted data constant performance impact below 10%
hardware benefit from crypto acceleration
benefits of encryption and compression in the same time

Column encryption

Architecture – Master key (called MK later) is stored in wallet and table keys (TK) are stored in oracle dictionary. MK encrypts TK and TK encrypts/decrypts tables data in columns.

NOTE – because of encrypted columns data in buffer cache are encrypted

Simple example how to encrypt column data.

CREATE TABLE test_tbl_tc
(
  id1  VARCHAR2(20) ENCRYPT,
  id2  VARCHAR2(20) ENCRYPT NO SALT
);

INSERT INTO test_tbl_tc VALUES ('No index data', 'Index data');
COMMIT;

SELECT * FROM test_tbl_tc;

ID1
--------------
Simple data

NOTE – Encrypted columns created without NO SALT can’t be indexed

CREATE INDEX test_tbl_tc_idx1 ON test_tbl_tc(id1);

SQL Error: ORA-28338: Column(s) cannot be both indexed 
and encrypted with salt

CREATE INDEX test_tbl_tc_idx2 ON test_tbl_tc(id2);

Index TEST_TBL_EN_IDX2 created.

Views DBA|ALL|USERS_ENCRYPTED_COLUMNS show encrypted columns in the database

SELECT * 
  FROM user_encrypted_columns
 WHERE table_name='TEST_TBL_TC';

TABLE_NAME   COLUMN_NAME ENCRYPTION_ALG   SAL INTEGRITY_AL
------------ ----------- ---------------- --- ------------
TEST_TBL_TC  ID1         AES 192 bits key YES SHA-1       
TEST_TBL_TC  ID2         AES 192 bits key NO  SHA-1

Once wallet is closed data can’t be read

ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY "oracle";

SELECT * FROM test_tbl_tc;
ORA-28365: wallet is not open

Tablespace encryption

Architecture – Master key (called MK later) is stored in wallet and tablespace keys (TK) are stored in headers of encrypted data files. MK encrypts TK and TK encrypts/decrypts in data files.

NOTE – data in buffer cache are decrypted during scans and encrypted during writes. According to Oracle TDE tablespace is extra 5-8%.

To create encrypted tablespace execute following command.

CREATE TABLESPACE tblsp_enc
DATAFILE '/ora01/app/oracle/oradata/ORA11G/te_01.dbf' SIZE 100m
ENCRYPTION USING 'AES128' 
DEFAULT STORAGE (ENCRYPT);

create table on encrypted tablespace and insert data

CREATE TABLE test_tbl_te
(
  id1  VARCHAR2(20)
) 
TABLESPACE tblsp_enc;

INSERT INTO test_tbl_te VALUES('Simple_text');
COMMIT;

column can be indexed without limits

CREATE INDEX test_tbl_te_idx1 ON test_tbl_te(id1);

View V$ENCRYPTED_TABLESPACES shows details for encrypted tablespaces.

NOTE – Enforce checkpoint to write changed data blocks from buffer cache into datafiles.

ALTER SYSTEM CHECKPOINT;

SELECT 
    t.name, et.encryptionalg, 
    et.blocks_decrypted, et.blocks_decrypted 
  FROM 
    v$encrypted_tablespaces et, v$tablespace t
 WHERE 
    et.ts# = t.ts#;

NAME       ENCRYPTIONALG BLOCKS_DECRYPTED BLOCKS_ENCRYPTED
---------- ------------- ---------------- ----------------
TBLSP_ENC  AES128                       0                5

Verify tablespace datafile that is encrypted on UNIX level. It shouldn’t return anything.

strings /ora01/app/oracle/oradata/ORA11G/te_01.dbf | grep Simple_text

Comparison TDE columns versus tablespace

Range scans for indexed columns can cause performance issues for TDE columns.

for TDE tablespaces all is fine

EXPLAIN PLAN FOR
SELECT *
  FROM test_tbl_et
 WHERE id1 LIKE 'S%';

SELECT * FROM TABLE(dbms_xplan.display(format=>'BASIC'));

Plan hash value: 1814360016
 
---------------------------------------------
| Id  | Operation        | Name             |
---------------------------------------------
|   0 | SELECT STATEMENT |                  |
|   1 |  INDEX RANGE SCAN| TEST_TBL_ET_IDX1 |
---------------------------------------------

for TDE columns it’s problem internal function is used

EXPLAIN PLAN FOR
SELECT /*+ INDEX(a) */ id2
  FROM test_tbl_tc A
 WHERE a.id2 LIKE 'S%';

Plan hash value: 2699224853
 
---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     1 |    46 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_TBL_TC |     1 |    46 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(INTERNAL_FUNCTION("A"."ID2") LIKE 'S%')
 
Note
-----
   - dynamic sampling used for this statement (level=2)

Have a fun 🙂

Tomasz

 

One thought on “Oracle Advanced Transparent Data Encryption (TDE) in Oracle 11g Database Release 2

  1. Tomek,

    As always great job.
    One suggestion from my side – Ive just configured encryption on my 12c instance and we have an option to creat autologin wallet using :

    sqlplus sys as sysdba

    administer key management create AUTO_LOGIN keystore from keystore ‘path to wallet’ identified by “secret password”;

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.