HOW TO OBTAIN USER ID

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

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

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?

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

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)

Quote:

---

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 :D

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.

Thanks a lot, this is exactly what I need.

Ed

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?

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.

I got it working, It's something I miss in the code. Still I am having problem implementing this, I talked to our Developers and they told me that they cannot give me the SID from oracle and the USER ID from their application because they are saying that they are using 'container management bean'.

How are you implementing Auditing at the database level when users are comming in from a webserver pool? (Using the same user_id).

What I mean by auditing is capturing the user id who perform a delete or update within the database. In a client server environment this will be easy.

Can you give me some advice on how to do just this in a three tier environement.