oracle 细粒度审计
1 细粒度审计作用
语句审计、权限审计以及对象审计等标准审计: 只能发现访问了哪些对象以及由哪个用户访问，而不能知道用户访问了哪个对象中的哪些行哪些列。为此Oracle从9i开始加入了细粒度审计.
细粒度审计<使得审计变得更为关注某个方面，并且更为精确。可以在访问某些行和列时审计对表的访问，从个极大地减少了审计表的记录数量。
细粒度的审计使用DBMS_FGA包完成配置和管理工作。细粒度审计除了审计功能外，还可用于绑定变量值的捕获，下面简单讨论一下DBMS_FGA包的使用：
2 细粒度审计介绍
细粒度审计被称为FGA，有dbms_fga的pl/sql程序来实现。
dbms_fga包含了以下4个过程：
add_policy() 添加使用为此和审计列的审计策略。
drop_policy() 删除审计策略
disable_policy() 禁用审计策略，但保留与表或视图关联的策略。
enable_policy() 启用策略。
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
ParameterDescriptionDefault Value
object_schemaThe schema of the object to be audited. (If NULL, the current log-on user schema is assumed.)NULL
object_nameThe name of the object to be audited.-
policy_nameThe unique name of the policy.-
audit_conditionA condition in a row that indicates a monitoring condition. NULL is allowed and acts as TRUE.NULL
audit_columnThe 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_schemaThe schema that contains the event handler. The default, NULL, causes the current schema to be used.NULL
handler_moduleThe 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
enableEnables the policy if TRUE, which is the default.TRUE
statement_typesThe SQL statement types to which this policy is applicable: INSERT, UPDATE, DELETE, or SELECT only.SELECT
audit_trailDestination (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_optsEstablishes whether a statement is audited when the query references any
column specified in the audit_column parameter or only when all such 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
references any column specified in the audit_column parameter
(audit_column_opts = DBMS_FGA.ANY_COLUMNS), or
only
when all such 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.
3 常用视图
下面介绍几个最常用的初始化参数和视图：
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动态性能视图的内容，是标准和细粒度审计记录。
4 实施细粒度审计
按照需求创建细粒度审计名为weisi_audit的审计如：
SQL>
create user weisi identified by weisi;
用户已创建。
SQL>
grant connect,resource to weisi;
授权成功。
SQL>
grant dba to weisi;
授权成功。
SQL>
connect weisi/weisi
已连接。
SQL>
show user
USER 为 "WEISI"
SQL>
create table t(id int,name varchar(100));
表已创建。
SQL>
insert into t values(1,'wu');
已创建 1 行。
SQL>
insert into t values(25,'zhen');
已创建 1 行。
SQL>
commit;
提交完成。
Oracle SYS用户执行：
begin
dbms_fga.add_policy(
object_schema
=>'weisi',
object_name
=>'t',
policy_name
=>'weisi_audit',
audit_column
=>'id',
audit_condition
=>'id > 20');
end;
/
--begin
-- dbms_fga.add_policy(
-- object_schema => 'weisi', 对象模式指定为soctt 用户模式
-- object_name => 't',
对象为dept表
-- policy_name =>'weisi_audit', 策略名称
-- audit_column =>'id', 对应的列
--
audit_condition => 'id > 20'); 对应列的条件>20,否则不会被审计。
--end;
--/
select *
from dba_audit_policies;
select * from t
where id < 20;
select * from t
where id > 20;
查看细粒度审计信息记录：
select timestamp,policy_name,sql_text from dba_fga_audit_trail;
