Home » SQL & PL/SQL » SQL & PL/SQL » capturing user details (oracle 10g,win xp)
capturing user details [message #318655] Wed, 07 May 2008 08:29 Go to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Hi All

There is an application in production and we want to get the user id of the user once it logs in to the application and when he performs a delete operation this user id should be recorded into an audit table?

There is a userdetails table where the user id field is present,but the problem is there can be multiple users loged in the same time,i need to be session specific as well.

How is it possible??

Thanks
Re: capturing user details [message #318656 is a reply to message #318655] Wed, 07 May 2008 08:32 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
AUDIT DELETE ON MY_table;

write an ON DELETE trigger
Re: capturing user details [message #318657 is a reply to message #318655] Wed, 07 May 2008 08:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Presumably there is a reason you can't use the USER builtin - is this some sort of Web app that reuses connections?
Re: capturing user details [message #318658 is a reply to message #318656] Wed, 07 May 2008 08:35 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
i understand that i need to write the trigger but to get the user information how to get that userid?
Re: capturing user details [message #318659 is a reply to message #318657] Wed, 07 May 2008 08:36 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
yes it is a web application but built from pl/sql package

you are correct i can't use user built in,it gives the schema name?
Re: capturing user details [message #318660 is a reply to message #318655] Wed, 07 May 2008 08:41 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>you are correct i can't use user built in,it gives the schema name?
The middle tier application must cooperate & make the "username" available to the PL/SQL code by some mutually agreeable mechanism.
Re: capturing user details [message #318661 is a reply to message #318660] Wed, 07 May 2008 08:44 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
I did not get you,sorry for that

But there isn't any middle tier application.

The package uses htp.print default package embedded with html codes to build the application,hence is it not possible to get this using oracle?
Re: capturing user details [message #318663 is a reply to message #318655] Wed, 07 May 2008 08:51 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>is it not possible to get this using oracle?
If YOUR application can differentiate between different users, then at some level it "knows" the current user.
The folks who can provide you answers are the ones who wrote the application.

[Updated on: Wed, 07 May 2008 09:02] by Moderator

Report message to a moderator

Re: capturing user details [message #318669 is a reply to message #318661] Wed, 07 May 2008 09:13 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Presumably you've got some sort of Users table which you check when they log in.

You need to either store something unique to their session (ip address, or username & machine name - have a look at OWA_UTIL.PRINT_CGI_ENV and GET_CGI_ENV)) in this table when they connect to your application, or generate a unique number, store it in a cookie on their machine, and store that in your user table.

Either of these solutions gives you a way of tying the current connection to a row in the Users table.
Re: capturing user details [message #318670 is a reply to message #318663] Wed, 07 May 2008 09:13 Go to previous message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Is it possible by owa_cookie default oracle package
Previous Topic: Which aggregate function should use for this problem
Next Topic: camn we use 2 instances of set linesize
Goto Forum:
  


Current Time: Sun Dec 11 06:09:05 CST 2016

Total time taken to generate the page: 0.11250 seconds