APEX and Application Contexts

My current project uses Oracle RLS (Row Level Security) to ensure each user can only see the data they’re allowed to see. This has made a potentially very complex application very simple. My APEX pages can query any table, no WHERE clauses needed (except for user-entered filters), and I can be confident they won’t be able to inadvertently see rows they’re not supposed to see if I forget to add the right predicate on every page that queries each table.

Now, remember that APEX is a stateless environment as far as database sessions are concerned: when a page is requested, it might be generated by any session running on the database; it might re-use a session that had just finished rendering a page for someone else entirely. Therefore, it is imperative that all my database code assumes nothing about the state of package variables, for example. Instead, we use Oracle Application Contexts to hold session-specific information. For example, a context might hold the current user’s identifier, or a flag that indicates they have been granted a particular privilege. As an added advantage, when we use contexts, we can easily pass data into views.

How do we guarantee that the context has been set correctly for the current user’s session? We could follow the guidelines in the APEX help (quoted below) to set the context at the start of every page call, then clear them at the end of the page call. If we were to do this, these are the steps we’d take (these are taken from APEX 4.1.1 but should be comparable in other releases):

  1. Create the context:
    CREATE OR REPLACE CONTEXT MY_CONTEXT USING MY_PACKAGE;
    
  2. Create a database package with a procedure to Set the context variable(s), e.g.:
    PROCEDURE set_session IS
    BEGIN
      DBMS_SESSION.set_context
        (namespace => 'MY_CONTEXT'
        ,attribute => 'is_admin'
        ,value     => 'Y');
    END new_session;
    
  3. Edit Application Properties; under Security, in the Database Session section, set Initialization PL/SQL Code to call MY_PACKAGE.set_session.

It is interesting that the APEX help page for Initialization PL/SQL Code has this to say:

“Use this attribute to enter a PL/SQL block that sets a context for the database session associated with the current “show page” or “accept page” request. The block you enter here is executed at a very early point during the page request, immediately after the APP_USER value is established. The value of APP_USER (using :APP_USER or v(‘APP_USER’)) may be used within the block. Values of other items in session state may be referenced as well, but any such items must have been established in session state before the initiation of the current page request. Consider the following example:

dbms_session.set_context(‘CTX_USER_QRY’,’USERPRIV’,my_package.my_function(:APP_USER));
It sets the value of USERPRIV in the context named CTX_USER_QRY to the value returned by the function my_function in package my_package. The function is passed the current value of APP_USER as an input argument. Presumably, the named context would be used in a VPD policy ( created within the application’s parsing schema) to effect the generation of predicates appropriate to the authenticated user.

Virtual Private Database, also know as Fine-Grained Access Control or FGAC, is an Oracle database feature that provides an application programming interface (API) that enables developers to assign security policies to database tables and views. Using PL/SQL, developers can create security policies with stored procedures and bind the procedures to a table or view by means of a call to an RDBMS package. Such policies are based on the content of application data stored within the database, or based on context variables provided by Oracle database. In this way, VPD permits access security mechanisms to be removed from applications, and to be situated closer to particular schemas.

…”

This works fine. Before every page is rendered, APEX calls our package to set the context variables. All our procedures, views, etc. as well as our RLS package can use these context variables as required.

Is there a simpler way? Yes.

Just this morning I attended an “Odds and Ends” session presented by Connor McDonald at the AUSOUG Monthly meeting in Perth. He briefly mentioned a cool feature of Oracle contexts – the fact that they can be made globally accessible. I had seen this in the documentation but didn’t know what the words meant or how it might be useful.

What Connor’s talk alerted me to was that what a Globally Accessible context does could be very useful for our APEX application: it allows a context to be simultaneously available from any session on the database, to any user who logs in and sets the correct Client Identifier. We can use this to avoid having to call any code from Initialization PL/SQL Code, which would be run before every page request, which may have a performance impact; instead, the context variables will follow the user around, no matter which database session services their page request.

