[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'
[20191128]oracle Audit文件管理2.txt
-
lfree
2019-11-28 16:38:32 -
Oracle
- 原创