前言
最近很多人咨询我Oracle的审计功能,下面我用一个非常清晰的过程给大家讲解一个相关案例。
我们都知道Oracle审计功能很强大,自身的审计功能分两种:
当然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_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /oracle/app/oracle/admin/fordb
/adump
SQL>
重启数据库生效
shutdown immediate
startup
修改审计策略
查看
select * from dba_stmt_audit_opts;
Note:
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(复制到浏览器或者点击Oracle标准审计实战,过程详解。 – 墨天轮立即查看)
关于作者
范伟林,中国DBA联盟(ACDU)成员,拥有5年工作经验,涉及电力、金融、政府等行业,拥有OCM、DCA、OCBA的证书。
来源:数据和云智能
原创文章,作者:jinwe2020,如若转载,请注明出处:https://www.biaojianku.com/archives/1379.html