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
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”;