Oracle标准审计实战过程详解
最近很多人咨询我Oracle的审计功能，下面我用一个非常清晰的过程给大家讲解一个相关案例。
我们都知道Oracle审计功能很强大，自身的审计功能分两种：
标准审计，基本审计数据库和数据库对象发生了什么操作。
精确审计，审计SQL级别、字段级别等。
当然Oracle也有自己的第三方审计产品——AVDF，感兴趣的朋友可以去了解。
以下以用户密码被修改作为审计案例。
审计级别
alter system set audit_trail=os scope=spfile;
Note：
默认是DB级别，存放在System表空间。若想将审计记录存放在表空间，可以把AUD$表迁移至专门的表空间存放，避免后期庞大影响系统登录。Oracle官方是建议将审计记录存放在本地，当然也要定期清理。
审计sys的操作
alter system set audit_sys_operations=true scope=spfile;
Note：
审计sys用户、SYSDBA、SYSOPER、SYSASM之类的权限的操作。
查看审计日志存放目录
show parameter audit_file_destNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /oracle/app/oracle/admin/fordb
/adump
SQL>
重启数据库生效
shutdown immediate
startup
查看
select * from dba_stmt_audit_opts;
Note：
dba_obj_audit_opts （对象级审计）
dba_priv_audit_opts （权限级审计）
dba_stmt_audit_opts （语句级审计）
11g默认有29个审计策略，12c以后就取消了。
修改审计策略
noaudit all;
noaudit all statements;
noaudit all privileges;
noaudit EXEMPT ACCESS POLICY;
audit alter user by access;
Note：
本次以审计用户密码被修改作为案例，可以根据自身情况自定义。
查看
SQL> select AUDIT_OPTION from dba_stmt_audit_opts;
AUDIT_OPTION
----------------------------------------
ALTER USER
修改密码
alter user system identified by oracle;
查看审计内容
[oracle@aix173 /oracle/app/oracle/admin/fordb/adump]$cat fordb_ora_16056492_20210818105717089062143795.aud
Audit file /oracle/app/oracle/admin/fordb/adump/fordb_ora_16056492_20210818105717089062143795.aud
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/app/oracle/product/11.2.0.4/dbhome_1
System name: AIX
Node name: aix173
Release: 1
Version: 7
Machine: 00C310D54C00
Instance name: fordb
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 16056492, image: oracle@aix173 (TNS V1-V3)
Wed Aug 18 10:57:17 2021 -05:00
LENGTH: "240"
SESSIONID:[5] "51387" ENTRYID:[1] "1" STATEMENT:[1] "9" USERID:[6] "SYSTEM" USERHOST:[6] "aix173" TERMINAL:[5] "pts/1" ACTION:[2] "43" RETURNCODE:[1] "0" OBJ$NAME:[6] "SYSTEM" OS$USERID:[6] "oracle" DBID:[9] "498190680" PRIV$USED:[2] "22"
[oracle@aix173 /oracle/app/oracle/admin/fordb/adump]$
Note：
审计文件后期会产生很多，很难查看，因为只要用sysdba登录数据库就会产生一个文件。不过sys产生的审计日志内容比较直观些。
sys产生的内容：
[oracle@aix173 /oracle/app/oracle/admin/fordb/adump]$cat fordb_ora_10879150_20210818105344009057143795.aud
Audit file /oracle/app/oracle/admin/fordb/adump/fordb_ora_10879150_20210818105344009057143795.aud
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/app/oracle/product/11.2.0.4/dbhome_1
System name: AIX
Node name: aix173
Release: 1
Version: 7
Machine: 00C310D54C00
Instance name: fordb
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 10879150, image: oracle@aix173 (TNS V1-V3)
Wed Aug 18 10:53:44 2021 -05:00
LENGTH : '158'
ACTION :[7] 'CONNECT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/1'
STATUS:[1] '0'
DBID:[9] '498190680'
Wed Aug 18 10:53:44 2021 -05:00
LENGTH : '157'
ACTION :[6] 'COMMIT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/1'
STATUS:[1] '0'
DBID:[9] '498190680'
Wed Aug 18 10:53:44 2021 -05:00
LENGTH : '157'
ACTION :[6] 'COMMIT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/1'
STATUS:[1] '0'
DBID:[9] '498190680'
Wed Aug 18 10:54:05 2021 -05:00
LENGTH : '186'
ACTION :[34] ' alter user system identified by *'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/1'
STATUS:[1] '0'
DBID:[9] '498190680'
墨天轮原文链接：https://www.modb.pro/db/100757（复制到浏览器或者点击“阅读原文”立即查看）