举例说明Oracle数据库审计的用法

举例说明Oracle数据库审计的用法

本节是从ORACLE METALINK的DOC:167293.1翻译整理而来的。通过举例的方式来说明ORACLE审计的用法。

ORACLE的审计可以从语句级、对象级和权限级几个方面进行。同样的，SYSDBA和SYSOPER用户的行为也可以被审计（从ORACLE 9i Release 2，9.2.0.1开始，SYS用户可以通过设置AUDIT_SYS_OPERATIONS参数来进行审计）。

1. 对象级审计

可以被审计的对象包括表、视图、序列发生器、包、存储过程等。由于对象的依赖性问题，可能同一件事情可能会产生多条审计信息。比如说某个函数关联到某个视图，某个视图关联到某个表。

对象级审计只能针对整个数据库的用户而不能对于某个用户进行审计。要查看对象级审计具有哪些审计选项，可以查询ALL_DEF_AUDIT_OPTS视图。

SQL> connect system/manager

SQL> select * from all_def_audit_opts;

ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE

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

-/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/- -/-

下面的例子是对SCOTT.EMP进行审计：

SQL> connect system/manager

SQL> audit select on SCOTT.emp by session;

查看审计信息是否被记录了：

SQL> col owner format a7

SQL> col object_name format a7

SQL> select * from dba_obj_audit_opts

where wner='SCOTT' and OBJECT_NAME='EMP';

OWNER OBJECT_ OBJECT_TY ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE

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

SCOTT EMP TABLE -/- -/- -/- -/- -/- -/- -/- -/- -/- S/S -/- -/- -/-

以下的语句可以生成一些审计信息：

SQL> connect scott/tiger

SQL> select * from emp;

SQL> connect TEST/TEST

SQL> select * from scott.emp;

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> connect system/manager

SQL> select * from scott.emp;

审计结果

SQL> connect system/manager

SQL> col username format a8

SQL> col priv_used format 999

SQL> /

SQL>select username, priv_used, ses_actions from

dba_audit_object

where obj_name='EMP' and wner='SCOTT';

结果

USERNAME PRIV_USED SES_ACTIONS

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

SCOTT ---------S------

TEST

SYSTEM SELECT ANY ---------S------

2. 从权限级进行审计

所有的系统权限都可以进行审计。从SYSTEM_PRIVILEGE_MAP中可查询到所有的系统权限。如果你要对一个不属于该视图中的权限进行审计，会出现错误：

SQL> audit drop snapshot by access;

audit drop snapshot by access

*

ERROR at line 1:

ORA-00956: missing or invalid auditing option

SQL> connect system/manager

SQL> select * from system_privilege_map;

PRIVILEGE NAME

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

-3 ALTER SYSTEM

-4 AUDIT SYSTEM

-5 CREATE SESSION

-6 ALTER SESSION

-7 RESTRICTED SESSION

-10 CREATE TABLESPACE

-11 ALTER TABLESPACE

-12 MANAGE TABLESPACE

-13 DROP TABLESPACE

.....

-167 GRANT ANY PRIVILEGE

-172 CREATE SNAPSHOT

-173 CREATE ANY SNAPSHOT

-174 ALTER ANY SNAPSHOT

-175 DROP ANY SNAPSHOT

-194 WRITEDOWN DBLOW

-195 READUP DBHIGH

-196 WRITEUP DBHIGH

-197 WRITEDOWN

-198 READUP

-199 WRITEUP

下面的例子是对SCOTT和TEST帐号的CREATE TABLE进行审计：

SQL> connect system/manager

SQL> audit create table by scott, test;

上面的例子产生的结果是，以SCOTT和TEST这两个用户登录的会话，在创建表的时候，会在AUD$中产生审计记录。

通过下列查询我们可以确认审计是否起作用：

SQL> col user_name format a7

SQL> col privilege format a15

SQL> connect system/manager

SQL> select * from sys.dba_priv_audit_opts;

查询结果：

USER_NA PRIVILEGE SUCCESS FAILURE

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

SCOTT CREATE TABLE BY ACCESS BY ACCESS

SYSTEM CREATE TABLE BY ACCESS BY ACCESS

以下操作可以生成一些审计信息：

SQL> connect scott/tiger

SQL> create table t1 (c1 number);

create table t1 (c1 number)

*

ERROR at line 1:

ORA-00955: name is already used by an existing object

SQL> create table tsc (c1 number);

table created

SQL> connect t/tl

SQL> create table tsc (c1 number);

table created

SQL> connect test/test

