Disable Database Audit In Oracle 19c RAC-20220111
导读：为什么要使用审计呢？通常情况下是为了通过审核来监控用户活动。同时数据库审计也是数据库安全的一个重要组成部分（官方描述）。现实中生产环境开启审计的目的：更重要的是为了满足合规审计要求（生产环境建议开启）。例如：等保3级测评，开启数据库审计是必须的。若开启审计功能注意建立审计记录生命周期管理，不要让审计记录撑爆你的数据库。
1.环境与db版本
环境：oracle 19c rac on three nodes for redhat8.3 db版本：19.9（已应用19.9RU） SQL> select * from v$version; BANNER ---------------------------------------------------------------------------------------------------------------------------------------------------------------- BANNER_FULL -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- BANNER_LEGACY CON_ID ---------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.9.0.0.0 Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production 0
2.查看database auditing状态
2.1 检查Rac内所有节点的database auditing和sql statements with SYS authorization状态（默认是开启）
# 方法1，通过show parameter查看
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/racdb/ad
ump
audit_sys_operations boolean TRUE <=default enable sql statements with SYS authorization
audit_syslog_level string
audit_trail string DB <=dbca create database default enable database shandard auditing
unified_audit_common_systemlog string
unified_audit_sga_queue_size integer 1048576
unified_audit_systemlog string
# 方法2，通过gv$parameter视图查看
col name for a40
col value for a30
set line 200
select inst_id,name,value from gv$parameter where name in ('audit_sys_operations','audit_trail') order by 1,2;
INST_ID NAME VALUE
---------- ---------------------------------------- ------------------------------
1 audit_sys_operations TRUE <=default enable sql statements with SYS authorization
1 audit_trail DB <=dbca create database default enable shandard database auditing
2 audit_sys_operations TRUE
2 audit_trail DB
3 audit_sys_operations TRUE
3 audit_trail DB
6 rows selected.
2.2 检查Rac内所有节点的审计sunified auditing状态（默认是关闭）
SQL> select inst_id,value,parameter from gv$option where parameter='Unified Auditing' order by 1; INST_ID VALUE PARAMETER ---------- ------------------------------ ---------------------------------------- 1 FALSE Unified Auditing 2 FALSE Unified Auditing 3 FALSE Unified Auditing # false表示未配置unified auditing
3.关闭database auditing
3.1 关闭Rac内所有节点的database auditing
关闭database auditing # audit_trail此参数仅能关闭database auditing。关于FGA的审计是无法关闭的 SQL> alter system set audit_trail=none sid='*' scope=spfile; System altered. 关闭sql statements with SYS authorization # audit_sys_operations此参数仅能减少sysdba的审计。关于sysdba的startup,shutdown,connect是无法关闭的 SQL> alter system set audit_sys_operations=false sid='*' scope=spfile; System altered.
3.2 重启db让参数立即生效
修改audit_trail或audit_sys_operations后需重启db才能立即生效。 # Rac重启db步骤 [oracle@rac3:/home/oracle]$srvctl stop database -db racdb [oracle@rac3:/home/oracle]$srvctl start database -db racdb
3.3 检查参数是否生效
已经闭关database shandard auditing和sql statements with SYS authorization
col name for a40
col value for a30
set line 200
select inst_id,name,value from gv$parameter where name in ('audit_sys_operations','audit_trail') order by 1,2;SQL> SQL> SQL>
INST_ID NAME VALUE
---------- ---------------------------------------- ------------------------------
1 audit_sys_operations FALSE <=closed
1 audit_trail NONE <=closed
2 audit_sys_operations FALSE
2 audit_trail NONE
3 audit_sys_operations FALSE
3 audit_trail NONE
6 rows selected.
4.参考文档
How to Disable Oracle Database Auditing (Doc ID 1528170.1)