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
- On New Instance (new session) - APP_USER is 'nobody'
- Redirect to login page
- User enters username/password and submits
- Pre-Authentication
- Validate credentials - APP_USER now set
- Post-Authentication
- 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.
