首页 » Cloud, ORACLE 9i-23ai » Oracle Audit write syslog(数据库审计写系统日志)

Oracle Audit write syslog(数据库审计写系统日志)

  • 2021/08/27
  • Cloud, ORACLE 9i-23ai
  • 415 views
  • Oracle Audit write syslog(数据库审计写系统日志)已关闭评论

之前分享过如何把GoldenGate写入syslog的笔记 Oracle goldengate write syslog(ogg 写系统日志)，今天有同事问如何把audit 日志记录syslog, 简单研究一下，syslog是操作系统级协议，oracle数据库没有直接把如alert,listener、audit log写入syslog的机制，但是可以通过DB或OS层的配置把audit日志写入, audit sys的日志默认是记录在audit_file_dest路径下的，当然DBA有权可以查看编辑。有时安全审计等要求希望在root用户可以保留一份防止DBA(e.g. oracle user)编辑，可能配置把audit日志写入syslog,如linux的messages.

从12c开始引入了新的Unified Auditing（统一审计）, 开始支持2种审计模式：混合审计和纯统一审计, 混合模式是为了过渡到UA（统一审计），如要像之前的版本一样，只有混合模式时需要配置audit_trail参数，升级到纯UA后，原来的audit trail记录仍旧可以使用，你也可以归档或清除之前trail，清除audit trail 使用DBMS_AUDIT_MGMT。

  • 12c When using unified auditing, the audit records will be written to the unified audit trail and cannot be redirected to syslog. If you would like to redirect the audit records to syslog,then use Mixed Mode auditing.
  • Starting with 18c, you can write the unified audit trail records to SYSLOG or the Windows Event Viewer by setting the UNIFIED_AUDIT_SYSTEMLOG initialization parameter. (Doc ID 2623138.1)
  • unified audit entries to SYSLOG will not include imported columns due to SYSLOG size limit (Doc ID 2520613.1)

可见使用unified audit 从18c通过配置UNIFIED_AUDIT_SYSTEMLOG可以输出到syslog，但是一样会缺少一些重要信息，否则就还使用Mixed mode(混合)模式的审计。

下面是使用测试oracle 21c 混合模式记录audit到syslog.

SQL> select banner_full from v$version;

BANNER_FULL
------------------------------------------------------------------------------------
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0


SQL> alter system set audit_trail='OS' scope=spfile;
System altered.

SQL> alter system set audit_syslog_level = 'user.notice' scope=spfile;
System altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

## OEL 7.8

[root@oel7db1 log]# service rsyslog stop

[root@oel7db1 log]# service rsyslog start

[root@oel7db1 log]# service rsyslog status
Redirecting to /bin/systemctl status rsyslog.service
● rsyslog.service - System Logging Service
   Loaded: loaded (/usr/lib/systemd/system/rsyslog.service; enabled; vendor preset: enabled)
   Active: active (running) since Thu 2021-08-26 22:51:34 EDT; 13min ago
     Docs: man:rsyslogd(8)
           http://www.rsyslog.com/doc/
 Main PID: 5851 (rsyslogd)
   CGroup: /system.slice/rsyslog.service
           └─5851 /usr/sbin/rsyslogd -n

Aug 26 22:51:34 oel7db1 systemd[1]: Starting System Logging Service...
Aug 26 22:51:34 oel7db1 rsyslogd[5851]:  [origin software="rsyslogd" swVersion="8.24.0-38.el7" x-pid="5851" x-info="http://www.rsyslog.com"] start
Aug 26 22:51:34 oel7db1 systemd[1]: Started System Logging Service.

测试sys和system

SQL> startup
ORACLE instance started.

Total System Global Area 1543503024 bytes
Fixed Size                  9686192 bytes
Variable Size             956301312 bytes
Database Buffers          570425344 bytes
Redo Buffers                7090176 bytes
Database mounted.
Database opened.

SQL> show parameter audit

PARAMETER_NAME                                               TYPE        VALUE
------------------------------------------------------------ ----------- ----------------------------------------------------------------------------------------------------
audit_file_dest                                              string      /u01/app/oracle/admin/orcl/adump
audit_sys_operations                                         boolean     TRUE
audit_syslog_level                                           string      USER.NOTICE
audit_trail                                                  string      OS
unified_audit_common_systemlog                               string
unified_audit_systemlog                                      string


SQL> create user c##a identified by a;
User created.

SQL> drop user c##a;
User dropped.

SQL> conn system/oracle
Connected.

USERNAME             INST_NAME            HOST_NAME                  I# SID   SERIAL#  VERSION    STARTED  SPID       OPID  CPID            SADDR            PADDR
-------------------- -------------------- ------------------------- --- ----- -------- ---------- -------- ---------- ----- --------------- ---------------- ----------------
SYSTEM               CDB$ROOT-anbob21c    oel7db1                     1 1     55561    21.0.0.0.0 20210826 9757       43    9391            0000000073741858 00000000735910E0


SQL> create user c##a identified by a;
User created.

SQL> drop user c##a;
User dropped.

# tail -f /var/log/messages

