13 Replies Latest reply: Feb 26, 2013 6:53 PM by scott.wesley RSS

    EXECUTING A LOG-IN PROCEDURE

    AquaNX4
      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
          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
            AquaNX4
            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
              AquaNX4
              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
                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
                  AquaNX4
                  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
                    AquaNX4
                    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
                      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
                        AquaNX4
                        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
                          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
                            AquaNX4
                            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
                              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
                                AquaNX4
                                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
                                  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.