oracle 11g审计查看,oracle 11g 审计测试

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这个子句不太理解，

请大神点拨点拨

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

https://www.cndba.cn/tianxiadishi/article/2065

https://www.cndba.cn/tianxiadishi/article/2065

https://www.cndba.cn/tianxiadishi/article/2065

https://www.cndba.cn/tianxiadishi/article/2065

https://www.cndba.cn/tianxiadishi/article/2065

https://www.cndba.cn/tianxiadishi/article/2065

https://www.cndba.cn/tianxiadishi/article/2065

https://www.cndba.cn/tianxiadishi/article/2065

https://www.cndba.cn/tianxiadishi/article/2065

https://www.cndba.cn/tianxiadishi/article/2065

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

不解