[20191128]oracle Audit檔案管理2.txt

[20191128]oracle Audit檔案管理2.txt

--//以前的測試,http://blog.itpub.net/267265/viewspace-2646161/ => [20190530]oracle Audit檔案管理.txt
--//今天我檢查發現exadata的asm例項配置的是:
SQL> show parameter audit
NAME                 TYPE        VALUE
-------------------- ----------- ------------------------------
audit_file_dest      string      /u01/app/11.2.0.4/grid/rdbms/audit
audit_sys_operations boolean     FALSE
audit_syslog_level   string      LOCAL0.INFO

--//exadate oracle的實施人員修改引數audit_syslog_level指向了LOCAL0.INFO.不過audit_sys_operations=false
--//而且實施人員並沒有定義LOCAL0.INFO在/etc/syslog.conf檔案中(有一些系統使用rsyslog代替syslog)

# grep -i local0 /etc/syslog.conf
# grep -i 'local0.info' /etc/rsyslog.conf
--//兩者都無顯示.順便說一下我們使用的是rsyslog服務.

# service syslog status
syslogd is stopped
klogd is stopped
#  service rsyslog status
rsyslogd (pid  116746) is running...

--//感覺oracle的實施人員有點丟臉.沒注意細節....
--//補充測試修改這些引數是否需要重啟資料庫,以及其它一些細節問題.

1.環境:
SYS@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- ----------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.測試1:
--//修改引數是否需要重啟.
SYS@book> show parameter audit
NAME                 TYPE    VALUE
-------------------- ------- --------------------------------
audit_file_dest      string  /u01/app/oracle/admin/book/adump
audit_sys_operations boolean TRUE
audit_syslog_level   string  LOCAL0.INFO
audit_trail          string  DB, EXTENDED

# grep "local0" /etc/syslog.conf
local0.info                     /var/log/oracleaudit.log

SYS@book> alter system set audit_sys_operations=false ;
alter system set audit_sys_operations=false
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SYS@book> alter system set audit_syslog_level=LOCAL1.INFO;
alter system set audit_syslog_level=LOCAL1.INFO
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

--//不行!!
--//audit_sys_operations,audit_syslog_level都不能線上修改.

3.測試2:
--//如果audit_sys_operations=false,audit_syslog_level=LOCAL0.INFO會怎樣?

SYS@book> alter system set audit_sys_operations=false scope=spfile;
System altered.

--//重啟資料庫.
--//可以發現登入審計依舊記錄在/var/log/oracleaudit.log,但是執行的命令不記錄在/var/log/oracleaudit.log檔案中.
# tail -f  /var/log/oracleaudit.log

--//執行如下可以發現tail -f沒有輸出.
SYS@book> show sga
Total System Global Area  643084288 bytes
Fixed Size                  2255872 bytes
Variable Size             205521920 bytes
Database Buffers          427819008 bytes
Redo Buffers                7487488 bytes

4.測試3:
SYS@book> alter system set audit_sys_operations=true scope=spfile;
System altered.

SYS@book> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@book> startup
ORACLE instance started.
Total System Global Area  643084288 bytes
Fixed Size                  2255872 bytes
Variable Size             205521920 bytes
Database Buffers          427819008 bytes
Redo Buffers                7487488 bytes
Database mounted.
Database opened.

--//另外注意一點不管何種方式模式,啟動的時候在目錄/u01/app/oracle/admin/book/adump都會有記錄.也就是還是有點東西記錄在這個
--//目錄.不過不會很多,除非你經常重啟asm例項.

$ ls -ltr | grep 2019-11-28
-rw-r----- 1 oracle oinstall 770 2019-11-28 15:22:15 book_ora_28379_20191128152215303883143795.aud
-rw-r----- 1 oracle oinstall 770 2019-11-28 15:28:26 book_ora_28615_20191128152826802446143795.aud
-rw-r----- 1 oracle oinstall 770 2019-11-28 15:34:17 book_ora_28726_20191128153417006021143795.aud

SYS@book> select sysdate from dual ;
SYSDATE
-------------------
2019-11-28 15:35:32

