Monday, September 19, 2011

Encript / Decript sensitive data



As part of our security policy, we needed to encrypt our sensitive data and decrypt it back when ever required. 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

select * from users;

   USERID USERNAME          PASSWORD
---------- ------------------------------ -----------------------------------
         1 BILAL                          bILAL123
         2 STEVE                        STEVE001
         3 TARIQ                         taRIa980

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

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.
grant execute on enc_dec to scott;
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: 
select enc_dec.encrypt('Hello World') encrypted 
from dual;
 
ENCRYPTED
----------------------------------
89738046FA0CFDD2581198FBF98DE2C5
 


/* A simple value encrypted using the package we just created. */
 
select enc_dec.decrypt('89738046FA0CFDD2581198FBF98DE2C5') decrypted
from dual;
 
DECRYPTED
------------------
Hello World
 
 
 
CASE 2:
 
select * from users;

   USERID USERNAME                       PASSWORD
---------- ------------------------------ -----------------------------------
         1 BILAL                          bILAL123
         2 STEVE                          STEVE001
         3 TARIQ                          taRIa980
 
SQL> update users
  2  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.
*/
 
column password format a32
 
select * from users;
 
USERID USERNAME                       PASSWORD
---------- ------------------------------ -----------------------------------
1 BILAL                          03077889420F4348EEA75EDA4DA3F088
2 STEVE                          E357A4E178A115FAF254EC08C0F97DE1     
        3 TARIQ                          67EB9262394146787485B7C51F3E2889
/* All existing passwords are now encrypted */
 

Now add one more record with encrypted password

SQL> insert into users
  2  values (4,'Regis',enc_dec.encrypt('ReGis_8976'));
 
 
USERID USERNAME                       PASSWORD
---------- ------------------------------ -----------------------------------
         1 BILAL                          03077889420F4348EEA75EDA4DA3F088
         2 STEVE                          E357A4E178A115FAF254EC08C0F97DE1
         3 TARIQ                          67EB9262394146787485B7C51F3E2889
         4 Regis                          C94F447042C428723B2F97393191DE65
 
In order to decrypt the password
 
select userid,username,enc_dec.decrypt(password) from users;

   USERID USERNAME                       PASSWORD
---------- ------------------------------ -----------------------------------
         1 BILAL                          bILAL123
         2 STEVE                          STEVE001
         3 TARIQ                          taRIa980
 

No comments:

Post a Comment