这篇文章主要简单的讨论一下Oracle的审计功能，包括粗粒度审计、细粒度审计(FGA)，另外还将引用一篇文章讨论Oracle默认的SYS用户审计和Oracle 11g Database默认开启的审计功能。
一.粗粒度审计功能。
1.审计级别：
语句级审计(stmt)：表示只审计某种类型的SQL语句，不指定结构或对象。
权限级审计(privs)：表示只审计执行相应动作的系统权限的使用情况。
实体级审计(obj)：表示只对指定模式上的实体指定语句的审计。
根据用户语句的执行结果，审计语句分为成功语句的审计(Whenever Successful)、不成功语句的审计(Whenever NOT Successful)以及无论成功与否都进行审计(默认情况)。
根据用户语句的执行次数，审计又分为对某一用户或全体用户的会话(By Session)审计、对某一用户或全体用户存取方式(By Access)的审计。
某一用户或全体用户的会话审计表示用户语句每执行一次就审计一次；某一用户或全体用户存取方式审计表示相同语句执行多次时，每执行一次就审计一次。
当数据库的审计功能被启动后，在用户语句执行阶段，系统会自动产生审计信息。审计信息中包括审计的操作、用户执行的操作、操作日期、操作时间等信息。
2.初始化参数：
audit_trail=none|false|db|true|os
none,false效果相同，db,true效果相同，os将结果存放到audit_dump_file参数对应的目录下。
audit_sys_operations=false|true 是否启动对sysdba,sysoper身份的用户的审计。
3.语法：
audit stmt opts|privs opts|obj opts by users by session|access whenever [not] successful;
4.审计类型：
语句级审计
语句级审计表示只审计某种类型的SQL语句。可以审计某个用户，也可以审计所有用户的SQL语句。语句级审计的语法如下：
AUDIT SQL语句选项 [by 用户名] [by session|access] [whenever [NOT] successful;
语句选项
被审计的语句
CLUSTER
Create Cluster、Audit Cluster、Drop
Cluster、Truncate
Cluster
DATABASE LINK
Create Database Link、Drop Database Link
DIRECTORY
Create Directory、Drop Directory
INDEX
Create Index、Alter
Index、Drop
Index
PROCEDURE
Create Function、Create Library、Create
Package、Create
Package Body、Create
Procedure、Drop
Function、Drop
Library、Drop
Package
PROFILE
Create Profile、Alter Profile、Drop
Profile
PUBLIC SYNONYM
Create Public Synonym、Drop Public Synonym
ROLE
Create Role、Alter
Role、Drop
Role、Set
Role
ROLLBACK SEGMENT
Create Rollback Segment、Alter Rollback Segment、Drop
Rollback Segment
SEQUENCE
Create Sequence、Drop Sequence
SESSION
Connect、Disconnect
SYNONYM
Create Synonym、Drop Synonym
SYSTEM AUDIT
Audit、Noaudit
SYSTEM GRANT
Grant、Revoke
TABLE
Create Table、Drop
Table、Truncate
Table
TABLESPACE
Create Tablespace、Alter Tablespace、Drop
Tablespace
TRIGGER
Create Trigger、Alter Trigger
USERS
Create User、Alter
User、Drop
User
TYPE
Create Type、Create
Type Body、Alter
Type、Drop
Type、Drop
Type Body
VIEW
Create View、Drop
View
ALTER SEQUENCE
Alter Sequence
ALTER TABLE
Alter Table
DELETE TABLE
Delete from tables、views
EXECUTE
Execute Function、Library、Package
GRANT SEQUENCE
Grant privilege On sequence、Revoke privilege On sequence
GRANT TABLE
Grant privilege on table、Revoke privilege on table
UPDATE TABLE
Lock Table
在使用时，不需要写出全部的SQL语句，只要写出语句的选项即可。
“by 用户名”表示只审计指定用户的SQL语句，不审计其他用户。没有指出用户名时，则审计全体用户。
audit table by scott; 表示scott用户在执行Create Table、Drop Table、Truncate Table操作时将被审计。
audit table; 表示所有用户执行该类型的语句都被审计。
“by session”表示按会话方式审计，在每个会话中，相同的语句只审计一次。这是系统默认的方式。
“by access”表示按存取方式审计，每一次语句都将审计。
“whenever successful”表示只审计成功语句。
“whenever not successful”表示只审计不成功语句。
audit table by scott by access;(审计scott用户每一次对表的CREATE、DROP、Truncate操作)。
audit table by scott by session;(审计scott用户对表的CREATE、DROP、Truncate操作，相同的操作只记录一次)。
audit session by tax02 by session whenever not successful;(审计tax02用户尝试连接数据库，但不成功的信息，相同的操作只记录一次)。
audit session by tax01,tax02;(审计tax01，tax02用户尝试连接数据库，成功的信息，相同的操作只记录一次)。
audit session whenever not successful;(审计尝试连接数据库，但不成功的信息)。
如果要了解对于哪些用户都进行了语句级审计及审计的选项，可以查询数据字典DBA_STMT_AUDIT_OPTS，该数据字典要以sys用户连接数据库查询。
当不再对用户进行审计时，可以使用noaudit命令，把用户进行的审计取消。
noaudit SQL语句或选项 [by 用户名] [by session|access] [Whenever [NOT] Successful];
权限级审计
权限级审计表示只审计某一个系统权限的使用情况。可以审计某个用户所使用的系统权限，也可以审计所有用户使用的系统权限。权限级审计的语法如下：
Audit 权限名称 [by 用户名] [by session|Access] [Whenever [NOT] Successful];
例如：
audit delete any table whenever not successful;(审计所有用户不成功的DELETE ANY TABLE权限使用情况)。
audit create table whenever not successful;(审计所有用户不成功的CREATE TABLE权限使用情况)。
audit alter any table,alter any procedure by scott by access whenever not successful;
audit create user by tax02 whenever not successful;
如果要了解对哪些用户进行了权限级审计及审计选项，可以查询数据字典DBA_PRIV_AUDIT_OPTS，该数据字典必须以sys用户连接数据库进行查询。
当不再对用户的系统权限进行审计时，可以使用noaudit命令取消对用户所进行的审计。取消用户权限审计的命令如下：
noaudit 权限名称 [by 用户名] [by session|access] [whenever [NOT] Successful];
例如：
noaudit alter any table,alter any procedure by scott by access whenever not successful;
noaudit create user by tax02 whenever not successful;
noaudit create table whenever not successful;
审计停止后，用户所进行的操作将不再记录。
实体级审计
实体审计用于监视所有用户对某一指定用户的表的存取状况。实体级审计是不分审计对象的，数据库管理员关心的重点是哪些用户操作某一个指定用户的表。实体级审计的语法如下：
audit 实体选项 on schema.实体名称 [by session | access] [Whenever [NOT] Successful];
实体级审计中的实体选项及对实体操作的语句
TABLE
VIEW
SEQUENCE
PROCEDURE
SNAPSHOTS
ALTER
√
√
DELETE
√
√
EXECUTE
√
INDEX
√
INSERT
√
√
REFERENCES
√
SELECT
√
√
√
√
UPDATE
√
√
例如：
audit delete on scott.emp by access whenever successful;(审计所有用户对scott.emp表所有成功的DELETE操作，每次操作都会记录)
audit delete on scott.dept by access whenever not successful;(审计所有用户对scott.emp表所有不成功的DELETE操作，每次操作都会记录)
audit select on sys.tab;(审计所有用户对sys.tab表的SELECT操作，相同的操作只会记录一次)
audit update on scott.dept;
audit update,delete on hr.employees by access whenever successful;
audit update,delete on hr.employees by user;(对user用户对hr.employees的update,delete进行审计)
如果要了解对哪些用户的实体进行了实体级审计及审计选项，可以查询数据字典DBA_OBJ_AUDIT_OPTS，该数据字典必须以sys用户连接数据库进行查询。例如对于用户scott所进行的实体级审计信息，可以使用以下命令：
select object_name,object_type,alt,aud,com,del,gra,ind,ins,loc,ren,sel,upd,ref,exe,cre,rea,wri from dba_obj_audit_opts where owner='SCOTT';
“-”表示没有设置该选项的审计。
“S”表示使用by session选项进行审计。
“A”表示使用by access选项进行审计。
“/”表示使用过whenever successful、whenever not successful选项值。
使用noaudit命令取消对用户实体所进行的审计。
例如：
noaudit delete on scott.emp by access whenever successful;
noaudit delete on scott.dept by access whenever not successful;
5.查看审计结果：
dba_audit_trail;
DBA_AUDIT_TRAIL displays all standard audit trail entries.
dba_audit_session;
DBA_AUDIT_SESSIONdisplays all audit trail records concerningCONNECTandDISCONNECT.
dba_audit_object;
DBA_AUDIT_OBJECTdisplays audit trail records for all objects in
the database.
6.查看创建了哪些审计：
dba_obj_audit_opts;(实体级审计)
dba_priv_audit_opts;(权限级审计)
dba_stmt_audit_opts;(语句级审计)
7.取消审计(将原有的audit语句的audit换成noaudit执行即可)：
noaudit all;
noaudit all privileges;
noaudit update,delete on table_name [by username];
8.删除审计记录：
delete from sys.aud$ where timestamp#
二.细粒度审计(FGA)功能。
细粒度的审计使用DBMS_FGA包完成配置和管理工作。细粒度审计除了审计功能外，还可用于绑定变量值的捕获，下面简单讨论一下DBMS_FGA包的使用：
SQL> desc dbms_fga
PROCEDURE ADD_POLICY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_SCHEMA VARCHAR2 IN DEFAULT
OBJECT_NAME VARCHAR2 IN
POLICY_NAME VARCHAR2 IN
AUDIT_CONDITION VARCHAR2 IN DEFAULT
AUDIT_COLUMN VARCHAR2 IN DEFAULT
HANDLER_SCHEMA VARCHAR2 IN DEFAULT
HANDLER_MODULE VARCHAR2 IN DEFAULT
ENABLE BOOLEAN IN DEFAULT
STATEMENT_TYPES VARCHAR2 IN DEFAULT
AUDIT_TRAIL BINARY_INTEGER IN DEFAULT
AUDIT_COLUMN_OPTS BINARY_INTEGER IN DEFAULT
PROCEDURE DISABLE_POLICY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_SCHEMA VARCHAR2 IN DEFAULT
OBJECT_NAME VARCHAR2 IN
POLICY_NAME VARCHAR2 IN
PROCEDURE DROP_POLICY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_SCHEMA VARCHAR2 IN DEFAULT
OBJECT_NAME VARCHAR2 IN
POLICY_NAME VARCHAR2 IN
PROCEDURE ENABLE_POLICY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_SCHEMA VARCHAR2 IN DEFAULT
OBJECT_NAME VARCHAR2 IN
POLICY_NAME VARCHAR2 IN
ENABLE BOOLEAN IN DEFAULT
从上面的结构很容易看出，DBMS_FGA包主要包括ADD_POLICY，ENABLE_POLICY，DISABLE_POLICY，和DROP_POLICY这4个存储过程。其中ADD_POLICY是最常用也是最DBMS_FGA包中最复杂的过程，参考下面的内容顺序ADD_POLICY存储过程中参数的含义：
Table 40-2 ADD_POLICY Procedure Parameters
Parameter
Description
Default Value
object_schema
The schema of the object to be audited. (If NULL, the current log-on user schema is assumed.)
NULL
object_name
The name of the object to be audited.
-
policy_name
The unique name of the policy.
-
audit_condition
A condition in a row that indicates a monitoring condition. NULL is allowed and acts as TRUE.
NULL
audit_column
The columns to be checked for access. These can include hidden columns. The default, NULL, causes audit if any column is accessed or affected.
NULL
handler_schema
The schema that contains the event handler. The default, NULL, causes the current schema to be used.
NULL
handler_module
The function name of the event handler; includes the package name if
necessary. This function is invoked only after the first row that
matches the audit condition in the query is processed. If the procedure
fails with an exception, the user SQL statement will fail as well.
NULL
enable
Enables the policy if TRUE, which is the default.
TRUE
statement_types
The SQL statement types to which this policy is applicable: INSERT, UPDATE, DELETE, or SELECT only.
SELECT
audit_trail
Destination (DB or XML) of fine grained audit records. Also specifies whether to populate LSQLTEXT and LSQLBIND in fga_log$.<<<< 要想捕获SQL语句和绑定变量值需要设置DBMS_FGA.EXTENDED，默认值即包含该设置，参数设置示例请参考下面的使用注意事项。
DB+EXTENDED
audit_column_opts
Establishes whether a statement is audited when the query referencesanycolumn specified in the audit_column parameter or only whenallsuch columns are referenced. <<<< 值有DBMS_FGA.ALL_COLUMNS和DBMS_FGA.ANY_COLUMNS，表示audit_column设置中是满足所有字段捕获还是满足一个字段捕获。
ANY_COLUMNS
使用过程应该注意以下内容：
Usage Notes
If object_schema is not specified, the current log-on user schema is assumed.
An FGA policy should not be applied to out-of-line columns such as LOB columns.
Each audit policy is applied to the query individually. However, at
most one audit record may be generated for each policy, no matter how
many rows being returned satisfy that policy's audit_condition.
In other words, whenever any number of rows being returned satisfy an
audit condition defined on the table, a single audit record will be
generated for each such policy.
If a table with an FGA policy defined on it receives a Fast Path
insert or a vectored update, the hint is automatically disabled before
any such operations. Disabling the hint allows auditing to occur
according to the policy's terms. (One example of a Fast Path insert is
the statement INSERT-WITH-APPEND-hint.)
The audit_condition must be a boolean expression that
can be evaluated using the values in the row being inserted, updated, or
deleted. This condition can be NULL (or omitted), which is interpreted
as TRUE, but it cannot contain the following elements:
Subqueries or sequences
Any direct use of SYSDATE, UID, USER or USERENV functions. However, a user-defined function and other SQL functions can use these functions to return the desired information.
Any use of the pseudo columns LEVEL, PRIOR, or ROWNUM.
Specifying an audit condition of "1=1" to force auditing of all specified statements ("statement_types") affecting the specified column ("audit_column") is no longer needed to achieve this purpose. NULL will cause audit even if no rows were processed, so that all actions on a table with this policy are audited.
The audit function (handler_module) is an alerting mechanism for the administrator. The required interface for such a function is as follows:
PROCEDURE ( object_schema VARCHAR2, object_name VARCHAR2, policy_name VARCHAR2 ) AS ...
where fname is the name of the procedure, object_schema is the name of the schema of the table audited, object_name is the name of the table to be audited, and policy_name is the name of the policy being enforced. The audit function will be executed with the function owner's privilege.
The audit_trail parameter specifies both where the
fine-grained audit trail will be written and whether it is to include
the query's SQL Text and SQL Bind variable information (typically in
columns named LSQLTEXT and LSQLBIND):
If audit_trail includes XML, then fine-grained audit records are
written to XML-format operating system files stored in the directory
specified by an AUDIT_FILE_DEST statement in SQL. (The default AUDIT_FILE_DEST is $ORACLE_BASE/admin/$DB_UNIQUE_NAME/adump on Unix-based systems, and $ORACLE_BASE\admin\$DB_UNIQUE_NAME\adump on Windows systems.)
If audit_trail includes DB instead, then the audit records are written to the SYS.FGA_LOG$ table in the database.
If audit_trail includes EXTENDED, then the query's SQL Text and SQL Bind variable information are included in the audit trail.
For example:
Setting audit_trail to DBMS_FGA.DB sends the audit trail to the SYS.FGA_LOG$ table in the database and omits SQL Text and SQL Bind.
Setting audit_trail to DBMS_FGA.DB + DBMS_FGA.EXTENDED sends the audit trail to the SYS.FGA_LOG$ table in the database and includes SQL Text and SQL Bind.
Setting audit_trail to DBMS_FGA.XML writes the audit trail in XML files sent to the operating system and omits SQL Text and SQL Bind.
Setting audit_trail to DBMS_FGA.XML + DBMS_FGA.EXTENDED writes the audit trail in XML files sent to the operating system and includes SQL Text and SQL Bind.
The audit_trail parameter appears in the ALL_AUDIT_POLICIES view.
You can change the operating system destination using the following command:
ALTER SYSTEM SET AUDIT_FILE_DEST = '' DEFERRED
On many platforms, XML audit files are named _.xml, for example, ora_2111.xml, or s002_11.xml.
On Windows, the XML audit files are named
_.xml (or
_ProcessId>.xml if the process is not running as a
thread).
The audit_column_opts parameter establishes whether a statement is audited
when the query referencesanycolumn specified in the audit_column parameter (audit_column_opts = DBMS_FGA.ANY_COLUMNS), or
only whenallsuch columns are referenced (audit_column_opts = DBMS_FGA.ALL_COLUMNS).
The default is DBMS_FGA.ANY_COLUMNS.
The ALL_AUDIT_POLICIES view also shows audit_column_opts.
When audit_column_opts is set to DBMS_FGA.ALL_COLUMNS, a SQL statement is audited only when all the columns mentioned in audit_column
have been explicitly referenced in the statement. And these columns
must be referenced in the same SQL-statement or in the sub-select.
Also, all these columns must refer to a single table/view or alias.
Thus, if a SQL statement selects the columns from different table aliases, the statement will not be audited.
下面介绍几个最常用的初始化参数和视图：
1).SYS.FGA_LOG$：如果audit_trail参数包含DB，那么审计记录会被记录在FGA_LOG$表中。
2).AUDIT_FILE_DEST初始化参数：设置审计操作系统文件的存放位置。
3).V$XML_AUDIT_TRAIL：如果audit_trail参数包含XML，那么审计记录会记录在AUDIT_FILE_DEST初始化参数指定的目的地下的XML文件中，Oracle会读取这些XML文件，生成V$XML_AUDIT_TRAIL动态性能视图，方便DBA查看审计详细信息。
4).DBA_AUDIT_POLICIES：详细记录了审计配置的策略信息。
5).DBA_FGA_AUDIT_TRAIL：查看到审计的SQL语句和绑定变量。
6).DBA_COMMON_AUDIT_TRAIL：包含V$XML_AUDIT_TRAIL动态性能视图的内容，是标准和细粒度审计记录。
注意：
启用细粒度的审计功能不需要设置数据库的AUDIT_TRAIL初始化参数，只需要设置DBMS_FGA.ADD_POLICY存储过程中的AUDIT_TRAIL参数或使用默认值即可。参考文章：《【实验】【审计】【FGA】使用Oracle的审计功能监控数据库中的可疑操作》：http://space.itpub.net/519536/viewspace-613323
三.Oracle数据对SYS用户的审计以及11g默认开启的审计功能。
参考文章：
《Oracle 11gR2 Database和ASM默认的审计策略和相关操作》：http://space.itpub.net/23135684/viewspace-723442
--end--
158
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
