oracle审计脚本,关于ORACLE 审计的一些视图和脚本

审计视图

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                       );