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
Using Oracle LogMiner to undo incorrectly committed changes TDE in Oracle 11g and Transparent Tablespace Encryption

Oracle 10g Transparent Data Encryption examples

Posted by Zahid on June 13, 2010.

Data theft is the worst nightmare of any organization, there is no doubt about it. Before oracle 10g release 2 this was done by saving encrypted data in datafiles and at the time of access the application was responsible to decrypt that data before showing it to the user. This involved a lots of extra coding at the application end. In oracle 10gR2 a new feature named Transparent Data Encryption (TDE) was introduced to provide convenience in this regard.

TDE makes sure that the data is stored in encrypted form and when the data is accessed, it automatically decrypt it and it all happens transparently so it does not require even a single line of code at the application or database end.

How Transparent Data Encryption (TDE) works?

All you have to do is tell oracle about the encrypted column, and when you do that oracle generates an encryption key for that table and stores it in the data dictionary. This key is also encrypted by a master key which is stored outside of the database in a secure location called Wallet.
When a user enters data into the column defined as encrypted, Oracle Database 10g gets the master key from the wallet, decrypts the encryption key for that table from the data dictionary, uses that encryption key on the input value, and stores the encrypted data in the database.
When a user selects encrypted columns, Oracle Database 10g transparently retrieves the encrypted table key from the data dictionary, fetches the master key from the wallet, and decrypts the table key. Then the database decrypts the encrypted data on the disk and returns the clear text to the user.

Since the data is stored in encrypted format, it is encrypted everywhere like datafiles, archive redo log files and backupsets. If any of these are stolen the data will be useless without the Wallet master key which doesn't exist in the database at all, and even if the wallet is also stolen it cannot be opened without a password.

How to setup Transparent Data Encryption (TDE)?

First of all we need to define wallet location. Open your sqlnet.ora file and define the wallet location which can be any directory at the database server.

$ cd /d01/apps/oracle/network/admin 
$ mkdir tde_wallet
$ vi sqlnet.ora 

ENCRYPTION_WALLET_LOCATION =
 (SOURCE=
   (METHOD=file)
     (METHOD_DATA=
       (DIRECTORY=/d01/apps/oracle/network/admin/tde_wallet)))

Create a Secure Wallet to hold the Master Encryption Key:

Next step is to create a wallet, set a password and then open it.

$ sqlplus / as sysdba

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

System altered.

This creates a wallet at the location defined in the sqlnet.ora, sets the password for the wallet for TDE to retrieve the master key for encryption of table keys used to encrypt values in the tables.

Data with and without TDE:

create tablespace ts_tde
datafile '/d01/apps/oradata/oraxpo/ts_tde01.dbf'
size 20m autoextend on next 5m
extent management local
segment space management auto;

create user tde_test 
identified by test 
default tablespace ts_tde
quota unlimited on ts_tde;

grant connect,resource to tde_test;

We have a user named tde_test with a default tablespace ts_tde whose datafile is /d01/apps/oradata/oraxpo/ts_tde01.dbf

conn tde_test/test
create table tde (sensitive_data varchar2(50));
insert into tde values ('This is very sensitive data');
commit;

select * from tde;

SENSITIVE_DATA
--------------------------------------------------
This is very sensitive data

SQL> conn / as sysdba
Connected.
/* 
  Flush the buffer_cache so that blocks in DB Buffer cache go to the datafile.
*/
SQL> alter system flush buffer_cache;

System altered.

SQL> exit

strings command in Linux prints all printable characters in a binary file to the screen. We will use grep with strings to verify our sensitive data is visible or not.

$ strings ts_tde01.dbf | grep sensitive
This is very sensitive data
$

It can be seen that the sensitive data is written in the data files as clear text. Since the data is in clear text format in the datafile so it is in the archived redo log files, backup sets etc. Any one who has access to your backup tapes can easily steal your backups, restore it somewhere and see all of your data.

Now we create a table with a column encrypted transparently.

$ sqlplus tde_test/test

SQL> drop table tde purge;

Table dropped.

/* 
 There are 4 encryption algorithms available for TDE.
   3DES168
   AES128
   AES192
   AES256
 AES192 is the default.
*/

create table tde (sensitive_data varchar2(50) encrypt using '3DES168');
insert into tde values ('This is very sensitive data');
commit;

select * from tde;

SENSITIVE_DATA
-------------------------------
This is very sensitive data

/*
  As you see the data entered in an encrypted column is accessible in clear text.
  And that is why this encryption is called Transparent Data Encryption.
*/

SQL> conn / as sysdba
Connected.

SQL> alter system flush buffer_cache;

System altered.

SQL> select table_name , column_name , ENCRYPTION_ALG , SALT
  2  from dba_encrypted_columns;

TABLE_NAME     COLUMN_NAME       ENCRYPTION_ALG                SAL
-------------- ----------------- ----------------------------- ---
TDE            SENSITIVE_DATA    3 Key Triple DES 168 bits key YES

/*
 DBA_ENCRYPTED_COLUMNS gives you a list of all encrypted columns in the database.
*/

SQL>   
SQL> exit

