|
| Steps to Setup Oracle 11g TDE (Transparent Data Encryption) |
| Thursday, May 6, 2010 |
Introduction
The Transparent Data Encryption (TDE) feature introduced in Oracle 10g Database Release 2 allows sensitive data to be encrypted within the datafiles to prevent access to it from the operating system.
Oracle Database 11g encrypts data using a master key, which is stored in a secure location called a wallet, which is a file on the database server. Table keys are placed in the data dictionary. Oracle Database 11g generates a single encrypted table key for the table and uses it to encrypt those columns.
Steps:
1. Add the following entry in sqlnet.ora under $ORACLE_HOME/network/admin
ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=file)
(METHOD_DATA=(DIRECTORY=/u01/app/oracle/wallets)))
2. Before attempting to create a table with encrypted columns, a wallet must be created to hold the encryption key.
Creates the wallet in the location specified in sqlnet.ora i.e. /u01/app/oracle/wallets
Set the password of the wallet as "passwd".
Open the wallet for TDE
[oracle@db wallets]$ pwd
/u01/app/oracle/wallets
[oracle@db wallets]$ ls -lrt
total 12
-rw------- 1 oracle oinstall 8757 May 6 12:56 ewallet.p12
3. You need to open the wallet and create the master encryption key. Open a SQL*Plus session and execute the following commands:
connect / as sysdba
SQL> alter system set key identified by "finnet1";
4. Open the wallet as below :
CONN sys/passwd@dbname AS SYSDBA
SQL> ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY "myPassword";
Or
The wallet must be opened explicitly, after the database instance starts.
SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN AUTHENTICATED BY "passwd";
SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "passwd";
5. The status of the wallet can be viewed using the view v$encryption_wallet:
SQL> SELECT * FROM v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS
-------------------- ------------------------------ ---------
file /u01/app/oracle/wallets OPEN
Encrypted Column
--------------------
6. Create a test table with an encrypted column and insert some data. Using the ENCRYPT clause on its own is the same as using the ENCRYPT USING 'AES192' clause, as AES192 is the default encryption method.
For encrypting the data, we can simply use
SQL> ALTER TABLE EMP MODIFY (SAL ENCRYPT);
SQL> ALTER TABLE EMP MODIFY (SAL DECRYPT);
Details of the encrypted columns are stored in DBA_ENCRYPTED_COLUMNS
SQL> SELECT * FROM DBA_ENCRYPTED_COLUMNS;
SQL> SELECT * FROM user_encrypted_columns;
7. We can explicitly close the wallet using
SQL> ALTER SYSTEM SET ENCRYPTION WALLET CLOSE; |
posted by Jaswinder Singh @ 4:19 AM   |
|
|
|
|
|
