对sys用户所有操作进行审计示例

最新推荐文章于 2021-04-11 03:30:54 发布

Yushan Bai

最新推荐文章于 2021-04-11 03:30:54 发布

阅读量2.5k

点赞数

分类专栏： ORACLE 用户/角色管理/审计

本文链接：https://blog.csdn.net/haibusuanyun/article/details/11212567

版权

ORACLE 用户/角色管理/审计专栏收录该内容

14 篇文章 0 订阅

订阅专栏

注意——一些输出为了节约篇幅，已经删除。比如查询后的提示---1 row created.等等

1.查看并修改相关的初始化参数

SYS@ bys001>show parameter audit_sys_opera
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_sys_operations                 boolean     FALSE
SYS@ bys001>alter system set audit_sys_operations = true scope=spfile;
SYS@ bys001>alter system set audit_syslog_level = 'user.notice' scope=spfile;
重启数据库，然后查询：
SYS@ bys001>show parameter audit
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/bys001/adump
audit_sys_operations                 boolean     TRUE
audit_syslog_level                   string      USER.NOTICE
audit_trail                          string      DB

2.使用root用户进行修改日志配置文件并重启：

在 /etc/syslog.conf 最下面增加下面的一句
user.notice     /var/log/oracle_dbms
重启日志服务
[root@oel-01 log]# /etc/rc.d/init.d/syslog restart
Shutting down kernel logger:                           [ OK ]
Shutting down system logger:                           [ OK ]
Starting system logger:                                [ OK ]
Starting kernel logger:                                [ OK ]
[root@oel-01 log]# pwd
/var/log
[root@oel-01 log]# ls |grep oracle_
oracle_dbms

3.执行DML及开关库操作时的日志

SYS执行查询的审计信息：
SYS@ bys001>select * from scott.emp;
[root@oel-01 log]# cat oracle_dbms
Jul 25 21:22:33 oel-01 Oracle Audit[7229]: LENGTH : '176' ACTION :[23] 'select * from scott.emp' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/2' STATUS:[1] '0' DBID:[10] '2041679290'
Jul 25 21:22:33 oel-01 Oracle Audit[7229]: LENGTH : '205' ACTION :[52] 'BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/2' STATUS:[1] '0' DBID:[10] '2041679290'

4.SYS执行删除并提交的审计信息：

[root@oel-01 log]# cat oracle_dbms
Jul 25 21:24:12 oel-01 Oracle Audit[7229]: LENGTH : '186' ACTION :[33] 'delete scott.emp where empno=1234' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/2' STATUS:[1] '0' DBID:[10] '2041679290'
Jul 25 21:24:12 oel-01 Oracle Audit[7229]: LENGTH : '205' ACTION :[52] 'BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/2' STATUS:[1] '0' DBID:[10] '2041679290'
Jul 25 21:24:21 oel-01 Oracle Audit[7229]: LENGTH : '158' ACTION :[6] 'commit' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/2' STATUS:[1] '0' DBID:[10] '2041679290'
Jul 25 21:24:21 oel-01 Oracle Audit[7229]: LENGTH : '205' ACTION :[52] 'BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/2' STATUS:[1] '0' DBID:[10] '2041679290'
##################################

5.SYS用户shutdown immediate;关库

[root@oel-01 log]# cat oracle_dbms
Jul 25 21:26:19 oel-01 Oracle Audit[7229]: LENGTH : '180' ACTION :[27] 'ALTER DATABASE CLOSE NORMAL' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/2' STATUS:[1] '0' DBID:[10] '2041679290'
Jul 25 21:26:29 oel-01 Oracle Audit[7229]: LENGTH : '165' ACTION :[23] 'ALTER DATABASE DISMOUNT'DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/2' STATUS:[1] '0' DBID:[0] ''
Jul 25 21:26:29 oel-01 Oracle Audit[7229]: LENGTH : '149' ACTION :[8] 'SHUTDOWN'DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/2' STATUS:[1] '0' DBID:[0] ''

###############################

6.SYS用户STARTUP打开库

[root@oel-01 log]# cat oracle_dbms
Jul 25 21:28:05 oel-01 Oracle Audit[7316]: LENGTH : '155' ACTION :[7] 'STARTUP'DATABASE USER:[1] '/' PRIVILEGE :[4] 'NONE' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[13] 'Not Available' STATUS:[1] '0' DBID:[0] ''
Jul 25 21:28:05 oel-01 Oracle Audit[7439]: LENGTH : '148' ACTION :[7] 'CONNECT' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/2' STATUS:[1] '0' DBID:[0] ''
Jul 25 21:28:05 oel-01 Oracle Audit[7439]: LENGTH : '424' 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/2' STATUS:[1] '0' DBID:[0] ''
Jul 25 21:28:11 oel-01 Oracle Audit[7439]: LENGTH : '175' ACTION :[22] 'ALTER DATABASE MOUNT'DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/2' STATUS:[1] '0' DBID:[10] '2041679290'
Jul 25 21:28:11 oel-01 Oracle Audit[7493]: LENGTH : '159' ACTION :[7] 'CONNECT' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/2' STATUS:[1] '0' DBID:[10] '2041679290'
Jul 25 21:28:13 oel-01 Oracle Audit[7493]: LENGTH : '172' ACTION :[19] 'ALTER DATABASE OPEN'DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'oracle' CLIENT TERMINAL:[5] 'pts/2' STATUS:[1] '0' DBID:[10] '2041679290'