Aug 26 23:36:25 oel7db1 journal: Oracle Audit[9658]: LENGTH : '281' ACTION :[32] 'create user c##a identified by *' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/1' STATUS:[1] '0' DBID:[10] '1608557415' SESSIONID:[10] '4294967295' USERHOST:[7] 'oel7db1' CLIENT ADDRESS:[0] '' ACTION NUMBER:[2] '51'
Aug 26 23:36:29 oel7db1 journal: Oracle Audit[9658]: LENGTH : '311' ACTION :[60] 'BEGIN dbms_cmp_int.drop_cmp_by_cmpid(:sb1, :sb2, :sb3); END;' DATABASE USER:[3] 'SYS' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/1' STATUS:[1] '0' DBID:[10] '1608557415' SESSIONID:[10] '4294967295' USERHOST:[7] 'oel7db1' CLIENT ADDRESS:[0] '' ACTION NUMBER:[2] '47'
Aug 26 23:36:29 oel7db1 journal: Oracle Audit[9658]: LENGTH : '263' ACTION :[14] 'drop user c##a' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/1' STATUS:[1] '0' DBID:[10] '1608557415' SESSIONID:[10] '4294967295' USERHOST:[7] 'oel7db1' CLIENT ADDRESS:[0] '' ACTION NUMBER:[2] '53'
Aug 26 23:37:26 oel7db1 journal: Oracle Audit[9757]: LENGTH : '307' ACTION :[60] 'BEGIN dbms_cmp_int.drop_cmp_by_cmpid(:sb1, :sb2, :sb3); END;' DATABASE USER:[6] 'SYSTEM' PRIVILEGE :[4] 'NONE' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/1' STATUS:[1] '0' DBID:[10] '1608557415' SESSIONID:[6] '170029' USERHOST:[7] 'oel7db1' CLIENT ADDRESS:[0] '' ACTION NUMBER:[2] '47'

记录了sys的创建、删除用户的操作，但是sys的查询一样会被审计，这样syslog的阅读性就差不容易解析，这点还是在数据库内部过滤方便。

下面是使用测试oracle 21c Unified Auditing记录audit到syslog.

SQL> select parameter , value from v$option where PARAMETER = 'Unified Auditing';

PARAMETER VALUE
------------------------- -------- ----------------------------------------------------------------
Unified Auditing FALSE

SQL> shut abort
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk uniaud_on ioracle

sqlplus / as sysdba
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1543503024 bytes
Fixed Size 9686192 bytes
Variable Size 973078528 bytes
Database Buffers 553648128 bytes
Redo Buffers 7090176 bytes
SQL> select parameter , value from v$option where PARAMETER = 'Unified Auditing';

PARAMETER                                                        VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Unified Auditing                                                 TRUE

SQL> alter system reset audit_syslog_level scope=spfile;
System altered.

SQL> alter system set UNIFIED_AUDIT_SYSTEMLOG='user.notice' scope=spfile;
System altered.

SQL> shut immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1543503024 bytes
Fixed Size                  9686192 bytes
Variable Size             973078528 bytes
Database Buffers          553648128 bytes
Redo Buffers                7090176 bytes
Database mounted.
Database opened.


SQL> show parameter audit

PARAMETER_NAME                                               TYPE        VALUE
------------------------------------------------------------ ----------- ----------------------------------------------------------------------------------------------------
audit_file_dest                                              string      /u01/app/oracle/admin/orcl/adump
audit_sys_operations                                         boolean     TRUE
audit_syslog_level                                           string
audit_trail                                                  string      OS
unified_audit_common_systemlog                               string
unified_audit_systemlog                                      string      USER.NOTICE

同样测试增删用户

sqlplus / as sysdba
SQL> create user c##b identified by b;
User created.

SQL> drop user c##b;
User dropped.

SQL> conn system/oracle
Connected.

SQL> create user c##b identified by b;
User created.

SQL> drop user c##b;
User dropped.

tail -f /var/log/messages

Aug 27 03:10:22 oel7db1 journal: Oracle Unified Audit[21418]: LENGTH: '193' TYPE:"4" DBID:"1608557415" SESID:"0" CLIENTID:"" ENTRYID:"5" STMTID:"15" DBUSER:"SYS" CURUSER:"SYS" ACTION:"51" RETCODE:"0" SCHEMA:"" OBJNAME:"C##B" PDB_GUID:"C8209F27C6B16005E053362EE80AE60E"
Aug 27 03:10:23 oel7db1 journal: Oracle Unified Audit[21418]: LENGTH: '193' TYPE:"4" DBID:"1608557415" SESID:"0" CLIENTID:"" ENTRYID:"6" STMTID:"16" DBUSER:"SYS" CURUSER:"SYS" ACTION:"53" RETCODE:"0" SCHEMA:"" OBJNAME:"C##B" PDB_GUID:"C8209F27C6B16005E053362EE80AE60E"
Aug 27 03:11:44 oel7db1 journal: Oracle Unified Audit[21885]: LENGTH: '208' TYPE:"4" DBID:"1608557415" SESID:"1182265899" CLIENTID:"" ENTRYID:"1" STMTID:"11" DBUSER:"SYSTEM" CURUSER:"SYSTEM" ACTION:"51" RETCODE:"0" SCHEMA:"" OBJNAME:"C##B" PDB_GUID:"C8209F27C6B16005E053362EE80AE60E"
Aug 27 03:11:48 oel7db1 journal: Oracle Unified Audit[21885]: LENGTH: '208' TYPE:"4" DBID:"1608557415" SESID:"1182265899" CLIENTID:"" ENTRYID:"2" STMTID:"12" DBUSER:"SYSTEM" CURUSER:"SYSTEM" ACTION:"53" RETCODE:"0" SCHEMA:"" OBJNAME:"C##B" PDB_GUID:"C8209F27C6B16005E053362EE80AE60E"

Note:
可见Unified Audit记录的信息比较少，比如OS_USERNAME， USERHOST， TERMINAL，SYSTEM_PRIVILEGE_USED， SQL_TEXT都是缺失的，Oracle表示这是一种预期形为。
— enjob —

打赏

,

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：