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这个子句不太理解,
请大神点拨点拨
感谢大神们,百忙之中,帮我理解这个是什么意思。