[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'