Cloud

O SpecGold OracleBusIntApps7 clr

 Gcloud

 

   Call us now 

  Manchester Office

  +44 (0) 8450 940 998

 

  

 

John's Blog

This is my blog of various topics, from things that I think might be useful to others, to things that I just find interesting personally. If you have any comments or questions on any of my posts then please do ask - any positive contribution is very much welcomed.

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.
  • Team Blogs
    Team Blogs Find your favorite team blogs here.
  • Login
    Login Login form

Querying HR Secured Views in Oracle Application Express

  • Font size: Larger Smaller
  • Subscribe to this entry
  • Print

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.

Last modified on
in Technical Hits: 3759 2 Comments
0
My primary area of interest and expertise is Oracle E-Business Suite, particularly Foundation, Human Resources, Payroll, Time and Labor, Warehouse Management and Inventory. Whilst I prefer to focus on functional aspects I do have a keen interest in technical areas too such as SQL tuning/the optimizer, Application Express (APEX), Unix (particularly Linux) and general development.


Comments

  • Guest
    LIOR Sunday, 22 January 2017

    Hello
    I read the article - it excellent solution and it works fine.

    I have one question:
    I created application process when "on load: after header" that make apps_initialized with session user and ebs resopnsibilty (which I get from EBS function).
    I have sql select and popup lov that use this initialization for hr_security, in my page.
    My problem is that the sql select in region work fine with the hr_security, but the popup lov didn’t get it right, it seems like it didn’t made any initialization at all.
    My versions are :
    APEX 5.0.3
    EBS 12.1.3

    Do you have any idea ?

    Thanks a lot
    Lior

  • John Keymer
    John Keymer Thursday, 26 January 2017

    Rather than calling fnd_global.apps_initialize in an application process, use the "VPD" functionality - you can find this at the bottom of the security tab in the application properties.

Leave your comment

Guest
Guest Tuesday, 24 October 2017