标准审计
审计是对选定的用户动作进行监控和记录，用来监视用户对oracle数据库所做的各种操作。
在语句执行阶段产生审计记录。审计记录包含有审计的时间、审计的对象，用户执行的操作，操作的结果等信息。审计记录可存在数据字典表（称为审计记录）或操作系统审计记录中。数据库审计记录均保存在AUD$表中。
SYS用户默认不被审计，除非AUDIT_SYS_OPERATIONS 参数置为true.审计结果存放位置由audit_file_dest参数决定。
Oracle安全审计分为标准审计，精细审计及强制审计。
启用标准审计
SQL> show parameter audit_trail;
NAME TYPE VALUE
----------------------------------------------- ------------------------------
audit_trail string NONE
SQL> alter system set audit_trail=dbscope=spfile;
System altered.
SQL> selectisses_modifiable,issys_modifiable,ismodified from v$parameter wherename='audit_trail';
ISSES ISSYS_MOD ISMODIFIED
----- --------- ----------
FALSE FALSE FALSE
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 549453824 bytes
Fixed Size 1274816 bytes
Variable Size 255855680 bytes
Database Buffers 289406976 bytes
Redo Buffers 2916352 bytes
Database mounted.
Database opened.
SQL> show parameter audit_trail;
NAME TYPE VALUE
----------------------------------------------- ------------------------------
audit_trail string DB
Audit_trail的值：
NONE:禁用审计（默认值）
DB审计记录存放在数据库中
OS审计记录存放在操作系统文件中，AUDIT_FILE_DEST初参指定审计文件存储的目录。
设置标准审计
Oracle支持三种标准审计类型：
语句（statement）审计：对某种类型的SQL语句审计，不指定结果或对象，会话连接也属于语句审计。
权限（privilege）审计：对执行相应动作的系统权限的使用审计。
对象（object）审计：对一特殊模式对象上的指定语句的审计。
Oracle支持的标准审计条件：
审计条件By user
By user对指定用户进行审计，默认对所有用户进行审计。
审计条件by session/byaccess
By session对每个session中发生的重复操作只记录一次。
By access对每个session中发生的每次操作都记录，而不管是否重复。
审计条件wheneversuccessful/whenever not successful
Whenever successful表示操作成功后才记录下来。
Whenever not successful 表示操作失败后才记录下来。
默认值两者都记录
SQL> audit session;
【记录每个会话连接（不论成功否）】
Audit succeeded.
SQL> audit create table by scott;
【记录指定用户的create table操作（不论成功否），注意：可单独对表的create,alter 操作进行审计，但若要审计drop操作则需要使用audit table命令。其同时包含有create table,drop table ,truncate table审计】
Audit succeeded.
SQL> audit insert any table by accesswhenever successful;
【记录在任何表上执行成功的每次insert操作，注意用户访问自己的表不会审计，访问他人表时将被审计（基本对象的审计除外）】
Audit succeeded.
SQL> audit select,delete on scott.emp byaccess whenever not successful;
【记录在指定对象上执行失败的每次select/delete操作 】
Audit succeeded.
SQL> select * from dba_stmt_audit_opts;
【查看所有有关于语句的审计选项设置，包括create /drop/truncate table等】
USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE
---------- ------------------------------ ------------------------------ ----------
CREATE SESSION BY ACCESS BY ACCESS
SCOTT CREATE TABLE BY ACCESS BY ACCESS
INSERT ANY TABLE BY ACCESS NOT SET
SQL> select * from dba_priv_audit_opts;
【查所有关于权限的审计选项设置，包括audit select /insert/delete/update any table 及grant select any table to a,用户访问自己的表不会被审计（基于对象的审计除外）】
USER_NAME PROXY PRIVILEGE SUCCESS FAILURE
---------- ----- --------------- ---------- ----------
CREATE SESSION BY ACCESS BY ACCESS
SCOTT CREATE TABLE BY ACCESS BY ACCESS
INSERT ANY TABL BY ACCESS NOT SET
E
SQL> select * from dba_obj_audit_opts;【查看所有对象上的审计设置，只审计on关键字指定对象上发生的相关操作】
OWNER OBJECT_NAME OBJECT_TYPE ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI FBK
------------------------------ ----------------------------------------------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ---------- --- ----- ----- ----- ----- -----
SCOTT EMP TABLE -/- -/- -/- -/A -/- -/- -/- -/- -/- -/A -/- -/- -/- -/- -/- -/- -/-
The character “_”indicates that the audit option is not set.
The character “S”indicates that the audit option is set,BY SESSION.
The character “A”indicates that the audit option is set ,by access.
SQL> noaudit all;
【取消所有语句审计】
Noaudit succeeded.
SQL> noaudit all privileges;
【取消所有权限审计（不能针对用户）】
Noaudit succeeded.
SQL> noaudit all privileges by scott;
【取消所有权限审计（针对用户）】
Noaudit succeeded.
SQL> noaudit all on scott.emp;
【取消对象审计（针对用户）】
Noaudit succeeded.
查询标准审计结果
SQL> select * from sys.aud$;
【列出审计记录（基表）】
no rows selected
SQL> select * from dba_audit_trail;
【列出所有审计跟踪条目（上表的视图）】
no rows selected
SQL> create or replace view yy_audit asselect substr(username,1,8) username,substr(userhost,1,10) host,
2 to_char(timestamp,'yyyy-mm-ddhh:mi:ss') logintime,
3 to_char(logoff_time,'yyyy-mm-ddhh:mi:ss') logofftime,
4 substr(obj_name,1,10)obj,action,returncode from dba_audit_trail;
【自定义审计视图】
View created.
SQL> select * from audit_actions;
【列出审计动作类型代码action的含义】
ACTION NAME
---------- ----------------------------
0 UNKNOWN
1 CREATE TABLE
2 INSERT
3 SELECT
4 CREATE CLUSTER
5 ALTER CLUSTER
6 UPDATE
7 DELETE
8 DROP CLUSTER
9 CREATE INDEX
10DROP INDEX
测试标准审计
[oracle@desktop241 ~]$ sqlplus sys/song assysdba
SQL*Plus: Release 10.2.0.5.0 - Productionon Thu Oct 18 11:36:13 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise EditionRelease 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Miningand Real Application Testing options
SQL> audit session;
【开启连结（语句）审计】
Audit succeeded.
SQL> select * from dba_stmt_audit_opts;
【查看语句审计设置】
USER_NAME PROXY_NAME
------------------------------------------------------------
AUDIT_OPTION SUCCESS FAILURE
-------------------------------------------------- ----------
CREATE SESSION BY ACCESS BY ACCESS
[oracle@desktop241 ~]$ sqlplus john/john
[oracle@desktop241 ~]$ sqlplus sys/song assysdba
[oracle@desktop241 ~]$ sqlplus john/john123
另开窗口分别以上述4个身份进行登录：
SQL> select * from yy_audit;
Sys以正确口令登录，然后查Yy—audit视图，可见连结未予记录；
Kitty以错误口令登录，再查，可见连接已记录
Kitty 以正确口令登录，再查,可见成功连接已记录，退出后
SQL> select * from yy_audit;
USERNAME HOST LOGINTIME LOGOFFTIME
---------------- --------------------------------------- -------------------
OBJ ACTION RETURNCODE
-------------------- ---------- ----------
JOHN desktop241 2012-10-18 11:39:36
100 1017
JOHN desktop241 2012-10-18 11:39:59
100 1045
SQL> select * from yy_audit;
【停止连接审计】
SQL> noaudit session;
Noaudit succeeded.
SQL> truncate table aud$;
【清空审计记录表】
Table truncated.
[oracle@desktop241 ~]$ sqlplus sys/song assysdba
SQL*Plus: Release 10.2.0.5.0 - Productionon Thu Oct 18 11:48:23 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise EditionRelease 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Miningand Real Application Testing options
SQL> audit create table by kitty;
【开启语句审计】
Audit succeeded.
SQL> select * from dba_stmt_audit_opts;
【查看语句审计设置】
USER_NAME PROXY_NAME
------------------------------------------------------------
AUDIT_OPTION SUCCESS FAILURE
-------------------------------------------------- ----------
KITTY
CREATE TABLE BY ACCESS BY ACCESS
[oracle@desktop241 ~]$ sqlplus sys/song assysdba
SQL*Plus: Release 10.2.0.5.0 - Productionon Thu Oct 18 11:50:21 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise EditionRelease 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Miningand Real Application Testing options
SQL> create table t1 (id number);
Table created.
SQL> create table t1 (id number);
create table t1 (id number)
*
ERROR at line 1:
ORA-00955: name is already used by anexisting object
【SYS用户登录后执行两次建表操作（前次成功后次失败），再查yy_audit视图，可见语句未予记录】
SQL> select * from yy_audit;
no rows selected
[oracle@desktop241 ~]$ sqlpluskitty/kitty123
SQL*Plus: Release 10.2.0.5.0 - Productionon Thu Oct 18 11:53:28 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise EditionRelease 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Miningand Real Application Testing options
SQL> create table t2(id number);
Table created.
SQL> create table t2(id number);
create table t2(id number)
*
ERROR at line 1:
ORA-00955: name is already used by anexisting object
【Kitty用户登录后执行两次建表操作（前次成功后次失败），再查yy_audit视图，可见两条语句（不论成功否）已经记录】
SQL> select * from yy_audit;
USERNAME HOST LOGINTIME LOGOFFTIME
---------------- --------------------------------------- -------------------
OBJ ACTION RETURNCODE
-------------------- ---------- ----------
KITTY desktop241 2012-10-18 11:53:55
T2 1 0
KITTY desktop241 2012-10-18 11:53:56
T2 1 955
[oracle@desktop241 ~]$ sqlplus scott/tiger
SQL*Plus: Release 10.2.0.5.0 - Productionon Thu Oct 18 11:58:05 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise EditionRelease 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Miningand Real Application Testing options
SQL> create table t3(id number);
Table created.
SQL> create table t3(id number);
create table t3(id number)
*
ERROR at line 1:
ORA-00955: name is already used by anexisting object
SQL> select * from yy_audit;
USERNAME HOST LOGINTIME LOGOFFTIME
---------------- --------------------------------------- -------------------
OBJ ACTION RETURNCODE
-------------------- ---------- ----------
KITTY desktop241 2012-10-18 11:53:55
T2 1 0
KITTY desktop241 2012-10-18 11:53:56
T2 1 955
【scott用户登录后执行两次建表操作（前次成功后次失败），再查Yy_audit视图，可见语句未予记录】
SQL> noaudit create table by kitty;【停止语句审计】
Noaudit succeeded.
SQL> truncate table aud$;
【清空审计记录表】
Table truncated.
[oracle@desktop241 ~]$ sqlplus sys/song assysdba
SQL> audit insert any table by accesswhenever successful;
【开启权限审计】
Audit succeeded.
SQL> select * from dba_priv_audit_opts;
【查看权限审计设置】
USER_NAME PROXY_NAME
------------------------------------------------------------
PRIVILEGE SUCCESS FAILURE
-------------------------------------------------- ----------
INSERT ANY TABLE BY ACCESS NOTSET
[oracle@desktop241 ~]$ sqlplus sys/song assysdba
SQL> insert into kitty.t2 values (111);
1 row created.
SQL> insert into kitty.t2 values ("aaa");
insert into kitty.t2 values ("aaa")
*
ERROR at line 1:
ORA-00984: column not allowed here
SQL> grant connect,resource,insert anytable to john;
Grant succeeded.
【以sys用户登录后执行两次insert操作（前次成功后次失败），再查yy_audit视图，可见语句未予记录（sys用户不被审计）】
SQL> select * from yy_audit;
no rows selected
[oracle@desktop241 ~]$ sqlpluskitty/kitty123
SQL> insert into kitty.t2 values (222);
1 row created.
SQL> insert into kitty.t2 values ("bbb");
insert into kitty.t2 values ("bbb")
*
ERROR at line 1:
ORA-00984: column not allowed here
【Kitty 用户登录后执行两次insert操作（前次成功后次失败），再查yy_audit视图，可见语句未予记录（用户访问自己的表不被权限审计规则进行审计）】
[oracle@desktop241 ~]$ sqlplus john/john123
SQL> insert into kitty.t2 values (333);
1 row created.
SQL> insert into kitty.t2 values("ccc");
insert into kitty.t2 values("ccc")
*
ERROR at line 1:
ORA-00984: column not allowed here
【john用户登录后对他人所属基表执行两insert操作（前次成功后才失败），再查yy_audit视图，可见前一条成功语句已经记录，而后一条失败语句未记录】
SQL> select * from yy_audit;
USERNAME HOST LOGINTIME LOGOFFTIME
---------------- --------------------------------------- -------------------
OBJ ACTION RETURNCODE
-------------------- ---------- ----------
JOHN desktop241 2012-10-18 03:52:54
T2 2 0
SQL> noaudit insert any table;
【停止权限审计】
Noaudit succeeded.
SQL> truncate table aud$;
【清空审计记录表】
Table truncated.
[oracle@desktop241 ~]$ sqlplus sys/song assysdba
SQL> audit select,delete on kitty.t2 byaccess whenever not successful;
【开启对象审计】
Audit succeeded.
SQL> select * from dba_obj_audit_opts;
【查看对象审计设置】
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------------------------------------ -----------------
ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE
----- ----- ----- ----- ----- ----- ---------- ----- ----- ----- --- -----
CRE REA WRI FBK
----- ----- ----- -----
KITTY T2 TABLE
-/- -/- -/- -/A -/- -/- -/- -/- -/- -/A -/- -/- -/-
-/- -/- -/- -/-
[oracle@desktop241 ~]$ sqlplus sys/song assysdba
SQL> delete from kitty.t2 where id=111;
1 row deleted.
SQL> delete from kitty.t2 where id=kkk;
delete from kitty.t2 where id=kkk
*
ERROR at line 1:
ORA-00904: "KKK": invalididentifier
【以sys用户登录后执行两次delete操作（前次成功后次失败），再查yy_audit视图，可见语句未予记录】
SQL> select * from yy_audit;
no rows selected
[oracle@desktop241 ~]$ sqlpluskitty/kitty123【kitty用户登录后执行测试操作】
SQL> select * from t2 where id=222;
【查询成功，未记录】
ID
----------
222
SQL> delete from t2 where id=222;
【删除成功，查未记录】
1 row deleted.
SQL> select * from t2 where id=kkk;
select * from t2 where id=kkk【查询失败，查yy_audit已记录】
*
ERROR at line 1:
ORA-00904: "KKK": invalididentifier
SQL> delete from t2 where id=kkk;
delete from t2 where id=kkk
*【删除失败，查yy_audit已记录】
ERROR at line 1:
ORA-00903: invalid table name
SQL> select * from t0l
2 ;【查询失败，未记录（因非指定对象）】
select * from t0l
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> delete from table t0;
delete from table t0
*【查询失败，未记录（因非指定对象）】
ERROR at line 1:
ORA-00903: invalid table name
SQL> select * from yy_audit;
USERNAME HOST LOGINTIME LOGOFFTIME
---------------- --------------------------------------- -------------------
OBJ ACTION RETURNCODE
-------------------- ---------- ----------
KITTY desktop241 2012-10-18 04:07:05
T2 3 904
KITTY desktop241 2012-10-18 04:09:36
T2 7 904
[oracle@desktop241 ~]$ sqlplus john/john123
SQL> delete from kitty.t2 where id=333;
delete from kitty.t2 where id=333
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> select * from yy_audit;
USERNAME HOST LOGINTIME LOGOFFTIME
---------------- --------------------------------------- -------------------
OBJ ACTION RETURNCODE
-------------------- ---------- ----------
KITTY desktop241 2012-10-18 04:07:05
T2 3 904
KITTY desktop241 2012-10-18 04:09:36
T2 7 904
JOHN desktop241 2012-10-18 04:14:51
T2 7 2004
USERNAME HOST LOGINTIME LOGOFFTIME
---------------- --------------------------------------- -------------------
OBJ ACTION RETURNCODE
-------------------- ---------- ----------
JOHN desktop241 2012-10-18 04:15:11
T2 3 2004
JOHN desktop241 2012-10-18 04:16:28
T2 7 2004
SCOTT desktop241 2012-10-18 04:17:45
T2 7 2004
6 rows selected.
【john 用户登录后执行两次delete操作（前次成功后次失败），再查yy_audit视图，可见语句未记录】
SQL> select * from audit_actions;
ACTION NAME
---------- ----------------------------
0 UNKNOWN
1 CREATE TABLE
2 INSERT
3 SELECT
4 CREATE CLUSTER
5 ALTER CLUSTER
6 UPDATE
7 DELETE
8 DROP CLUSTER
9 CREATE INDEX
10DROP INDEX
ACTION NAME
---------- ----------------------------
11ALTER INDEX
12 DROP TABLE
13CREATE SEQUENCE
14ALTER SEQUENCE
15ALTER TABLE
16DROP SEQUENCE
17GRANT OBJECT
18REVOKE OBJECT
19CREATE SYNONYM
20DROP SYNONYM
21CREATE VIEW
ACTION NAME
---------- ----------------------------
22DROP VIEW
23VALIDATE INDEX
24CREATE PROCEDURE
25ALTER PROCEDURE
26LOCK
27NO-OP
28RENAME
29COMMENT
30AUDIT OBJECT
31NOAUDIT OBJECT
32CREATE DATABASE LINK
ACTION NAME
---------- ----------------------------
33DROP DATABASE LINK
34CREATE DATABASE
35ALTER DATABASE
36CREATE ROLLBACK SEG
37ALTER ROLLBACK SEG
38DROP ROLLBACK SEG
39CREATE TABLESPACE
40ALTER TABLESPACE
41DROP TABLESPACE
42ALTER SESSION
43ALTER USER
ACTION NAME
---------- ----------------------------
44 COMMIT
45ROLLBACK
46SAVEPOINT
47PL/SQL EXECUTE
48SET TRANSACTION
49ALTER SYSTEM
50EXPLAIN
51CREATE USER
52CREATE ROLE
53DROP USER
54DROP ROLE
ACTION NAME
---------- ----------------------------
55SET ROLE
56CREATE SCHEMA
57CREATE CONTROL FILE
59CREATE TRIGGER
60ALTER TRIGGER
61DROP TRIGGER
62ANALYZE TABLE
63ANALYZE INDEX
64ANALYZE CLUSTER
65CREATE PROFILE
66DROP PROFILE
ACTION NAME
---------- ----------------------------
67ALTER PROFILE
68DROP PROCEDURE
70ALTER RESOURCE COST
71CREATE MATERIALIZED VIEW LOG
72ALTER MATERIALIZED VIEW LOG
73DROP MATERIALIZED VIEW LOG
74CREATE MATERIALIZED VIEW
75ALTER MATERIALIZED VIEW
76DROP MATERIALIZED VIEW
77CREATE TYPE
78DROP TYPE
ACTION NAME
---------- ----------------------------
79ALTER ROLE
80ALTER TYPE
81CREATE TYPE BODY
82ALTER TYPE BODY
83DROP TYPE BODY
84DROP LIBRARY
85TRUNCATE TABLE
86TRUNCATE CLUSTER
91CREATE FUNCTION
92ALTER FUNCTION
93DROP FUNCTION
ACTION NAME
---------- ----------------------------
94CREATE PACKAGE
95ALTER PACKAGE
96DROP PACKAGE
97CREATE PACKAGE BODY
98ALTER PACKAGE BODY
99DROP PACKAGE BODY
100 LOGON
101 LOGOFF
102 LOGOFF BY CLEANUP
103 SESSION REC
104 SYSTEM AUDIT
ACTION NAME
---------- ----------------------------
105 SYSTEM NOAUDIT
106 AUDIT DEFAULT
107 NOAUDIT DEFAULT
108 SYSTEM GRANT
109 SYSTEM REVOKE
110 CREATE PUBLIC SYNONYM
111 DROP PUBLIC SYNONYM
112 CREATE PUBLIC DATABASE LINK
113 DROP PUBLIC DATABASE LINK
114 GRANT ROLE
115 REVOKE ROLE
ACTION NAME
---------- ----------------------------
116 EXECUTE PROCEDURE
117 USER COMMENT
118 ENABLE TRIGGER
119 DISABLE TRIGGER
120 ENABLE ALL TRIGGERS
121 DISABLE ALL TRIGGERS
122 NETWORK ERROR
123 EXECUTE TYPE
128 FLASHBACK
129 CREATE SESSION
157 CREATE DIRECTORY
ACTION NAME
---------- ----------------------------
158 DROP DIRECTORY
159 CREATE LIBRARY
160 CREATE JAVA
161 ALTER JAVA
162 DROP JAVA
163 CREATE OPERATOR
164 CREATE INDEXTYPE
165 DROP INDEXTYPE
167 DROP OPERATOR
168 ASSOCIATE STATISTICS
169 DISASSOCIATE STATISTICS
ACTION NAME
---------- ----------------------------
170 CALL METHOD
171 CREATE SUMMARY
172 ALTER SUMMARY
173 DROP SUMMARY
174 CREATE DIMENSION
175 ALTER DIMENSION
176 DROP DIMENSION
177 CREATE CONTEXT
178 DROP CONTEXT
179 ALTER OUTLINE
180 CREATE OUTLINE
ACTION NAME
---------- ----------------------------
181 DROP OUTLINE
182 UPDATE INDEXES
183 ALTER OPERATOR
197 PURGE USER_RECYCLEBIN
198 PURGE DBA_RECYCLEBIN
199 PURGE TABLESAPCE
200 PURGE TABLE
201 PURGE INDEX
202 UNDROP OBJECT
204 FLASHBACK DATABASE
205 FLASHBACK TABLE
ACTION NAME
---------- ----------------------------
206 CREATE RESTORE POINT
207 DROP RESTORE POINT
208 PROXY AUTHENTICATION ONLY
209 DECLARE REWRITE EQUIVALENCE
210 ALTER REWRITE EQUIVALENCE
211 DROP REWRITE EQUIVALENCE
160 rows selected.
扫一扫
4368
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
