Zahid Karim
Recent Posts
1.Draw plus and cross sign into PDF via PLSQL using PL_FPDF
2.Draw Triangle directly into PDF via PLSQL using PL_FPDF
3.Write fast and efficient PLSQL - DBMS_PROFILER
4.Fix physically corrupt data blocks using PLSQL - DBMS_REPAIR
5.Encrypt or Decrypt sensitive data using PLSQL - DBMS_CRYPTO
Find me on
Oracle 10g Transparent Data Encryption examples Oracle 11g Using LogMiner to analyze redo log files

TDE in Oracle 11g and Transparent Tablespace Encryption

Posted by Zahid on June 19, 2010.

Column based transparent data encryption that was introduced in Oracle 10g, is continued in oracle 11g along with a new feature called transparent tablespace encryption. You can create a tablespace transparently encrypted i.e. all data it contains is stored in encrypted format at the block level. Which means the whole tablespace data is completely hidden from thief.

Transparent tablespace encryption also uses Oracle Encryption Wallet just like column based transparent encryption to store the tablespace encryption key.

Create oracle encryption wallet:

create a directory on the oracle server.

$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin
$ mkdir tde_wallet

Open the sqlnet.ora and add these lines to set the location of the wallet.

ENCRYPTION_WALLET_LOCATION=
  (SOURCE=(METHOD=FILE)(METHOD_DATA=
    (DIRECTORY=/u01/app/oracle/product/11.2.0/db_1/network/admin/tde_wallet)))

Set the master encryption key in the wallet.

$ sqlplus / as sysdba

SQL> alter system set encryption key authenticated by "ora11g";

System altered.

This creates a wallet at the location defined in the sqlnet.ora, sets the password for the wallet and set the master encryption key that will be used to decrypt the tablespace encryption keys stored in the database for every encrypted tablespace.

How Transparent Tablespace Encryption works:

CREATE TABLESPACE ts_tde
DATAFILE '/u01/app/oracle/oradata/ora11g/ts_tde01.dbf' 
	 SIZE 20m ATOEXTEND ON NEXT 5m
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ENCRYPTION USING '3DES168'
DEFAULT STORAGE (ENCRYPT);

At the time of tablespace creation specify the encryption and default storage clause.
Define the encryption algorithem as " using 'algorithem' " along with the encryption clause.
You can use the following algorithems while creating an encrypted tablespace.

AES128
AES192
AED256
3DES168
If you don't specify any algorithem with the encryption clause it will use AES128 as default.

See below how to get information about transparently encrypted tablespaces from oracle dictionary.

SELECT tablespace_name , encrypted 
FROM dba_tablespaces;

TABLESPACE_NAME                ENC
------------------------------ ---
SYSTEM                         NO
SYSAUX                         NO
UNDOTBS1                       NO
TEMP                           NO
USERS                          NO
EXAMPLE                        NO
TS_TDE                         YES

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

NAME                           ENCRYPT ENC
------------------------------ ------- ---
TS_TDE                         3DES168 YES

Make sure user SCOTT has enough quota to write on the new encrypted tablespace.

SQL> alter user scott quota unlimited on ts_tde;

User altered.

SQL> exit;

Login with user scott and create a table in the encrypted tablespace.

$sqlplus scott/tiger

CREATE TABLE tbs_tde TABLESPACE ts_tde
AS
SELECT * FROM all_objects;

CREATE INDEX tbs_tde_idx
ON tbs_tde (object_name);

Performance improvements with Transparent Tablespace Encryption:

Column based transparent encryption has a performance limitation on using an index while doing range scans e.g. using LIKE operator. The index on an encrypted column is never used for range scans. This limitation is removed in transarent tablespace encryption because data is decrypted before being placed into the buffer cache, allowing index range scans.

Lets examine the execution plan of the query with LIKE operator on our table in the encrypted tablespace to see if an index is being used.

SET AUTOTRACE ON EXPLAIN
/*
  Turn on AUTOTRACE to trace execution plan of the queries.
*/                                                       
SELECT object_name , object_type , owner 
FROM tbs_tde 
WHERE object_name LIKE 'CU%';

OBJECT_NAME      OBJECT_TYPE         OWNERCURVE
---------------- ------------------- ---------------
CUBE_TABLE       FUNCTION            SYS
CUBE_TABLE       SYNONYM             PUBLIC
CURVE            SYNONYM             PUBLIC
CURVEPOLYGON     SYNONYM             PUBLIC


Execution Plan
----------------------------------------------------------
Plan hash value: 2300272712

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     4 |   180 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TBS_TDE     |     4 |   180 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TBS_TDE_IDX |     4 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_NAME" LIKE 'CU%')
       filter("OBJECT_NAME" LIKE 'CU%')

Note
-----
   - dynamic sampling used for this statement (level=2)

The execution plan shows that the index TBS_TDE_IDX on table TBS_TDE was used for a range scan done by LIKE operator.

And also we can transparently see the encrypted data returned by the query. Lets now verify if we can see the same data in the datafile as a clear text.

$ cd /u01/app/oracle/oradata/ora11g/
$ strings ts_tde01.dbf | grep CUBE_TABLE
$ 
$ strings ts_tde01.dbf | grep CURVE
$ 
$ strings ts_tde01.dbf | grep CURVEPOLYGON
$ 

The strings commands on the datafile ts_tde01.dbf didn't find the data CUBE_TABLE, CURVE and CURVEPOLYGON in clear text as it is stored in encrypted format inside this datafile.

Encrypting an existing tablespace:

SQL> alter tablespace users
  2  encryption using 'AES256'
  3  default storage (encrypt);
encryption using 'AES256'
*
ERROR at line 2:
ORA-02142: missing or invalid ALTER TABLESPACE option

An existing tablespace cannot be encrypted. If you need your existing data to be encrypted, create a new encrypted tablespace and move your data to it by using:

ALTER TABLE table_name MOVE encrypted_tablespace_name;
OR
CREATE TABLE table_in_encrypted_form
TABLESPACE encrypted_tablespace_name
AS
SELECT * FROM table_in_clear_text_form;
OR any others means you have, to move data from one tablespace to the other.

See also:
Oracle 10g Transparent Data Encryption examples
Encrypt or Decrypt sensitive data using PLSQL - DBMS_CRYPTO