平台:10gR2:
1.开启审计:
alter system set audit_sys_operations=truescope=spfile;
alter system set audit_trail=db_extended scope=spfile;
重启database
2.基本概念
审计3个级别:
a) statement语句审计
查询:DBA_STMT_AUDIT_OPTS
定义:STMT_AUDIT_OPTION_MAP
b) privilege权限审计
查询:DBA_PRIV_AUDIT_OPTS
定义:system_privilege_map
c) object对象审计
查询:DBA_OBJ_AUDIT_OPTS
定义:sys.tab$.audit$
3.其它选项:
by access / by session (default)
by access 每个审计记录一条
by session 一个session一类audit记录一条
建议都用by access
whenever [not] successful
对应dba_audit_trial.returncode,无not则不记录操作失败的记录
default 为有not
4.相关视图和表:
可通过select * from dict where table_name like '%AUDIT%'; 查看所有视图
重要视图:
select * from dba_audit_mgmt_config_params;--查看配置参数
select * from DBA_STMT_AUDIT_OPTS;--查看语句审计设置
select * from DBA_PRIV_AUDIT_OPTS;--查看权限审计设置
select * from DBA_OBJ_AUDIT_OPTS;--查看对象审计设置
select * from DBA_AUDIT_POLICIES;--查看FGA设置
select * from DBA_COMMON_AUDIT_TRAIL;--审计日志,DBA_AUDIT_TRAIL和DBA_FGA_AUDIT_TRAIL的集合体
重要表:
sys.audit$
sys.aud$
sys.fga_log$
5.示例:
a) 添加一个statement审计:
查找STMT_AUDIT_OPTION_MAP找到你想要的审计类型,如:CREATE ANY TABLE
SQL> audit CREATE ANY TABLE by access;
Audit succeeded
查看DBA_STMT_AUDIT_OPTS就能找到该审计设置
查看DBA_COMMON_AUDIT_TRAIL看审计结果
SQL> noaudit CREATE ANY TABLE;
Noaudit succeeded
取消审计
b) 添加一个object审计:(qxy1为表)
SQL> audit insert,delete,update on qxy1 by access;
Audit succeeded
查看DBA_OBJ_AUDIT_OPTS找到审计设置
查看DBA_COMMON_AUDIT_TRAIL看审计结果
SQL> noaudit all on qxy1;
Noaudit succeeded
取消审计
c) 设置FGA(细粒度审计)
增加FGA策略:
begin
dbms_fga.add_policy(object_name => 'qxy1',
policy_name => 'chk_qxy1',
statement_types => 'insert,update,delete');
end;
通过DBA_AUDIT_POLICIES查看FGA策略
通过DBA_COMMON_AUDIT_TRAIL查看审计日志
删除FGA策略:
begin
dbms_fga.drop_policy(object_name => 'qxy1', policy_name => 'chk_qxy1');
end;
6.审计管理:
a) 移动日志的存放位置:
主要涉及两张log表:sys.aud$ 、sys.fga_log$,可通过dba_audit_mgmt_config_params查看存放位置,默认为sysaux
sys.aud$可直接用alter table .. move tablespace ..实现,但建议采用如下方式:
BEGIN
IF NOT
DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
DBMS_AUDIT_MGMT.INIT_CLEANUP(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
default_cleanup_interval => 48 /* hours */);
END IF;
END;
/
BEGIN
IF NOT
DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD) THEN
DBMS_AUDIT_MGMT.INIT_CLEANUP(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
default_cleanup_interval => 48 /* hours */);
END IF;
END;
/
begin
sys.DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type => sys.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => 'USERS');
end;
/
begin
sys.DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type => sys.DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
audit_trail_location_value => 'USERS');
end;
/
b) 审计日志的清理
可直接delete、truncate 两张日志表sys.aud$ 、sys.fga_log$
或通过DBMS_AUDIT_MGMT.INIT_CLEANUP设置保存策略
更多的DBMS_AUDIT_MGMT操作参看:
http://download.oracle.com/docs/cd/E11062_01/admin.1023/e11059/avadm_app_d_audit_mgmt.htm#BABDAHBG