标题: Audit Users with "DROP ANY TABLE" Privilege: Example [打印本页] 作者: 刘泽宇 时间: 2025-4-19 10:32 标题: 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.
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.