Oracle标准数据库审核

Oracle标准数据库审核可以对一般用户（不包括SYS）的各种权限操作进行审核和跟踪。

一、标准数据库审核的基本方法

1、开启标准数据库审核

初始化参数audit_trail是一个静态参数，该参数确定如何启用审核，不同的值表示是否开启审核以及如何记录审核。

该参数可以设定为如下的值：

none或flase（10g为默认）：不审核；

db或true（11g为默认）：审核结果记录到数据库表sys.aud$，可以通过视图dba_audit_trail来查看结果；

os：审核结果记录到操作系统文件中，Unix在audit_file_dest参数中指定，Windows则在应用程序日志中（事件查看器eventvwr）；

db_extended：与db大致相同，但审核结果包含了具有绑定变量的SQL语句；

xml：与os大致相同，但使用xml来标记；

xml_extended：与xml大致相同，但审核结果包含了具有绑定变量的SQL语句。

2、指定审核选项

使用audit命令可以配置数据库审核，标准数据库审核包含以下几类：

1）系统权限审核

审核系统权限的操作，如

audit create any table;

audit create any trigger;

审核某用户的系统权限操作，如

audit select any table by scott;

访问自己的表时不会做审核。

审核用户的创建和删除

audit create user, drop user;

查开启的系统权限审核，通过数据字典dba_priv_audit_opts，11g默认会开启以下审核

col user_name for a20

col proxy_name for a20

col privilege for a30

col success for a20

col failure for a20

select * from dba_priv_audit_opts;

USER_NAME       PROXY_NAME      PRIVILEGE                                SUCCESS    FAILURE

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

                                CREATE EXTERNAL JOB                      BY ACCESS  BY ACCESS

                                CREATE ANY JOB                           BY ACCESS  BY ACCESS

                                GRANT ANY OBJECT PRIVILEGE               BY ACCESS  BY ACCESS

                                EXEMPT ACCESS POLICY                     BY ACCESS  BY ACCESS

                                CREATE ANY LIBRARY                       BY ACCESS  BY ACCESS

                                GRANT ANY PRIVILEGE                      BY ACCESS  BY ACCESS

                                DROP PROFILE                             BY ACCESS  BY ACCESS

                                ALTER PROFILE                            BY ACCESS  BY ACCESS

                                DROP ANY PROCEDURE                       BY ACCESS  BY ACCESS

                                ALTER ANY PROCEDURE                      BY ACCESS  BY ACCESS

                                CREATE ANY PROCEDURE                     BY ACCESS  BY ACCESS

                                ALTER DATABASE                           BY ACCESS  BY ACCESS

                                GRANT ANY ROLE                           BY ACCESS  BY ACCESS

                                CREATE PUBLIC DATABASE LINK              BY ACCESS  BY ACCESS

                                DROP ANY TABLE                           BY ACCESS  BY ACCESS

                                ALTER ANY TABLE                          BY ACCESS  BY ACCESS

                                CREATE ANY TABLE                         BY ACCESS  BY ACCESS

                                DROP USER                                BY ACCESS  BY ACCESS

                                ALTER USER                               BY ACCESS  BY ACCESS

                                CREATE USER                              BY ACCESS  BY ACCESS

                                CREATE SESSION                           BY ACCESS  BY ACCESS

                                AUDIT SYSTEM                             BY ACCESS  BY ACCESS

                                ALTER SYSTEM                             BY ACCESS  BY ACCESS

2）对象权限审核

对所有用户（不包括sys，sys是不审核的），如

aduit alter, delete, drop, insert on scott.emp;

对某个用户，如

audit select on hr.employees by scott;

对所有操作，如

audit all on hr.employees;

查开启的对象审核，通过数据字典dba_obj_audit_opts，默认是都没有开启

select * from dba_obj_audit_opts;

OWNER      OBJECT_NAME     OBJECT_TYPE     ALT   AUD   COM   DEL   GRA   IND   INS   LOC   REN   SEL   UPD   REF EXE   CRE   REA   WRI   FBK

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

3）语句审核

如审核表的所有DDL操作

audit table;