Lets see what do we get out of strings command.

$ strings ts_tde01.dbf | grep sensitive
$ 

Nothing, the data is now in encrypted format and not in clear text. A number figure 5000 is may be stored as @^*/-% or something like that.

SALT option with Transparent Data Encryption (TDE):

Lets discuss a scenario where there are 100 employees in an organization with a salary of 5000. salary is an encrypted column, so it will contain the value "@^*/-%" a 100 times in the data files making it vulnerable and guessable for the crackers. That is where the SALT option with the encryption comes into play. By default the encryption is done with SALT. Refer to the query we done above on DBA_ENCRYPTED_COLUMNS where you can see the SALT=YES.

What it does is, when a values is inserted in an encrypted column oracle appends a random string with the value before encrypting it. In result the encrypted value for 5000 will differ from the encrypted value of another 5000 salary. e.g the encrypted values with SALT may look like this:

 SALARY SALARY+SALT   ENCRYPTED VALUE
------- ------------- ----------------
   5000 5000abcz      @@_^/%%
   5000 5000xuyq      ^_%$#@@
   5000 5000vbgh      ~`@@@%$

Each encrypted value for the same text will be different at all. At the time of decryption oracle knows what it added to the original value and removes that string from original value before showing it to the user.

$ sqlplus tde_test/test

/*
 Since the default encryption is done with SALT so our sensitive_data column is encrypted 
 with salt.
 A column encrypted with SALT can not has an index on it. The reason is simple, the idea 
 behind index is to keep same value together to make data retrieval fast but with SALT even 
 one single value 5000 will be scattered all over the index since the encrypted values are
 different. So the index will never be used as it will be faster to scan the table and get 
 the data.
 Primary Key, Unique Key and Foreign key columns cannot be encrypted with SALT option.
*/

SQL> create index tde_sensitive_idx on tde (sensitive_data);
on tde (sensitive_data)
        *
ERROR at line 2:
ORA-28338: cannot encrypt indexed column(s) with salt

SQL> alter table tde modify (sensitive_data encrypt no salt);

Table altered.

/*
  Now our sensitive data column is changed from SALT to NO SALT.
*/

SQL> create index tde_sensitive_idx on tde (sensitive_data);

Index created.

/*
  And we can have an index on it now.
*/

SQL>

Indexes on encrypted columns; Performance considerations:

Index on an encrypted column also requires a few considerations. When an index encrypted column is used in the where clause of a query with equality operator the index is used to scan the table and data retrieval is fast. Whereas, the range scans are concerned as LIKE operator the index doesn't do well and is not used for the data retrieval. Lets take some sample data to understand why?

ACC_FIRST_NAME  ENCRYPTED_VALUE
--------------- ---------------
John		/*^@@_
Joseph		@@^~`%$$
Jonathen	**^%@@#~`
John		/*^@@_
John		/*^@@_

The acc_first_name is an encrypted column with NO SALT and there is an index on it. Think about the following query:

SELECT * FROM accounts WHERE acc_first_name = 'John';

Since all encrypted values for "John" will be same so they are stored together in the index and it will be useful to scan the index for this query. But as far as the following query is concerned:

SELECT * FROM accounts WHERE acc_first_name LIKE 'Jo%';

If the index would contain clear text, this query is a good candidate for index scan since all values starting with 'Jo' will be together in the index. But if the index contains encrypted values, they will not be together as encrypted value for John will be a lot different from encrypted value of Joseph and it will be worthless to perform a range scan on the index. So the index will not be used for a range scan on an encrypted column.

$ sqlplus scott/tiger

CREATE TABLE test_tde
AS
SELECT * FROM all_objects;

ALTER TABLE test_tde 
MODIFY (object_name ENCRYPT NO SALT);

CREATE INDEX test_tde_idx
ON TEST_TDE (object_name);

SET AUTOTRACE ON EXPLAIN;
/* 
 This will turn on autotrace and will show execution plan of each query we execute.
 Lets run a SELECT statement on table test_tde using "=" on column object_name.
*/
SELECT object_name , object_type
FROM test_tde
WHERE object_name = 'CURSORMANAGERINTERFACE';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
CURSORMANAGERINTERFACE         PACKAGE
CURSORMANAGERINTERFACE         SYNONYM


Execution Plan
----------------------------------------------------------
Plan hash value: 3162070991

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |   573 | 35526 |   103   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_TDE     |   573 | 35526 |   103   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | TEST_TDE_IDX |   229 |       |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_NAME"='CURSORMANAGERINTERFACE')

/*
 Lets now run a SELECT statement using LIKE operator on column opbject_name.
*/

SELECT object_name , object_type
FROM test_tde
WHERE object_name LIKE 'CU%';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
CURVE                          SYNONYM
CURVEPOLYGON                   SYNONYM
CURSORMANAGERINTERFACE         PACKAGE
CURSORMANAGERINTERFACE         SYNONYM


Execution Plan
----------------------------------------------------------
Plan hash value: 2894826712

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     8 |   496 |   159   (3)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TEST_TDE |     8 |   496 |   159   (3)| 00:00:02 |
------------------------------------------------------------------------------

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

   1 - filter(INTERNAL_FUNCTION("OBJECT_NAME") LIKE 'CU%')

