Database Security: Transparent Data Encryption

Data in the database is secured by using authentication, authorization and auditing mechanism, but the information stored in the datafiles can be read using any hex-editor tools or other OS level utilities, which is causing threat to sensitive  data. To overcome this limitation TDE is used to secure sensitive data, in database columns, stored in the datafiles. TDE is key-based access control mechanism. For each table, having encrypted column in it, an encryption key is generated which is again encrypted by database server’s master key. The database server’s master key is generated at the time when Wallet is ceated. The master key is stored outside oracle database in a Wallet and the keys for all tables containing encrypted columns are stored in dictionary tables in database.

How to setup TDE:

To use TDE one must have “ALTER SYSTEM” privilege and a valid password for oracle Wallet. 1. Make sure that listener and database is up and running and compatibility level of database is set to 10.2 . Enabling Transparent Data Encryption. Issue following command:

<span style="font-size: small;">S</span>QL &gt; alter system set encryption key identified by "password";

If the password is not given in “” then it will be taken in capital letters. This command will do the following things: – Create a Wallet file (ewallet.p12) – Open the Wallet. – Generate databse server’s master encryption key. By default, above command will create ewallet.p12 file under location $ORACLE_BASE/admin/SID/WALLET directory/folder. So it is mandatory to create a directory/folder with name as “WALLET” under $ORACLE_BASE/admin/SID, if it is not created then following error will occur while creating  Wallet file:

SQL> alter system set encryption key identified by "oracle";
*
ERROR at line 1:
ORA-28368: cannot auto-create wallet

The default location is helpful if someone wants different Wallets for different instances running under same ORACLE_HOME. We can manually specify location of Wallet file by modifying $ORACLE_HOME/network/admin/sqlnet.ora file as:

ENCRYPTION_WALLET_LOCATION=
(SOURCE=(METHOD=FILE)(METHOD_DATA=
(DIRECTORY=C:\Oracle\product\10.2.0\db_1\wallet)))

Any valid existing directory can be specified here.

Why Wallet is needed?

The encryption keys are stored in the wallet.Database needs to load master key and column encryption keys into memory from the Wallet before it can start encryption/decryption of columns. Wallet remains open until we shutdown the database. It will be closed after the database is shutdown and needs to be started again after database startup as:

<span style="font-size: small;"><span style="font-family: arial,helvetica,sans-serif;">SQL &gt; alter system set encryption wallet open identified by "password";</span></span>

If the wallet remains closed, we will not be able to access the table which contains encrypted columns and will get following error:

ORA-28365: wallet is not open

— Now TDE can be used for this database.

Example:

SQL > conn scott/tiger

SQL > SQL> l
1  create table sood(
2  first_name varchar2(10),
3  last_name varchar2(10),
4  empid number,
5* salary number ENCRYPT);

SQL> create user saurabh identified by oracle;

User created.

SQL> grant create session to saurabh;

Grant succeeded.

Insert data into scott.sood as:

SQL> insert into sood values ('jonh','wallter',1,30000);

1 row created.

SQL> insert into sood values ('Michael','Clark',2,20000);

1 row created.

SQL> insert into sood values ('Ricky','Ponting',3,10000);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from sood;

FIRST_NAME LAST_NAME       EMPID     SALARY
---------- ---------- ---------- ----------
jonh       wallter             1      30000
Michael    Clark               2      20000
Ricky      Ponting             3      10000

SQL> show user

USER is “SCOTT”

SQL> grant select on sood to saurabh;

Grant succeeded.

As user saurabh has select privilige on sood(table containing encrypted column), it will be able to retrive (decrypt) data without performing any additional step.

SQL> conn saurabh/oracle

Connected.

SQL> select * from scott.sood;

FIRST_NAME LAST_NAME       EMPID     SALARY
---------- ---------- ---------- ----------
jonh       wallter             1      30000
Michael    Clark               2      20000
Ricky      Ponting             3      10000

If we mine the redo logs, the logminer will show Salary column as “Unsupported Type” under SQL_REDO, because the data was encrypted just before it was written to disk.

Export/Import and TDE:

1. exp/imp will not work with tables having encrypted columns, following error will be given:

<span style="font-size: small;">-</span>-----------------------------------------------------------
C:\Documents and Settings\sausood&gt;exp scott/tiger tables=sood
Export: Release 10.2.0.1.0 - Production on Fri Dec 19 13:05:55 2008

Copyright (c) 1982, 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
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
EXP-00107: Feature (COLUMN ENCRYPTION) of column SALARY in table SCOTT.SOOD is not supported. The table will not be exported.
Export terminated successfully with warnings.
--------------------------------------------------------------

TDE is only compatible with DATAPUMP export/import.

2. Backing up tables containing encrypted columns:

SQL> select * from DBA_ENCRYPTED_COLUMNS;

OWNER  TABLE_NAM COLUMN_NAM ENCRYPTION_ALG                SAL

—— ——— ———- —————————– —

SCOTT  SOOD      SALARY     AES 192 bits key              YES

Use the following command to export TDE enabled tables.

$ expdp scott/tiger directory=dir dumpfile=exptde.dmp tables=sood <strong>encryption_password</strong>="oracle"

Parameter ENCRYPTION_PASSWORD allows encrypted columns to be encrypted while exporting these.The password has nothing to deal with encryption keys, it is to used while importing the tables. If the ENCRYPTION_PASSWORD is not used the the data is exported in clear text and a warning message is displayed at the time of datapump export:

"<strong>ORA-39173</strong>: Encrypted data has been stored unencrypted in dump file set. "

While importing it is mandatory to have the same wallet file for database, otherwise following error will occur:

"<strong>ORA-28362</strong>: master key not found"

Transparent Data Encryption FAQ

Tags: 10goracleSecurity