[20190530]oracle Audit文件管理.txt

最新推荐文章于 2024-01-04 19:05:25 发布

cuixun7780

最新推荐文章于 2024-01-04 19:05:25 发布

阅读量162

点赞数

[20190530]oracle Audit文件管理.txt

--//昨天听课,讲一些oracle相关安全的问题,对方提到audit file的管理,应该引入OS audit,这样目的是仅仅root查看audit信息.

--//增加一定安全性,并且对方提到原来的目录就没有任何记录.自己测试看看.

--//像我们生产系统这个目录简直是暴涨,使用一些我自己都不知道的监测软件,感觉每15秒就使用sys用户登录一次.

--//参考链接:https://www.cnblogs.com/lfree/p/10475829.html

--//自己在测试环境测试看看.

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

SYS@book> alter system set AUDIT_SYS_OPERATIONS=TRUE scope=spfile sid='*';

System altered.

SYS@book> alter system set AUDIT_SYSLOG_LEVEL='local0.info' scope=spfile sid='*';

System altered.

--//修改/etc/syslog.conf加入如下,注意如果使用rsyslog,修改/etc/rsyslog.conf文件.

# vi /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> 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.

2.检查:

SYS@book> show parameter audit_file_dest

NAME TYPE VALUE

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

audit_file_dest string /u01/app/oracle/admin/book/adump

$ rm -f /u01/app/oracle/admin/book/adump/*.aud */

$ ls -l /u01/app/oracle/admin/book/adump

total 0

--//以sys用户登录看看:

$ ls -l /u01/app/oracle/admin/book/adump

total 0

--//可以发现这样操作后根本不会在/u01/app/oracle/admin/book/adump目录建立文件.

$ ls -l /var/log/oracleaudit.log

-rw------- 1 root root 6894 2019-05-30 10:40:11 /var/log/oracleaudit.log

--//可以发现建立的/var/log/oracleaudit.log仅仅root用户可以查看.

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

--//查看/var/log/oracleaudit.log文件:

May 30 10:42:38 xxx Oracle Audit[38014]: LENGTH : '435' ACTION :[281] 'SELECT DECODE(null,'','Total System Global Area','') NAME_COL_PLUS_SHOW_SGA, SUM(VALUE), DECODE (null,'', 'bytes','') units_col_plus_show_sga FROM V$SGA UNION ALL SELECT NAME NAME_COL_PLUS_SHOW_SGA , VALUE, DECODE (null,'', 'bytes','') units_col_plus_show_sga FROM V$SGA' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/7' STATUS:[1] '0' DBID:[10] '1337401710'

--//可以发现执行的sql语句也有记录.才想起来以前也做过类似测试.

--//参考链接:http://blog.itpub.net/267265/viewspace-740683/=>[20120810]linux使用syslog审计数据库.txt

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

May 30 10:42:38 xxxx Oracle Audit[38014]: LENGTH : '435'

ACTION :[281] 'SELECT DECODE(null,'','Total System Global Area','') NAME_COL_PLUS_SHOW_SGA, SUM(VALUE), DECODE (null,'', 'bytes','') units_col_plus_show_sga FROM V$SGA UNION ALL SELECT NAME NAME_COL_PLUS_SHOW_SGA , VALUE, DECODE (null,'', 'bytes','') units_col_plus_show_sga FROM V$SGA'

DATABASE USER:[1] '/'

PRIVILEGE :[6] 'SYSDBA'

CLIENT USER:[6] 'oracle'

CLIENT TERMINAL:[5] 'pts/7'

STATUS:[1] '0'

DBID:[10] '1337401710'

--//语句格式化如下:

SELECT DECODE(null,'','Total System Global Area','') NAME_COL_PLUS_SHOW_SGA, SUM(VALUE), DECODE (null,'',

'bytes','') units_col_plus_show_sga

FROM V$SGA

UNION ALL

SELECT NAME NAME_COL_PLUS_SHOW_SGA, VALUE, DECODE (null,'', 'bytes','') units_col_plus_show_sga