Note
-----
   - dynamic sampling used for this statement

As you see above when we used "equal to operator (=)", the index TEST_TDE_IDX was used but with LIKE operator it was not used.

Open and Close the wallet:

Whenever you restart your database, you also need to open the wallet to make use of Transparent Data Encryption.

$ sqlplus / as sysdba

/*
  The database was restarted so the wallet is closed at this moment.
  Any query on encrypted columns will throw an error.
*/

SQL> select * from tde_test.tde;
select * from tde_test.tde
                       *
ERROR at line 1:
ORA-28365: wallet is not open

/*
  Let's open the wallet and notice you will need a password to open the wallet.
*/

SQL> alter system set encryption wallet open authenticated by "ImOracle";

System altered.

SQL> select * from tde_test.tde;

SENSITIVE_DATA
--------------------------------------------------
This is very sensitive

/*
  This is how we close the wallet.
*/

SQL> alter system set encryption wallet close;

System altered.

SQL> select * from tde_test.tde;
select * from tde_test.tde
                       *
ERROR at line 1:
ORA-28365: wallet is not open

Using DataPump with the encrypted columns:

When a table with encrypted columns is exported using Data Pump Export utility (EXPDP) the data is exported in the dump file in the clear text format. Lets have a look at it.

$ sqlplus / as sysdba

SQL> grant read , write on directory data_pump_dir to tde_test;

Grant succeeded.

/*
 If user tde_test wants to crate/read dump of export in DATA_PUMP_DIR it will need 
 read/write permissions on the directory.
*/

SQL> exit

$ expdp tde_test/test directory=data_pump_dir dumpfile=tde.dmp tables=tde

Export: Release 10.2.0.1.0 - Production on Saturday, 12 June, 2010 18:06:13

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "TDE_TEST"."SYS_EXPORT_TABLE_01":  tde_test/******** directory=data_pump_dir 
dumpfile=tde.dmp tables=tde 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
. . exported "TDE_TEST"."TDE"                            4.953 KB       1 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "TDE_TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TDE_TEST.SYS_EXPORT_TABLE_01 is:
  /d01/apps/oracle/rdbms/log/tde.dmp
Job "TDE_TEST"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 18:06:18

Lets use the strings command on the dump file and see if we can see our sensitive data in it.

$ strings /d01/apps/oracle/rdbms/log/tde.dmp | grep sensitive
This is very sensitive
$

There you go, the encrypted column's data is visible in clear text and you also may notice a warning in the export output as ORA-39173: Encrypted data has been stored unencrypted in dump file set.

To keep the sensitive data as encrypted in the dump file use the encryption_password parameter while using expdp and provide a password. The export will be done and data in the export dump file will be in encrypted format.

$ rm -fr /d01/apps/oracle/rdbms/log/tde.dmp
$ expdp tde_test/test encryption_password=secure \
  directory=data_pump_dir dumpfile=tde.dmp tables=tde

Export: Release 10.2.0.1.0 - Production on Saturday, 12 June, 2010 18:10:30

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "TDE_TEST"."SYS_EXPORT_TABLE_01":  tde_test/******** encryption_password=********* 
directory=data_pump_dir dumpfile=tde.dmp tables=tde 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
. . exported "TDE_TEST"."TDE"                            5.007 KB       1 rows
Master table "TDE_TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TDE_TEST.SYS_EXPORT_TABLE_01 is:
  /d01/apps/oracle/rdbms/log/tde.dmp
Job "TDE_TEST"."SYS_EXPORT_TABLE_01" successfully completed at 18:10:34

Please notice in the output the encryption_password is displayed as "********".
Lets see what do we have in the dumpfile generated.

$ strings /d01/apps/oracle/rdbms/log/tde.dmp | grep sensitive
$ 

Nothing as you see the result for strings command above.

Now when this dumpfile is required to be imported the encryption_password must be specified.

$ impdp tde_test/test directory=data_pump_dir dumpfile=tde.dmp table_exists_action=replace

Import: Release 10.2.0.1.0 - Production on Saturday, 12 June, 2010 18:14:00

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39002: invalid operation
ORA-39174: Encryption password must be supplied.

We do the same import operation but with the encryption_password.

$ impdp tde_test/test encryption_password=secure \
  directory=data_pump_dir dumpfile=tde.dmp table_exists_action=replace

Import: Release 10.2.0.1.0 - Production on Saturday, 12 June, 2010 18:14:20

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "TDE_TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TDE_TEST"."SYS_IMPORT_FULL_01":  tde_test/******** encryption_password=********* 
directory=data_pump_dir dumpfile=tde.dmp table_exists_action=replace 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TDE_TEST"."TDE"                            5.007 KB       1 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Job "TDE_TEST"."SYS_IMPORT_FULL_01" successfully completed at 18:14:26

$

See also:
TDE in Oracle 11g and Transparent Tablespace Encryption
Encrypt or Decrypt sensitive data using PLSQL - DBMS_CRYPTO