To do this, you simply:

  1. Create our context to be globally accessible:
    CREATE OR REPLACE CONTEXT MY_CONTEXT USING MY_PACKAGE
      ACCESSED GLOBALLY;
    
  2. Alter our database package to set the context variables, this time sending the client_id parameter, e.g.:
    PROCEDURE set_session IS
    v_real_client_id VARCHAR2(200);
    BEGIN
      v_real_client_id := REPLACE(
        SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER')
                   ,'nobody'
                   ,v('APP_USER'));
      DBMS_SESSION.set_context
        (namespace => 'MY_CONTEXT'
        ,attribute => 'is_admin'
        ,value     => 'Y'
        ,client_id => v_real_client_id);
    END set_session;
    
  3. In APEX, remove the Initialization PL/SQL Code – we don’t need it anymore.
  4. In the Authentication scheme, set Post-Authentication Procedure Name to MY_PACKAGE.set_session.

Now, our context is only set once – just after login. Excellent.

“Now,” you’ll no doubt want to ask me, “where did you actually set the Client Identifier? And what’s the deal with the REPLACE?”

We don’t have to worry about setting Client Identifier – APEX already sets it for us automatically for each page request. If you log into an ordinary APEX application, and examine the Client Identifier, you’ll get something like this:

SCOTT:15135454564654

Where “SCOTT” is the login username, and “15135454564654” is the APEX session ID. We can bind our context to this combination of values simply by passing the Client Identifier to the client_id parameter of DBMS_SESSION.set_context. In fact, most of the time this sort of code will work fine:

