7 Replies Latest reply: Jan 2, 2013 7:02 AM by Billy~Verreynne RSS

    How to get user information

    912130
      Hi All,

      We have created one application. If USER1* runs this application, then it will execute some set of procedures,packages with the hardcoded USER2_ credentials in that application. Due to this process execution few tables(let us say TABLE1*) is getting updated.

      Now my problem is, I want to know the details of USER1. i.e who ran the application.

      For this I have tried the below option,
      1. Created a trigger on table TABLE1
      2. by using 'user' keyword I tried to capture the user credentials.

      But, since that updation of table is taking place with the help of hard-coded USER2 credentials, i am getting ouptut as USER2.

      But in the reality I have to get USER1 as output.

      Please provide the resolution
        • 1. Re: How to get user information
          BluShadow
          909127 wrote:
          Hi All,

          We have created one application. If USER1* runs this application, then it will execute some set of procedures,packages with the hardcoded USER2_ credentials in that application. Due to this process execution few tables(let us say TABLE1*) is getting updated.

          Now my problem is, I want to know the details of USER1. i.e who ran the application.

          For this I have tried the below option,
          1. Created a trigger on table TABLE1
          2. by using 'user' keyword I tried to capture the user credentials.

          But, since that updation of table is taking place with the help of hard-coded USER2 credentials, i am getting ouptut as USER2.

          But in the reality I have to get USER1 as output.
          What application layer are you using? How are these credentials hardcoded?
          How are you expecting the database layer to know details of the client especially if the client is logging onto the database under User2's credentials.

          You haven't supplied any code or details to allow us to actually help you, you've only talked in generic terms which could relate to any software, operating system and rdbms platform.

          {message:id=9360002}
          • 2. Re: How to get user information
            LPS
            Find the Documentation of the below link gives you the info

            http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions184.htm#SQLRF06117


            select sys_context('USERENV','SESSION_USER')
            ,sys_context('USERENV','CLIENT_INFO'),
            sys_context('USERENV','HOST') ,
            sys_context('USERENV','IP_ADDRESS') ,
            sys_context('USERENV','INSTANCE')
            from dual;

            Edited by: LPS on 13-Dec-2012 03:01
            • 3. Re: How to get user information
              912130
              HI,
              Thanks for your quick response.
              Actually we are using 3rd party application and this was developed by .NET
              • 4. Re: How to get user information
                BluShadow
                909127 wrote:
                HI,
                Thanks for your quick response.
                Actually we are using 3rd party application and this was developed by .NET
                Ok, so it's a .net application.

                You still haven't explained how the "credentials" are being applied. How is User1 (is this a real person? a windows user? a database user? what?) running the application with credentials of user2 (real person? windows user? database user? what?) against the database.

                The more information you can give us the more help we can be...

                At the minute we have very little to go on.
                • 5. Re: How to get user information
                  912130
                  Hi,
                  Please find the details below.

                  You still haven't explained how the "credentials" are being applied.
                  How is User1 (is this a real person? a windows user? a database user? what?) running the application with credentials of user2 (real person? windows user? database user? what?) against the database.


                  --> Both User1&2 are database users.
                  --> in the application they might be executing the procedure with the help of USER2 credentials.
                  This is something like below in our Oracle SQL commands.

                  Sqlplus USER2/USER2password @procedure


                  Please let me know, if you need any other information.

                  Thank You.
                  • 6. Re: How to get user information
                    912130
                    HI,
                    Can anyone Please provide resolution for this issue.
                    • 7. Re: How to get user information
                      Billy~Verreynne
                      909127 wrote:

                      --> Both User1&2 are database users.
                      --> in the application they might be executing the procedure with the help of USER2 credentials.
                      This is something like below in our Oracle SQL commands.

                      Sqlplus USER2/USER2password @procedure
                      So USER1 executes "+sqlplus USER2/USER2password @procedure+" to run the required procedure?

                      This is a bit of a hack. And a security issue as USER1 needs to know USER2's credentials (database password).

                      From an Oracle database perspective, the following approach should be considered.

                      USER2 defines the procedure as running with USER2 credentials. E.g.
                      create or replace procedure ... authid definer is 
                      ..
                      USER2 allows USER1 to execute the procedure. E.g.
                      grant execute on <procedure> to USER1
                      USER1 executes that procedure using his credentials. E.g.
                      sqlplus USER1/USER1 @USER2.<procedure>
                      Look at the AUTHID concept in the PL/SQL Reference Guide (manuals available via http://tahiti.oracle.com).