Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted May 27, 2004

Decrypting Oracle's DBMS_OBFUSCATION_TOOLKIT

By Steve Callan

To secure sensitive data, Oracle provides developers with the DBMS_OBFUSCATION_TOOLKIT. This supplied PL/SQL package, available in both the Standard and Enterprise editions, is the industrial strength version of the DBMS_RANDOM package. Upon reading Oracle's documentation about this package, you will need a decoder just to keep track of all the acronyms and to decrypt the errors in Oracle's sample code. However, once you see how easy it is to use, you will be able to take Oracle's code example and modify it for your own use.

If you have never worked or dealt with encryption, Oracle's documentation provides a short summary of encryption principles in the Oracle9i Supplied PL/SQL Packages and Types Reference documentation. Like many other things in Oracle, just because you can do something does not mean you should do it. Encryption is one of those features best used when used appropriately. What is not pointed out is this truism: what is created by man can be broken by man. How does this apply to Oracle's obfuscation toolkit? Let's look at the one of the Data Encryption Standard options.

Oracle states that the "Triple DES (3DES) is a far stronger cipher than DES; the resulting ciphertext (encrypted data) is much harder to break using an exhaustive search: 2**112 or 2**168 attempts instead of 2**56 attempts." What is the significance of these numbers? Suppose you build a computer capable of making 1000 attempts each second. How long would it take to exhaust 2 to the 56 (256) attempts? Before you get your calculator, take a wild guess. Did you guess something close to just over two million years? A very significant obstacle in launching a brute force attack against encrypted data is time. If you are dealing with 2 to the 168 attempts, your next biggest obstacle, after time itself, is the sun, because it will go supernova many billions of years before you'll finish.

Unlike port assignments, when it comes to encryption, the standards are much clearer, and in fact, are promulgated by the government. You can read more about the standards (plus get some background on encryption) at http://www.itl.nist.gov/fipspubs/fip46-2.htm.

Oracle's documentation

One example Oracle uses in its documentation may leave you feeling a bit mystified. Why is that? Because of the errors in the code, that's why. If you use the example shown in Chapter 14 of the Oracle9i Application Developer's Guide - Fundamentals, you will need to make the changes shown in the code below.

DECLARE
 input_string        VARCHAR2(16) := 'tigertigertigert';
 key_string          VARCHAR2(8)  := 'scottsco';
   
 encrypted_string    VARCHAR2(2048);
 decrypted_string    VARCHAR2(2048); 
 
 error_in_input_buffer_length EXCEPTION;
 
 PRAGMA EXCEPTION_INIT(error_in_input_buffer_length, -28232);
   INPUT_BUFFER_LENGTH_ERR_MSG VARCHAR2(100) :=
    '*** DES INPUT BUFFER NOT A MULTIPLE OF 8 BYTES ***';

BEGIN
   dbms_output.put_line('> ========= BEGIN TEST =========');
   dbms_output.put_line('> Input string                 : ' || 
 input_string);
   --BEGIN <-- ignore this, typo in Oracle's documentation
      dbms_obfuscation_toolkit.DESEncrypt( 
                   input_string => input_string, 
                   key_string => key_string, 
                   encrypted_string => encrypted_string );
      dbms_output.put_line('> Encrypted string             : ' || 
                   encrypted_string);
-- Add DESDecrypt as shown, change raw to key_string
      dbms_obfuscation_toolkit.DESDecrypt(
                   input_string => encrypted_string, 
                   key_string => key_string, 
                   decrypted_string => decrypted_string);
      dbms_output.put_line('> Decrypted output             : ' || 
                   decrypted_string);
      dbms_output.put_line('>  ');      
      if input_string = 
                   decrypted_string THEN
         dbms_output.put_line('> DES Encryption and Decryption successful');
      END IF;
EXCEPTION
      
   WHEN error_in_input_buffer_length THEN
      dbms_output.put_line('> ' || INPUT_BUFFER_LENGTH_ERR_MSG);
END;

Let's run this and see what the output is like.

The encrypted string portion may look different in your SQL*Plus session because some of the characters will appear as darkened rectangles (the ANSI equivalent of "I don't know how to display this character, so here's a black rectangle for you."). Once you get past the long package and subprogram names, using the toolkit is pretty simple. The example shown in Chapter 34 of the Oracle9i Supplied PL/SQL Packages and Types Reference actually works as is and produces the output shown below.

DECLARE
   input_string   VARCHAR2(16) := 'tigertigertigert';
   raw_input      RAW(128) := UTL_RAW.CAST_TO_RAW(input_string);
   key_string     VARCHAR2(8)  := 'scottsco';
   raw_key        RAW(128) := UTL_RAW.CAST_TO_RAW(key_string);
   encrypted_raw               RAW(2048);
   encrypted_string            VARCHAR2(2048);
   decrypted_raw               RAW(2048);
   decrypted_string            VARCHAR2(2048); 
   error_in_input_buffer_length EXCEPTION;

   PRAGMA EXCEPTION_INIT(error_in_input_buffer_length, -28232);
   INPUT_BUFFER_LENGTH_ERR_MSG VARCHAR2(100) :=
    '*** DES INPUT BUFFER NOT A MULTIPLE OF 8 BYTES - IGNORING 
