Systems Engineering and RDBMS

Encrypting a single column in Oracle

Posted by decipherinfosys on March 1, 2009

In one of our previous blog post, we had shown how to encrypt an entire tablespace, which is supported from Oracle 11g onwards.

That post covered encryption at a much broader level…there could be a requirement where we need to encrypt only specific column(s) like SSN or credit card information or password etc. Oracle provides column level encryption as well. Let us see it with an example.

CREATE TABLE TEST_USER
(
USER_ID VARCHAR2(10),
PASSWD  VARCHAR2(30)ENCRYPT,
CREATE_DATE_TIME DATE,
MOD_DATE_TIME DATE
);

Above statement will run into an error “wallet is not open”. As in the case of tablespace encryption, for column encryption also we will have to create an Oracle wallet to store the master encryption. First create a wallet directory under $ORACLE_BASE/admin/<sid> folder. For windows it will be %ORACLE_BASE%/admin/<sid>. Once directory is created log in as sysdba and issue following command.

ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY “DeciPher”;

Above statement opens the wallet and sets the password as “DeciPher”. Password is case sensitive and must be enclosed in (“) double quotes. Wallet remains open and available to all sessions. Let us run the create table statement again to create the table. Make sure to connect to appropriate user schema. Table will be created without any errors. We will populate the table.

SQL> INSERT INTO TEST_USER VALUES(‘Decipher’,’Decipher’,sysdate,sysdate);

Commit the changes and select the record from the table. You will see column value for encrypted column as is.

SQL> SELECT user_id,passwd FROM test_user;

USER_ID    PASSWD
———- ——————————
Decipher   Decipher

So where does the encryption happen? Encryption happens at the storage level. When the column data is stored on the disk, it is stored in encrypted format. So subsequent operations like archive logs, backup etc. contains data in encrypted format. So if data is stolen, it is of no use since it is encrypted.
When user selects the record from the table, Oracle internally decrypts the column data using wallet key and display to the user in decrypted format and hence we see data as is. One thing to remember though is that in order to see encrypted column data, wallet must be open. If wallet is closed, then one cannot view encrypted column data but other column for which data is stored as clear text is visible. Let us test that out. Connect as sysdba and issue following command.

SQL> SELECT user_id,passwd FROM test_user;

USER_ID    PASSWD
———- ——————————
Decipher   Decipher

Now try to retrieve data from encrypted column of the table.

SQL> SELECT passwd FROM test_user;
SELECT passwd FROM test_user
*
ERROR at line 1:
ORA-28365: wallet is not open

We still can select the regular column.

SQL> SELECT user_id from test_user;

USER_ID
———-
Decipher

In order to view encrypted data we have to open the wallet. Wallet also needs to be explicitly opened after database is bounced. One has to use following command to open the wallet.

SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY “DeciPher”;

We can even alter the table to add encrypted column and yes more than one column of the same table can be encrypted as well. We can also specify which algorithm we want to use for encryption. If no algorithm is specified like in our example, column data will be encrypted using default algorithm.

Resources:

  • Oracle documentation (SQL Language Reference manual) across releases – here.
  • Oracle ACE Arup Nanda’s article – here
  • Article from Oracle-Base website – here

2 Responses to “Encrypting a single column in Oracle”

  1. The said

    Alternative to Oracle TDE for both Standard and Enterprise editions of Oracle:

    Pricing starts at $4,950 per Oracle SID.

    The Encryption Wizard for Oracle is an easy-to-use database encryption software suite. Since 2002, the Encryption Wizard has helped Oracle security specialists meet compliance standards such as CISP, HIPAA and PCI.

    No other database encryption tool can automatically encrypt Oracle date, number and character data at the column, table, or schema level with the click of a mouse.

    Free downloads are available at:

    http://www.relationalwizards.com

  2. LL said

    Is the encrypted column still valid after I export this table and import to another schema?

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

 
%d bloggers like this: