This content has been marked as final. Show 13 replies
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.1 person found this helpful
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...1 person found this helpful
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.1 person found this helpful
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:2Action: 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.
However, what is the exact syntax for the parameterless procedure in the post-authentication field?
You just put the procedure name.1 person found this helpful
If it's within a package, then the usual notation:
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".
There may be an error in the procedure. Try dumbing it right down to make sure your syntax is ok, then isolate the problem by adding to it.1 person found this helpful
The procedure is OK (I believe). Just having a problem calling it into the authentication scheme...
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.