Data Encryption Decryption

For security purpose, some times we need to hide data in database level. Only selected person can see the actual data. In Oracle there are some different ways to accomplish the objective.

Among those ways, Encoding and Decoding technique is describe in this article :

The idea is quite simple, we first generate a random key which is used to encode a column's data of a table. Store the key in a different table. Finally using the stored key we decode the encoded column and view actual data.

In real word, keys are stored in table and table that contains sensitive data are placed in two different schemas. But for simplicity we place both table in one schema.

Here the following steps :

1. Create an schema with necessary privilege :
SQL> Create user test2 identified by test default tablespace user temporary tablespace temp;

User Created.

SQL> Grant connect,resource to test2;
Grant succeeded.

SQL> Grant execute on dbms_crypto to test2;
Grant succeeded.

SQL> Grant execute on UTL_I18N to test2;
Grant succeeded.

Here dbms_crypto is the package used for encryption decryption and UTL_I18N is used for row to string conversion (vice versa).

2. EMP is the table which we want to protect. we will encrypt the NAME column.

CREATE TABLE EMP ( ID NUMBER (10), NAME VARCHAR2 (40) );

ALTER TABLE EMP ADD CONSTRAINT pk_emp_id PRIMARY KEY (ID);

INSERT INTO EMP VALUES (1,'HASAN');

COMMIT;

3. ENC_INFO is the table where we store the key.

CREATE TABLE ENC_INFO ( TABLE_NAME VARCHAR2 (40),
COLUMN_NAME VARCHAR2 (40), ENC_KEY raw (200) );

ALTER TABLE ENC_INFO ADD CONSTRAINT pk_enc_info_tname_colname
PRIMARY KEY (TABLE_NAME,COLUMN_NAME);

INSERT INTO ENC_INFO (TABLE_NAME,COLUMN_NAME,ENC_KEY)
VALUES ('EMP','NAME',dbms_crypto.randombytes (56));

COMMIT;

Here dbms_crypto.randombytes generate 56 random bytes which we use as encryption decryption key.

4. A simple encryption function

create or replace function enc_val
(
content in varchar2,
enc_key raw
)
return varchar2 is
l_enc_val varchar2 (2000);
l_enc_val_raw raw(2000);
l_mod number := dbms_crypto.ENCRYPT_DES
+ dbms_crypto.CHAIN_CBC
+ dbms_crypto.PAD_PKCS5;
begin

l_enc_val_raw := dbms_crypto.encrypt
(
UTL_I18N.STRING_TO_RAW
(content,'WE8ISO8859P1'),
l_mod,
enc_key
);
l_enc_val:= UTL_I18N.RAW_TO_CHAR
(l_enc_val_raw,'WE8ISO8859P1');
return l_enc_val;
end;

5. A simple decryption function

create or replace function dec_val
(
content in varchar2,
enc_key in raw
)
return varchar2
is
l_ret varchar2 (2000);
l_dec_val raw (2000);
content_raw raw (2000);
l_mod number := dbms_crypto.ENCRYPT_DES
+ dbms_crypto.CHAIN_CBC
+ dbms_crypto.PAD_PKCS5;
begin

content_raw := UTL_I18N.STRING_TO_RAW
(content,'WE8ISO8859P1');
l_dec_val := dbms_crypto.decrypt
(
content_raw,
l_mod,
enc_key
);
l_ret:= UTL_I18N.RAW_TO_CHAR
(l_dec_val,'WE8ISO8859P1');
return l_ret;
end;

6. The following procedure encrypt EMP table (NAME column)

DECLARE

l_key raw(200);

begin

select enc_key into l_key from ENC_INFO where table_name='EMP' and column_name='NAME';

dbms_output.put_line(l_key);

dbms_output.put_line( enc_val('hasan',l_key)) ;

UPDATE EMP set name=enc_val(name,l_key) where id=1;
commit;

end;
/

7. Now view the table (decrypt using stored key)

select y.id, dec_val(y.name,x.enc_key) name from ENC_INFO x, EMP y;


NB: In above article i just try to explain the mechanism so some important security measure are not shown (actually every one plan his own security measure so you need to plan it your self !)