SQL> create table scott.t1(c1 number);

=> create table scott.t1(c1 number)

ERROR at line 1:

ORA-00955: name is already used by an existing object

SQL> create table tsc (c1 number);

=> table created

审计结果如下：

SQL> connect system/manager

SQL> col username format a8

SQL> col priv_used format 999

SQL> select username, priv_used, ses_actions from dba_audit_object;

结果如下：

USERNAME PRIV_USED SES_ACTIONS

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

SCOTT CREATE TABLE

SCOTT CREATE TABLE

TEST CREATE TABLE

SQL> select action, action_name, username

from dba_audit_trail ;

ACTION ACTION_NAME USERNAME

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

1 CREATE TABLE SCOTT

1 CREATE TABLE SCOTT

1 CREATE TABLE TEST

3. 语句级审计

语句级审计可以捕获数据库中的DDL和DML。

所有的可以审计的语句都登记在STMT_AUDIT_OPTION_MAP中。

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

The statements taht can be adited can be seen from stmt_audit_option_map.

SQL> connect system/manager

SQL> select * from stmt_audit_option_map;

SQL> select * from stmt_audit_option_map;

OPTION# NAME PROPERTY

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

3 ALTER SYSTEM 0

4 SYSTEM AUDIT 0

5 CREATE SESSION 0

6 ALTER SESSION 0

7 RESTRICTED SESSION 0

8 TABLE 0

9 CLUSTER 0

10 CREATE TABLESPACE 0

11 ALTER TABLESPACE 0

12 MANAGE TABLESPACE 0

13 DROP TABLESPACE 0

OPTION# NAME PROPERTY

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

14 TABLESPACE 0

15 UNLIMITED TABLESPACE 0

16 USER 0

17 ROLLBACK SEGMENT 0

18 TYPE 0

19 INDEX 0

20 CREATE USER 0

21 BECOME USER 0

22 ALTER USER 0

23 DROP USER 0

24 SYNONYM 0

OPTION# NAME PROPERTY

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

25 PUBLIC SYNONYM 0

26 VIEW 0

27 SEQUENCE 0

28 DATABASE LINK 0

29 PUBLIC DATABASE LINK 0

30 CREATE ROLLBACK SEGMENT 0

31 ALTER ROLLBACK SEGMENT 0

32 DROP ROLLBACK SEGMENT 0

33 ROLE 0

34 DIMENSION 0

35 PROCEDURE 0

OPTION# NAME PROPERTY

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

36 TRIGGER 0

37 PROFILE 0

38 DIRECTORY 0

39 MATERIALIZED VIEW 0

40 CREATE TABLE 0

41 CREATE ANY TABLE 0

42 ALTER ANY TABLE 0

43 BACKUP ANY TABLE 0

44 DROP ANY TABLE 0

45 LOCK ANY TABLE 0

46 COMMENT ANY TABLE 0

OPTION# NAME PROPERTY

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

47 SELECT ANY TABLE 0

48 INSERT ANY TABLE 0

49 UPDATE ANY TABLE 0

50 DELETE ANY TABLE 0

60 CREATE CLUSTER 0

61 CREATE ANY CLUSTER 0

62 ALTER ANY CLUSTER 0

63 DROP ANY CLUSTER 0

71 CREATE ANY INDEX 0

72 ALTER ANY INDEX 0

73 DROP ANY INDEX 0

OPTION# NAME PROPERTY

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

80 CREATE SYNONYM 0

81 CREATE ANY SYNONYM 0

82 DROP ANY SYNONYM 0

83 SYSDBA 0

84 SYSOPER 0

85 CREATE PUBLIC SYNONYM 0

86 DROP PUBLIC SYNONYM 0

90 CREATE VIEW 0

91 CREATE ANY VIEW 0

92 DROP ANY VIEW 0

105 CREATE SEQUENCE 0

OPTION# NAME PROPERTY

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

106 CREATE ANY SEQUENCE 0

107 ALTER ANY SEQUENCE 0

108 DROP ANY SEQUENCE 0

109 SELECT ANY SEQUENCE 0

111 GRANT SEQUENCE 0

115 CREATE DATABASE LINK 0

120 CREATE PUBLIC DATABASE LINK 0

121 DROP PUBLIC DATABASE LINK 0

125 CREATE ROLE 0

126 DROP ANY ROLE 0

127 GRANT ANY ROLE 0

OPTION# NAME PROPERTY

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

128 ALTER ANY ROLE 0

130 AUDIT ANY 0

131 SYSTEM GRANT 0

135 ALTER DATABASE 0

