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(复制到浏览器或者点击“阅读原文”立即查看)

关于作者
范伟林,中国DBA联盟(ACDU)成员,拥有5年工作经验,涉及电力、金融、政府等行业,拥有OCM、DCA、OCBA的证书。
END
(0)

相关推荐