审计视图
STMT_AUDIT_OPTION_MAP -- 审计选项类型代码
AUDIT_ACTIONS -- action代码
ALL_DEF_AUDIT_OPTS -- 对象创建时默认的对象审计选项
DBA_STMT_AUDIT_OPTS -- 当前数据库系统审计选项
DBA_PRIV_AUDIT_OPTS -- 权限审计选项
DBA_OBJ_AUDIT_OPTS
USER_OBJ_AUDIT_OPTS -- 对象审计选项
DBA_AUDIT_TRAIL
USER_AUDIT_TRAIL -- 审计记录
DBA_AUDIT_OBJECT
USER_AUDIT_OBJECT -- 审计对象列表
DBA_AUDIT_SESSION
USER_AUDIT_SESSION -- session审计
DBA_AUDIT_STATEMENT
USER_AUDIT_STATEMENT -- 语句审计
DBA_AUDIT_EXISTS -- 使用BY AUDIT NOT EXISTS选项的审计
DBA_AUDIT_POLICIES -- 审计POLICIES
DBA_COMMON_AUDIT_TRAIL -- 标准审计+精细审计
一些脚本
下面这个脚本可以找出系统开了哪些 系统审计功能(非object 审计)
select nvl(user_name,'SYSTEM WIDE AUDITING')"User_Name",proxy_name "Proxy_Name",audit_option "Audit_Option",
success "Success?",failure "Failure?" from DBA_STMT_AUDIT_OPTS;
例子：
SQL> select nvl(user_name,'SYSTEM WIDE AUDITING')"User_Name",proxy_name "Proxy_Name",audit_option "Audit_Option",
2 success "Success?",failure "Failure?" from DBA_STMT_AUDIT_OPTS;
User_Name Proxy_Name Audit_Option Success? Failure?
------------------------------ ------------------------------ ---------------------------------------- ---------- ----------
SYSTEM WIDE AUDITING CREATE ANY TABLE BY ACCESS BY ACCESS
SCOTT CREATE ANY VIEW BY ACCESS BY ACCESS
可以看到系统对任何一个 用户 执行create table 做了审计，对scott 用户做了 create any view 审计
下面这个脚本可以查出系统开了哪些 对象审计功能
select owner || '.' || object_name "Object",object_type "Object_Type",
decode(alt,'-/-','NONE','S/-','WHENEVER SUCCESSFUL','-/S','WHENEVER UNSUCCESSFUL','S/S',
'WHENEVER SUCCESSFUL AND UNSUCCESSFUL')"Alter",
decode(aud,'-/-','NONE','S/-','WHENEVER SUCCESSFUL','-/S','WHENEVER UNSUCCESSFUL','S/S',
'WHENEVER SUCCESSFUL AND UNSUCCESSFUL')"Audit",
decode(com,'-/-','NONE','S/-','WHENEVER SUCCESSFUL','-/S','WHENEVER UNSUCCESSFUL','S/S',
'WHENEVER SUCCESSFUL AND UNSUCCESSFUL')"Commit",
decode(del,'-/-','NONE','S/-','WHENEVER SUCCESSFUL','-/S','WHENEVER UNSUCCESSFUL','S/S',
'WHENEVER SUCCESSFUL AND UNSUCCESSFUL')"Delete",
decode(gra,'-/-','NONE','S/-','WHENEVER SUCCESSFUL','-/S','WHENEVER UNSUCCESSFUL','S/S',
'WHENEVER SUCCESSFUL AND UNSUCCESSFUL')"Grant",
decode(ind,'-/-','NONE','S/-','WHENEVER SUCCESSFUL','-/S','WHENEVER UNSUCCESSFUL','S/S',
'WHENEVER SUCCESSFUL AND UNSUCCESSFUL')"Index",
decode(ins,'-/-','NONE','S/-','WHENEVER SUCCESSFUL','-/S','WHENEVER UNSUCCESSFUL','S/S',
'WHENEVER SUCCESSFUL AND UNSUCCESSFUL')"Insert",
decode(loc,'-/-','NONE','S/-','WHENEVER SUCCESSFUL','-/S','WHENEVER UNSUCCESSFUL','S/S',
'WHENEVER SUCCESSFUL AND UNSUCCESSFUL')"Lock",
decode(ren,'-/-','NONE','S/-','WHENEVER SUCCESSFUL','-/S','WHENEVER UNSUCCESSFUL','S/S',
'WHENEVER SUCCESSFUL AND UNSUCCESSFUL')"Rename",
decode(sel,'-/-','NONE','S/-','WHENEVER SUCCESSFUL','-/S','WHENEVER UNSUCCESSFUL','S/S',
'WHENEVER SUCCESSFUL AND UNSUCCESSFUL')"Select",
decode(upd,'-/-','NONE','S/-','WHENEVER SUCCESSFUL','-/S','WHENEVER UNSUCCESSFUL','S/S',
'WHENEVER SUCCESSFUL AND UNSUCCESSFUL')"Update",
decode(ref,'-/-','NONE','S/-','WHENEVER SUCCESSFUL','-/S','WHENEVER UNSUCCESSFUL','S/S',
'WHENEVER SUCCESSFUL AND UNSUCCESSFUL')"Reference",
decode(exe,'-/-','NONE','S/-','WHENEVER SUCCESSFUL','-/S','WHENEVER UNSUCCESSFUL','S/S',
'WHENEVER SUCCESSFUL AND UNSUCCESSFUL')"Execute",
decode(cre,'-/-','NONE','S/-','WHENEVER SUCCESSFUL','-/S','WHENEVER UNSUCCESSFUL','S/S',
'WHENEVER SUCCESSFUL AND UNSUCCESSFUL')"Create",
decode(rea,'-/-','NONE','S/-','WHENEVER SUCCESSFUL','-/S','WHENEVER UNSUCCESSFUL','S/S',
'WHENEVER SUCCESSFUL AND UNSUCCESSFUL')"Read",
decode(wri,'-/-','NONE','S/-','WHENEVER SUCCESSFUL','-/S','WHENEVER UNSUCCESSFUL','S/S',
'WHENEVER SUCCESSFUL AND UNSUCCESSFUL')"Write",
decode(fbk,'-/-','NONE','S/-','WHENEVER SUCCESSFUL','-/S','WHENEVER UNSUCCESSFUL','S/S',
'WHENEVER SUCCESSFUL AND UNSUCCESSFUL')"Flashback"
from dba_obj_audit_opts;
由于显示效果不好 ，就不贴执行结果了
下面这个脚本可以查看系统 开了哪些FGA 审计
当然你也可以select * from dba_audit_policies;我只是将其还原回了dbms_fga.add_policies。
declare
cursor fga_defination is
select object_schema, object_name ,policy_name ,policy_text ,
policy_column ,nvl(pf_schema,'NULL') pf_schema ,NVL(pf_package,'NULL') pf_package,nvl(pf_function,'NULL') pf_function,
decode(enabled,'YES','TRUE','NO','FALSE') enabled,
decode(sel||','||ins||','||upd||','||del,'YES,YES,YES,YES','SELECT,INSERT,UPDATE,DELETE','YES,YES,YES,NO','SELECT,INSERT,UPDATE','YES,YES,NO,NO',
'SELECT,INSERT','YES,NO,NO,NO','SELECT','YES,YES,NO,YES','SELECT,INSERT,DELETE','NO,YES,NO,YES','INSERT,DELETE','NO,NO,YES,YES','UPDATE,DELETE',
'YES,NO,NO,YES','SELECT,DELETE','YES,NO,YES,NO','SELECT,UPDATE','YES,NO,YES,YES','SELECT,UPDATE,DELETE',
'NO,YES,YES,YES','INSERT,UPDATE,DELETE','NO,YES,YES,NO','INSERT,UPDATE','NO,YES,NO,NO','INSERT','NO,NO,YES,NO','UPDATE','NO,NO,NO,YES','DELETE') stat,
substr(audit_trail,0,instr(audit_trail,'+')-1) audit_trail1,substr(audit_trail,instr(audit_trail,'+')+1) audit_trail2,policy_column_options from dba_audit_policies;
begin
dbms_output.enable(100000000);
for fga in fga_defination loop
dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -');
dbms_output.put_line(' DBMS_FGA.ADD_POLICY(');
dbms_output.put_line(' object_schema => '''|| fga.object_schema ||''',' );
dbms_output.put_line(' object_name => '''|| fga.object_name ||''',' );
dbms_output.put_line(' policy_name => '''|| fga.policy_name ||''',' );
dbms_output.put_line(' audit_condition => '''|| fga.policy_text ||''',' );
dbms_output.put_line(' audit_column => '''|| fga.policy_column ||''',' );
if (fga.pf_schema!='NULL') then
dbms_output.put_line(' handler_schema => '''|| fga.pf_schema ||''',' );
end if;
if (fga.pf_package!='NULL') then
dbms_output.put_line(' handler_module => '''|| fga.pf_package ||'.' ||fga.pf_function ||''',' );
end if;
dbms_output.put_line(' enable => ' || fga.enabled || ',' );
dbms_output.put_line(' statement_types => '''|| fga.stat ||''',');
if (fga.audit_trail1 IS NULL) then
dbms_output.put_line(' audit_trail => ' || 'DBMS_FGA.' ||fga.audit_trail2 || ',' );
else
dbms_output.put_line(' audit_trail => ' || 'DBMS_FGA.' || fga.audit_trail1 || '+DBMS_FGA.' ||fga.audit_trail2 || ',' );
end if;
dbms_output.put_line(' audit_column_opts => ' || 'DBMS_FGA.' || fga.policy_column_options );
dbms_output.put_line(' );' );
end loop;
dbms_output.put_line('- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -');
end;
/
例子：(部分结果)
Found FGA Audit:ROBINSON4
DBMS_FGA.ADD_POLICY(
object_schema => 'SCOTT',
object_name => 'EMP',
policy_name => 'ROBINSON4',
audit_condition => 'sal>1000',
audit_column => 'SAL',
enable => TRUE,
statement_types => 'INSERT,UPDATE,DELETE',
audit_trail => DBMS_FGA.DB+DBMS_FGA.EXTENDED,
audit_column_opts => DBMS_FGA.ANY_COLUMNS
);