Oracle-审计功能

简述

审计 (Audit) 用于监视和记录用户所执行的数据库操作,审计记录可存在数据字典表(默认存储在system表空间中的SYS.AUD$表中,可通过视图dba_audit_trail查看审计记录)或操作系统审计记录文件中(默认位置为$ORACLE_BASE/admin/$ORACLE_SID/adump/)。默认情况下,是没有开启审计功能的

审计记录内容包含

  • 操作的日期和时间等信息
  • 用户执行的特定类型操作

注意: 无论是否打开数据库的审计功能,以系统管理员(sysdba, sysoper)权限连接数据库实例,启动数据库和关闭数据库的操作都会强制写入操作系统日志文件中。

存储位置

旧(12c以前版本)审计引擎不同的组件存放在不同的位置
  • SYS.AUD$ : 会存放数据库的标准审计结果
  • SYS.FGA_LOG$ : 会存放细粒度审计结果(fine-grained auditing
  • DVSYS.AUDIT_TRAIL$ : 会存放Oracle Database Vault和Oracle Label Security等组件的审计结果
12C 统一审计引擎
  • 所有的审计记录都存储在字典表UNIFIED_AUDIT_TRAIL

审计功能

  • 审计语句的成功执行、不成功执行或其两者

审计维度

标准审计(AUD)
细粒度审计(FGA)-Fine Grained Auditing

细粒度的审计使用DBMS_FGA包完成配置和管理工作。细粒度审计除了审计功能外,还可用于绑定变量值的捕获。oracle 9i引进的一个新特性。

审计级别

Statement (语句)、Privilege (权限)、object(对象)

Statement

对某种类型的SQL语句审计,不指定结构或对象。如audit table 会审计数据库中所有的create table,drop table,truncate table语句。通过视图DBA_STMT_AUDIT_OPTS查看当前开启的审计选项

Privilege

对某些系统权限的使用情况进行审计。通过视图DBA_PRIV_AUDIT_OPTS查看当前开启的审计选项

object

对指定模式对象上的指定语句的审计。通过视图DBA_OBJ_AUDIT_OPTS查看当前开启的审计选项

审计选项

noaudit | audit
  • audit: 开启指定对象的审计
  • noaudit: 取消审计
by ACCESS | by session
  • by access: 每一个被审计的操作都会生成一条审计记录

  • by session: 同一个会话中同类型操作只会生成一条审计记录,默认by session

whenever [not] successful
  • whenever successful: 操作成功时才被审计
  • whenever not successful: 操作失败时才被审计

省略该子句时,不管操作成功与否都会审计。

新特性

11g 引入新特性之X$DBGALERTEXT内部视图

若需要利用SQL查询Oracle的alert.log告警日志,在11g以前可以通过创建Alert.log内容的外部表来实现查询日志内容的目的。在11g开始,引入了新的ADR(Automatic Diagnostic Repository) 自动诊断仓库特性之后,自动将alert.log文件格式自动转换为XML的格式,默认存放在$ADR_HOME/alert&trace目录下,并且为日志条目增加了更多的属性(如Level之类),使得告警日志能提供更多有用的信息。

从11g开始,就不需要手动创建外部表的方式查询,它已经由Oracle的内部视图X$DBGALERTEXT直接提供。

set linesize 168 pagesize 99
select originating_timestamp, message_group, problem_key, message_text
  from X$DBGALERTEXT
where message_text like '%ORA-00600%'
-- and message_group = 'Generic Internal Error' -- 最近出现过的Internal Errors
-- and message_group = 'admin_ddl' -- 审计最近2天内管理员执行过的DDL语句
  and rownum < 10;

X$DBGALERTEXT的内容极其丰富,包括message记录发生的日期、发生问题的进程号、trace日志的位置、问题的关键(problem key),为分析数据库日志提供极为强大的接口,节约大量的时间。

当log.xml的大小超过10MB时,Oracle会将其内容归档到如log_$N.xml这样的归档文件中,并清空当前的log.xml的内容。

注意: X$DBGALERTEXT的数据实际来源于ADR中的log.xml文件, 若xml被删除或者内容缺失都会影响信息完整性

12C 新特性审计模式

  • Mixed Auditing Policy(混合审计模式):支持新的审计引擎和老的审计引擎一起工作,数据库创建后,默认是使用混合模式,使用ora_secureconfig策略

    • 审计策略:

      set lines 168 pages 99
      col POLICY_NAME for a32
      col USER_NAME for a18
      col SUCCESS for a8
      col FAILURE for a8
      select  USER_NAME, POLICY_NAME, ENABLED_OPT, ENABLED_OPTION, SUCCESS, FAILURE from AUDIT_UNIFIED_ENABLED_POLICIES;
      
      • ora_secureconfig 策略
      • ora_account_mgmt 策略
      • ora_database_parameter 策略
  • Unified Auditing Policy(统一审计模式):12C新引入的审计引擎,审计数据默认放在sysaux表空间中的只读审计表,其属主是audsys(该用户默认是被锁定的)

查看统一审计模式下的审计记录数据

通过字典表UNIFIED_AUDIT_TRAIL查看审计记录

col OS_USERNAME format a12
 col TERMINAL for a12
 col SQL_TEXT format a60
 COL AUDIT_TYPE format a10
 col  ACTION_NAME format a24
 col UNIFIED_AUDIT_POLICIES  format a18
 select AUDIT_TYPE,OS_USERNAME,TERMINAL,ACTION_NAME,
 SQL_TEXT,UNIFIED_AUDIT_POLICIES
  from UNIFIED_AUDIT_TRAIL 
  where OBJECT_NAME='EMP';

相关参数

audit_sys_operations

默认为false,当设置为true时,所有sys用户(包括以sysdba, sysoper身份登录的用户)的操作都会被记录,审计记录不会写到sys.aud$表中。如果是Windows平台,它会记录在windows的事件管理中,如果是linux/unix平台则会记录在audit_file_dest参数指定的文件中。

注意:该参数是(static)静态参数,需要重新启动数据库才能生效

audit_trail

注意:该参数是(static)静态参数,需要重新启动数据库才能生效

  • None:是默认值,不做审计;

  • DB:将审计记录在数据库的审计相关表中,如aud$,审计的结果只有连接信息;

  • DB,Extended:审计结果里面除了连接信息还包含了当时执行的具体语句;

  • OS:将审计记录在操作系统文件中,文件名由audit_file_dest参数指定;

  • XML:10g里新增的参数

安装配置

desc sys.aud$

若上面语句返回结果:表不存在时,说明审计相关的表,视图等对象还没有安装

安装审计表

由于审计表默认安装在SYSTEM表空间,因此,需要确保SYSTEM表空间有足够的空间存放审计信息

sqlplus "/ as sysdba"<<EOF
@$ORACLE_HOME/rdbms/admin/cataudit.sql
shutdown immediate;
startup;
EOF

开启审计

alter system set audit_sys_operations=TRUE scope=spfile;
alter system set audit_trail=db,extended scope=spfile;

shutdown immediate;
startup;
show parameter audit

查看审计配置信息

col PARAMETER_NAME FOR a30
col PARAMETER_VALUE FOR a16
col AUDIT_TRAIL FOR a20
SELECT PARAMETER_NAME, PARAMETER_VALUE, AUDIT_TRAIL
FROM DBA_AUDIT_MGMT_CONFIG_PARAMS
WHERE audit_trail = 'STANDARD AUDIT TRAIL';

12C环境中配置审计模式

开启统一审计模式

检查当前审计模式
SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

若为FALSE,则表示使用混合审计模式

取消统一审计策略
set lines 168 pages 99
col POLICY_NAME for a32
col USER_NAME for a18
col SUCCESS for a8
col FAILURE for a8
select  USER_NAME, POLICY_NAME, ENABLED_OPT, ENABLED_OPTION, SUCCESS, FAILURE from AUDIT_UNIFIED_ENABLED_POLICIES;

-- 取消审计策略
NOAUDIT POLICY ORA_LOGON_FAILURES ;
-- 开启审计策略
AUDIT POLICY ORA_LOGON_FAILURES WHENEVER NOT SUCCESSFUL;
关闭数据库
sqlplus "/ as sysdba"<<EOF
shutdown immediate;
EOF
srvctl stop database -d orcl 
cd $ORACLE_HOME/rdbms/lib
# 开启统一审计模式
make -f ins_rdbms.mk uniaud_on ioracle

# 关闭统一审计模式
make -f ins_rdbms.mk uniaud_off ioracle

注意:

  • 在Windows平台开启统一审计模式时,必须将%ORACLE_HOME%\bin\orauniaud12.dll.dbl 文件重命名为%ORACLE_HOME%\bin\orauniaud12.dll
  • 在Windows平台关闭统一审计模式时,必须将%ORACLE_HOME%\bin\orauniaud12.dll.dll 文件重命名为%ORACLE_HOME%\bin\orauniaud12.dbl
重启数据库
sqlplus "/ as sysdba"<<EOF
startup;
EOF

srvctl start database -d orcl 
检查确认
-- 检查开启哪种审计模式
SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

-- 检查审计数据
set lines 200 pages 99
col name for a18
col username for a18
col account_status for a24
col def_tbs for a16
col temp_tbs for a16
col lt_tbs for a16
select u.con_id,
       c.name,
       username,
       account_status,
       DEFAULT_TABLESPACE def_tbs,
       TEMPORARY_TABLESPACE temp_tbs,
       LOCAL_TEMP_TABLESPACE lt_tbs
  from cdb_users u, v$containers c
 where u.con_id = c.con_id
   and u.username = upper('audsys');

Oracle-审计功能

审计相关的表移动到其他表空间

建议把AUD$表移动到其他的表空间上

手动迁移方式

alter table aud$ move tablespace AUDTBS;
alter table FGA_LOG$ move tablespace AUDTBS;

set lines 168 pages 999
col TABLE_NAME for a32
col SEGMENT_NAME for a32
col TABLESPACE_NAME for a16
col COLUMN_NAME for a32

select TABLE_NAME, SEGMENT_NAME, TABLESPACE_NAME,COLUMN_NAME,'alter table '|| TABLE_NAME || ' move lob('|| COLUMN_NAME ||') store as '|| SEGMENT_NAME ||' (tablespace AUDTBS);'  sql_cmd
  from dba_lobs
 where table_name in ('AUD$', 'FGA_LOG$');

-- alter table AUD$ move lob(SQLBIND) store as SYS_LOB0000000407C00040$$ (tablespace AUDTBS);
-- alter table AUD$ move lob(SQLTEXT) store as SYS_LOB0000000407C00041$$ (tablespace AUDTBS);

select INDEX_NAME, TABLE_NAME, 'alter index ' || INDEX_NAME || ' rebuild online tablespace AUDTBS;' sql_cmd
from dba_indexes
where table_name in ('AUD$', 'FGA_LOG$');

使用存储过程迁移方式

使用Oracle自带的包DBMS_AUDIT_MGMT中的存储过程SET_AUDIT_TRAIL_LOCATION来实现迁移审计表空间,该存储过程接受2个参数,顺序依次是AUDIT_TRAIL_TYPE和AUDIT_TRAIL_LOCATION_VALUE

-- 迁移审计表到audtbs的表空间
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => 'AUDTBS');
END;
/
 
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
audit_trail_location_value => 'AUDTBS');
END;
/

