SamoraTech.com

Encryption/Decryption By DBMS_OBFUSCATION_TOOLKIT

SAMEER WADHWA

Wadhwa_S@Hotmail.com

DBMS_OBSFUSCATION_TOOLKIT introduced in Oracle 8i for encryption and decryption.

If you have Oracle 8.0.6.x ,you have to install this package as follows

ora816 SamSQL :> @e:\ora816\rdbms\admin\dbmsobtk.sql;

ora816 SamSQL :> @e:\ora816\rdbms\admin\prvtobtk.plb;

ora816 SamSQL :> grant execute on dbms_obfuscation_toolkit to public;

ora816 SamSQL :> /* @e:\ora816 is my oracle Home on Windows NT */

Note : No need to run the above scripts in 8.1.7

PROCEDURE DESENCRYPT

Argument Name

Type

In/Out Default?

INPUT

KEY

ENCRYPTED_DATA

RAW

OUT

PROCEDURE DESDECRYPT

Argument Name

Type

In/Out Default?

INPUT_STRING

KEY_STRING

DECRYPTED_STRING

VARCHAR2

VARCHAR2

OUT

Restriction :- Input data must be multiple of 8 bytes and Key should be exactly of 8 bytes

Let us create the following setup for understanding the functionality .

For setup , I will first create a database table named security table for storing the encrypt data, and two procedures named insert_encrypt_data for encryption and Decrypt_encrypted_data for decryption.

In insert_encrypt_data procedure I will pass two input parameters ,first one is the string which I want to encrypt and second one is the security key . This procedure encrypt data by using DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT procedure and store it into a table named security_table.

In Decrypt _encrypt_data procedure , I will pass one input parameter security key and get the result in the output parameter in decrypt string.This procedure reads the encrypted data from security table and decrypt it through DB_OBFUSCATION_TOOLKIT.DESDECRYPT and return it as a output parameter.

SETUP

Step 1. Creation of a security table

SQL> create table security_table (username varchar2(30),input_date DATE,Secure_data varchar2(80));

Step 2. Creation of a procedure to store encrypted data in the security table

CREATE OR REPLACE PROCEDURE

Insert_encrypt_data(l_string in VARCHAR2,

l_key in varchar2)

l_data varchar2(2000);

Key_check_flag number;

l_encrypted_string varchar2(2000);

BEGIN

--- the key and the input data must have a length

-- divisible by eight (the key must be exactly 8 bytes long).

l_data := RPAD(l_string,(TRUNC(LENGTH(l_string)/8)+1)*8,CHR(0));

key_check_flag := mod(length(l_key),8);

if key_check_flag != 0 then

Raise_application_error(-20199,'Key should be 8 char long');

end if;

-- Encrypt the input string

DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT

(input_string => l_data,

key_string => l_key,

encrypted_string => l_encrypted_string);

--DBMS_OUTPUT.PUT_LINE('l_string ENCRYPTED: ' || l_encrypted_string);

insert into security_table values(user,sysdate,l_encrypted_string);

commit;

-- Dbms_output.put_line('Encrypted data inserted');

END;

Step 3: Creation of a procedure to decrypt data from the security table

CREATE OR REPLACE PROCEDURE

Decrypt_encrypted_data(l_key in varchar2,decrypt out varchar2)

cursor c1 is select secure_data from security_table;

l_data varchar2(2000);

Key_check_flag number;

l_decrypted_string varchar2(2000);

BEGIN

for c1rec in c1 loop

-- Both the key and the input data must have a length

-- divisible by eight (the key must be exactly 8 bytes long).

key_check_flag := mod(length(l_key),8);

if key_check_flag != 0 then

Raise_application_error(-20199,'Key should be 8 char long');

end if;

DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT

(input_string => c1rec.secure_data,

key_string => l_key,

decrypted_string => l_decrypted_string);

-- DBMS_OUTPUT.PUT_LINE('l_string DECRYPT: ' || l_decrypted_string);

decrypt := l_decrypted_string;

end loop;

END;

SETUP HAS COMPLETED , NOW LET US SEE HOW IT WORKS

Execute the procedure for inserting encrypted data

ora816 SamSQL :> execute insert_encrypt_data('YOU ARE INSERTING ENCRYPTED INFORMATION IN SECURITY TABLE','SECURITY');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.91

ora816 SamSQL :> select * from security_table;

USERNAME INPUT_DAT SECURE_DATA

------------ --------- ------------------------------------------------------------------------------

SCOTT 13-APR-01 ║ªv¿-▲(┘↔N¡Z╓∞Å♥d♣¢·├5♫nk╟à@x?e╢~w►╔■(╣9]Ot ╬↕Dδ╕▐╪⌐⌐=∩Φ╧s╞²‼▓

Elapsed: 00:00:00.30

In the above example

SECURITY is a key and 'YOU ARE INSERTING ENCRYPTED INFORMATION IN SECURITY TABLE' is a Data in a SECURE_DATA column as above which we stored in the encrypted form.

Remember that your key should be exactly of 8 bytes and your data should be multiple of 8 bytes. My procedure insert_encrypt_data is taking care of multiplication of 8 bytes string through RPAD function.

Now we will see how encrypted data will return in decrypted form

ora816 SamSQL :> set serveroutput on

ora816 SamSQL :> set autoprint on

ora816 SamSQL :> var decrypt_String varchar2(2000)

ora816 SamSQL :> execute Decrypt_encrypted_data('SECURITY',:decrypt_string);

PL/SQL procedure successfully completed.

DECRYPT_STRING

---------------------------------------------------------

YOU ARE INSERTING ENCRYPTED INFORMATION IN SECURITY TABLE

Elapsed: 00:00:00.50

In the above example SECURITY is the key and decrypt_string is the output variable.

Also if you store multiple encrypted string with a different set of security keys .In that case you can get back your decrypted string corresponding to your security key. It means you only able to decrypt the string which will match your security key.

See the Test Demo :-

Hope this article helps you to understand the functionality of DBMS_OBFUSCATION_TOOLKIT

Please feel free to send me your comments at Wadhwa_S@Hotmail.com