Encryption in Oracle database.

There are few ways to enforce security in oracle database . Virtual Private Database ( VPD)  , Oracle Label Security  (OLS) , Transparent Data Encyption , Data Redaction , Vault , and so on.  These options can be used to enforce security in the database ; however for some privileged users  , the data is  visible in the clear text.  To mitigate this issue , we can store the sensitive information encrypted.  We can use dbms_crypto ( in 10g and later ) or dbms_obfuscation_toolkit  ( in pre-10g  releases ) .


Here is  the simple example .
Let us create two functions . Ideally , I would prefer all the stored procedures and functions in a package . This is for illustrations only.

For this to work , the developer has to granted  execute privilege on dbms_crypto from a user with SYSDBA privilege .

SQL> show user
USER is "ZAHIR"
SQL> CREATE OR REPLACE FUNCTION encrypt_constant
  2    RETURN INTEGER
  3  AS
  4  BEGIN
  5    RETURN DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5;
  6  END ;
  7  /

Function created.

SQL> CREATE OR REPLACE FUNCTION rtn_key ( p_key in varchar2)
  2  return raw
  3  as
  4  begin
  5  return utl_raw.cast_to_raw(rpad(p_key,32));
  6  end ;
  7  /

Function created.


The above functions are created to set the encryption algorithm  and to return the raw from varchar.

SQL> drop table t_users purge
  2  /

Table dropped.

SQL> CREATE TABLE t_users

  2    (

  3      usid     INT ,

  4      lname    VARCHAR2(50) ,

  5      clear_password VARCHAR2(20) ,

  6      encrypted_password raw(32)

  7    )

  8    /

Table created.

Now , let us use the two functions to use it in our insert statement.

SQL> INSERT

  2  INTO t_users

  3    (

  4      usid ,

  5      lname ,

  6      clear_password ,

  7      encrypted_password

  8    )

  9    VALUES

 10    (

 11      1 ,

 12      'Zahir Mohideen' ,

 13      'password' ,

 14      DBMS_CRYPTO.ENCRYPT ( src => UTL_I18N.STRING_TO_RAW ('password', 'AL32UTF8'), typ => encrypt_constant , KEY => rtn_key(TO_CHAR(1)) )

 15    ) ;

1 row created.

SQL> set linesize 1000

SQL> select clear_password , encrypted_password from t_users;

CLEAR_PASSWORD       ENCRYPTED_PASSWORD

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

password             A798E993760F0AD22E3AAD0814A98A15

Now , as you can see , the password is stored in clear text format in "CLEAR_PASSWORD" column and the password is stored in encrypted format in "ENCRYPTED_PASSWORD" column . Ideally , any security minded professional would prefer to the sensitive information to be encrypted. 

To decrypt ,  we can use dbms_crypto.decrypt procedure , as seen below.

SQL>  SElect UTL_I18N.RAW_TO_CHAR  (

  2   DBMS_CRYPTO.DECRYPT ( src => encrypted_password ,  typ => encrypt_constant ,  key =>  rtn_key(TO_CHAR(usid)) )

  3   ) pwd from t_users ;

PWD

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

password

SQL>

So , here is our simple example to encrypt and decrypt using one of Oracle supplied packages. 

Comments welcome.