Encrypt a single column of a table using Oracle wallet

This post is basically for all DBA who are looking a way to implement column level encryption policy in their database. I know most of the time we start looking this kind of security implementation for data confidentiality when we get an alert from Audit team to be align with information & security compliance policy of the organization. This happened to me also J

Well, I am going to implement this using Oracle wallet.

Platform Detail:

Database version: 11.2.0.3

Operating System: Linux 5.3

Step 1: created a user “Saurabh” and creating a table with an encrypted column.

Connect saurabh:

SQL> CREATE TABLE TEST_USER

(

USER VARCHAR2(10),

PWD  VARCHAR2(30)ENCRYPT,

CREATE_DATE_TIME DATE,

MOD_DATE_TIME DATE

);

CREATE TABLE TEST_USER

*

ERROR at line 1:

ORA-28365: wallet is not open

Note: We get above error message “wallet is not open” so before encrypting any column we must have a wallet created and encryption key defined.

Step 2: Connected to oracle user at command line, created a directory under “$ORACLE_BASE/admin/<SID>” folder.

$ cd $ORACLE_BASE/admin/<SID>

$ mkdir wallet

Step 3: Connected to Sys and defined encryption key for wallet.

Connect as sys:

SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "SauPass";

System altered.

Note: Now we can see one file is created under wallet directory:

$ pwd

/u01/app/oracle/admin/orcl/wallet

$ ll

total 4

-rw-rw-r-- 1 oracle oracle 1573 Sep  6 11:02 ewallet.p12

Step 4: Connected to Saurabh user and created a table with one encrypted column.

Connect saurabh:

SQL> CREATE TABLE TEST_USER

(

USER VARCHAR2(10),

PWD  VARCHAR2(30)ENCRYPT,

CREATE_DATE_TIME DATE,

MOD_DATE_TIME DATE

);

Table created.

Step 5: Data inserted into the table.

SQL> INSERT INTO TEST_USER VALUES(‘abcde’,'xyzer’,sysdate,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test_user;

USER    PWD                         CREATE_DA MOD_DATE_

---------- ------------------------------ --------- ---------

abcde      xyzer                          06-SEP-12 06-SEP-12

We can fetch the data and encrypted column data is appearing with no issues.

Step 6: Connect to Sys and close the wallet.

Connect as sys:

SQL> ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY "SauPass";

System altered.

Step 7: Connect to Saurabh user and fetch the table data.

Connect saurabh:

SQL> select * from test_user;

select * from test_user

              *

ERROR at line 1:

ORA-28365: wallet is not open

Note: “wallet is not open” error message appears when we try to fetch all columns data of the table but we are able to fetch the columns data other than encrypted column:

SQL> SELECT USER FROM TEST_USER;

USER

----------

abcde

SQL> SELECT PWD FROM TEST_USER;

SELECT PWD FROM TEST_USER

                   *

ERROR at line 1:

ORA-28365: wallet is not open

Step 8: Hence, once a table’s column is encrypted and we need to see the encrypted column data, the wallet must be opened. Connected to sys and opened the wallet.

Connect as sys:

SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "SauPass";

System altered.

Step 9: Connected to Saurabh user and fetch all the columns.

Connect saurabh:

SQL> select * from test_user;

USER    PWD                         CREATE_DA MOD_DATE_

---------- ------------------------------ --------- ---------

abcde      xyzer                          06-SEP-12 06-SEP-12

SQL> SELECT PWD FROM TEST_USER;

PWD

------------------------------

xyzer

Bye,

Saurabh

Follow @saurabhxgupta