舉例說明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