查开启的语句审核，通过数据字典dba_stmt_audit_opts，11g默认会开启以下审核，其中也包含了上述属于系统权限的审核

col user_name for a20

col proxy_name for a20

col audit_option for a30

col success for a20

col failure for a20

select * from dba_stmt_audit_opts;

USER_NAME       PROXY_NAME      AUDIT_OPTION                             SUCCESS    FAILURE

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

                                ALTER SYSTEM                             BY ACCESS  BY ACCESS

                                SYSTEM AUDIT                             BY ACCESS  BY ACCESS

                                CREATE SESSION                           BY ACCESS  BY ACCESS

                                CREATE USER                              BY ACCESS  BY ACCESS

                                ALTER USER                               BY ACCESS  BY ACCESS

                                DROP USER                                BY ACCESS  BY ACCESS

                                PUBLIC SYNONYM                           BY ACCESS  BY ACCESS

                                DATABASE LINK                            BY ACCESS  BY ACCESS

                                ROLE                                     BY ACCESS  BY ACCESS

                                PROFILE                                  BY ACCESS  BY ACCESS

                                CREATE ANY TABLE                         BY ACCESS  BY ACCESS

                                ALTER ANY TABLE                          BY ACCESS  BY ACCESS

                                DROP ANY TABLE                           BY ACCESS  BY ACCESS

                                CREATE PUBLIC DATABASE LINK              BY ACCESS  BY ACCESS

                                GRANT ANY ROLE                           BY ACCESS  BY ACCESS

                                SYSTEM GRANT                             BY ACCESS  BY ACCESS

                                ALTER DATABASE                           BY ACCESS  BY ACCESS

                                CREATE ANY PROCEDURE                     BY ACCESS  BY ACCESS

                                ALTER ANY PROCEDURE                      BY ACCESS  BY ACCESS

                                DROP ANY PROCEDURE                       BY ACCESS  BY ACCESS

                                ALTER PROFILE                            BY ACCESS  BY ACCESS

                                DROP PROFILE                             BY ACCESS  BY ACCESS

                                GRANT ANY PRIVILEGE                      BY ACCESS  BY ACCESS

                                CREATE ANY LIBRARY                       BY ACCESS  BY ACCESS

                                EXEMPT ACCESS POLICY                     BY ACCESS  BY ACCESS

                                GRANT ANY OBJECT PRIVILEGE               BY ACCESS  BY ACCESS

                                CREATE ANY JOB                           BY ACCESS  BY ACCESS

                                CREATE EXTERNAL JOB                      BY ACCESS  BY ACCESS

4）其它审核配置

审核会话登录

audit session;

这与审核create session权限的使用效果相同。

取消审核，通过noaudit命令指定

noaudit session;

noaudit all on scott.emp;

审核成功的操作，通过whenever successful选项指定，如审核表的成功插入

audit insert on scott.emp whenever successful;

审核不成功的操作，通过whenever not successful选项指定，如审核失败的会话登录

audit session whenever not successful;

默认情况下是审核所有的操作，不论成功与否。

会话级别上的审核，通过by session选项指定

audit update on scott.emp by session;

操作级别上的审核，通过by access选项指定

audit update on scott.emp by access;

对象权限审核默认是by session

系统权限审核默认是by access

3、查看审核记录

如果审核针对数据库（audit_trail=db或db_extended），则审核记录写入数据字典表sys.aud$中，虽然可以直接查看，但通过建立在其上的视图来查看将更加方便。

常用的视图是dba_audit_trail，其常用列的解释如下：

os_username：执行操作的用户的操作系统用户名

username：执行操作的用户的Oracle用户名

userhost：运行用户进程的计算机名称

timestamp：审核事件的发生时间

owner，obj_name：受影响对象的模式和名称

action，action_name：审核的操作，操作代码action的对照含义可查数据字典表audit_actions

priv_used：使用的系统权限

sql_text：执行的语句

如果没有表aud$及视图dba_audit_trail，则需要执行审核相关的数据字典表的安装脚本，安装后要重启数据库，安装脚本位于

%ORACLE_HOME%\rdbms\admin\cataudit.sql

其它审核视图显示了dba_audit_trail视图的一个子集：

dba_audit_object

