Querying HR Secured Views in Oracle Application Express

Oracle Application Express (APEX) is increasingly being used to produce custom developments within Oracle E-Business Suite. I’ve posted previously about how to configure your custom applications to use E-Business Suite context for purposes such as foundation API’s, MOAC security, profile values and such (using fnd_global.apps_initialize), however I’ve generally put in a note of warning that such techniques may not work with Human Resources Security. So here I will explain why that is, what you might be able to do to get around that. This assumes the Embedded PL/SQL Gateway (EPG) is being used.

First, let’s recap how HR Security is implemented with E-Business Suite, taking People table as an example.
We have the base table per_all_people_f which is not secured (by VPD or otherwise). Built upon this are a set of secure views such as per_people_f which are queries from the base table however with a call to the API hr_security as follows.

The key call in this code is to hr_security.show_person(...) which evaluates the users access to that person record based upon the security profile the user is currently using (the actual method for this depends on whether Cross Business Group security is in use).

So, assume you have set up APEX for E-Business Suite authentication and write a query to bring back some users based on say a supervisor hierarchy security profile.

You might be shocked to find you get no data returned. However emulating your SQL in your favourite developer tool as the application user works as expected.
The reason for this is due to the way the EPG connects to the database. APEX connects to the database as the ANONYMOUS user, however parses as the schema specified in the application. This is for security reasons and there is some further reading in this old post.
So why does connecting as ANONYMOUS cause an issue? Well, it’s all down to the initialization of the HR Security mechanisms in E-Business Suite. Prior to checking security, a call to hr_security.initialise_globals is made. This checks what database user is being used and returns a schema mode flag based on the following.

So the cursor checks the value of the read_only_flag column in the FND_ORACLE_USERID table and returns a ‘Y’ if the value is U, M or K. These are reserved values for the APPS user, Multi-Lingual and Multi-Currency users respectively. This means the value of ‘N’ is returned for the APEX user. It would be nice to be able to simply register the ANONYMOUS user with a valid value, however unfortunately the values U, M and K are reserved and not selectable in the professional maintenance form. It is of course not recommended to update this via the back-end as there could be a whole host of unknown implications, even if multi-currency/languages are not used.
So why does HR do this? Well it’s all related to reporting users which effectively allow you to secure hr data to an external (not E-Business Suite) application using database accounts. Sounds great, apart from you are restricted to a single security profile per user, and of course you cannot have multiple users on the APEX side to support that.

All this aside, how do we deal with this? Well, without customisation of the standard security package it’s pretty much impossible without risking greater implications as described above. So personally, I’d recommend customising the hr_security module as follows

…and then register your change as a customisation within the application framework to protect against future patching. You also need to register the ANONYMOUS user so you get the record in FND_ORACLE_USERID however it can simply be registered with a status of Enabled.

This will allow you to use standard HR security within APEX; there is still of course a slight risk as there is a customisation to the standard code however this is minimal as the ANONYMOUS user is a record you have created.
There are other options however I personally believe this to be the safest and with least impact.