oracle 11g 审计测试

1.开启审计（db_Extended方式）
alter system set audit_trail=db_extended scope=spfile;
shutdown immediate
startup 

2.查看是否开启审计
SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      C:/APP/ADMINISTRATOR/ADMIN/ORC
                                                 L/ADUMP
audit_sys_operations                 boolean     FALSE
audit_trail                          string      DB_EXTENDED

3.创建测试用户a
create user a identified by a account unlock;
grant connect,resource to a;

4.查看当前库中存在的权限审计
SQL> 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

已选择23行。
可以看出，在开启审计后，系统会自动对所有用户的某些权限成功或者失败执行都会进行审计

5.为了方便查看测试结果，先把aud$截断
truncate table sys.aud$

6.在hr用户下创建几张测试表
SQL> create table hr.t1 as select * from hr.employees;
SQL> create table hr.t2 as select * from hr.employees;
SQL> create table hr.t3 as select * from hr.employees;
SQL> create table hr.t4 as select * from hr.employees;
SQL> create table hr.t5 as select * from hr.employees;



6.此时，使用a帐号登录数据库，并查看当前a用户有哪些系统权限，并测试drop any table的审计方式
sqlplus a/a
SQL> select * from user_sys_privs;

USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
A                              UNLIMITED TABLESPACE                     NO

SQL> select * from role_sys_privs;

ROLE                           PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
RESOURCE                       CREATE SEQUENCE                          NO
RESOURCE                       CREATE TRIGGER                           NO
RESOURCE                       CREATE CLUSTER                           NO
RESOURCE                       CREATE PROCEDURE                         NO
RESOURCE                       CREATE TYPE                              NO
CONNECT                        CREATE SESSION                           NO
RESOURCE                       CREATE OPERATOR                          NO
RESOURCE                       CREATE TABLE                             NO
RESOURCE                       CREATE INDEXTYPE                         NO

此时，我们尝试drop table hr.t1,因为a用户并没有drop any table的权限，所以结果肯定会失败，
SQL> show user
USER 为 "A"
SQL> drop table hr.t1;
drop table hr.t1
              *
第 1 行出现错误:
ORA-00942: 表或视图不存在

7.此时，使用sys帐号查看dba_audit_Trail表，发现没有审计条目
select username,sql_text from dba_audit_trail where username='A';

8.我们用sys用户给a帐号授予drop any table的系统权限。
grant drop any table to a;

9.此时，在使用a帐号drop table hr.t1,
SQL> drop table hr.t1;

表已删除。

10.此时，用sys帐号查看dba_audit_trail视图，会发现有记录。
SQL> select username,sql_text from dba_audit_trail where username='A';

USERNAME             SQL_TEXT
-------------------- --------------------------------------------------
A                    drop table hr.t1


结论，
对于系统权限的审计，则用户必须有对应的权限，才会被审计，否则不审计？那如何理解对权限的不成功审计呢？
对于a用户来讲，当没有drop any table权限时，他执行drop table hr.t1是不是应该作为权限使用失败而被审计呢？
所以，对whenever not SUCCESSFUL这个子句不太理解，
请大神点拨点拨

感谢大神们，百忙之中，帮我理解这个是什么意思。

版权声明：本文为博主原创文章，未经博主允许不得转载。

不解