Last week, I was trying to enable ENCRYPTION property for Oracle 11g Secure File. But I couldn’t find sufficient information on the web. However, I have tried it now and would like to demonstrate on my blog.
Suppose I have a table EMPLOYEES where employees image (BLOB) has to be loaded into the database. You want to compress it and encrypt it as per AES192 encryption standard.
Let us see how to do it.
1. Compression can be easily enabled for the BLOB as below
ALTER TABLE EMPLOYEES
MODIFY LOB (IMAGE)
2. Enabling the encryption feature for the IMAGE column in EMPLOYEES table
The TDE is an encryption system which encrypts the columns value with a confidential key. An encrypted key can hold multiple encrypted columns of same table. There exists second level of security where the column keys are again encrypted with database’s master key. But note that none of the keys are stored in database. They reside within an Oracle wallet. An encryption wallet has to be set as an external security module before enabling the feature for the LOB column.
Step 1: Create a Folder C:\ExternalSecurity\Wallets\ on the Oracle database server to store the Transparent Data Encryption (TDE) wallet.
Step 2: Edit the SQLNET.ora file at \\ORACLE_HOME\NETWORK\ADMIN location on the server. Include the ENCRYPTION_WALLET_LOCATION to indicate the location of the TDE wallet.
(METHOD_DATA= (DIRECTORY= C:\ExternalSecurity\Wallets)
Step 3: Open the command prompt on the server machine to reload the server
Reload the Listener using RELOAD command
Step 4: Now login as DBA
Set the encryption key along with its password
SQL> ALTER system SET ENCRYPTION KEY IDENTIFIED BY "wallet";
The ALTER SYSTEM command above keeps the wallet open until the database is up. If the database is down and restarted subsequently, the wallet has to be explicitly opened.
The error message raised below is not an error since the wallet gets automatically opened while setting
SQL> ALTER system SET ENCRYPTION WALLET OPEN IDENTIFIED BY "wallet";
ALTER system SET ENCRYPTION WALLET OPEN IDENTIFIED BY "wallet"
ERROR at line 1:
ORA-28354: wallet already open
Step 5: Login as SCOTT user
Enter user-name: SCOTT/TIGER
SQL> ALTER TABLE EMPLOYEES
4 IMAGE ENCRYPT USING 'AES192'
SQL> SELECT *
2 FROM user_encrypted_columns;
TABLE_NAME COLUMN_NAME ENCRYPTION_ALG SAL INTEGRITY_AL
---------- -------------- ----------------------------- --- ------------
EMPLOYEES IMAGE AES 192 bits key YES SHA-1
Step 6: Verify the server wallet location. Check the ENCRYPTED WALLET LOCATION directory. You can find the ewallet.p12 filer created.
Note that the table carrying encrypted column(s) cannot participate in conventional export or import process. But data pump rescues the situation by supporting encrypted exports and imports.