13 Replies Latest reply on Feb 27, 2013 12:53 AM by Scott Wesley

    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
          1 person found this helpful
          • 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?
                1 person found this helpful
                • 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
                      1 person found this helpful
                      • 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
                          1 person found this helpful
                          • 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.
                              1 person found this helpful
                              • 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.