Data Encryption

The DBMS_OBFUSCATION_TOOLKIT package provides a simple API for data encryption. Oracle8i only provides DES encryption whilst Oracle9i also includes DES3 encryption. In this article I'll create a simple package containing functions to DES encrypt and decrypt test strings:

1. Create the Package
2. Create the Package Body
3. Test It
4. Encrypt Table Data

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>
With the exception of the calls to the UTL_RAW package, this method hides most of the work from the developer.For more information see:

1. DBMS_OBFUSCATION_TOOLKIT