DBMS_SESSION.set_context
 (namespace => 'MY_CONTEXT'
 ,attribute => 'my_attribute'
 ,value     => 'xyz'
 ,client_id => SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER'));

Unfortunately, however, if we look at CLIENT_IDENTIFIER from the post-login procedure (i.e. immediately after the user has successfully logged in), we instead find it is something like this:

nobody:15135454564654

So the session ID is correct, but the username hasn’t been set yet. Looking at the APEX debug logs it’s apparent that the client identifier gets set with the username very soon after the post-login procedure is run – which is a tad inconvenient. However, APP_USER is set to SCOTT prior to the post-login procedure; therefore, my set_session procedure simply replaces “nobody” with v(‘APP_USER’), so that the client_id will agree with the client identifier that will be used from that point on.

That works great – my views, packages and RLS predicates can use SYS_CONTEXT to get context variables with negligible performance impact (remembering that context variables are always strings, so don’t forget to convert your datatypes if necessary).

As an added bonus, as a developer or DBA we can log into the database and diagnose an active session by pretending to be it. For example, Joe sends us an email with a screenshot of an APEX page showing “No Data Found”. When you log into your tool of choice and run the same query, you can see plenty of data. You want your session to pretend to be Joe. To do this, you would get Joe’s username and the session ID from the URL on the APEX page (it’s the 3rd parameter after the f?p= bit in the URL), and run this:

DBMS_SESSION.set_identifier('JOE:18765421554654');

What this does is tell Oracle that your session is Joe’s session. Your session will automatically see the same context variables that Joe’s session currently sees. You run the query again, and you can see that it returns no rows – ah, maybe there’s something wrong with the RLS predicate your application is generating. You now have a testbed under which you can test any change you might want to make in order to resolve the issue (or at least, to be able to tell Joe why he’s not allowed to see any data).

Alexandria Updated
APEX Dynamic Action silently fails – a story

Comments

  1. raoul mangoensentono (@m_raoul)
    1 March 2013 - 7:59 pm

    I have never heard of application contexts and will read more on it. My question now is about this statement: “My apex pages can query any table, no WHERE clauses needed (except for user-entered filters), and I can be confident they won’t be able to inadvertently see rows they’re not supposed to see if I forget to add the right predicate on every page that queries each table.”

    How did you do that without a WHERE clause? Did you do it with VPD or dynamic queries?
    Asking because I use a WHERE clause that looks at who is logged in v(‘app_user’), but not using dynamic queries.

    Raoul

    • Hi Raoul,

      Thanks for your question! I am using VPD, otherwise known as Row Level Security. For example, I have a policy on one table that adds the predicate “data_owner = SYS_CONTEXT(‘appctx’,’APPUSER’)”. I initialise the “APPUSER” context variable to v(‘APP_USER’) in my post-logon procedure.

      Hope this helps.

      Jeff

  2. Although the blog posts mentions using the Oracle RLS (aka VPD) feature, it’s worth mentioning that this approach can also be used with “Poor Man’s VPD”, as explained here: http://www.talkapex.com/2010/07/poor-mans-vpd-in-apex-and-oracle-xe.html

    I’d just like to add my word of caution from the linked blog post, namely that the values in a global context persist in server memory (see sys.v_$globalcontext) until the database is restarted or dbms_session.clear_context is called (typically as part of the logout process). Since not all web users logout explicitly, a background job may need to be set up to clean up old sessions.

  3. Hi Jeff,

    Thanks, excellent post.

    It got me thinking, could you apply this approach to page security in an application? Currently I’m using an authorization scheme (function call returning boolean) that checks whether the current user is allowed to navigate to the requested page. The function checks a custom table that maps Apex user groups to Apex pages and compares to the :APP_PAGE_ID session value. Since the value of :APP_PAGE_ID changes throughout the session, I need to set the scheme to evaluate “Once per page view” to get the desired security.

    What if you could establish name/value pairs in a globally-accessible application context to determine which pages the current user is allowed to view – e.g. for a user who is only allowed to access pages 1 and 2 of the application:

    1 / Y
    2 / Y
    3 / N
    4 / N
    5 / N
    etc.

    Then this access mapping could be set at the beginning of the session using the post-login procedure as you’ve done above. And the authorization scheme could be modified to check these values instead and translate to the required booleans. While I guess this check would still need to happen every page load, the Oracle Docs claim performance benefits from using application context values stored in the SGA/UGA:

    “This cache saves the repeated overhead of querying the database each time these attributes are needed…..”

    Just a thought, really.
    Anybody care to comment on any potential pros / cons….?
    Thanks again!

    Jon.

    • Hi Jon,

      Thanks for stopping by!

      If every page is currently querying a table to check the user’s authorization then having this info cached in an application context is quite likely to improve performance. Depending on how well tuned your query is, however, you may not see any noticeable difference. Test it and see – even if it doesn’t make it faster, I’m sure it will reduce the workload on your database.

      However, depending on what stage your application development is at, I’d consider a slight redesign of the authorization. At the moment I imagine you have a table that maps between page ID and user group, and so your authorization has to keep checking the table to see if the current user is in a group that has access to the current page ID. Maybe you require this because your users want to be able to edit the page IDs accessible to each user group?

      If that’s not really the requirement, an alternative design (which may perform much better) is to assign one or more “roles” to each user group, and then create one authorization for each “role”; each page should then be assigned to one “role”.

      Your table would then, instead of mapping individual page IDs to user groups, map one or more “roles” to each user group. Your authorization function now only needs to check if the current user is in a user group that has access to that particular role. Thus, your authorization doesn’t need to be checked for each page access – it only needs to be checked the first time any page for that role is accessed.

      I’m guessing the above description is about as clear as mud, so here’s a rundown of the implementation of this alternative design:

      1. Create Table: UserGroup, Role, Granted_YN (unique over UserGroup + Role)
      2. Create a single Authorization for each Role – be as granular as necessary
      3. Each page is only allowed to be accessed by ONE Role. This is a hard-and-fast rule. (Some Roles might only be able to access a single Page)
      4. The table allows a UserGroup to access any number of Roles.

      As soon as you find that a group of pages share exactly the same UserGroups, they are a *possible* candidate for consolidating into a single Role. This is where you’ll see a potential performance benefit, as the Authorization scheme will only need to be executed once per user session.

      I should emphasize that this is just a suggestion and may not take into account other constraints of your environment, so take it with a pinch of salt.

      I hope this helps!

      Jeff

      • Thanks a lot for this Jeff.

        Actually I was initially trying to be “clever” by building a “one authorization scheme fits all” by making it pass in the current page so only having one authorization to maintain. At the expense of page performance, needless to say!

        Currently our application is very small but it’s conceivable that it will grow, so the role-based approach above sounds like a sensible design. Fortunately, we’re also early enough in the dev timeline to try these things out, so I’ll implement and see the results.

        Many thanks for the suggestion!

        Jon.

  4. I just found after setting up without client_id set, not accessed globally – that the context value was shared between users/sessions. So setting the client identifier is a must, in this man’s eyes.

    • Hi Scott,

      That’s right – if you’re NOT using a globally accessible context, it’s imperative to use the Initialization PL/SQL Code to set/reset the context between calls – because database sessions are shared among Apex users. In fact, I’d recommend resetting the context variables at the end of each call (using Cleanup PL/SQL Code).

      However, if you use a globally accessible context, and set client_id in each call, the data will be safely insulated from other Apex users, and you no longer need to call anything in your Initialization PL/SQL Code.

  5. For some reason the SYS_CONTEXT(‘userenv’, ‘client_identifier’) value is null at the time that the post-authentication procedure is run on our system. Not sure why this is the case for us but not for you… We are using Apex 4.2.1 and our authentication system is Oracle SSO. Don’t suppose you have any idea how I can fix this?

    • Jeffrey Kemp
      16 May 2013 - 1:39 pm

      Thanks for the note. It seems we cannot depend on those context variables in a post-auth procedure.

  6. Alex Shenton
    15 May 2013 - 1:00 am

    Actually, I managed to fix it by doing this instead of your replace:

    v_real_client_id := NVL(REPLACE(
      SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER')
                 ,'nobody'
                 ,v('APP_USER'))
      ,v('APP_USER') || ':' || v('APP_SESSION')) ;
    

    It seems that the app_user and app_session are set before the client_identifier is!

    • Jeffrey Kemp
      16 May 2013 - 1:40 pm

      In which case we may as well ignore the context – just use

      v_real_client_id := v('APP_USER')||':'||v('APP_SESSION');
      

      and be done with it, I think.

      Thanks!

  7. Gregory Grimes
    25 June 2020 - 5:41 am

    Could I use this idea to determine which page a user will be sent to based on their context? For example, if I query a user table and see the user doesn’t exist, then send him/her to registration page. If the user exists but has to roles assigned, send her/him to the roles not assigned message page. Once they have jumped through the hoops, send them to the splash page. The context would be set once.

    Thoughts?

    Thanks.

    • Hi Gregory,

      You could use an application context for this, but for what you are describing I would use an Application Item instead.

      The advantage of using an Application Item is that it is readily available in many different ways in your APEX application, including as a substitution string for a page redirect.

      An Application Context, on the other hand, is only accessible within a SQL or PL/SQL statement, so it would not always be usable in the same way.

      Your mileage may vary; you might find it useful to use both in conjunction; use an Application Context to set the value, along with an Application Item that is set via a Computation on each page load that copies the context value into the item.

      Jeff

  8. Hi Jeff,

    I am doing apps initialization in a procedure as below:

    BEGIN
    fnd_global.apps_initialize(102780,
    22448,
    800);

    EXCEPTION WHEN OTHERS THEN
    P_OUT_ERROR_DESC := ‘Error while apps initialize.SQLERRM:’||SQLERRM;
    END;

    This procedure is being called from an APEX page. However I am getting below error in the apps initialize section.

    SQLERRM:ORA-00942: table or view does not exist

    Can you suggest, if I need to change or add something here ?

    Thanks.

    • Jeffrey Kemp
      7 July 2020 - 7:45 pm

      Hi Ankit, I’m not sure sorry. The error suggests that the schema that is executing the procedure does not have the necessary grants to an object (I’m guessing apps_initialize runs with invokers rights?). You will probably get better advice on the Oracle community forum: https://community.oracle.com/

  9. Hi Jeff, thanks for your articles on this and converting a single-schema app to multitenancy. I’m having some trouble with an error that I have insufficient privileges to create a context. I’ve gone into the internal workspace > Workspace Database Privileges and found that there is indeed no privilege for my schema to be able to create contexts and I can’t find anything online about how to add said privilege. I know I’m asking a lot but would you have any workaround to still be able to use contexts or to add the workspace database privilege?

    Thanks again for the articles, they’re very useful.

    • Hi John,

      Since application contexts are instance-wide objects, they are typically created using a highly privileged account like SYS or SYSTEM; i.e. a user with the CREATE ANY CONTEXT privilege. Typically you (or your DBA) would create the context just once and then your application can use it from that point on; the only thing you need to “fix in stone” is the owner and name of the database package that will control the context.

      Jeff

  10. Hello Jeff,
    I have apex 21.1 and I have follow your steps above and I am facing a strange behavior of the system. where I found some of the session connection have the context and some are not?

    any Idea how to solve this problem?

    Regards,
    Ayman

    • Hi Ayman, I’m sorry but I can’t tell from that small amount of information. I can only assume there was at least one step you haven’t quite got right.

  11. Hi Jeff,

    In the global context scenario, what location do you think is best to issue a dbms_session.clear_context? Session timeout URL? I wouldn’t want to load up the SGA with thousands of persistent global variables.

    Or maybe a nightly ALTER SYSTEM FLUSH GLOBAL_CONTEXT command…

    Steve

    • Hi Steve,

      If your application has an opportunity to clear the context before the session ends, then that’s a good idea. Running a SYSTEM FLUSH GLOBAL_CONTEXT is a bit drastic as it would affect all current sessions as well – in my experience I’ve never seen a system that ran for so long that the contexts took up too much SGA but I wouldn’t rule it out.

      According to a colleague of mine, if a system was never restarted after a very long time of high activity, and you started seeing SGA-related errors (e.g. ORA-04031), doing the flush might resolve things; however, it’s likely that in such an extreme situation that other things will be failing as well and restarting the instance may be the better solution.

      You may be interested in this recent exchange between Scott and Connor: https://asktom.oracle.com/pls/apex/asktom.search?tag=local-context-vs-global-context#9546475000346056402

      I hope this helps.

      Jeff

  12. Hi Jeff, great read! Is this approach still valid for Apex 22.2?

    Cheers,
    Daren

  13. Hi Jeff,

    I am a complete beginner in APEX and for me, your site is a treasure to learn from.

    We are currently studying how we could update our very large Oracle Forms system to APEX.

    Coincidentally one of our problems is how to match the use of the VPD (ACCESSED GLOBALLY) in APEX, since in APEX the login user is always APEX_PUBLIC_USER (or at least that is what I have understood) and we use the more detailed form of dbms_session.set_context with the use of the current login user:
    dbms_session.set_context( ‘MY_CONTEXT’, ‘MY_ATTR’, ‘ATTR_VAL’, user, ‘GUID’ ),
    so when we use the sys_context( ‘MY_CONTEXT’, ‘MY_ATTR’ ) to get the value ( ‘ATTR_VAL’ ), the DB It is forcing also the use of the current login user ( and of course the client_identifier that has the current session). In APEX, this way will not work because always the current login user is APEX_PUBLIC_USER, so the use of the Username argument on set_context It is unnecessary and the trick that APEX use for setting the client_id:
    DBMS_SESSION.set_context
    (namespace => ‘MY_CONTEXT’
    ,attribute => ‘MY_ATTR’
    ,value => ‘ATTR_VAL’
    ,client_id => v(‘APP_USER’)||’:’||v(‘APP_SESSION’) );
    It is not safe because this value (client_id) can be read simply using select client_identifier from v$session, and as you yourself pointed out, anyone who has access to the DB and the application can do it themselves: DBMS_SESSION.set_identifier( client_identifier from some DB session ); and read the values that would not be allowed. For example, the employee of branch A of a bank can see the customers of branch B of the same bank

    I would greatly appreciate your opinion.

    Greetings

    Isaac

    • Hi Isaac,

      If someone has direct access to the database like that, it makes little difference in any case, your database security is already under threat.

      That said, you could choose to use an ordinary application context (but not globally accessible); other sessions will not be able to impersonate that session. In fact, this is the type of context that APEX itself uses.

      The only downside is that every db call must set up the session context first – something you would add to the Initialization PL/SQL Code attribute of your APEX application. This is what id described in the first few paragraphs of this article. You’d want to make this initialisation code as fast as possible, of course, since it will be executed for each page request and server-side dynamic action.

      Jeff

Leave a Reply

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