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:

  2. Create a database package with a procedure to Set the context variable(s), e.g.:

    PROCEDURE set_session IS
        (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:

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:

  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);
      v_real_client_id := REPLACE(
        (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:


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:

 (namespace => 'MY_CONTEXT'
 ,attribute => 'my_attribute'
 ,value     => 'xyz'

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:


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:


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).

13 thoughts on “Apex and Application Contexts

  1. 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.



    1. 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.



  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

    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!



    1. 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!



      1. 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!



  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.


    1. 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?


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

    v_real_client_id := NVL(REPLACE(
      ,v('APP_USER') || ':' || v('APP_SESSION')) ;

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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s