Discoverer with EBS R12 - Sheet contains no data for custom Oracle eBusiness Suite Responsibility

So you're working with Discoverer 10g integrated with the Oracle eBusiness Suite on Release 12. You've installed and set everything up per Metalink/MOS Note 373634.1 "Using Discoverer 10.1.2 with Oracle E-Business Suite Release 12" plus created a custom application and responsibility to have it's own menu items corresponding to your Discoverer Workbooks/Worksheets.

You login to your new responsibility and click on your new menu entry that you created per Metalink/MOS Note "How to Create a Link to a Discoverer Workbook in Apps R12" and what do you get when you query subledger data such as Payables Invoices, or secured General Ledger data?

This sheet currently contains no data.

Well, its a quick fix. Simply save the following value in the "Initialization SQL Statement - Custom" profile option at Responsibility level for your new Responsibility.

begin gl_security_pkg.init; mo_global.init('M'); end;

Note: this may depend on your setup of the following profile options:

• GL Ledger Name
• GL: Data Access Set
• MO: Operating Unit
• MO: Security Profile

All sorted!

References

• Using Discoverer 10.1.2 with Oracle E-Business Suite Release 12
• How to Create a Link to a Discoverer Workbook in Apps R12

Related Posts

• Discoverer Plus 10g: contact with Server lost: DiscoApplet Error DiscoNetworkException CORBA.COMM_FAILURE. ICX Territory!
• Discoverer Plus window not appearing or Jinitiator JVM conflicts on the Oracle eBusiness Suite: Java JRE EnableSecureStaticVersioning
• Setting your Oracle Applications session: fnd_global.apps_initialize (org_id)

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.

Must have: ATG RUP 6 Patch 5972626 Security Update

Following on from the excellent ATG RUP 5 (5473858) which was a must have in terms of pure number of new technical components, ATG RUP 6 (5903765) is a must have with respect to security.

In my opinion, one of the biggest security holes in the Oracle eBusiness Suite looks to have a fix. Non reversible one way hashed passwords have been missing since day dot. Oracle has been criticized for this gap. Published password decryption methods are publicly available ... but with the release of ATG RUP 6, it seems like a solution is here today!

See the usage of FNDCPASS USERMIGRATE in Note 457166.1 to convert to one way hashed (SHA) passwords. Be aware that desktop smart client software authenticating to the eBusiness Suite may need to be patched, e.g. Discoverer, ADI, Balanced Scorecard, Files Online.

For any of you System Administrator types that have often had Workflow errors occur for a particular reason and then had to sit there and retry each workflow one by one will be glad to know there is now a Retry Errored Workflow Activities program. Nice.

Download ATG RUP 6 now!