Home » SQL & PL/SQL » SQL & PL/SQL » Who deleted the data (12c)
Who deleted the data [message #672668] Tue, 23 October 2018 01:24 Go to next message
deepakdot
Messages: 51
Registered: July 2015
Member
Hello,

Sometime I notice some data is getting deleted from tables. So I want to know who (actual OS user) deleted the data in a schema for the table. How to retrieve this information. I want the OS user detail and which table was deleted. I don't need the actual Data which got deleted.

e.g. I want to capture the OS user 'JAMES' deleted the table 'EMP' from oracle schema 'SCOTT'
the output of the requirement would be

OSUSER - TABLENAME - SCHEMANAME
-------------------------------
JAMES - EMP - SCOTT


Thanks
Deepak
Re: Who deleted the data [message #672669 is a reply to message #672668] Tue, 23 October 2018 01:34 Go to previous messageGo to next message
John Watson
Messages: 8351
Registered: January 2010
Location: Global Village
Senior Member
I would use audit. Something like
create audit policy aud_del_emp actions delete on scott.emp;
audt policy aud_Del_emp;
Re: Who deleted the data [message #672670 is a reply to message #672668] Tue, 23 October 2018 02:09 Go to previous messageGo to next message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Or in the old way:
audit delete on scott.emp;
and query DBA_AUDIT_TRAIL.

And for the past, you can use Log Miner.

Re: Who deleted the data [message #672671 is a reply to message #672670] Tue, 23 October 2018 02:59 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
Unified Auditing.
Re: Who deleted the data [message #672678 is a reply to message #672671] Tue, 23 October 2018 07:31 Go to previous messageGo to next message
Bill B
Messages: 1969
Registered: December 2004
Senior Member
and if you don't want to turn on auditing then make an on delete after trigger on the table that will log the infromation that you want to an "audit" table.
Re: Who deleted the data [message #672687 is a reply to message #672678] Wed, 24 October 2018 06:20 Go to previous messageGo to next message
EdStevens
Messages: 1250
Registered: September 2013
Senior Member
Bill B wrote on Tue, 23 October 2018 07:31
and if you don't want to turn on auditing then make an on delete after trigger on the table that will log the infromation that you want to an "audit" table.
One could, but that is just reinventing the wheel. Why would one not just use AUDIT for what it was intended?
Re: Who deleted the data [message #672689 is a reply to message #672687] Wed, 24 October 2018 07:15 Go to previous messageGo to next message
Bill B
Messages: 1969
Registered: December 2004
Senior Member
Because turning on auditing puts a load on the database and slows it down. A single trigger on a single table that only fires on a delete action would have no effect

[Updated on: Wed, 24 October 2018 07:15]

Report message to a moderator

Re: Who deleted the data [message #672690 is a reply to message #672689] Wed, 24 October 2018 07:19 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
It is true that enabling auditing might be akin to using a sledgehammer to crack a walnut. It depends on the OP's requirements.
Re: Who deleted the data [message #672691 is a reply to message #672689] Wed, 24 October 2018 07:59 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Given that you can turn on auditing just for delete on the given table I'm not sure what load you're worried about vs using a trigger.
Re: Who deleted the data [message #672693 is a reply to message #672691] Wed, 24 October 2018 08:32 Go to previous messageGo to next message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I concur, and saying that the trigger has no effect is not true (and even more if it is a trigger for each row) without speaking about what happens when an error occurs during the trigger execution.

I made some tests (many years ago on versions <= 10g so maybe not true now) between built-in (traditional) audit and custom audit (for less data recorded in the trigger as not available in USERENV context or ora_% variables) and there is no doubt you have to use built-in audit.

Re: Who deleted the data [message #672694 is a reply to message #672693] Wed, 24 October 2018 08:46 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
Obviously with 12c, OP has the option of traditional auditing or the new Unified Auditing. This involves relinking the Oracle binary to enable so requires downtime.
Re: Who deleted the data [message #672695 is a reply to message #672694] Wed, 24 October 2018 08:51 Go to previous messageGo to next message
John Watson
Messages: 8351
Registered: January 2010
Location: Global Village
Senior Member
I've hardly ever bothered with the relink. I know you are supposed to do it, but as far as I can see the only functional result of the relink is that what we now have to call "traditional" audit stops working. Without doing the relink, you can run either use trad or unified audit. Or indeed both.
Re: Who deleted the data [message #672696 is a reply to message #672695] Wed, 24 October 2018 08:53 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
I didn't realise. That's useful to know, John. Thank you.
Re: Who deleted the data [message #672697 is a reply to message #672693] Wed, 24 October 2018 08:59 Go to previous messageGo to next message
Bill B
Messages: 1969
Registered: December 2004
Senior Member
Michel Cadot wrote on Wed, 24 October 2018 09:32

I concur, and saying that the trigger has no effect is not true (and even more if it is a trigger for each row) without speaking about what happens when an error occurs during the trigger execution.

I made some tests (many years ago on versions <= 10g so maybe not true now) between built-in (traditional) audit and custom audit (for less data recorded in the trigger as not available in USERENV context or ora_% variables) and there is no doubt you have to use built-in audit.

If it is a choice between using triggers to audit the database or oracle auditing I concur that Oracle auditing should be used. But that's NOT what the OP requested. He wants to know on one table only who is deleting the rows. The trigger will only be fired by the database on a delete action on the single, specific table. There is now way turning auditing on is less of a drag on the database.
Re: Who deleted the data [message #672698 is a reply to message #672697] Wed, 24 October 2018 09:03 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
You seem to be missing the bit where you can turn on auditing for delete on the table only.
So it'll have zero effect on any other operation.
And Michel seems to have done tests that show audit is more efficient than a trigger.
Re: Who deleted the data [message #672700 is a reply to message #672698] Wed, 24 October 2018 13:04 Go to previous messageGo to next message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Here's a small test made on my Windows XP laptop with Oracle 11.2.0.4.

Whole script(@sys is "connect sys..." and @c is "connect michel..."): 10,000 rows out of 1,000,000 are deleted in the table:
drop table t purge;
create table t as 
select level id, cast(to_char(level) as char(100)) val from dual connect by level <= 1000000
/
create index i_id on t (id)
/
show parameter audit_trail
audit delete on t;
set timing on
declare
  i pls_integer;
begin
  for i in 1..10000 loop
    delete t where id = 100*i;
  end loop;
end;
/
rollback;
set timing off
noaudit delete on t;
drop table audit_log purge;
create table audit_log (
  dt         timestamp with time zone, 
  os_user    varchar2(255), 
  owner      varchar2(30), 
  table_name varchar2(30))
/
create or replace procedure prc_audit (
  p_user  varchar2,
  p_owner varchar2,
  p_table varchar2)
is
  pragma autonomous_transaction;
begin
  insert into audit_log (dt, os_user, owner, table_name)
    values (systimestamp, p_user, p_owner, p_table);
  commit;
end;
/
create or replace trigger t_trg before delete on t
call prc_audit(sys_context('USERENV','OS_USER'), 'MICHEL', 'T')
/
@sys
alter system set audit_trail=false scope=spfile;
shutdown immediate;
startup;
@c
show parameter audit_trail
set timing on
declare
  i pls_integer;
begin
  for i in 1..10000 loop
    delete t where id = 100*i;
  end loop;
end;
/
rollback;
set timing off

Execution 1 (setup and traditional audit):
SQL> drop table t purge;

Table dropped.

SQL> create table t as
  2  select level id, cast(to_char(level) as char(100)) val from dual connect by level <= 1000000
  3  /

Table created.

SQL> create index i_id on t (id)
  2  /

Index created.

SQL> show parameter audit_trail
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------------------------------------
audit_trail                          string      DB
SQL> audit delete on t;

Audit succeeded.

SQL> set timing on
SQL> declare
  2    i pls_integer;
  3  begin
  4    for i in 1..10000 loop
  5      delete t where id = 100*i;
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:27.06
SQL> rollback;

Rollback complete.

Elapsed: 00:00:11.71
SQL> set timing off
SQL> noaudit delete on t;

Noaudit succeeded.

Execution 2 (custom trigger):
SQL> drop table audit_log purge;

Table dropped.

SQL> create table audit_log (
  2    dt         timestamp with time zone,
  3    os_user    varchar2(255),
  4    owner      varchar2(30),
  5    table_name varchar2(30))
  6  /

Table created.

SQL> create or replace procedure prc_audit (
  2    p_user  varchar2,
  3    p_owner varchar2,
  4    p_table varchar2)
  5  is
  6    pragma autonomous_transaction;
  7  begin
  8    insert into audit_log (dt, os_user, owner, table_name)
  9      values (systimestamp, p_user, p_owner, p_table);
 10    commit;
 11  end;
 12  /

Procedure created.

SQL> create or replace trigger t_trg before delete on t
  2  call prc_audit(sys_context('USERENV','OS_USER'), 'MICHEL', 'T')
  3  /

Trigger created.

SQL> @sys
Connected.
SYS> alter system set audit_trail=false scope=spfile;

System altered.

SYS> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS> startup;
ORACLE instance started.
Total System Global Area  644468736 bytes
Fixed Size                  1407172 bytes
Variable Size             520095548 bytes
Database Buffers          117440512 bytes
Redo Buffers                5525504 bytes
Database mounted.
Database opened.
SYS> @c
Connected.
SQL> show parameter audit_trail
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------------------------------------
audit_trail                          string      FALSE
SQL> set timing on
SQL> declare
  2    i pls_integer;
  3  begin
  4    for i in 1..10000 loop
  5      delete t where id = 100*i;
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:38.17
SQL> rollback;

Rollback complete.

Elapsed: 00:00:10.51
SQL> set timing off
So, traditional audit 27.06 s, custom trigger 38.17 s that is 40% more (and you have much more information in traditional audit trail).
This is in a single user environment, it is worse in multi-user deleting the same table (not easy to do in Windows but those who have Linux/Unix environment can easily send 10 sessions in background and see the results).

Re: Who deleted the data [message #672753 is a reply to message #672700] Fri, 26 October 2018 07:28 Go to previous messageGo to next message
Bill B
Messages: 1969
Registered: December 2004
Senior Member
lol,
I stand corrected. It's a good day when you learn something new. Thank you!!!
Re: Who deleted the data [message #672758 is a reply to message #672753] Fri, 26 October 2018 07:46 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
Nice example, Michel. Merci Smile

[Edit: courtesy]

[Updated on: Fri, 26 October 2018 07:47]

Report message to a moderator

Re: Who deleted the data [message #672837 is a reply to message #672758] Mon, 29 October 2018 00:54 Go to previous messageGo to next message
deepakdot
Messages: 51
Registered: July 2015
Member
Thank you All. Audit is actually helps me and this is what I wanted.

audit delete on <Table_Name>;
noaudit delete on <Table_Name>;

This is perfectly working. But for Any table i see its not working properly.

AUDIT DELETE ANY TABLE;

This is not working. Individual table Audit is working. Anyway My requirement is for few table and I am continuing with audit those tables.
Re: Who deleted the data [message #672838 is a reply to message #672837] Mon, 29 October 2018 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

"AUDIT DELETE ANY TABLE;" does NOT audit DELETE on all tables it audits when the privilege "DELETE ANY TABLE" is used.
There is no statement to audit DELETE on all tables, you have to do it table by table.

Re: Who deleted the data [message #672839 is a reply to message #672838] Mon, 29 October 2018 01:37 Go to previous messageGo to next message
John Watson
Messages: 8351
Registered: January 2010
Location: Global Village
Senior Member
That's a case where Unified Audit is superior:
pdby1> create audit policy audit_delete actions delete;

Audit policy created.

pdby1> audot policy audit_delete;
SP2-0734: unknown command beginning "audot poli..." - rest of line ignored.
pdby1> audit policy audit_delete;

Audit succeeded.

pdby1>
Re: Who deleted the data [message #672842 is a reply to message #672839] Mon, 29 October 2018 02:10 Go to previous message
deepakdot
Messages: 51
Registered: July 2015
Member
Got it. Thank you.
Previous Topic: How to make a trigger on the table for the history table
Next Topic: date column displays strange output
Goto Forum:
  


Current Time: Tue Sep 29 21:11:34 CDT 2020