Data Encryption - DBMS_OBFUSCATION_TOOLKIT Security

Create the Package

First we create the package header containing the two conversion functions.

CREATE OR REPLACE PACKAGE toolkit AS

  FUNCTION encrypt (p_text  IN  VARCHAR2) RETURN RAW;
  
  FUNCTION decrypt (p_raw  IN  RAW) RETURN VARCHAR2;
  
END toolkit;
/

Create the Package Body

All VARCHAR2 inputs are padded to multiples of 8 charaters, with the encryption key also being a multiple of 8 charaters. The encryption key and padding characters can be altered to suit.

CREATE OR REPLACE PACKAGE BODY toolkit AS

  g_key     RAW(32767)  := UTL_RAW.cast_to_raw('12345678');
  g_pad_chr VARCHAR2(1) := '~';

  PROCEDURE padstring (p_text  IN OUT  VARCHAR2);


  -- --------------------------------------------------
  FUNCTION encrypt (p_text  IN  VARCHAR2) RETURN RAW IS
  -- --------------------------------------------------
    l_text       VARCHAR2(32767) := p_text;
    l_encrypted  RAW(32767);
  BEGIN
    padstring(l_text);
    DBMS_OBFUSCATION_TOOLKIT.desencrypt(input          => UTL_RAW.cast_to_raw(l_text),
                                        key            => g_key,
                                        encrypted_data => l_encrypted);
    RETURN l_encrypted;
  END;
  -- --------------------------------------------------



  -- --------------------------------------------------
  FUNCTION decrypt (p_raw  IN  RAW) RETURN VARCHAR2 IS
  -- --------------------------------------------------
    l_decrypted  VARCHAR2(32767);
  BEGIN
    DBMS_OBFUSCATION_TOOLKIT.desdecrypt(input => p_raw,
                                        key   => g_key,
                                        decrypted_data => l_decrypted);
                                        
    RETURN RTrim(UTL_RAW.cast_to_varchar2(l_decrypted), g_pad_chr);
  END;
  -- --------------------------------------------------


  -- --------------------------------------------------
  PROCEDURE padstring (p_text  IN OUT  VARCHAR2) IS
  -- --------------------------------------------------
    l_units  NUMBER;
  BEGIN
    IF LENGTH(p_text) MOD 8 > 0 THEN
      l_units := TRUNC(LENGTH(p_text)/8) + 1;
      p_text  := RPAD(p_text, l_units * 8, g_pad_chr);
    END IF;
  END;
  -- --------------------------------------------------

END toolkit;
/

Test It

We can test the basic functionality using the following code.

DECLARE
  l_value VARCHAR2(16) := 'ORACLE-BASE';
  l_raw   RAW(16);
BEGIN
  DBMS_OUTPUT.put_line('l_value: ' || l_value);
  l_raw := toolkit.encrypt(l_value);
  DBMS_OUTPUT.put_line('l_raw: ' || l_raw);
  DBMS_OUTPUT.put_line('Original Value : ' || toolkit.decrypt(l_raw));
END;
/

Remember that the length of the output from the encryption routine will be rounded up to the next multiple of 8 characters. If the results are to be stored as RAW datatypes in the database you must make sure enough room is allocated. SQL*Plus displays the contents of RAW variable in HEX so it appears to be twice as long as it actually is.

Encrypt Table Data

The following code provides a simple example of how data in a table might be encrypted using a trigger. First we must create a test table with an appropriate trigger.

CREATE TABLE encrypted_data (
  username  VARCHAR2(20),
  data      RAW(16)
);

CREATE OR REPLACE TRIGGER encrypted_data_biur_trg 
BEFORE INSERT OR UPDATE ON encrypted_data
FOR EACH ROW
DECLARE
BEGIN
  :new.data := toolkit.encrypt(UTL_RAW.cast_to_varchar2(:new.data));
END;
/

Next we test the trigger using some simple insert, update and query statements.

SQL> INSERT INTO encrypted_data (username, data)
  2  VALUES ('tim_hall', UTL_RAW.cast_to_raw('My Secret Data'));

1 row created.

SQL> SELECT * FROM encrypted_data;

USERNAME             DATA
-------------------- ----------------------------------------
tim_hall             FA57C55510D258C73DE93059E3DC49EC

1 row selected.

SQL> COLUMN data FORMAT A40
SQL> SELECT username, toolkit.decrypt(data) AS data FROM encrypted_data;

USERNAME             DATA
-------------------- ----------------------------------------
tim_hall             My Secret Data

1 row selected.

SQL> UPDATE encrypted_data
  2  SET    data     = UTL_RAW.cast_to_raw('My NEW Secret')
  3  WHERE  username = 'tim_hall';

1 row updated.

SQL> COLUMN data FORMAT A40
SQL> SELECT username, toolkit.decrypt(data) AS data FROM encrypted_data;

USERNAME             DATA
-------------------- ----------------------------------------
tim_hall             My NEW Secret

1 row selected.

SQL>