Oracle审计delete操作

原表数据：

JCOM@QYSBDB> select * from t1;

ID NAME1 NAME2 DT1
---------- ---------------------------------------- ---------------------------------------- ------------------
1 aaa a1
2 bbb a2
3 ccc a3
4 ddd a4

创建细粒度审计策略

BEGIN
DBMS_FGA.ADD_POLICY(
object_schema => ‘jcom‘,
object_name => ‘t1‘,
policy_name => ‘mypolicy1‘,
enable => TRUE,
statement_types => ‘DELETE‘,
AUDIT_TRAIL => DBMS_FGA.DB + DBMS_FGA.EXTENDED,
AUDIT_COLUMN_OPTS => DBMS_FGA.ANY_COLUMNS);
END;
/

SYS@QYSBDB> select policy_name from dba_audit_policies;

POLICY_NAME
--------------------------------------------------------------------------------------------------------------------------------
MYPOLICY1

SYS@QYSBDB> select LSQLTEXT,LSQLBIND from fga_log$;

no rows selected

或者

SYS@QYSBDB> select SQL_TEXT,SQL_BIND from dba_fga_audit_trail;

no rows selected

创建存储过程，执行表数据删除测试（通过存储过程传递2个变量）
JCOM@QYSBDB> create or replace procedure test_del
2 (x varchar,
3 y varchar)
4 as
5 BEGIN
6 delete from t1 where name1=x and name2=y;
7 END;
8 /

Procedure created.

JCOM@QYSBDB> desc test_del
PROCEDURE test_del
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
X VARCHAR2 IN
Y VARCHAR2 IN

通过存储过程删除2行数据

JCOM@QYSBDB> exec test_del(‘aaa‘,‘a1‘);

PL/SQL procedure successfully completed.

JCOM@QYSBDB> exec test_del(‘ccc‘,‘a3‘);

PL/SQL procedure successfully completed.

JCOM@QYSBDB> rollback;

Rollback complete.

查看审计结果

Oracle审计delete操作

原文：https://www.cnblogs.com/little-wu/p/11197848.html