# tail -f  /var/log/oracleaudit.log
Nov 28 15:34:23 xxxxxxxx Oracle Audit[28777]: LENGTH : '160' ACTION :[7] 'CONNECT' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[6] 'pts/11' STATUS:[1] '0' DBID:[10] '1337401710'
Nov 28 15:34:25 xxxxxxxx Oracle Audit[28777]: LENGTH : '173' ACTION :[19] 'ALTER DATABASE OPEN' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[6] 'pts/11' STATUS:[1] '0' DBID:[10] '1337401710'
Nov 28 15:35:32 xxxxxxxx Oracle Audit[28777]: LENGTH : '179' ACTION :[25] 'select sysdate from dual ' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[6] 'pts/11' STATUS:[1] '0' DBID:[10] '1337401710'
--//最後1條記錄記錄執行select sysdate from dual命令.

5.測試4:
--//註解如下,也就是exadate遇到的情況:
# grep "local0" /etc/syslog.conf
#local0.info                     /var/log/oracleaudit.log

--//重啟syslog服務.
# service syslog restart
Shutting down kernel logger:  [  OK  ]
Shutting down system logger:  [  OK  ]
Starting system logger:       [  OK  ]
Starting kernel logger:       [  OK  ]

SYS@book> select sysdate from dual ;
SYSDATE
-------------------
2019-11-28 15:39:48

# tail -f  /var/log/oracleaudit.log
--//沒有輸出.這種情況僅僅記錄登入的審計.
--//以sys使用者登入後檢查:

$ ls -ltr | grep 2019-11-28
-rw-r----- 1 oracle oinstall 770 2019-11-28 15:22:15 book_ora_28379_20191128152215303883143795.aud
-rw-r----- 1 oracle oinstall 770 2019-11-28 15:28:26 book_ora_28615_20191128152826802446143795.aud
-rw-r----- 1 oracle oinstall 770 2019-11-28 15:34:17 book_ora_28726_20191128153417006021143795.aud
--//在/u01/app/oracle/admin/book/adump目錄下不產生審計檔案.
--//也就是這樣的情況會出現丟失審計的情況!!!

6.測試5:
--//測試audit_syslog_level引數大小寫混合輸入會是什麼情況?
SYS@book> alter system set audit_syslog_level='Local0.info' scope=spfile ;
System altered.

SYS@book> show spparameter audit
SID      NAME                 TYPE    VALUE
-------- -------------------- ------- --------------------------------
*        audit_file_dest      string  /u01/app/oracle/admin/book/adump
*        audit_sys_operations boolean TRUE
*        audit_syslog_level   string  Local0.info
*        audit_trail          string  DB
*        audit_trail          string  EXTENDED

--//取消註解,注意後面的O我輸入的大寫.
# grep "local0" /etc/syslog.conf
local0.infO                     /var/log/oracleaudit.log

--//重啟syslog服務.
# service syslog restart
Shutting down kernel logger:  [  OK  ]
Shutting down system logger:  [  OK  ]
Starting system logger:       [  OK  ]
Starting kernel logger:       [  OK  ]

--//重啟資料庫:
SYS@book> show spparameter audit
SID      NAME                 TYPE     VALUE
-------- -------------------- -------- --------------------------------
*        audit_file_dest      string   /u01/app/oracle/admin/book/adump
*        audit_sys_operations boolean  TRUE
*        audit_syslog_level   string   Local0.info
*        audit_trail          string   DB
*        audit_trail          string   EXTENDED

SYS@book> show parameter audit
NAME                 TYPE    VALUE
-------------------- ------- --------------------------------
audit_file_dest      string  /u01/app/oracle/admin/book/adump
audit_sys_operations boolean TRUE
audit_syslog_level   string  LOCAL0.INFO
audit_trail          string  DB, EXTENDED
--//實際上啟動後audit_syslog_level定義是大寫.

SYS@book> show sga
Total System Global Area  643084288 bytes
Fixed Size                  2255872 bytes
Variable Size             205521920 bytes
Database Buffers          427819008 bytes
Redo Buffers                7487488 bytes

SYS@book> select Sysdate from dual;
SYSDATE
-------------------
2019-11-28 15:54:19

# tail -f  /var/log/oracleaudit.log
Nov 28 15:54:19 gxqyydg4 Oracle Audit[29236]: LENGTH : '178' ACTION :[24] 'select Sysdate from dual' DATABASE USER:[1]
'/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[6] 'pts/11' STATUS:[1] '0' DBID:[10] '1337401710'