Encrypt / Decrypt customers sensitive data via custom define procedure and functions

Some times customer request to encrypt his sensitive data and decrypt it back when ever required. This may be the customers security policy.

With the below mentioned tested methodology, when data is inserted, the user actually provides the plain text and oracle automatically converts the plain text into encrypted form and stores it in the data files. Now, whenever users access that data, Oracle decrypts the data and show it to the users. This encryption/decryption is completely transparent to users. So the whole point behind transparent encryption is to keep the sensitive data in data files safe.

Summary of steps:
1.     Create or point out the table with sensitive information (developers)
2.     Create encryption/decryption package (DBA)
3.     Assign permission on package created in step 2 to user/schema with sensitive data (DBA)
4.     Use different DML scenario to encrypt and decrypt info (developers)

STEP 1:
I am using scott/tiger and a table ‘USERS’ with ‘password’ column containing sensitive info

1.    

$ sqlplus scott/tiger
SQL> select * from users;
   USERID USERNAME          PASSWORD
---------- ----------- ------------------------
         1 GOURANGA                  GOURANGA123
         2 JUSTEEN                   JUSTEEN001
         3 HUSSAIN                   HUSSAIN980

STEP 2:

Connect to sysdba user create an encryption/decryption mechanism for the password field.

$ sqlplus / as sysdba
SQL>
CREATE OR REPLACE PACKAGE enc_dec
AS
   FUNCTION encrypt (p_plainText VARCHAR2) RETURN RAW DETERMINISTIC;
   FUNCTION decrypt (p_encryptedText RAW) RETURN VARCHAR2 DETERMINISTIC;
END;
/

CREATE OR REPLACE PACKAGE BODY enc_dec
AS
     encryption_type    PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_DES
                                     + DBMS_CRYPTO.CHAIN_CBC
                                     + DBMS_CRYPTO.PAD_PKCS5;
     /*
       ENCRYPT_DES is the encryption algorithem. Data Encryption Standard. Block cipher. 
       Uses key length of 56 bits.
       CHAIN_CBC Cipher Block Chaining. Plaintext is XORed with the previous ciphertext 
       block before it is encrypted.
       PAD_PKCS5 Provides padding which complies with the PKCS #5: Password-Based 
       Cryptography Standard
     */
     encryption_key     RAW (32) := UTL_RAW.cast_to_raw('MyEncryptionKey');
     -- The encryption key for DES algorithem, should be 8 bytes or more.

     FUNCTION encrypt (p_plainText VARCHAR2) RETURN RAW DETERMINISTIC
     IS
        encrypted_raw      RAW (2000);
     BEGIN
        encrypted_raw := DBMS_CRYPTO.ENCRYPT
        (
           src => UTL_RAW.CAST_TO_RAW (p_plainText),
           typ => encryption_type,
           key => encryption_key
        );
       RETURN encrypted_raw;
     END encrypt;
     FUNCTION decrypt (p_encryptedText RAW) RETURN VARCHAR2 DETERMINISTIC
     IS
        decrypted_raw      RAW (2000);
     BEGIN
        decrypted_raw := DBMS_CRYPTO.DECRYPT
        (
            src => p_encryptedText,
            typ => encryption_type,
            key => encryption_key
        );
        RETURN (UTL_RAW.CAST_TO_VARCHAR2 (decrypted_raw));
     END decrypt;
END;
/


STEP 3: 
Grant execution permissions on the above mentioned package to user scott.

$ sqlplus / as sysdba
SQL> grant execute on enc_dec to scott;
SQL> create public synonym enc_dec for sys.enc_dec;


Now connect to sqlplus scott/tiger and test out the encryption/decription using following scenarios

CASE 1: 

SQL> select enc_dec.encrypt('Hello World') encrypted from dual;
ENCRYPTED
----------------------------------
43718046FA0CFDD2581198FBF98DE2C5

/* A simple value encrypted using the package we just created. */

select enc_dec.decrypt('43718046FA0CFDD2581198FBF98DE2C5') decrypted
from dual;

DECRYPTED
------------------
Hello World

CASE 2:

select * from users;

   USERID USERNAME                       PASSWORD
---------- ----------------- ----------------------------
         1 GOURANGA                     GOURANGA123
         2 JUSTEEN                      JUSTEEN001
         3 HUSSAIN                      HUSSAIN980

SQL>  update users
set password = enc_dec.encrypt (password);

3 rows updated.

SQL> commit;
Commit complete.


/*
   We just encrypted the password data using the algorithm and key specified in the
   package ENC_DEC.
   We also need to make sure any newly created record has Password value encrypted using
   the package ENC_DEC.
*/

SQL> column password format a32
SQL> select * from users;
USERID USERNAME                       PASSWORD
---------- ---------------------- -----------------------
1 GOURANGA                         03077889420F4348EEA75EDA4DA3F088
2 JUSTEEN                          E357A4E178A115FAF254EC08C0F97DE4 
3 HUSSAIN                          67EB9262394146787485B7C51F3E2889

/* All existing passwords are now encrypted */
Now add one more record with encrypted password

SQL> insert into users values (4,'Regis',enc_dec.encrypt('kU_Fi_4383'));

USERID USERNAME                       PASSWORD
---------- ------------------------------ -----------------------------------
         1 GOURANGA                       03077889420F4348EEA75EDA4DA3F088
         2 JUSTEEN                        E357A4E178A115FAF254EC08C0F97DE1
         3 HUSSAIN                        67EB9262394146787485B7C51F3E2889
         4 Regis                          C94F447042C428723B2F97393191DE65

In order to decrypt the password

SQL> select userid,username,enc_dec.decrypt(password) from users;

Note: You can use this policy to encrypt credit card information, bank a/c details, any personal data etc.

 You can test yourself...