This discussion is archived
13 Replies Latest reply: Feb 26, 2013 4:53 PM by scott.wesley RSS

EXECUTING A LOG-IN PROCEDURE

972703 Newbie
Currently Being Moderated
Good Day to everyone:

I have created a procedure as follows:

*create or replace
PROCEDURE set_user(app_user_IN IN VARCHAR2)
IS
BEGIN
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(APP_USER_IN);
END set_user;*

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
  • 1. Re: EXECUTING A LOG-IN PROCEDURE
    scott.wesley Guru
    Currently Being Moderated
    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.

    Scott
  • 2. Re: EXECUTING A LOG-IN PROCEDURE
    972703 Newbie
    Currently Being Moderated
    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.
  • 3. Re: EXECUTING A LOG-IN PROCEDURE
    972703 Newbie
    Currently Being Moderated
    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.
  • 4. Re: EXECUTING A LOG-IN PROCEDURE
    scott.wesley Guru
    Currently Being Moderated
    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?
  • 5. Re: EXECUTING A LOG-IN PROCEDURE
    972703 Newbie
    Currently Being Moderated
    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.
  • 6. Re: EXECUTING A LOG-IN PROCEDURE
    972703 Newbie
    Currently Being Moderated
    This is exactly what I am trying to do:

    Example

    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)
    IS
    BEGIN
    DBMS_APPLICATION_INFO.SET_CLIENT_INFO(app_user_IN);
    END set_user;
    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

    SOURCE: http://dbpedias.com/wiki/Oracle:DBMS_APPLICATION_INFO.SET_CLIENT_INFO
  • 7. Re: EXECUTING A LOG-IN PROCEDURE
    scott.wesley Guru
    Currently Being Moderated
    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
    Client_Info: WESLEYS:1261984616516161981>
    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.

    Scott
  • 8. Re: EXECUTING A LOG-IN PROCEDURE
    972703 Newbie
    Currently Being Moderated
    Understood.

    However, what is the exact syntax for the parameterless procedure in the post-authentication field?
  • 9. Re: EXECUTING A LOG-IN PROCEDURE
    scott.wesley Guru
    Currently Being Moderated
    You just put the procedure name.

    If it's within a package, then the usual notation:
    my_pkg.post_auth_proc
    Scott
  • 10. Re: EXECUTING A LOG-IN PROCEDURE
    972703 Newbie
    Currently Being Moderated
    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".
  • 11. Re: EXECUTING A LOG-IN PROCEDURE
    scott.wesley Guru
    Currently Being Moderated
    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.
  • 12. Re: EXECUTING A LOG-IN PROCEDURE
    972703 Newbie
    Currently Being Moderated
    The procedure is OK (I believe). Just having a problem calling it into the authentication scheme...
  • 13. Re: EXECUTING A LOG-IN PROCEDURE
    scott.wesley Guru
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points