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.
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; /
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; /
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.
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:
Hope this helps. Regards Tim...
Back to normal view: https://oracle-base.com/articles/8i/data-encryption