Querying HR Secured Views in Oracle Application Express

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.

per_people_f snippet

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.

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.

Screenshot

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

Customisation
…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.

2 Comments

  1. JJ Kruger

    Hi John

    Thanks for the article, but I have sort of the reverse effect.
    I need to enforce the built-in security on per_people_f but at this stage all records are shown.

    To give you a background, we managed to successfully integrate Apex with EBS using the EBS authentication when launching an application from the EBS menu.

    This all works well when checking for which functions users have access to through the responsibilities in EBS.

    When I query a record in per_people_f with a restricted user after the global initialization in my developer tool, the access is restricted as expected and no record is shown. When running the query in Apex after being logged in through EBS the record is still shown in the interactive report.

    If you have any ideas please let me know.

    Thanks
    JJ

  2. John

    Hi,
    The default for secure views is to show all records rather than hide all – so if you don’t have any security profile being picked up, you’ll get everything returned.

    Have you put a call to fnd_global.apps_initialize in the session initialization section of your APEX app? APEX sessions are stateless, and you’re not guaranteed to get the same session for each page – so you need to call apps_initialize in that section so that each session is initialized with your apps context.

Leave a Reply

Your email address will not be published. Required fields are marked *

3 + four =

This site uses Akismet to reduce spam. Learn how your comment data is processed.