Monday 8 September 2014

Using Post-Authentication to run process after APEX login

A frequent requirement, and hence frequent question on the forums is
How do I run procedure x after the user has logged in?
This is often required for such tasks as determining user access, such as populating a restricted Application Item relating to a role like F_IS_ADMIN based on the username defined in substitition string APP_USER

Those new to APEX and unfamiliar with certain concepts may consider using an Application Computation, firing "On New Instance (new session)"
Application Computation Frequency

It sounds fair enough, and I remember doing the same thing when I was first learning APEX. The documentation on understanding page computations states
The computation point On New Instance executes the computation when a new session (or instance) is generated
This still isn't clear, but this actually fires when you first navigate to a page in your application - any page. This means when you first open a page like /apex/f?p=100:1, which may redirect you to the login page - the 'on new instance' event has already fired since APEX needed to provide you with a new session to render the home/login page.

In other words, these events can be generally described in the following order:
  • Open home page
    1. On New Instance (new session) - APP_USER is 'nobody'
    2. Redirect to login page
  • User enters username/password and submits
    1. Pre-Authentication 
    2. Validate credentials - APP_USER now set
    3. Post-Authentication
    4. Redirect to relevant page

I think I've only used 'On New Instance' once or twice, possibly to prime content of application items - but I use 'Post-Authentication' all the time to calculate values based on the user who just logged in.

Post-Authentication is defined in the Authentication Scheme, and expects the name of a stored procedure.
Shared Components -> Authentication Scheme
This stored procedure can be defined within the Authentication Scheme -> Source -> PL/SQL code as an anonymous block:
PROCEDURE post_authentication IS
BEGIN
  -- do stuff here

  null;
END 
post_authentication;

For better performance and code management you should place it within a PL/SQL package. That way it doesn't need to be interpreted dynamically every time a user logs in.

The Post-Authentication Procedure Name attribute just needs the name of the stored procedure, no semi-colon.
apx_auth_util.post_authentication

The PL/SQL may run something like:
IF v('APP_USER') = 'WESLEYS' THEN
  apex_util.set_session_state('F_IS_ADMIN','Y');
END IF;
I understand changes are coming in APEX 5 regarding when these events fire as you navigate between applications that share authentication.

This also reminds me of a little experiment I wrote ages ago to determine the order of page/application events.

4 comments:

Steve O said...

Looking for a way to run the PL/SQL process at this exact time (post login) that could pop up a warning message that DB authenticated password will expire in X days. Getting PL/SQL to run at that time is easy, getting a popup message seems difficult.

Scott Wesley said...

What have you tried? You could set an app item, then while opening the home page render a region if the value is set. Do you want a popup, to be displayed where?

Diego Gerena (SNIPERCAT) said...

Thanks for this post!!!... I was like Half hour trying to understand why My procedure didn't execute after login, I was using the "On New Instance (new session)"

Scott Wesley said...

I also spent time discovering that fact, partially inspiring this post ;p

A simple thing, discovered early, can make an effective difference.