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:
- Anyone with System Administrator access can see the passwords, or worse:
- Anyone with access to Profile Option Values can see the password(s) if the "visibility" on the profile option is not configured appropriately
- 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
- 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:
- 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.
- A Profile Option to store the password
- 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.
