This discussion is archived
7 Replies Latest reply: Jan 2, 2013 5:02 AM by BillyVerreynne RSS

How to get user information

912130 Newbie
Currently Being Moderated
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 Guru Moderator
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    HI,
    Can anyone Please provide resolution for this issue.
  • 7. Re: How to get user information
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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).

Legend

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