最近很多人咨询我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: 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./db/100757(复制到浏览器或者点击“阅读原文”立即查看) 范伟林,中国DBA联盟(ACDU)成员,拥有5年工作经验,涉及电力、金融、政府等行业,拥有OCM、DCA、OCBA的证书。
|