[20120810]linux使用syslog審計資料庫.txt

[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使用者登入,日誌檔案會增加很快,要仔細權衡利弊!