Wednesday, November 21, 2007

Secure storage of passwords in Oracle Applications via Encryption of Profile Option Values using dbms_obfuscation_toolkit and Forms Personalization

Have you ever had the need to store a password for accessing external systems in an Oracle Applications field such as Profile Option Values? In a few instances I've seen plain text passwords stored in Profile Option Values both at Site or User levels for scenarios such as connections to external FTP servers and the like. This raises a number of security concerns:

  1. Anyone with System Administrator access can see the passwords, or worse:
  2. Anyone with access to Profile Option Values can see the password(s) if the "visibility" on the profile option is not configured appropriately
  3. Anyone with "read" access to the base table e.g. fnd_profile_option_values - at the database level can select the value from the table
  4. Anybody watching over your shoulder can see the password as you enter it!

Well, now those prying eyes can be kept at bay, with a mix of dbms_obfuscation_toolkit, hex/raw conversions and Forms Personalization trickery. This is especially nice considering that now the Oracle Applications User passwords are also able to be made secure.

Our goals here are:

  • Provide a way to securely input a password on the Oracle eBusiness Suite User interface using User Level Profile Option Values
  • Encrypt the password on save
  • Convert encrypted password to hex and for more friendly viewing on screen if necessary
  • Ensure the encrypted hex version of the password has specific content so we know what is an encrypted value and what isn't
  • Ensure when the User is entering the password it can't be seen on screen
  • Provide a secure decryption routine for backend processes

Here's how we achieve this:

  1. A PL/SQL package providing the encrypt and decrypt routines including the hex conversion with a marker so we know what is an encrypted password. The decrypt routine will only work for the "APPS" user.
  2. A Profile Option to store the password
  3. A Forms Personalization to call encryption routine, overwrite the Profile Option value, plus conceal the data entry field. Note custom.pll could be used for installations prior to 11.5.10.

Okay, so lets get to it.

1. PL/SQL Package.

I'll just provide an extract of the key psuedo code here, if anyone wants a sample package see my profile for contact information.

function encrypt ...
  l_enc := dbms_obfuscation_toolkit.desencrypt( input_string => p_value_in_multiple_of_8_bytes, key_string => my_private_key);
  return rawtohex(utl_raw.cast_to_raw(l_enc));
...
end encrypt;

function decrypt ...
  if user = 'APPS' then
    l_str := utl_raw.cast_to_varchar2(hextoraw(p_value));
    l_str  := dbms_obfuscation_toolkit.desdecrypt( input_string => l_str, key_string => my_private_key );
  end if;
  return l_str
...
end decrypt;

2. Profile Option

Navigate to Application Developer > Profile

3. Forms Personalization

Setup the "conceal" data entry when the row is for our password profile option

Setup the call to encrypt and overwrite the visible value

Copy visible value to backend column

4. Do the same Forms Personalization steps as above for the Personal Profile Values form (function FND_FNDPOMSV)

Sweet! All sorted.

7 comments:

Unknown said...

Hi Gareth Roberts,
Can u kindly provide me the sample coding for the encryption and decryption function. My mail id is g.rathish@consultant.com

With Regards,
G.Rathish Kumar
g.rathish@rediffmail.com

Jayesh said...

I tried this on our 12.1.3 environment and ran into a problem with the forms personalization. The when-validate-record is firing twice once when you save a password and a second time when you are exiting the form. So the password is getting encrypted twice. This may be a little unpredictable based on user navigation. Any workarounds or solutions?

Jayesh said...

I did a workaround for the forms personalization when-validate-record issue mentioned above. I added a menu option for encrypt on the profile option form that would explicity require the sysadmin to select encrypt in order to encrypt the password.

Anonymous said...

Your contents are too straightforward to browse and easy to understand.Password Manager

Anonymous said...

I’m impressed with the special and informative contents that you just offer in such short timing.what is full coverage car insurance

Unknown said...

The gorgeous post learned a great deal Thanks greatly! pay day lenders

Anonymous said...

The gorgeous post learned a great deal Thanks greatly! payday lenders in new york city