Oracle DES3Encrypt Security Oracle Security Tips by Burleson Consulting

This is an excerpt from the bestselling book "Oracle Privacy Security Auditing", a complete Oracle security reference with working Oracle security scripts.
 

---

DES3Encrypt

This is the primary way to encrypt the data. Interestingly, this is an overloaded code, i.e. there are procedures and functions both named after this, and the functions and procedures can take multiple parameters. This may seem like adding to the confusion, but in reality, this may be a boon to work with – since you will have flexibility.

Here is the explanation of the procedure versions of the code. This version takes string values as parameters.

PARAMETER	DESCRIPTION
INPUT_STRING	This is the string to be encrypted in VARCHAR2 datatype. The input must be a multiple of 8 characters, since the encryption algorithm breaks it into blocks of 8 bits and applies the encryption key to it. If the string is not a multiple of 8, it should be padded with blanks to make it that. During decryption, these padded characters must be stripped to arrive at the original value.
KEY_STRING	This is the encryption key in the VARCHAR2 datatype. The key must be either 16 characters (if the double pass is used) or 24 (if the triple pass is used). These passes will be explained in the next parameter
WHICH	This is the parameter to instruct the encryption routine to make it a double pass of a triple pass. The value of 0 here, the default, makes the encryption double pass. This means, the encryption is done with 8 bits once and then again with 8 bits. In triple pass, the double passed one is encrypted again with 8 bits, making it more difficult to crack. Passing a value of 1 in this parameter indicates triple pass.
ENCRYPTED_STRING	This is the only OUT parameter of the procedure, again in VARCHAR2 datatype, which produces the encrypted string.
IV_STRING	This is a parameter newly introduced in Oracle 9i. In addition to the key supplied, the parameter input_vector can also be passed to further strengthen the encrypted code, as well as provide support to the third party encryption routines where the use of this input vector is common. The default is null.

Table 6.1 Parameters for DES3Encrypt Procedure for VARCHAR2

Another version of the same procedure takes raw values as parameters.

PARAMETER	DESCRIPTION
INPUT	This is the input value to be encrypted in RAW datatype.
KEY	This is the encryption key in the RAW datatype.
WHICH	0 – for double pass and 1 – for triple pass.
ENCRYPTED_DATA	This is the only OUT parameter of the procedure, again in RAW datatype.
IV	The same as the explanation for IV_STRING in the string version of the procedure, but in RAW datatype.

Table 6.2 Parameters for DES3Encrypt Procedure for RAW

Here is the function version of the same code using raw data as parameters. This function returns the encrypted value in RAW datatype.

PARAMETER	DESCRIPTION
INPUT	This is the input value to be encrypted in RAW datatype.
KEY	This is the encryption key in the RAW datatype.
WHICH	0 – for double pass and 1 – for triple pass. 0 is the default.
IV	The same as the explanation for IV_STRING in the string version of the procedure, but in RAW datatype. Default is null.

Table 6.3 Parameters for DES3Encrypt Function for RAW

As in case of the procedure, the function also has a counterpart using parameters in VARCHAR2 datatype.

* Since the code DES3Encrypt is defined as both a function and a procedure, as well as overloaded with several types of parameters, there are actually four declarations for the code segment. Therefore, the calling of this code segment with parameters may not work without naming the parameters. You have to use named parameters, as described in the example in the beginning of the chapter.

DES3Decrypt

Similar to the encryption routines, the package also has decryption routines – and there are four declarations to it, just like its encryption counterpart. Here is the explanation of the procedure version using RAW datatypes.

PARAMETER	DESCRIPTION
INPUT	This is the input value to be decrypted in RAW datatype.
KEY	This is the encryption key in the RAW datatype.
DECRYPTED_DATA	This is the only OUT parameter of the procedure, again in RAW datatype. This returns the decrypted raw data.
WHICH	0 – for double pass and 1 – for triple pass.
IV	The same as the explanation for IV in the encryption routines, in RAW datatype. The default is a null value.

Table 6.4 Parameters for DES3Decrypt Function for RAW

The parameters are very similar to the ones found in encryption routines. An example of the decryption routine is provided at the beginning of the chapter.

The DESEncrypt and DESDecrypt code segments, available both as functions and procedures and with overloaded parameters using varchar2 and raw datatype, do the same thing as the DES3 counterparts, only do it in one pass, using a 64-bit key. Actually, the 8 bits of the 64 bits are thrown out and only 56 bits are used. Since they use a low length key, they are more amenable to breakage. In addition, the iv and iv_string parameters are not available in these stored codes.

RAW or VARCHAR2

As you have noticed earlier, the functions and procedures have been overloaded with parameters both in RAW and VARCHAR2. Which one should be used?

The use of varchar2 parameters makes it simpler to use; but may prove difficult to use in some situations. From the example given at the beginning of the chapter note that the encrypted data are in special characters which are not printable. Sometimes this can cause problems, such as in string assignments in some native languages. Also, when Oracle Globalization (a.k.a. National Language System - NLS) is used, the use of these special characters may pose some problems – especially while exporting and importing the data. Therefore, it is better to use the raw datatypes, since Oracle will handle the explicit conversion and these problems will not arise.

However, the use of raw datatypes poses another problem – the storage of these values in tables. Using RAW columns in tables is not the most convenient feature to use – and therefore database administrators and developers alike may eschew it.

To use the raw datatypes, there is another useful feature that can convert the raw data into hexadecimal format, which can be easily stored as a string.

The following code converts the value of the variable of raw datatype to a character format.

v_char_var := utl_raw.cast_to_varchar2(v_raw_var)

To convert a varchar2 value to raw, use the other function:

v_raw_var := utl_raw.cast_to_raw(v_char_var)

A raw value can be further converted into a hexadecimal value using the function RAWTOHEX, and a hexadecimal value can be converted into raw using the corresponding function, hextoraw.

 

This is an excerpt from the book "Oracle Privacy Security Auditing". You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle security and auditing scripts.