8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Data Encryption - DBMS_OBFUSCATION_TOOLKIT
The DBMS_OBFUSCATION_TOOLKIT
package was desupported in Oracle 10.2. Please use the DBMS_CRYPTO
package instead, described here.
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.
Related articles.
- Database Security Enhancements in Oracle Database 10g - DBMS_CRYPTO
- Transparent Data Encryption (TDE) in Oracle 10g Database Release 2
- SecureFiles in Oracle 11g Database Release 1 - LOB Encryption
- Tablespace Encryption in Oracle 11g Database Release 1
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:
- DBMS_OBFUSCATION_TOOLKIT
- Database Security Enhancements in Oracle Database 10g - DBMS_CRYPTO
- Transparent Data Encryption (TDE) in Oracle 10g Database Release 2
- SecureFiles in Oracle 11g Database Release 1 - LOB Encryption
- Tablespace Encryption in Oracle 11g Database Release 1
Hope this helps. Regards Tim...