>>
You're reading...
Oracle

Enable Encryption for SecureFiles

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)
(
COMPRESS HIGH
)
/

Table altered.

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.

ENCRYPTION_WALLET_LOCATION=
(SOURCE=(METHOD=FILE)
(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";

System altered.

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
2 MODIFY
3 (
4 IMAGE ENCRYPT USING 'AES192'
5 );

Table altered.

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.

About these ads

About Saurabh K. Gupta

Product Manager at Oracle, Author and blogger

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Saurabh K. Gupta

Saurabh K. Gupta

Product Manager at Oracle, Author and blogger

Personal Links

View Full Profile →

Twitter Profile

My Book

Oracle Advanced PL/SQL Developer Professional Guide

Disclaimer

SBHOracle is an independent blog and all the posts are based on my self experience and hands on with the technologies. It shares no relations with any of my current projects or from those in the past.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 368 other followers

SbhOracle Blog stats

  • 42,270 hits
Aggregated by OraNA
Follow

Get every new post delivered to your Inbox.

Join 368 other followers

%d bloggers like this: