How to obtain the OS user who modified table data

[issacgeorge]

Hai every body,
I have a table that contains very critical data. I just wanted to know the windows OS user(or machine number) who performed the modification. I thought of writing a trigger in the table which populates another table with the required information whenever there is an update or insert or delete on the first table. How can i get os level information inside a trigger.

The schema doesnot have DBA privileges. Hence it cannot access V$ views. Even if it has how can i get the current SID so that i can query V$SESSION and obtain the os user info.

Again How a normal schema can be allowed to access V$SESSION.

Please help...

Regards
Issac

[thomasp]

You can add two new columns(eg: chuser,date_stamp) to the existing table to trap the user name and date stamp.

CREATE OR REPLACE TRIGGER TRIG_TABLEA
  before insert or update
  on TABLEA
  for each row
begin
    :new.DATE_STAMP  :=sysdate;
    :new.chuser := sys_context('userenv','os_user') ;
END;

Otherwise, if you are using another table, it would be along the lines ...

SQl>create table actions_table (action varchar2(20),....,chuser varchar2(30),date_stamp date); -- you need to capture more info regarding the 							 -- table etc etc, this is just an example
Table created.
CREATE OR REPLACE TRIGGER TRIG_TABLEA
  before insert or update
  on TABLEA
  for each row
begin
 insert into actions_table values ('',....,sys_context('userenv','os_user'),sysdate);
END;

Check out the function SYS_CONTEXT(). You can get a lot of info using this.

[issacgeorge]

Its solves every thing... Thank you very much !!!!!