dba_audit_statement

dba_audit_session

二、标准数据库审核实验

1、创建实验用表

create table scott.emp1 as select * from scott.emp;

grant all on scott.emp1 to hr;

2、启用审核

audit all on scott.emp1 by access;

audit table;

alter system set audit_sys_operations=true scope=spfile;

alter system set audit_trail='db_extended' scope=spfile;

重启数据库实例

shutdown immediate

startup

实验前可先清除审核结果表的所有记录

truncate table sys.aud$;

3、进行sysdba的活动，并查看审核结果

以sysdba身份登录并操作

select * from dba_users;

select * from scott.emp1;

create user audr1 identified by audr1;

drop user audr1;

create table scott.emp2 as select * from scott.emp1;

select * from scott.emp2;

drop table scott.emp2 purge;

查看sysdba的审核结果，由于开启了sysdba审核，所以可以在操作系统文件和日志中看到所有操作记录。Unix查看audit_file_dest指定的目标文件，Windows通过事件查看器eventvwr查看应用程序日志。另外可以看到SYS管理员的操作不会记入aud$中，视图dba_audit_trail没有相关记录。

4、用system用户登录操作，并查看审核结果

select * from scott.emp;

select * from scott.emp1;

create user audr1 identified by audr1;

grant connect to audr1;

create table audr1.t1(n number);

select * from audr1.t1;

drop table audr1.t1 purge;

drop user audr1;

退出system的登录

查看审核结果

col os_username for a20

col username for a20

col userhost for a20

col owner for a10

col obj_name for a20

col action_name for a20

col priv_used for a20

col sql_text for a50

select os_username, username, userhost, timestamp, owner, obj_name, action, action_name, priv_used, sql_text from dba_audit_trail order by timestamp desc;

OS_USERNAME          USERNAME             USERHOST             TIMESTAMP           OWNER      OBJ_NAME                 ACTION ACTION_NAME          PRIV_USED            SQL_TEXT

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

Administrator        SYSTEM               WORKGROUP\MYPC       2017-09-20 10:36:05                                101 LOGOFF

Administrator        SYSTEM               WORKGROUP\MYPC       2017-09-20 10:35:59            AUDR1                53 DROP USER            DROP USER            drop user audr1

Administrator        SYSTEM               WORKGROUP\MYPC       2017-09-20 10:35:49 AUDR1      T1                   12 DROP TABLE           DROP ANY TABLE       drop table audr1.t1 purge

Administrator        SYSTEM               WORKGROUP\MYPC       2017-09-20 10:35:32 AUDR1      T1                    1 CREATE TABLE         CREATE ANY TABLE     create table audr1.t1(n number)

Administrator        SYSTEM               WORKGROUP\MYPC       2017-09-20 10:35:24            CONNECT                     114 GRANT ROLE           GRANT ANY ROLE       grant connect to audr1

Administrator        SYSTEM               WORKGROUP\MYPC       2017-09-20 10:35:14            AUDR1                51 CREATE USER          CREATE USER          create user audr1 identified by *****

Administrator        SYSTEM               WORKGROUP\MYPC       2017-09-20 10:35:04 SCOTT      EMP1                  3 SELECT               SELECT ANY TABLE     select * from scott.emp1

Administrator        SYSTEM               WORKGROUP\MYPC       2017-09-20 10:34:33                                100 LOGON                CREATE SESSION

由于没有对表scott.emp加入审核，因此对它的查询未计入表中，而会话的登入登出、建表、删表、授权是默认开启的系统权限审核，因此这些操作被记入表中，同样对表audr1.t1的查询也不会加入审核。

5、清空aud$记录，在sys下创建用户audr1，并分别用audr1和hr用户登录操作，查看审核结果

sys的操作

create user audr1 identified by audr1;

grant connect to audr1;

audr1用户登录操作

select * from scott.emp1;

由于没有给audr1访问scott.emp1的权限，因此以上查询将失败

audr1用户退出登录

hr用户登录操作

select * from scott.emp1;

update scott.emp1 set sal=2000 where empno=7369;

commit;

update scott.emp1 set sal=2500 where empno=7369;

rollback;

hr用户退出登录

