getting user information in a trigger
Greg Starnes asked how to find the acting user in an audit trigger. Kevin Zou said to use the USER variable, noting triggers cannot audit selects (use a tool like I-SPY). Irwin Goldstein thought IDS.2000 would allow select triggers. Ben Draper gave a procedure using dbinfo('sessionid') and sysmaster:syssessions to get the username.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
im building a trigger to audit user activity in certain tables. Is there a way to query the system to determine a user id that is performing some activity on a table?
I used to write triggers for auditing purpose. You can use a variable 'user' to find out who is accessing database. -- 'user' is as same as 'TODAY', in case you have n't used it before. Keep in mind that you can't audit select statment by using triggers. If you want to audit any sql/database activities, you need some audit tools such as I-SPY. regard Kevin Zou
I beleive IDS.2000 will let you create triggers based on a select operation. In article <8e4nr9$ein$1@bob.news.rcn.net>, "Kevin Zou" <Kevinzou@hotmail.com> wrote: > I used to write triggers for auditing purpose. You can use a variable 'user' > to find out who is accessing database. -- 'user' is as same as 'TODAY', in > case you have n't used it before. > > Keep in mind that you can't audit select statment by using triggers. If you > want to audit any sql/database activities, you need some audit tools such as > I-SPY. > > regard > Kevin Zou > > -- Irwin Goldstein Objective Software Systems, Inc. http://www.objectsoft.com Sent via Deja.com http://www.deja.com/ Before you buy.
have the trigger call a stored procedure which calls dbinfo('sessionid')
then query the sysmaster:syssessions for the current username which matches
the 'sid' from dbinfo
define i_sesid integer;
define c_userid char(8);
let i_sesid = dbinfo('sessionid');
select username
into c_userid
from sysmaster:syssessions
where sid = i_sesid;
insert into <<audit table here>> values (c_userid);
HTH...
Greg Starnes wrote in message <8e4md1$q9p$1@paxfeed.eni.net>...
>im building a trigger to audit user activity in certain tables. Is there a
>way to query the system to determine a user id that is performing some
>activity on a table?
>
>