Good Day to everyone:
I have created a procedure as follows:
*create or replace
PROCEDURE set_user(app_user_IN IN VARCHAR2)
I want this to execute everytime some one logs into my application. In regard to that, I have a couple questions.
1. Is the procedure written correctly?
2. How do I execute this everytime someone logs into my application?
I am using Application Express 2.1.0.00.39.
Thank you for your help.
Edited by: 969700 on Feb 24, 2013 10:41 PM
Before you go any further, have a few sessions logged into APEX and view v$session to see how APEX adds this instrumentation out-of-the-box, then see if you still need this procedure.
Although APEX 2.1 is before my time... you might want to consider upgrading.
This being said, a post-authentication is the relevant location within your authentication scheme.
Thank you for replying.
[CLIENT_INFO] comes back null, and I want it to display the V('APP_USER') value, since my DB utilizes internal authentication. I'm not sure if the aforementioned procedure I'm using will suffice.
OK. I went into the authentication scheme and located the "post-authentication process". How do I tell APEX to call or execute that procedure which is called "SET_USER".
Of course, I appreciate your patience with me, as I am FOREVER a beginner with this stuff.
Actually, I'll retract what I've just said - I confused myself again by jumping between Forms/Apex at my current site and my memory of something else...
Since APEX pools connections, using v$session information for this purpose is invalid. You may wish to see if the view apex_workspace_sessions is available in your version - and useful for your purpose.
What are you trying to achieve?
OK. I have written a trigger for an audit table (log-in, log-off). However, since my DB utilizes an internal authentication scheme, the user data that come back (i.e. "client_info") is null. I want to be able to see the "APP_USER". All of my other audit tables work, since they are triggered after they have logged on, and they are inserting, updating, or deleting a table. It is easy to retrieve APP_USER data at that point. The trick for me, and it is probably a cake-walk for others, is to get the APP_USER credentials when they are logging in the database.
I hope I clarified what I am trying to ultimately achieve.
Thanks for your help again.
This is exactly what I am trying to do:
The following procedure could be utilized by an application in which all sessions connect to a common Oracle username and security is handled within the application. This would allow the DBA to externally distinguish between user sessions in V$SESSION by examining the CLIENT_INFO column.
CREATE OR REPLACE PROCEDURE set_user(app_user_IN IN VARCHAR2)
Suppose that users JOE SMITH and SALLY DALLY log into the application, which connects to Oracle as the user OPBIP. If the application calls the set_user procedure at login, we can distinguish between the database sessions for Joe and Sally in V$SESSION as follows:
SQL> SELECT sid, username, client_info
2 FROM v$session
3 WHERE username='OPBIP';
SID USERNAME CLIENT_INFO
--------- ---------- --------------------
14 OPBIP JOE SMITH
24 OPBIP SALLY DALLY
Ahh, the theory works fine in a Forms environment, but in APEX the v$session entry for APEX_PUBLIC_USER might be shared amongst a number of APEX sessions.
In my understanding - at any instant in time, a session might be used to render a particular page, and that's when you'll see information such as
Module: PARSING_SCHEMA/APEX:APP 110:2
Action: Processes - point: BEFORE_HEADER
but during rest there will be no identifying information - it's just waiting for the next time an APEX page needs interaction.
This was the visual I remembered when first answering your question, but completely forgot about the pooling - sorry.
If you would like a procedure to fire after APEX login, then you would enter the parameterless procedure name in the post-authentication attribute of the authentication scheme. Within that procedure you can refer to v('APP_USER')
Logout would be less reliable as the user could just close the browser window.
i tried placing the name of the procedure "SET_USER" in the post-authentication process attribute of the authentication scheme but I got an error informing me it wasn't able to process "custom post-authentication process".
Some things may not be tolerated within post-authentication.
I'm suggesting you dumb it right down to eliminate an issue with the call syntax.
Do you still have a required formal parameter defined? Is so, that would be your issue.