-- 检查确认
SELECT table_name, tablespace_name FROM dba_tables WHERE table_name IN ('AUD$', 'FGA_LOG$');
select TABLE_NAME, SEGMENT_NAME, TABLESPACE_NAME from dba_lobs where table_name in ('AUD$', 'FGA_LOG$');

参数AUDIT_TRAIL_TYPE有以下几种取值
  • AUDIT_TRAIL_ALL: 所有的审计类型,包括标准数据库审计、细粒度审计、操作系统审计和XML文件审计

  • AUDIT_TRAIL_AUD_STD: 标准数据库审计

  • AUDIT_TRAIL_DB_STD :标准数据库审计和细粒度审计

  • AUDIT_TRAIL_FGA_STD: 细粒度审计

  • AUDIT_TRAIL_FILES :操作系统和XML文件审计

  • AUDIT_TRAIL_OS : 操作系统审计,审计数据存放在操作系统的文件里

  • AUDIT_TRAIL_XML :XML文件审计,审计数据存放在XML文件里

参数AUDIT_TRAIL_LOCATION_TYPE的取值是指定要迁移到的表空间的名字

审计记录数据维护

通过Oracle自带的包DBMS_AUDIT_MGMT中的存储过程管理

  • SET_AUDIT_TRAIL_PROPERTY:设置审计的维护属性,即每清除多少条数据提交一次
  • INIT_CLEANUP:设置审计数据保留的天数
  • SET_LAST_ARCHIVE_TIMESTAMP:设置上次归档审计记录的时间戳
  • CREATE_PURGE_JOB:设置每隔多长时间清除审计数据的JOB
