Oracle Transparent Data Encryption belongs to the Advanced Security Option and is available as an Option for the Oracle Database Enterprise Edition only. So you will not find TDE option for Oracle Database Standard Edition or Personal Edition. Transparent Data Encryption is a key based access control system. In the same database there is no restriction of accessing data for a user if he has the proper permission on the table. This means that TDE is no replacement for normal access control, any database user that has the proper select privileges on a certain table can still query the rows even if they are encrypted using TDE. TDE only protects data that is outside the scope of the designated instance. For example you can't move an TDE encrypted table to another database instance unless you have the encryption key. So, TDE access control comes across database instances not within same instance.
Concepts
In case of Oracle Transparent Data Encryption (TDE) the database server master key is stored in the OS file system which is called the wallet file. The keys for all tables containing encrypted columns are encrypted with the database server master key and stored in a dictionary table in the database. When you create a wallet with the statement "ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY ...", the master key is generated automatically using a secure random number generator, it has no relationship with the wallet password that you specify with the IDENTIFIED BY clause. Particularly this means that if you create a new wallet even with the same wallet password, you will generate a new master key that cannot be used to decrypt data that was encrypted using a previous wallet.
Note that, if you lose the wallet file or wallet password and you are not able to open the wallet, then there is no way to recover the encrypted data.
Also note that we need a wallet file which is outside the database and only accessible to security administrator. This wallet file has mainly two tasks. i) Stores master key. ii) Used to generate encryption keys and perform encryption and decryption.
Setup Oracle Transparent Data Encryption (TDE)
Step 01)Specify location for wallet file (optional)
Specify the location of the wallet file used to store the encryption master key by adding the following entry in $ORACLE_HOME/network/admin/sqlnet.ora:
ENCRYPTION_WALLET_LOCATION=
(SOURCE=(METHOD=FILE)(METHOD_DATA=
(DIRECTORY=D:\admin\oracle\wallet)))
This step is optional and if your don't specify this entry inside sqlnet.ora the server may create the wallet file in the default location $ORACLE_BASE/ADMIN/{SID}/WALLET. You need to create the WALLET folder manually.
However on certain platform the ENCRYPTION_WALLET_LOCATION entry inside sqlnet.ora file is mandatory otherwise wallet creation fails with ORA-28368 as it is demonstrated in ORA-28368: cannot auto-create wallet ORA-28353: failed to open wallet.
Step 02) Start the listener (optional if you don't need path inside sqlnet.ora)
$ lsnrctl start
Step 03) Connect to to database as sysdba.
$ sqlplus / as sysdba
Step 04) Create the encrypted wallet file (you can omit the encryption clause from following command).
SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "pass";
This command do the following task:
If no encrypted wallet is present in the directory defined in sqlnet.ora :
1.) It creates an encrypted wallet (ewallet.p12)
2.) It opens the wallet
3.) It creates the database server master encryption key for TDE
If an encrypted wallet already exists :
1.) It opens the wallet
2.) It creates or re-creates the database server master encryption key for TDE.
It is very important to note that this command is usually executed ONLY ONCE unless you want to re-encrypt your data with a new encryption key for the whole database. You should NOT repeat above statement on different RAC nodes, instead, copy the wallet over to the other nodes or make it available by pointing sqlnet.ora parameter ENCRYPTION_WALLET_LOCATION to a shared drive. The master key is automatically created with a secure random key generator, the password you supply ('pass' in the above example) is just the wallet password. The wallet password can be changed later using Oracle Wallet Manager (owm) without affecting the master key, do this only when the wallet is closed.
For later sessions, you do not have to use the same command. You only need to open the wallet (it is automatically closed when you shut down the database).
To load the master key after the database is restarted :
SQL> ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY "pass";
The database is now ready to allow encryption: how to encrypt/decrypt data in table columns.
Create a table named payment_information with one encrypted column.
SQL> create table payment_information 2 (emp_id VARCHAR2(10), 3 ORDER_NO VARCHAR2(10), 4 CREDIT_CARD_NUMBER VARCHAR2(20) ENCRYPT); Table created.
If you want to encrypt a tablespace then your command should be like,
SQL> create tablespace idencraft_data datafile 'E:\APP\ADMINISTRATOR\ORADATA\BDD
IPLOC\IDENCRAFT_ENCRYPT.DBF' size 100M autoextend on next 256M maxsize unlimited E
NCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);
Insert data into table.
SQL> insert into payment_information values('101','9898','987-28787-898797');
1 row created.
SQL> commit;
Commit complete.
Check the data from the encrypted table, it is nothing more than normal table. Also from USER_ENCRYPTED_COLUMNS you can check from database which columns are encrypted.SQL> select * from payment_information; EMP_ID ORDER_NO CREDIT_CARD_NUMBER ---------- ---------- -------------------- 101 9898 987-28787-898797 SQL> set lines 150 SQL> select * from USER_ENCRYPTED_COLUMNS; TABLE_NAME COLUMN_NAME ENCRYPTION_ALG SAL ------------------------------ ------------------------------ ----------------------------- --- PAYMENT_INFORMATION CREDIT_CARD_NUMBER AES 192 bits key YESClose the wallet. You will not be able to access table until you open the wallet again.
SQL> alter system set wallet close;
System altered.
SQL> select * from payment_information;
select * from payment_information
*
ERROR at line 1:
ORA-28365: wallet is not open
You will not be able to open wallet if password is wrong.
SQL> alter system set wallet open identified by "test"; alter system set wallet open identified by "test" * ERROR at line 1: ORA-28353: failed to open wallet SQL> alter system set wallet open identified by "pass"; System altered. SQL> select * from payment_information; EMP_ID ORDER_NO CREDIT_CARD_NUMBER ---------- ---------- -------------------- 101 9898 987-28787-898797
