场景:经常需要查看某些表做了哪些操作。
Oracle中,可以添加细粒度策略来获取,如下:
begin
dbms_fga.add_policy(object_schema => 'portxx',----所有者
object_name => 't_xxx',----表名
policy_name => 'delete_t_xxx',---审核策略名称,自定义
statement_types => 'DELETE');---'INSERT, UPDATE, DELETE, SELECT'
end;
begin
dbms_fga.add_policy(object_schema => 'portxx',----所有者
object_name => 't_xxx',----表名
policy_name => 'update_t_xxx',---审核策略名称,自定义
statement_types => 'UPDATE');---'INSERT, UPDATE, DELETE, SELECT'
end;
begin
dbms_fga.add_policy(object_schema => 'portxx',----所有者
object_name => 't_xxx',----表名
policy_name => 'insert_t_xxx',---审核策略名称,自定义
statement_types => 'INSERT');---'INSERT, UPDATE, DELETE, SELECT'
end;
这样找到某张表的增,删,改的操作记录
再通过
select * from dba_fga_audit_trail; ##具体的明细记录
查看设置了多少策略
select * from dba_audit_policies;
禁用策略
begin
dbms_fga.drop_policy(
object_schema => 'porxxxx',
object_name => 't_Portal_xx',
policy_name => 'insert_t_xxx');
end;