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

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;

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

To know more information about DBMS_CRYPTO please visit:

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