Oracle & Encryption

Recently I had a requirement from a customer to provide a Single Sign On passthrough solution for our web application called Coreportal (Coreportal is an employee and manager self service application within the HR space). Our application already has a single sign on adapter which nicely authenticates against LDAP v.3 directories. I have been meaning to write a blog about this SSO adapter for a while, I hope to get around to completing that article shortly. This specific requirement needed to bypass our authentication process but needed to generate our session cookies. Effectively I had to build a trust between both applications, one of the most important links required passing some unique identifier between applications this identifier needed to be encrypted. Customers application had already completed its own authentication process; once logged into their application the employee wanted to seamlessly link to our application. I decided to go with AES encryption, this required me to refresh my knowledge with encryption. This post details the knowledge I gained while researching. I had already completed a previous project with DES encryption. The back end database is Oracle. Prior to Oracle 10G, AES is not available instead DES and Triple DES are available using an oracle package called dbmsobfuscation this package was useful but I needed to ensure stronger encryption. Note that Oracle 10G still supports dbmsobfuscation for backward compatibility. Oracle 10G provides a nice new package called dbmscrypto I decided to build a wrapper package to make it easier to use this package, I had a requirement to use the dbmscrypto package in several projects and to also include hashing which dbms_crypto also supports. My objectives were

  • wrapper package making it easy for encryption and hashing
  • make it easy for my colleagues to call
  • provide facility allowing more complex parameters be supplied (chaining, padding, initialisation vectors, keys, use of different algorithms etc)

In the end I provided the ability to call encryption as follows

declare
-- Local variables here
v_encrypt cp_encrypt.encryption_object;
v_decrypt cp_encrypt.decryption_object;
v_value   varchar2(200) := 'darragh';


begin
cp_encrypt.encrypt_value(p_value => v_value,p_encrytion_object => v_encrypt);
dbms_output.put_line('Encrypted Value is '||v_encrypt.encrypted_value);
cp_encrypt.decrypt_value(p_value => v_encrypt.encrypted_value,p_decryption_object => v_decrypt);
dbms_output.put_line('Decrypted Value is '||v_decrypt.plaintext);
end;

And, the output of this is

Encrypted Value is BE0FB3CD88E804CE1E126ED331CD14FE
Decrypted Value is darragh

Of course this is the simplest use of the package; it did satisfy my immediate requirement providing a simple solution for my colleagues, of course the package itself is making some default assumptions especially around the private key, chaining, padding, IV and of course what encryption algorithm to use. Most of my colleagues don't really care about any of these parameters, they only need to be sure the value returned is encrypted and some one else manages the key. The default settings being used

  • Algorithm AES256
  • Chaining CBC (cipher block chaining)
  • Padding use Zero's
  • Initialisation Vector (IV) none
  • Private Key picks up a key from a privately held database table

Each of the above can actually be passed to the package thereby over writing the default values. A more complex call to the package allows for testing FIPS-197 FIPS-197 test vectors. These test vectors are known results when using certain parameters. Below presents an example of a more complex call to the package, the test vector for AES256 says using

key "000102030405060708090a0b0c0d0e0f101112131415161718191a1b1c1d1e1f" plaintext "00112233445566778899aabbccddeeff"

should produce cipher text "8ea2b7ca516745bfeafc49904b496089"

declare
-- Local variables here
v_encrypt cp_encrypt.encryption_object;
v_decrypt cp_encrypt.decryption_object;
v_value   varchar2(200) := '00112233445566778899aabbccddeeff';</code>

begin

cp_encrypt.encrypt_value(p_value => v_value,
p_encrypt_alg => 'AES256',
p_encryption_key => '000102030405060708090a0b0c0d0e0f101112131415161718191a1b1c1d1e1f',
p_convert_key_to_raw => false,p_convert_value_to_raw => false,
p_encrytion_object => v_encrypt);

dbms_output.put_line('Encrypted Value is '||v_encrypt.encrypted_value);

end;

this produces
Encrypted Value is 8EA2B7CA516745BFEAFC49904B496089

and thereby proving compliance with FIPS-197. Note in this use of my package I had to set a lot more parameters, firstly I indicate which algorithm I wanted to use in this case "AES256", I also pass in a private key, this therefore will ensure my package does not use the stored private key, also note I set a parameter not to convert the key to a Raw Hex value because the supplied key is already in HEX, this also applies to the value produced by the package which indicates not to set the returned cipher text into RAW. This too parameters convertkeytoraw and convertvaluetoraw are only used to test FIPS-197 test vectors.

The above two examples present how easy or complex you want to get, my package supports AES128, AES256, DES and Triple Des, in addition hashing algorithms may also be used both SHA-1 and MD5, below is an example of using SHA-1.

declare
-- Local variables here
v_encrypt cp_encrypt.encryption_object;
v_decrypt cp_encrypt.decryption_object;
v_value   varchar2(200) := 'darragh';</code>

begin

cp_encrypt.encrypt_value(p_value => v_value,
p_encrypt_alg => 'SHA-1',
p_encrytion_object => v_encrypt);

dbms_output.put_line('HASH Value is '||v_encrypt.encrypted_value);

end;

producing the following result

HASH Value is B99CE16F63B6C05F43995D16FA3E6266CFF0541A

At the end of my project I provided a nice wrapper package catering for simple use or more complex use along with hashing facilities. Note you will need to grant dbms_crypto from your sys account to public.

You should be able to download this package from the following location

location is current unavailable.