Using DBMS_CRYPTO for column encryption

The DBMS_CRYPTO PL/SQL package is an important component of Oracle Cryptographic API. DBMS_CRYPTO can be wrapped in your own packages and used for encryption and decryption. It is mainly used for hindering data access using encryption on designated columns. Consider it as a selective method of encryption—the columns are stored in encrypted format on storage and remain encrypted during data access unless they are decrypted with the appropriate function.

In this recipe we will create a table EMPLOYEES_ENC and encrypt and decrypt the salary and commission_pct columns of this table by using DBMS_CRYPTO wrapped in two functions.

Getting Ready

All steps will be performed on the HACKDB database.

How to do it...

1. As root create a directory named hashkeydir and make the oracle user the owner:

   mkdir /hashkeydir 
   chown oracle:oinstall /hashkeydir
   

2. Connect as system and create a directory named encryption_keys as follows:

   SQL> conn system
   Enter password:
   Connected...