HOW TO OBTAIN USER ID
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: HOW TO OBTAIN USER ID

  1. #1
    Join Date
    Jan 2001
    Location
    St. Louis. MO
    Posts
    150

    Question

    Can anynone tell me how to get user id from a web application that passes thru a weblogic Server.

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well if you mean the userid of database it's easy but if you mean the userid of the application I dont have a clue!

    in first case if you want to some kind of auditing what we do here is add a column named user_id then add a default value to the column

    like

    alter table xxx add (user_id default user not null)

    then you would have to write triggers to update that column whenever there is an DML

    I dont know if this what you are lokking for but that's how we set up out Web Application with WebLogic

  3. #3
    Join Date
    Jan 2001
    Location
    St. Louis. MO
    Posts
    150
    I am talking about the user id of the application, You see when a user logs in (using his user id) in the web application it passes to the Weblogic Server, and oracle sees the connection pool the user is using and not the user id. I am looking at another way of doing that with the help of the application, only I need to find out my current session id when I log into the database. Using sqlplus, how can I do that?

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you mean session ID of Oracle?

    it's the SID in v$session I guess is what you are looking for? But anyway how can you know which application user is using which connection, at least in our case the connection from the connection pool are shared mostly.... and there isnt anyway to determine in what moment an application user is using that connection (or not that I am aware since I am not very familiar with Java and WebLogic) And from v$session it just says JDBC connection

  5. #5
    Join Date
    Jan 2001
    Location
    St. Louis. MO
    Posts
    150
    What I am thingking is to embed a code in the application so that everytime a user logs in the Web application, it first check his own Oracle session id, then together with the user id, the java code will store that information to a table, which I can relate to the v$session.

    Let's say I log in to the database(SQL Plus) as my own user id, how can I querry my own session id from v$session without getting the other session id.

    Ex.

    Select sid from v$session where sid=(my current sid)

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by edsperez
    Select sid from v$session where sid=(my current sid)
    how are you gonna find your own sid if you dont know your sid...? well at least from this SQL you just wrote

    I also thought of using SID as well but then because of this sharing connection problem we had to abort

  7. #7
    Join Date
    Aug 2000
    Location
    CA
    Posts
    2

    How to Obtain User Id

    Hello,

    You might want to try this>

    SQL> select sid, username
    from v$session
    where audsid = userenv('sessionid');

    This will give you the SID that you're using and your user name as well.

    Good Luck.


  8. #8
    Join Date
    Jan 2001
    Location
    St. Louis. MO
    Posts
    150

    Re: How to Obtain User Id

    Thanks a lot, this is exactly what I need.

    Ed

  9. #9
    Join Date
    Jan 2001
    Location
    St. Louis. MO
    Posts
    150

    How to Obtain User Id

    Hi,

    ------------------------------------------
    select sid, username
    from v$session
    where audsid = userenv('sessionid');
    ------------------------------------------


    Have you tried putting this SQL in a DB trigger? I tried but I am getting some errors. I am tring to get the session id of user who fired the trigger(update or delete a table). Is this possible?

  10. #10
    Join Date
    Jul 2000
    Posts
    296
    Without the error(s) it is difficult to tell what can be the problem.

    Probably privilege SELECT ON V_$SESSION is granted to the owner of the trigger trough a role and not directly to the user.
    Try granting SELECT ON V_$SESSION, as SYS, to the owner of the trigger.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width