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.