查看审核记录

col os_username for a20

col username for a20

col userhost for a20

col owner for a10

col obj_name for a20

col action_name for a20

col priv_used for a20

col sql_text for a50

select os_username, username, userhost, timestamp, owner, obj_name, action, action_name, priv_used, sql_text from dba_audit_trail order by timestamp desc;

OS_USERNAME          USERNAME             USERHOST             TIMESTAMP           OWNER      OBJ_NAME                 ACTION ACTION_NAME          PRIV_USED            SQL_TEXT

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

Administrator        HR                   WORKGROUP\MYPC       2017-09-20 10:42:29                                101 LOGOFF

Administrator        HR                   WORKGROUP\MYPC       2017-09-20 10:45:33 SCOTT      EMP1                  6 UPDATE                                    update scott.emp1 set sal=2500 where empno=7369

Administrator        HR                   WORKGROUP\MYPC       2017-09-20 10:42:17 SCOTT      EMP1                  6 UPDATE                                    update scott.emp1 set sal=2000 where empno=7369

Administrator        HR                   WORKGROUP\MYPC       2017-09-20 10:42:09 SCOTT      EMP1                  3 SELECT                                    select * from scott.emp1

Administrator        HR                   WORKGROUP\MYPC       2017-09-20 10:41:49                                100 LOGON                CREATE SESSION

Administrator        AUDR1                WORKGROUP\MYPC       2017-09-20 10:41:41                                101 LOGOFF

Administrator        AUDR1                WORKGROUP\MYPC       2017-09-20 10:41:29 SCOTT      EMP1                  3 SELECT                                    select * from scott.emp1

Administrator        AUDR1                WORKGROUP\MYPC       2017-09-20 10:41:01                                100 LOGON                CREATE SESSION

由于默认是操作不论成功与否都会纳入审核，因此audr1用户失败的查询也被记录，commit和rollback语句并没有记录，不管执行的语句最后是被提交还是回滚，更新操作总是被审核的。

6、取消对象审核

noaudit all on scott.emp1;

hr用户再次登录操作

select * from scott.emp1;

hr用户退出登录

查看审核记录，确认审核只有用户的登入登出，其它已取消。如果要将会话的登入登出记录也取消，则执行noaudit session，但这样一来，默认的系统权限审核将不再包括该项，除非执行audit session重新加入。

7、清空aud$记录，改为会话级别的审核，并查看结果

audit all on scott.emp1 by session;

再次以hr用户登录并操作

select * from scott.emp1;

update scott.emp1 set sal=800 where empno=7369;

commit;

hr用户退出登录

查看审核记录

col os_username for a20

col username for a20

col userhost for a20

col owner for a10

col obj_name for a20

col action_name for a20

col priv_used for a20

col sql_text for a50

select os_username, username, userhost, timestamp, owner, obj_name, action, action_name, priv_used, sql_text from dba_audit_trail order by timestamp desc;

OS_USERNAME          USERNAME             USERHOST             TIMESTAMP           OWNER      OBJ_NAME                 ACTION ACTION_NAME          PRIV_USED            SQL_TEXT

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

Administrator        HR                   WORKGROUP\MYPC       2017-09-20 10:54:55                                101 LOGOFF

Administrator        HR                   WORKGROUP\MYPC       2017-09-20 10:54:44 SCOTT      EMP1                103 SESSION REC                               update scott.emp1 set sal=800 where empno=7369

Administrator        HR                   WORKGROUP\MYPC       2017-09-20 10:54:36 SCOTT      EMP1                103 SESSION REC                               select * from scott.emp1

Administrator        HR                   WORKGROUP\MYPC       2017-09-20 10:54:29                                100 LOGON                CREATE SESSION

比较可知，操作级别的审核明确记录了action_name为select、update等，而会话级别的审核action_name只标明为session rec，但sql_text仍记录了会话中每一步操作的SQL语句。

8、取消审核，清理恢复

noaudit all on scott.emp1;

drop user audr1;

drop table scott.emp1 purge;

alter system set audit_trail=false scope=spfile;

alter system set audit_sys_operations=false scope=spfile;

重启数据库实例

清空aud$记录