[20120810]linux使用syslog审计数据库.txt

linux下使用syslog记录系统的日志,是否可以使用它来记录oracle的一些日志呢?
今天看了一些文档,尝试看看.

SQL> select * from v$version ;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

SQL> show parameter audit

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------------------------------------

audit_file_dest                      string      /u01/app/oracle11g/admin/test/adump

audit_sys_operations                 boolean     FALSE

audit_syslog_level                   string

audit_trail                          string      DB

--要使用OS audit必须设置audit_sys_operations=true.
--audit_syslog_level级别我选择:LOCAL1.WARNING.

alter system set audit_sys_operations=true scope=spfile;

alter system set audit_syslog_level='LOCAL1.WARNING' scope=spfile;

SQL> alter system set audit_syslog_level='LOCAL1.WARNING' scope=both ;

alter system set audit_syslog_level='LOCAL1.WARNING' scope=both

                 *

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

--不支持scope=both,后面要重启oracle数据库!

2.修改服务器配置:

# touch /var/log/oracle_audit.log 

modify /etc/syslog.conf,append :

# vi /etc/syslog.conf

local1.warning /var/log/oracle_audit.log

# restar syslog server:

service syslog restart

3.重启数据库看看.
以sys用户执行如下:

SQL> show parameter audit

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------------------------------------

audit_file_dest                      string      /u01/app/oracle11g/admin/test/adump

audit_sys_operations                 boolean     TRUE

audit_syslog_level                   string      LOCAL1.WARNING

audit_trail                          string      DB

SQL> select * from scott.dept;

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

--查看/var/log/oracle_audit.log文件,发现如下:

Aug 10 17:35:04 XXX Oracle Audit[26274]: LENGTH : '446' ACTION :[289] 'SELECT NAME NAME_COL_PLUS_SHOW_PARAM,DECODE(TYPE,1,'boolean',2,'string',3,'integer',4,'file',5,'number', 6,'big integer', 'unknown') TYPE,DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM FROM V$PARAMETER WHERE UPPER(NAME) LIKE UPPER(:NMBIND_SHOW_OBJ) ORDER BY NAME_COL_PLUS_SHOW_PARAM, ROWNUM' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[9] 'oracle11g' CLIENT TERMINAL:[5] 'pts/2' STATUS:[1] '0' DBID:[10] '2071943378'

Aug 10 17:35:22 XXX Oracle Audit[26274]: LENGTH : '180' ACTION :[24] 'select * from scott.dept' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[9] 'oracle11g' CLIENT TERMINAL:[5] 'pts/2' STATUS:[1] '0' DBID:[10] '2071943378'

--使用sed格式化看看,这样好看一些.

# grep scott /var/log/oracle_audit.log | tail -1 | sed -e "s/' /'\n/g"

Aug 10 17:39:18 hisdg Oracle Audit[26539]: LENGTH : '180'

ACTION :[24] 'select * from scott.dept'

DATABASE USER:[1] '/'

PRIVILEGE :[6] 'SYSDBA'

CLIENT USER:[9] 'oracle11g'

CLIENT TERMINAL:[5] 'pts/2'

STATUS:[1] '0'

DBID:[10] '2071943378'

--注意一下,如果经常使用sys用户登录,日志文件会增加很快,要仔细权衡利弊!