EXCEPTION ***';
   double_encrypt_not_permitted EXCEPTION;
   PRAGMA EXCEPTION_INIT(double_encrypt_not_permitted, -28233);
   DOUBLE_ENCRYPTION_ERR_MSG VARCHAR2(100) :=
    '*** CANNOT DOUBLE ENCRYPT DATA - IGNORING EXCEPTION ***';

-- 1. Begin testing raw data encryption and decryption
   BEGIN
   dbms_output.put_line('> ========= BEGIN TEST RAW DATA =========');
   dbms_output.put_line('> Raw input                        : ' || 
                 UTL_RAW.CAST_TO_VARCHAR2(raw_input));
   BEGIN 
      dbms_obfuscation_toolkit.DESEncrypt(input => raw_input, 
               key => raw_key, encrypted_data => encrypted_raw );
      dbms_output.put_line('> encrypted hex value              : ' || 
               rawtohex(encrypted_raw));
      dbms_obfuscation_toolkit.DESDecrypt(input => encrypted_raw, 
               key => raw_key, decrypted_data => decrypted_raw);
      dbms_output.put_line('> Decrypted raw output             : ' || 
                    UTL_RAW.CAST_TO_VARCHAR2(decrypted_raw));
      dbms_output.put_line('>  ');      
      if UTL_RAW.CAST_TO_VARCHAR2(raw_input) = 
                    UTL_RAW.CAST_TO_VARCHAR2(decrypted_raw) THEN
         dbms_output.put_line('> Raw DES Encyption and Decryption successful');
      END if;
   EXCEPTION
      WHEN error_in_input_buffer_length THEN
             dbms_output.put_line('> ' || INPUT_BUFFER_LENGTH_ERR_MSG);
   END;
   dbms_output.put_line('>  ');


-- 2. Begin testing string data encryption and decryption
   dbms_output.put_line('> ========= BEGIN TEST STRING DATA =========');

   BEGIN 
      dbms_output.put_line('> input string                     : ' 
                           || input_string);
      dbms_obfuscation_toolkit.DESEncrypt(
               input_string => input_string, 
               key_string => key_string, 
               encrypted_string => encrypted_string );
      dbms_output.put_line('> encrypted hex value              : ' || 
                   rawtohex(UTL_RAW.CAST_TO_RAW(encrypted_string)));
      dbms_obfuscation_toolkit.DESDecrypt(
               input_string => encrypted_string, 
               key_string => key_string, 
               decrypted_string => decrypted_string );
      dbms_output.put_line('> decrypted string output          : ' || 
                 decrypted_string);
      if input_string = decrypted_string THEN
         dbms_output.put_line('> String DES Encyption and Decryption successful');
      END if;
   EXCEPTION
      WHEN error_in_input_buffer_length THEN
             dbms_output.put_line(' ' || INPUT_BUFFER_LENGTH_ERR_MSG);
   END;
   dbms_output.put_line('>  ');
END;

> ========= BEGIN TEST RAW DATA =========
> Raw input              : tigertigertigert
> encrypted hex value    : 5AAB8C0D278AD75CA1968790D00FD75A
> Decrypted raw output   : tigertigertigert
>
> Raw DES Encyption and Decryption successful
>
> ========= BEGIN TEST STRING DATA =========
> input string           : tigertigertigert
> encrypted hex value    : 5AAB8C0D278AD75CA1968790D00FD75A
> decrypted string output: tigertigertigert
> String DES Encyption and Decryption successful

This does the same thing as the previous example, except that the output is shown using hexadecimal values (it's more "readable?").

Putting it to use

Let's take the code from Oracle and modify it for our use. We will not worry about the exception code, and we will take input from the user to encrypt a 16-digit credit card number.

Here is the shortened code, turned into a procedure, along with the sample data.

So far, so good. Can the credit number be unencrypted? Let's see.

SQL> CREATE or REPLACE PROCEDURE decrypt_cc_no
  2  (empno number) 
  3  IS
  4  
  5   key_string          VARCHAR2(8)  := 'scottsco';
  6     
  7   encrypted_string    VARCHAR2(2048);
  8   decrypted_string    VARCHAR2(2048);
  9   
 10  BEGIN
 11    select cc_no into encrypted_string
 12    from cc_table
 13    where empno = empno;
 14  
 15    dbms_obfuscation_toolkit.DESDecrypt(
 16                     input_string => encrypted_string, 
 17                     key_string => key_string, 
 18                     decrypted_string => decrypted_string);
 19             
 20    update cc_table
 21    set cc_no = decrypted_string
 22    where empno = empno;          
 23                  
 24  END;
 25  /

Procedure created.

SQL> exec decrypt_cc_no(4569);

PL/SQL procedure successfully completed.

SQL> select * from cc_table;

     EMPNO CC_NO
---------- ------------------
      4569 4323445698329120

We are good to go! The credit number was successfully restored to its unencrypted value.

In closing

Although the examples shown were quite simple (and many of the variable names stayed the same to help with comparing code), the power and ease of use of the DBMS_OBFUSCATION_TOOLKIT is readily seen. If you were charged with encrypting a table's worth of credit card numbers, you would probably use a cursor to gather all of the empno's and then use a cursor for loop to update the records in one fell swoop. I hope that this article has "decrypted" some of the mystery behind one of Oracle's more powerful features.

» See All Articles by Columnist Steve Callan



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date