Oracle Database Column level data encryption using dbms_crypto

1)      To Implement  Encryption using dbms_crypto  run below script file to create required packages

Run     {ORACLE_HOME}/rdbms/admin/dbmsobtk.sql as sysdba

2)      Grant execute privilege on dbms_crypto to the user

grant execute on dbms_crypto to sirima1;

3)      Connect to user sirima1 (drop table if already exists)

drop table tab_dbms_crypto purge;

4)      Crete table for encryption and insert data

CREATE TABLE TAB_DBMS_CRYPTO

(

ACCOUNT_NAME VARCHAR2(60 BYTE),

ACCOUNT_PASSWD VARCHAR2(256 BYTE),

ENC_HEX_PASSWORD VARCHAR2(256 BYTE)

) ;

--Inserting some rows into table ( sql developer also can be used)

insert into tab_dbms_crypto  (account_name,account_passwd) values ('user1', '#123$');

insert into tab_dbms_crypto  (account_name,account_passwd) values ('user2', '$456%');

insert into tab_dbms_crypto  (account_name,account_passwd) values ('user3', '(876%');

--> encrypt_aes128:  Advanced encryption standard. Block cipher. Uses 128-bit key.

--> chain_cbc:  Cipher block chaining

--> pad_pkcs5:  Password-based cryptography standard

--> randombytes:  This function generates random key values

5)      Create Package for Encryption

----> Package Definition

create or replace package pkg_encrypt_decrypt

   as

   function enc_account_passwd(

                      p_account_passwd in varchar2,

                      p_key in varchar2

                      )

                      return varchar2;

   function dec_account_passwd(

                      p_account_passwd in varchar2,

                      p_key in varchar2

                      )

                      return varchar2;

end;

/

----> Package Body

create or replace

package body pkg_encrypt_decrypt

as

   raw_key  raw(256);

   f_enc_raw_password raw(256);

   f_enc_raw_enc_password raw(256);

 

   f_dec_raw_enc_password raw(256);

   f_dec_raw_dec_password raw(256);

   enc_mode number := dbms_crypto.des_cbc_pkcs5;

                                                                                 

    function enc_account_passwd(

                      p_account_passwd in varchar2,

                      p_key    in varchar2

                       )

                      return varchar2 as

                                 enc_hex_password varchar2(256);

                      begin

                           raw_key := utl_i18n.string_to_raw(p_key);    -- Convers key String to RAW

                           f_enc_raw_password := UTL_I18N.STRING_TO_RAW(p_account_passwd);  -- Convers password String to RAW

                           f_enc_raw_enc_password := dbms_crypto.encrypt(f_enc_raw_password,enc_mode, raw_key); -- Encrypts RAW password  wing raw key

                           enc_hex_password := RAWTOHEX(f_enc_raw_enc_password); --- Converts encrypted raw password to hex

                           return enc_hex_password;

                      end;

    function dec_account_passwd(

                      p_account_passwd in varchar2,

                      p_key    in varchar2

                       )

                      return varchar2 as

                                 dec_string varchar2(256);

                      begin

                           raw_key := utl_i18n.string_to_raw(p_key); -- Convers key String to RAW

                           f_dec_raw_enc_password := HEXTORAW(p_account_passwd); --- Converts hex to encrypted raw password

                            --dec_raw_enc_password := UTL_I18N.STRING_TO_RAW(p_account_passwd);  

                           f_dec_raw_dec_password := dbms_crypto.decrypt(src => f_dec_raw_enc_password,  typ => enc_mode, key => raw_key); -- decrypt of encrypted raw password to raw password using raw key

                           dec_string := UTL_I18N.RAW_TO_CHAR(f_dec_raw_dec_password); -- Convers RAW password to String

                              return dec_string;

                      end;

  end;

/

============================

6)      Update Table  with encrypted data

Select * from tab_dbms_crypto;

update tab_dbms_crypto set enc_hex_password = pkg_encrypt_decrypt.enc_account_passwd(account_passwd,'abcdef');

commit;

Note: abcdef : is the key for encryption

7)      Select eccrypted data

Select * from tab_dbms_crypto;

8)      Select decrypted data

select account_name,account_passwd,pkg_encrypt_decrypt.dec_account_passwd(enc_hex_password,'abcdef') from tab_dbms_crypto;