Oracle PL/SQL Tutorial/System Packages/dbms obfuscation toolkit

dbms_obfuscation_toolkit.DES3Decrypt

SQL> create table emp(
  2           emp_id                integer         primary key
  3          ,lastname               varchar2(20)    not null
  4          ,firstname              varchar2(15)    not null
  5          ,midinit                varchar2(1)
  6          ,street                 varchar2(30)
  7          ,city                   varchar2(20)
  8          ,state                  varchar2(2)
  9          ,zip                    varchar2(5)
 10          ,shortZipCode                   varchar2(4)
 11          ,area_code              varchar2(3)
 12          ,phone                  varchar2(8)
 13          ,company_name           varchar2(50));
Table created.
SQL>
SQL>
SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
  2                      (1,"Jones","Joe","J","1 Ave","New York","NY","11202","1111","212", "221-4333","Big Company");
1 row created.
SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
  2                      (2,"Smith","Sue","J","1 Street","New York","NY","11444","1111","212", "436-6773","Little Company");
1 row created.
SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
  2                      (3,"X","Peggy","J","1 Drive","New York","NY","45502","2222","212", "234-4444","Medium Company");
1 row created.
SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
  2                      (4,"Murdy","Jill", null,"930 Eady St","New York","NY","45452","6458","212", "634-7733","Wilton Company");
1 row created.
SQL>
SQL> ALTER TABLE emp
  2  ADD ccn  VARCHAR2(2048);
Table altered.
SQL>
SQL> CREATE TABLE emp_id_key_table(emp_id NUMBER PRIMARY KEY,key_value VARCHAR2(80));
Table created.
SQL>
SQL>
SQL> CREATE or REPLACE FUNCTION ccn_decrypt(v_emp_id IN NUMBER, v_ccn IN VARCHAR2)
  2  RETURN VARCHAR2
  3  IS
  4     v_key VARCHAR2(80);
  5     v_decrypted_string VARCHAR2(2048);
  6  BEGIN
  7        SELECT key_value INTO v_key FROM emp_id_key_table WHERE emp_id = v_emp_id;
  8
  9        dbms_obfuscation_toolkit.DES3Decrypt(input_string => v_ccn, key_string => v_key, decrypted_string => v_decrypted_string);
 10        RETURN (v_decrypted_string);
 11  END;
 12  /
Function created.
SQL>
SQL> select emp_id, ccn_decrypt(emp_id, ccn) from emp where ccn is not null;
no rows selected
SQL>
SQL> drop table emp;
Table dropped.
SQL> drop table emp_id_key_table;
Table dropped.

dbms_obfuscation_toolkit.DES3GETKEY and dbms_obfuscation_toolkit.DES3ENCRYPT

SQL> -- create demo table
SQL> create table emp(
  2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
  3    fname         VARCHAR2(10 BYTE),
  4    lname          VARCHAR2(10 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(10 BYTE),
  9    Description        VARCHAR2(15 BYTE)
 10  )
 11  /
Table created.
SQL>
SQL>
SQL>
SQL> CREATE or REPLACE TRIGGER ccn_encrypt_trig
  2  BEFORE INSERT or UPDATE of fname on emp
  3  FOR EACH ROW
  4  DECLARE
  5     v_key VARCHAR2(80);
  6     v_seed VARCHAR2(80) := "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ";
  7     v_encrypted_string VARCHAR2(2048);
  8  BEGIN
  9        dbms_obfuscation_toolkit.DES3GETKEY(which=>1, seed_string=>v_seed,key=>v_key);
 10        dbms_obfuscation_toolkit.DES3ENCRYPT(input_string => RPAD(:NEW.fname,16," "), key_string => v_key, encrypted_string => v_encrypted_string );
 11  END;
 12  /
Trigger created.
SQL>
SQL> -- prepare data
SQL> insert into emp(ID,  fname, lname, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
  3  /
insert into emp(ID,  fname, lname, Start_Date,                     End_Date,
                   Salary,  City,       Description)
            *
ERROR at line 1:
ORA-28237: seed length too short
ORA-06512: at "SYS.DBMS_OBFUSCATION_TOOLKIT_FFI",
line 3
ORA-06512: at "SYS.DBMS_OBFUSCATION_TOOLKIT", line 76
ORA-06512: at "sqle.CCN_ENCRYPT_TRIG", line 6
ORA-04088: error during execution of trigger
"sqle.CCN_ENCRYPT_TRIG"

SQL>
SQL> drop table emp;
Table dropped.
SQL>

Demonstrate DES3 encryption

SQL>
SQL> DECLARE
  2   input_string        VARCHAR2(160) := "password01";
  3   key_string          VARCHAR2(24);
  4
  5   encrypted_string            VARCHAR2(2048);
  6   decrypted_string            VARCHAR2(2048);
  7     error_in_input_buffer_length EXCEPTION;
  8     PRAGMA EXCEPTION_INIT(error_in_input_buffer_length, -28232);
  9     INPUT_BUFFER_LENGTH_ERR_MSG VARCHAR2(100) := "              ";
 10
 11  BEGIN
 12        input_string := input_string||input_string||input_string||input_string||input_string||input_string||input_string||input_string;
 13        dbms_output.put_line("input string              : " || input_string);
 14        dbms_obfuscation_toolkit.DES3GETKEY(which=>1, seed_string=>input_string,key=>key_string);
 15        dbms_obfuscation_toolkit.DESENCRYPT(input_string => input_string,key_string => key_string, encrypted_string => encrypted_string );
 16        dbms_output.put_line("encrypted string              : " ||encrypted_string);
 17        dbms_obfuscation_toolkit.DESDecrypt(input_string => encrypted_string,key_string => key_string, decrypted_string => decrypted_string);
 18        dbms_output.put_line("Decrypted output             : " ||decrypted_string);
 19        if input_string = decrypted_string THEN
 20           dbms_output.put_line("> DES Encryption and Decryption successful");
 21        END if;
 22     EXCEPTION
 23        WHEN error_in_input_buffer_length THEN dbms_output.put_line("> " || INPUT_BUFFER_LENGTH_ERR_MSG);
 24     END;
 25
 26  /

PL/SQL procedure successfully completed.
SQL>