Audit Users with "DROP ANY TABLE" Privilege: Example

刘泽宇

目的：
This article explains how to track/audit the tables being dropped by a user who has the "DROP ANY TABLE" privilege.

详细：
Step 1: Create the custom audit table:
------------------------------------------------------------------------
SQL> connect sys/sys as sysdba;
Connected.

create table event_table (
ora_sysevent varchar2(20),
ora_login_user varchar2(30),
ora_instance_num number,
ora_database_name varchar2(50),
ora_dict_obj_name varchar2(30),
ora_dict_obj_type varchar2(20),
ora_dict_obj_owner varchar2(30),
timestamp date)
/

Step 2: Create "AFTER DROP" Client Event Trigger Under SYS schema:
------------------------------------------------------------------
Note: Assume that User "test1" has "DROP ANY TABLE" privilege and you want audit
user "test1".
This step creates a trigger on "test1" schema and it is fired whenever the user
"test1" issues DROP command.

CREATE or REPLACE TRIGGER after_DROP AFTER DROP on test1.schema
BEGIN
IF( ora_sysevent='DROP' ) THEN
insert into event_table values
(ora_sysevent,
ora_login_user,
ora_instance_num,
ora_database_name,
ora_dict_obj_name,
ora_dict_obj_type,
ora_dict_obj_owner,
sysdate);
END IF;
END;
/

Step 3: Connect to "test1" user and drop a table that is owned by user "test2"
-------------------------------------------------------------------------------
SQL> connect test1/<password>
SQL> drop table test2.exp_tst1;

Table dropped.


Step 4: Now Login in as sys and query on event_table:
-----------------------------------------------------
SQL> connect sys/sys as sysdba
Connected.

SQL> select * from event_table;

ORA_SYSEVENT ORA_LOGIN_USER ORA_INSTANCE_NUM
-------------------- ------------------------------ ----------------
ORA_DATABASE_NAME
--------------------------------------------------
ORA_DICT_OBJ_NAME ORA_DICT_OBJ_TYPE
------------------------------ --------------------
ORA_DICT_OBJ_OWNER TIMESTAMP
------------------------------ ---------
DROP TEST1 1
XXX.ORACLE.COM
EXP_TST1 TABLE
TEST2 31-MAY-02


By querying the event_table, we can see the required details have been captured.

Other DDL client events that can be used in similar way:

1) BEFORE ALTER
2) AFTER ALTER
3) BEFORE ANALYZE
4) AFTER ANALYZE
5) BEFORE DDL
6) AFTER DDL
7) BEFORE TRUNCATE
8) AFTER TRUNCATE