Oracle PL/SQL for DBAs

Encrypting Data

It’s time to see encryption in action in the Oracle database. I’ll show a simple example and then explain the details. Let’s assume here that you are trying to get the encrypted value of the string "SHHH..TOP SECRET“. The following snippet of code does the trick, invoking the DES3ENCRYPT program in the DBMS_OBSFUSCATION_TOOLKIT package:

    1  DECLARE
    2     l_enc_val   VARCHAR2 (200);
    3  BEGIN

    4     DBMS_OBFUSCATION_TOOLKIT.des3encrypt
    5                       (input_string          => 'SHHH..TOP SECRET',
    6                        key_string            => 'ABCDEFGHIJKLMNOP',
    7                        encrypted_string      => l_enc_val
    8                       );
    9                        DBMS_OUTPUT.put_line ('Encrypted Value = ' || l_enc_val);
    10    END;

The output is:

    Encrypted Value = ¿jVªå¬F.(e)?«?0

Line 6 specifies the key string used to encrypt the value, a key that is 16 characters long. The encrypted value is a VARCHAR2 value but is full of control characters. This type of output may not be useful in real-life applications, especially if you want to store it, print it, or tell someone what it is; we may have to make it more manageable by changing it into readable characters. Note, however, that sometimes you don’t want to convert the values from or to the RAW datatype; see the sidebar "When Should You Use Raw Encryption?” later in this chapter. Our first task is to convert the value to the RAW datatype using the built-in package UTL_RAW.

    l_enc_val := utl_raw.cast_to_raw(l_enc_val);

Next, I convert this to hexadecimal using the function RAWTOHEX to make it easier to manipulate:

 l_enc_val := rawtohex(utl_raw.cast_to_raw(l_enc_val)); ...