一.开启审计
alter system set audit_sys_operations=TRUE scope=spfile; --审计管理用户 alter system set audit_trail=db,extended scope=spfile; //将sql语句写入审计表中
重启数据库查看
shutdown immediate; startup; show parameter audit;
二.迁移审计表(因审计表默认存放于system表空间，为避免发生审计表过大将system表空间撑满，故移出审计表)
create tablespace AUDITSPACE datafile ‘/dbfile/oracle/oradata/system/AUDITSPACE.dbf‘ size 30G;
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => ‘AUDITSPACE‘);
END;
/
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,--this moves table FGA_LOG$
audit_trail_location_value => ‘AUDITSPACE‘);
END;
/
alter table audit$ move tablespace AUDITSPACE;
alter index i_audit rebuild online tablespace AUDITSPACE;
alter table audit_actions move tablespace AUDITSPACE;
alter index i_audit_actions rebuild online tablespace AUDITSPACE;
三.增加审计对象
审计用户username所有成功的操作
audit all by username by access whenever successful;
针对用户的审计(未执行成功的也审计)
audit select table by username by access; //查表审计 audit update table by username by access; //更新审计 audit delete table by username by access; //删除审计 audit insert table by username by access; //插入审计
针对某表的更新、删除审计(错误也审计)
AUDIT UPDATE,DELETE,INSERT ON T_TEST by access;
保护审计
audit all on sys.aud$ by access;
其他审计：
select * from AUDIT_ACTIONS t; --所有可审计的操作
audit alter table by USERNAME by access;
audit alter sequence by USERNAME by access;
查看审计
select * from dba_audit_trail;
select * from dba_stmt_audit_opts where user_name=‘USERNAME‘;
四.自动删除审计表
BEGIN --做审计清理的初始化工作 SYS.dbms_audit_mgmt.init_cleanup(audit_trail_type => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD, default_cleanup_interval => 24 ); END; /
BEGIN --新建调度，对审计日志符合过期要求（要求自定义）的数据打上时间戳，交由后面的清理JOB完成清理,保留7天的审计日志，每晚22点打标签。 DBMS_SCHEDULER.CREATE_JOB(job_name => ‘DAILY_AUD_TIMESTAMP‘, job_type => ‘PLSQL_BLOCK‘, job_action => ‘BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,LAST_ARCHIVE_TIME => SYSDATE-7); END;‘, --为超过7天的数据打上过期标签 start_date => sysdate, repeat_interval => ‘FREQ=DAILY;BYHOUR=22‘, --每天的22点执行 enabled => TRUE, comments => ‘Create an archive timestamp‘); END; /
BEGIN --清理JOB，每天清理一次 SYS.DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(AUDIT_TRAIL_TYPE => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, AUDIT_TRAIL_PURGE_INTERVAL => 24, --单位小时 AUDIT_TRAIL_PURGE_NAME => ‘AudPurge‘, USE_LAST_ARCH_TIMESTAMP => TRUE); END; /