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;
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