init_cleanup
-- 设定清除间隔
BEGIN
  DBMS_AUDIT_MGMT.init_cleanup(
    audit_trail_type         => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
    default_cleanup_interval => 120 /* hours */);
END;
/
验证
SET SERVEROUTPUT ON
BEGIN
  IF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
    DBMS_OUTPUT.put_line('YES');
  ELSE
    DBMS_OUTPUT.put_line('NO');
  END IF;
END;
/

select to_char(min(ntimestamp#),'yyyy-mm-dd hh24:mi:ss') ntime from sys.aud$;
SET_LAST_ARCHIVE_TIMESTAMP
-- 设定归档间隔
BEGIN
DBMS_AUDIT_MGMT.set_last_archive_timestamp(
  audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  last_archive_time => SYSTIMESTAMP-8
  );
END;
/



-- 查看设定的归档间隔
SELECT * FROM dba_audit_mgmt_last_arch_ts;
CREATE_PURGE_JOB
-- 通过创建一个purge Job来进行清理已归档的历史审计记录
BEGIN
  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
    AUDIT_TRAIL_TYPE           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    AUDIT_TRAIL_PURGE_INTERVAL => 24 /* hours */,
    AUDIT_TRAIL_PURGE_NAME     => 'DAILY_AUDIT_PURGE_JOB',
    USE_LAST_ARCH_TIMESTAMP    => TRUE
  );
END;
/

exec DBMS_SCHEDULER.RUN_JOB(job_name => 'SYS.DAILY_AUDIT_PURGE_JOB');

手动清理审计记录数据

sqlplus -S / as sysdba << EOF > clear_aud_`date +%Y%m%d`.log
create table tb_aud_`date +%Y%m` nologging tablespace AUDTBS compress as select * from aud\$;
truncate table sys.aud\$;
EOF

# 表如果太大则采用该方式清理,keep值根据实际情况设置,例如当前aud$大小为5000m
truncate table sys.aud$ reuse storage;
alter table sys.aud$ deallocate unused keep 4000m;
alter table sys.aud$ deallocate unused keep 3000m;
alter table sys.aud$ deallocate unused keep 2000m;
alter table sys.aud$ deallocate unused keep 1000m;

## fga_log$ 表使用类似的清理方法

示例

审计登录失败事件

alter session set nls_date_format='yyyy-mm-dd HH24:Mi:SS';
set lines 168 pages 99
col userhost for a32
col terminal for a18
col LOGOFF$TIME for a32
select userid,userhost,terminal,action#,LOGOFF$TIME,NTIMESTAMP# 
from sys.aud$ 
where userid ='GDYX2' 
  and rownum<20;

alter session set nls_date_format='yyyy-mm-dd HH24:Mi:SS';
set lines 168 pages 999
col os_username for a18
col userhost for a32
col terminal for a18
col username for a18
select os_username,userhost,terminal,username,TIMESTAMP,count(*) 
from dba_audit_trail 
where returncode = 1017 
  and TIMESTAMP > (sysdate - 10)
group by os_username,userhost,username,terminal,TIMESTAMP
order by TIMESTAMP desc ;

Oracle-审计功能

附录

参考文档

  • How to - Monitor Non Critical 11g Database Alert Log Errors Using a SQL UDM (Doc ID 961682.1)
上一篇:Mybatis踩坑——if 标签条件判断一直不成立


下一篇:mysql 触发器