138 FORCE TRANSACTION 0

139 FORCE ANY TRANSACTION 0

140 CREATE PROCEDURE 0

141 CREATE ANY PROCEDURE 0

142 ALTER ANY PROCEDURE 0

143 DROP ANY PROCEDURE 0

144 EXECUTE ANY PROCEDURE 0

OPTION# NAME PROPERTY

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

146 EXECUTE PROCEDURE 0

147 GRANT PROCEDURE 0

151 CREATE TRIGGER 0

152 CREATE ANY TRIGGER 0

153 ALTER ANY TRIGGER 0

154 DROP ANY TRIGGER 0

157 CREATE DIRECTORY 0

158 DROP DIRECTORY 0

160 CREATE PROFILE 0

161 ALTER PROFILE 0

162 DROP PROFILE 0

OPTION# NAME PROPERTY

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

163 ALTER RESOURCE COST 0

165 ANALYZE ANY 0

167 GRANT ANY PRIVILEGE 0

172 CREATE SNAPSHOT 0

173 CREATE ANY SNAPSHOT 0

174 ALTER ANY SNAPSHOT 0

175 DROP ANY SNAPSHOT 0

176 NETWORK 0

177 CREATE ANY DIRECTORY 0

178 DROP ANY DIRECTORY 0

179 GRANT DIRECTORY 0

OPTION# NAME PROPERTY

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

180 CREATE TYPE 0

181 CREATE ANY TYPE 0

182 ALTER ANY TYPE 0

183 DROP ANY TYPE 0

184 EXECUTE ANY TYPE 0

185 GRANT LIBRARY 0

187 GRANT TYPE 0

188 CREATE LIBRARY 0

189 CREATE ANY LIBRARY 0

190 ALTER ANY LIBRARY 0

191 DROP ANY LIBRARY 0

OPTION# NAME PROPERTY

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

192 EXECUTE ANY LIBRARY 0

193 EXECUTE LIBRARY 0

194 WRITEDOWN DBLOW 0

195 READUP DBHIGH 0

196 WRITEUP DBHIGH 0

197 WRITEDOWN 0

198 READUP 0

199 WRITEUP 0

210 QUERY REWRITE 0

211 GLOBAL QUERY REWRITE 0

214 CREATE DIMENSION 0

OPTION# NAME PROPERTY

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

215 CREATE ANY DIMENSION 0

216 ALTER ANY DIMENSION 0

217 DROP ANY DIMENSION 0

218 MANAGE ANY QUEUE 1

219 ENQUEUE ANY QUEUE 1

220 DEQUEUE ANY QUEUE 1

222 CREATE ANY CONTEXT 0

223 DROP ANY CONTEXT 0

224 CREATE ANY OUTLINE 0

225 ALTER ANY OUTLINE 0

226 DROP ANY OUTLINE 0

OPTION# NAME PROPERTY

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

77 NOT EXISTS 0

87 EXISTS 0

54 ALTER TABLE 0

57 LOCK TABLE 0

58 COMMENT TABLE 0

65 SELECT TABLE 0

66 INSERT TABLE 0

67 UPDATE TABLE 0

68 DELETE TABLE 0

69 GRANT TABLE 0

103 ALTER SEQUENCE 0

OPTION# NAME PROPERTY

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

104 SELECT SEQUENCE 0

221 CONTEXT 0

234 ON COMMIT REFRESH 0

235 EXEMPT ACCESS POLICY 0

236 RESUMABLE 0

237 SELECT ANY DICTIONARY 0

238 DEBUG CONNECT SESSION 0

239 DEBUG CONNECT USER 0

240 DEBUG CONNECT ANY 0

241 DEBUG ANY PROCEDURE 0

242 DEBUG PROCEDURE 0

OPTION# NAME PROPERTY

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

243 FLASHBACK ANY TABLE 0

244 GRANT ANY OBJECT PRIVILEGE 0

SQL> select * from stmt_audit_option_map where name like '%TABLE%';

OPTION# NAME PROPERTY

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

8 TABLE 0

10 CREATE TABLESPACE 0

11 ALTER TABLESPACE 0

12 MANAGE TABLESPACE 0

13 DROP TABLESPACE 0

14 TABLESPACE 0

15 UNLIMITED TABLESPACE 0

40 CREATE TABLE 0

41 CREATE ANY TABLE 0

42 ALTER ANY TABLE 0

43 BACKUP ANY TABLE 0

OPTION# NAME PROPERTY

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

44 DROP ANY TABLE 0

