一.开启审计

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