General Information
Library Note
Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com . Request a Workshop for
your organization today.
Note To run the demos on this page a wallet must exist and be open: See link at page bottom
Warning As of 25 September, 2007 TDE was considered part of the Advanced Security option for the Enterprise Edition only.
Before putting any of this code into production verify Oracle's licensing requirements are met.
Algorithms
3DES168
AES192
AES256
AES128 (default)
Dependencies
COL$ DBA_ENCRYPTED_COLUMNS
GV$WALLET
CDB_ENCRYPTED_COLUMNS
DBA_TABLES
TS$
CDB_TABLES
GV$ENCRYPTION_WALLET
Restrictions
Only the following data types can be encrypted
BINARY_FLOAT BINARY_DOUBLE CHAR DATE NCHAR
NUMBER NVARCHAR2 RAW TIMESTAMP VARCHAR2
You cannot use transparent data encryption to encrypt columns used in foreign key constraints. This is because every table has a unique column encryption key.
Index types other than B-tree
Range scan search through an index
External large objects (BFILE)
Materialized View Logs
Synchronous Change Data Capture
Transportable Tablespaces
Original import/export utilities
Warning: Before using TDE be sure you have read the documentation and understand backup and recovery, export / import, and other important considerations.
Tablespace Level
Create tablespace
CREATE TABLESPACE <tablespace_name>
conn sys@pdbdev as sysdbaENCRYPTION ENCRYPT );ENCRYPTION USING '3DES168' ENCRYPT );-- not listed in this view
Master Encryption Key Rekey
When you reset the master encryption rekey (see the REKEY Encrypted Column(s) Demo below) it resets the master encryption key at the tablespace level too.
Table Level
Default Encryption
CREATE TABLE <table_name>(
conn uwclass/uwclass@pdbdevENCRYPT );
-- from $ORACLE_HOME/rdbms/admin/dcore.bsq create table col$ /* column table */ 67108864 = Column is encrypted */ 536870912 = Column is encrypted without salt */
conn uwclass/uwclass@pdbdev
Dump the block
set serveroutput on5926 ;
SELECT * FROM tde;Start dump data blocks tsn: 7 file#:6 minblk 5926 maxblk 5926 5926 ) class: 1 ba: 0x107500005926 )Hex dump of block : st=0, typ_found=1Dump of memory from 0x10750000 to 0x10752000 Dan ]Morgan 4.B.Zm.>u]5926 ) class: 1 ba: 0x1A0C00005926 )Hex dump of block: st=0, typ_found=1Dump of memory from 0x1A0C0000 to 0x1A0C2000 Dan ]Morgan 4."....<]]5926 )Dump of memory from 0x0EAC8200 to 0x0EACA200 Dan ]Morgan 4.B.Zm.>u] 5926 maxblk 5926
TDE Variations
Encrypt Using
CREATE TABLE <table_name> (
conn uwclass/uwclass@pdbdev ENCRYPT USING '3DES168' );
Encrypt Identified By
CREATE TABLE <table_name> (
conn uwclass/uwclass@pdbdevENCRYPT IDENTIFIED BY "xIcf3T9u" ,ENCRYPT IDENTIFIED BY "xIcf3T9u" )-- open the file in the file system
SALT
CREATE TABLE <table_name> (
conn uwclass/uwclass@pdbdev ENCRYPT SALT );
NOSALT
CREATE TABLE <table_name> (
conn uwclass/uwclass@pdbdevENCRYPT NO SALT );
ALTER TABLE with Transparent Data Encryption
ENCRYPT
ALTER TABLE <table_name> MODIFY (<column_name> ENCRYPT>);
desc tde_saltENCRYPT );
SALT
ALTER TABLE <table_name> MODIFY (<column_name> ENCRYPT SALT>);
ALTER TABLE tde_salt MODIFY (last_name ENCRYPT SALT );
NOSALT
ALTER TABLE <table_name> MODIFY (<column_name> ENCRYPT NO SALT>);
ALTER TABLE tde_salt MODIFY (first_name ENCRYPT NO SALT );
REKEY Encrypted Column(s)
ALTER TABLE <table_name> REKEY [USING] <encryption_algorithm> <integrity_algorithm>;
ALTER TABLE tde_salt REKEY ;ALTER TABLE tde_salt REKEY USING 'AES256' 'SHA-1';
End Decryption
DECRYPT
ALTER TABLE <table_name> MODIFY (<column_name> DECRYPT>);
desc tde_saltDECRYPT );
Column Definition
Column Definition Demo
SQL> CREATE TABLE tde_test (VARCHAR2(1) ENCRYPT USING 'AES256');'A' );
