How to retrieve OSUSER and MACHINE in Trigger?

[JimHorton]

How can I get the correct aforementioned values from v$session in a trigger? ie. I have a trigger on delete that archives the record that was deleted. I want to know who deleted it, and which workstation they were on when they deleted it. So, I need a way to know the SID and Serial# of the user who sent the delete statement, so I can get the osuser and machine. Can anyone help? Thanks,<br>
--Jim

 

[hammondj]

Jim,<br>
I have enclosed a bit of code that does part of what you are looking to do. Note that<br>
I am using V_$SESSION instead of V$SESSION. You need to grant select on V_$SESSION from sys to whoever is the owner of the trigger. <br>
<br>
SELECT decode(OSUSER, null,'?',OSUSER),<br>
decode(MACHINE, null,'?',MACHINE),<br>
decode(TERMINAL,null,'?',TERMINAL),<br>
decode(PROGRAM, null,'?',PROGRAM),<br>
SYSDATE<br>
INTO V_OSUSER,<br>
V_MACHINE,<br>
V_TERMINAL,<br>
V_PROGRAM,<br>
V_SYSDATE<br>
FROM SYS.V_$SESSION WHERE<br>
(SYS.V_$SESSION.AUDSID = (Select userenv('SESSIONID') from DUAL));<br>
<br>
Jim H.<br>

 

[JimHorton]

Jim,<br>
Thanks, I will give it a try,<br>
--Jim