Encryption Decryption using DBMS_CRYPTO

Problem Definition: Data need to keep in database in encrypted way, only application user will be able to encrypt and decrypt data when needed. Anyone having DBA privileges should not be able to see the source code for security purpose, source code should be hidden.

 

Solution:

Oracle 10g introduced DBMS_CRYPTO package to encrypt and decrypt stored data. It provides support for several industry standard encryption and hashing algorithm, including the Advance Encryption Standard (AES). I am using here that package to convert the plain text into encrypted form and store in database. Application user can decrypt it show the user. In the example I am using scott user as an application user.  I have used wrap utility so that the source code will be hidden and even not be visible to the people have DBA access. No user except scott have permission to execute the package, therefore no other user will be able to decrypt the password.

 

-- As scott user

SQL> show user

USER is "SCOTT"

 

SQL> CREATE TABLE customer (

            id           NUMBER,

           username     VARCHAR2(30),

          cardnumber   NUMBER,

         password     VARCHAR2(200),

        CONSTRAINT customer_pk PRIMARY KEY(id)

         );

 

SQL>create sequence customer_seq;

 

-- As SYSDBA user

SQL> show user

USER is "SYS"

 

SQL>

CREATE OR REPLACE PACKAGE encrypt_decrypt

AS

   FUNCTION encrypt (p_plainText VARCHAR2) RETURN RAW DETERMINISTIC;

   FUNCTION decrypt (p_encryptedText RAW) RETURN VARCHAR2 DETERMINISTIC;

END;

/

 

CREATE OR REPLACE PACKAGE BODY encrypt_decrypt

AS

     encryption_type    PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_DES

                                     + DBMS_CRYPTO.CHAIN_CBC

                                     + DBMS_CRYPTO.PAD_PKCS5;

     encryption_key     RAW (32) := UTL_RAW.cast_to_raw('MESSYKICKGOAL');

 

     -- The encryption key should be 8 bytes or more for DES algorithm. Here I used MESSYKICKGOAL as encrypted key

 

     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;

/

Now save the package in file encrypt_decrypt.sql and using the wrap utility hide the source code.
# wrap iname=encrypt_decrypt.sql oname=encrypt_decrypt.sql.wrp
 -- As SYSDBA user

SQL> show user

USER is "SYS"

 
SQL> @ encrypt_decrypt.sql.wrp
Package created.
Package body created.

Now if you see the content in wrp file or in database it will be look like below:

CREATE OR REPLACE PACKAGE SYS.encrypt_decrypt wrapped

a000000

367

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

9

ae be

DLewh9SpdUV4IP2ZKPYCABDSoFswg0xH7cusaXRAOBfiBv0yMgEnI+XfJ60+lU/0NZPwf9Fo

AEDK9Ny70iJYMK66Nf5T7ZLBmO2/C5nlmaYT1FegZeMtD8dwWAXPyg3IDI+i/9qbgZ7Rs1Ov

KV8UqD/D63uJ2SwU+l9ZQRrzks8DCOm1qC3D0TkfWXwr

/
SQL>  grant execute on encrypt_decrypt to scott;
create public synonym encrypt_decrypt for sys.encrypt_decrypt;

-- As scott user

 

SQL> show user

USER is "SCOTT"

 

SQL> insert into customer(id,username,cardnumber,password) values (customer_seq.nextval,'Fredrik',32435683, encrypt_decrypt.encrypt('kI32432J'));

SQL> insert into customer(id,username,cardnumber,password) values (customer_seq.nextval,'Ron',64898762, encrypt_decrypt.encrypt('Mk673Ert'));

 

SQL> select * from scott.customer;

ID

USERNAME

CARDNUMBER

PASSWORD

2

Fredrik

32435683

CAD449B06E529A36D4B03FB6540D876C

3

Ron

64898762

1ADE7AFFBC5091E842907B8445FB0482

  

SQL> Select id,username,cardnumber,password,encrypt_decrypt(password) decrypted password from scott.customer;

ID

USERNAME

CARDNUMBER

PASSWORD

DECRYPTED_PASSWORD

2

Fredrik

32435683

CAD449B06E529A36D4B03FB6540D876C

kI32432J

3

Ron

64898762

1ADE7AFFBC5091E842907B8445FB0482

Mk673Ert

  

To know more information about DBMS_CRYPTO please visit:

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_crypto.htm

Comments