ORACLE审计

一.开启审计

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;
ORACLE审计

二.迁移审计表(因审计表默认存放于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;
/

 

ORACLE审计

上一篇:既有Nginx重新动态编译增加http2.0模块


下一篇:Threejs第一个3D场景