Nalanta

DBMS_CRYPTO package provides an interface to encrypt and decrypt stored data. For example, the table CUST_MASTER has three columns ACNO,NAME,BALANCE and you wish to encrypt the column NAME and BALANCE. Using the dbms_crypto.ENCRYPT functions,you can create your function to encrypt data.

Example:

Create a table CUST_MASTER .

SQL> create table cust_master(acno number(10),name varchar2(200),balance varchar2(200));

Table created.

SQL> desc cust_master
 Name                                      Null?    Type
 —————————————– ——– —————————-
 ACNO                                               NUMBER(10)
 NAME                                               VARCHAR2(200)
 BALANCE                                            VARCHAR2(200)

After creating the table, using the dbms_crypto package create your function. Before creating the function you should have execute privilege on dbms_crypto.

CREATING FUNCTION FOR ENCRYPTION

SQL> create or replace FUNCTION encrypt(p_in in varchar2)
  2  return raw is
  3  l_enc_val raw (2000);
  4  l_mod PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_AES128 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5;
  5  l_key raw(16) := UTL_I18N.STRING_TO_RAW( ‘preventbettrcure’, ‘AL32UTF8′ );
  6  begin
  7      l_enc_val := dbms_crypto.encrypt(UTL_I18N.STRING_TO_RAW(p_in, ‘AL32UTF8′), l_mod, l_key);
  8      return l_enc_val;
  9  END;
 10  /

Function created.

After creating function, insert the records in the table using the function.

SQL> insert into cust_master (acno,name,balance) values (1001,encrypt(’ABDUL_KALAM’),encrypt(’10000000′));

1 row created.

QUERY THE TABLE and you will see the encrypted values.

SQL> select * from cust_master;

      ACNO NAME                                     BALANCE
———- —————————————- ————————————————–
      1001 B42D559293EAA24F44FF3D1C821E80B7         49A6A38E96A1E89CA3D24BAF122261F1

CREATING FUNCTION FOR DECRYPTION

SQL> create or replace FUNCTION decrypt(p_in in raw)
  2  return varchar2
  3  is
  4  l_ret varchar2 (2000);
  5  l_key raw(16) := UTL_I18N.STRING_TO_RAW( ‘preventbetrcure’, ‘AL32UTF8′ );
  6  l_dec_val raw (2000);
  7  l_mod number := dbms_crypto.ENCRYPT_AES128 + dbms_crypto.CHAIN_CBC + dbms_crypto.PAD_PKCS5;
  8  begin
  9      l_dec_val := dbms_crypto.decrypt(p_in, l_mod, l_key );
 10      l_ret:= UTL_I18N.RAW_TO_CHAR(l_dec_val, ‘AL32UTF8′);
 11      return l_ret;
 12  end;
 13  /

Function created.

RETRIVEING ACTUAL VALUES USING DECRYPTION FUNCTION

SQL> SELECT ACNO,DECRYPT(’B42D559293EAA24F44FF3D1C821E80B7′),DECRYPT(’49A6A38E96A1E89CA3D24BAF122261F1′) FROM cust_master;

      ACNO
———-
DECRYPT(’B42D559293EAA24F44FF3D1C821E80B7′)
————————————————————————————————————————
DECRYPT(’49A6A38E96A1E89CA3D24BAF122261F1′)
————————————————————————————————————————
      1001
ABDUL_KALAM
10000000