7 Replies Latest reply: May 15, 2013 12:36 PM by Ingenio RSS

    How to connect as a real database account user for auditing?

    Ingenio
      Hi apex experts..

      For auditing, I need to insert the user, among other data, into different tables.
      The thing is, I have an application with DB account authentication, so a real database user is connected, when auditing, the user field inserted is "ANONYMOUS".

      i need to audit the real user, is it possible?

      thanks!

      Apex 4.2
      EPG
      Oracle Enterprise Linux 5.5
      Database 11.2 EE
        • 1. Re: How to connect as a real database account user?
          InoL
          For Apex, you have to use v('APP_USER') in your auditing procedures, not USER.
          E.g. we use this in table triggers to record the user:
          :new.update_user  := nvl(v('APP_USER'),user);
          This way (with the nvl) you also record the user if the update is not coming from Apex.
          • 2. Re: How to connect as a real database account user?
            fac586
            Ingenio wrote:

            For auditing, I need to insert the user, among other data, into different tables.
            The thing is, I have an application with DB account authentication, so a real database user is connected, when auditing, the user field inserted is "ANONYMOUS".
            DB account authentication only uses the DB account for authentication purposes, not to establish a connection to the database. APEX requests always run using ANONYMOUS connections from the pool. In the APEX session the <tt>APP_USER</tt> substitution string will contain the name of the database user used to authenticate.
            i need to audit the real user, is it possible?
            Use the following expression in the audit code to return the username:
            coalesce(v('APP_USER'), user)
            • 3. Re: How to connect as a real database account user?
              Ingenio
              well, I am not clear with this, but you may know that the audit is performed by table triggers, I have more than 300 tables, you mean I'd edit all these trigger?
              when I insert into a table, that table have the trigger "after insert" which insert into the audit table... and that audit table have a default field 'user_aud" filled with default 'USER'

              I appreciate if you can clarify and help me to perform this challenge..

              thanks
              • 4. Re: How to connect as a real database account user?
                InoL
                you mean I'd edit all these trigger?
                If only your audit table uses USER instead of nvl(v('APP_USER'),user), you only have to alter the audit table.
                • 5. Re: How to connect as a real database account user?
                  Ingenio
                  Hi InoL

                  I tried alter the trigger, and it works..

                  but what I need is alter the default value of the field on the table.

                  is this alter correct or possible?

                  ALTER TABLE AUD$CA_AUTOGN
                  MODIFY(AUD$_USER DEFAULT nvl(v('APP_USER'),USER))
                  /

                  it gives me ORA-02262: ORA-4044

                  thanks!
                  • 6. Re: How to connect as a real database account user?
                    InoL
                    Please give full error messages, so we don't have to look it up first:
                    ORA-04044: procedure, function, package, or type is not allowed here

                    That seems a clear message to me. You have to use a trigger, not a default value.

                    From the Oracle documentation:
                    A DEFAULT expression cannot contain references to PL/SQL functions or to other columns, the pseudocolumns CURRVAL, NEXTVAL, LEVEL, PRIOR, and ROWNUM, or date constants that are not fully specified.

                    Edited by: InoL on May 15, 2013 1:36 PM
                    • 7. Re: How to connect as a real database account user?
                      Ingenio
                      right!
                      I will need to alter the trigger instead the default value, that's the answer.

                      thank you very much!