45 LOCK ANY TABLE 0

46 COMMENT ANY TABLE 0

47 SELECT ANY TABLE 0

48 INSERT ANY TABLE 0

49 UPDATE ANY TABLE 0

50 DELETE ANY TABLE 0

54 ALTER TABLE 0

57 LOCK TABLE 0

58 COMMENT TABLE 0

65 SELECT TABLE 0

OPTION# NAME PROPERTY

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

66 INSERT TABLE 0

67 UPDATE TABLE 0

68 DELETE TABLE 0

69 GRANT TABLE 0

243 FLASHBACK ANY TABLE 0

已选择27行。

下面的例子对SCOTT和SYSTEM审计TABLE类的访问:

SQL> connect system/manager

SQL> audit table by scott, system;

设置了审计后，可以通过下列查询确认审计是否设置成功：

SQL> col user_name format a8

SQL> col proxy_name format a6

SQL> col audit_option format a9

SQL> col privilege format a15

SQL> connect system/manager

SQL> select * from dba_stmt_audit_opts;

USER_NAM PROXY_ AUDIT_OPT SUCCESS FAILURE

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

SYSTEM TABLE BY ACCESS BY ACCESS

SCOTT TABLE BY ACCESS BY ACCESS

下面生成一些审计信息：

SQL> connect scott/tiger

SQL> create table t1 (c1 number);

create table t1 (c1 number)

*

ERROR at line 1:

ORA-00955: name is already used by an existing object

SQL> drop table t1;

=> table dropped

SQL> connect system/manager

SQL> create table scott.t1(c1 number);

=> table created

SQL> drop table no;

=> *

ERROR at line 1:

ORA-00942: table or view does not exist

Results of auditing:

SQL> connect system/manager

SQL> col username format a8

SQL> col priv_used format 999

SQL> select username, priv_used, ses_actions from dba_audit_object;

USERNAME PRIV_USED SES_ACTIONS

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

SCOTT CREATE TABLE

SCOTT CREATE TABLE

SYSTEM CREATE TABLE

SQL> select action, action_name, username

from dba_audit_trail ;

ACTION ACTION_NAME USERNAME

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

1 CREATE TABLE SCOTT

12 DROP TABLE SCOTT

1 CREATE TABLE SYSTEM

12 DROP TABLE SYSTEM

SQL> select username, priv_used, ses_actions from dba_audit_object;

USERNAME PRIV_USED SES_ACTIONS

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

SCOTT CREATE TABLE

SCOTT

SYSTEM CREATE ANY TABLE

SYSTEM

设置SELECT审计：

SQL> connect system/manager

SQL> audit select table by scott, system;

检查审计是否设置成功：

SQL> col user_name format a8

SQL> col proxy_name format a6

SQL> col audit_option format a13

SQL> col privilege format a15

SQL> connect system/manager

SQL> select * from dba_stmt_audit_opts;

USER_NAM PROXY_ AUDIT_OPTION SUCCESS FAILURE

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

SYSTEM SELECT TABLE BY SESSION BY SESSION

SCOTT SELECT TABLE BY SESSION BY SESSION

生成审计信息：

SQL> connect scott/tiger

SQL> delete from emp where ename='KING';

SQL> connect system/manager

SQL> insert into scott.emp (empno, ename) values (1, 'TEST');

SQL> select * from scott.emp;

SQL> connect system/manager

SQL> col username format a8

SQL> col priv_used format 999

SQL> select username, priv_used, ses_actions from dba_audit_object;

USERNAME PRIV_USED SES_ACTIONS

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

SCOTT ---------S------

SCOTT ---------S------

SYSTEM ---------S------

SYSTEM SELECT ANY TABLE --------S------

SYSTEM ---------S------

SYSTEM ---------S------

SYSTEM ---------S------

SCOTT ---------S------

SYSTEM ---------S------

SYSTEM ---------S------

SYSTEM ---------S------

SQL> connect system/manager

SQL> select action, action_name, username

from dba_audit_trail ;

ACTION ACTION_NAME USERNAME

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

103 SESSION REC SCOTT

103 SESSION REC SCOTT

103 SESSION REC SYSTEM

103 SESSION REC SYSTEM

103 SESSION REC SYSTEM

103 SESSION REC SYSTEM

103 SESSION REC SYSTEM

103 SESSION REC SCOTT

103 SESSION REC SYSTEM

103 SESSION REC SYSTEM

103 SESSION REC SYSTEM

103 SESSION REC SYSTEM

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17252115/viewspace-747043/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17252115/viewspace-747043/