How to identify which user executed update statement on particular table?

Question

So this is my scenario, Here I want to identify who updated my test table and I don't have access to sys.

is there any way to find out without using sys??

create table test
                 (
                  id      number,
                  name    varchar2(32767),
                  age     number
                 );

insert into test values(2,'XYZ',18);
insert into test values(3,'ABC',19);

Random User updated test table

update test set age =20 where id =3;

Please elaborate "user". Do you mean Oracle user (schema)? – sohail Jul 17 '18 at 18:13 — sohail, Jul 17 '18 at 18:13
yes ,@sohail schema user – ankush bansal Jul 18 '18 at 01:58 — ankush bansal, Jul 18 '18 at 01:58

Tajinder · Answer 1 · 2018-07-17T18:06:35.707

You can check v$sql and v$session. Below query will help you to get the session details.

select * from v$sql a join v$session b 
on a.sql_id = b.sql_id
where upper(b.sql_fulltext) like %UPDATE%your_table_name%;

You can check below links for more details.

V$SESSION - https://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2088.htm#REFRN30223

V$SQL - https://docs.oracle.com/database/121/REFRN/GUID-2B9340D7-4AA8-4894-94C0-D5990F67BE75.htm#REFRN30246

V$ACTIVE_SESSION_HISTORY https://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_1007.htm#REFRN30299

Other Helping Links - Find out the history of SQL queries

However, for the future, I suggest you follow the below steps for logging.

Create a trigger on before update statement of the table.
Insert in the logs table, with two extra columns (IP and HOST) with valuesSYS_CONTEXT('USERENV', 'IP_ADDRESS', 15) and SYS_CONTEXT('USERENV', 'HOST', 15).
This will provide IP and Hostname of the machine from which update was run.

You can check the output of the below queries.

select SYS_CONTEXT('USERENV', 'IP_ADDRESS', 15) ipaddr from dual;

select SYS_CONTEXT('USERENV', 'HOST', 15) host_name from dual;

score 1 · Answer 2 · answered Jul 17 '18 at 18:53

Mostly, it's preferable to use a DB Trigger for DML operations for logging People and related Module Information. At the Application layer Client and Module Info might be set as :

dbms_application_info.set_client_info( my_client_name ); 
dbms_application_info.set_module( my_mdl, null );

It's proper to create a table for logging for DML operations applied for the table test as :

create table test( col1 int, col2 varchar2(50) .... ); -- <-- assuming test is as this.
create table log_test( col1 int, col2 varchar2(50) ...., col_cl_info varchar2(64),
           col_terminal varchar2(64), col_opr_time date, col_machine varchar2(64), 
           col_dml_type varchar2(1) , col_module varchar2(64) );

and then, you may create such a trigger as follows :

create or replace trigger trg_log_test
  before update or insert on test   
  for each row    
declare
  v_cl_info  v$session.client_info%type;
  v_machine  v$session.machine%type;
  v_module   v$session.module%type;
  v_action   v$session.osuser%type;
  v_dml_type varchar2(1);
begin

  select client_info, machine, module
    into v_cl_info, v_machine, v_module
    from v$session
   where audsid = userenv('SESSIONID');


if v_cl_info in ('oracle') then 
 dbms_application_info.read_client_info( v_cl_info ); 
 dbms_application_info.read_module( v_module, v_action ); 
end if; 

if     inserting then 
  v_dml_type := 'I';
elsif  updating then
  v_dml_type := 'U';
end if;  

  insert into log_test( col1, col2, .... ,
         col_cl_info, col_terminal, col_opr_time , col_machine, col_dml_type, col_module )
  values(:new.col1, :new.col2, .........., 
         v_cl_info , userenv('TERMINAL'),sysdate , v_machine , v_dml_type , v_module  );

end;

where v$session is a public synonym for sys.v_$session dictionary view.

How to identify which user executed update statement on particular table?

2 Answers2