dbms_fga使用

fga

fga的记录放在sys.fga_log$及dba_fga_audit_trail视图中，dba_common_audit_trail视图包含了标准的和fga的记录。

fga中的策略

使用策略，你可以指定列和你想审计的条件。

可扩展的使用事件处理功能的接口

可以定义fga策略来指定出发审计的事件，这些策略使用灵活的事件处理器通知管理员。比如让hr专员访问员工工资信息，当访问超过500k刀的记录时触发审计，审计策略（where salary>500000)被应用到employees表通过审计策略接口。

审计功能（handler_module)是一个告警机制，要求的接口如下：

PROCEDURE fname ( object_schema VARCHAR2, object_name VARCHAR2, policy_name VARCHAR2 )  AS ...

• 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

• policy_name is the name of the policy being enforced

定义fga策略

管理员使用dbms_fga.add_policy接口来定义fga策略为表或视图，包含select update,delete,或insert.oracle也支持merge语句。对merge语句来说，对每个策略来说只有一个记录产生。

例子

The following example shows how you can audit statements (INSERT, UPDATE, DELETE, and SELECT) on table hr.emp to monitor any query that accesses the salary column of the employee records that belong to sales department:

DBMS_FGA.ADD_POLICY(
object_schema => 'hr',
object_name   => 'emp',
policy_name   => 'chk_hr_emp',
audit_condition => 'dept = ''SALES'' ', 
audit_column => 'salary'
statement_types => 'insert,update,delete,select');

Then, any of the following SQL statements will cause the database to log an audit event record.

SELECT count(*) FROM hr.emp WHERE dept = 'SALES' and salary > 10000000;

SELECT salary FROM hr.emp WHERE dept = 'SALES';

DELETE from hr.emp where salary >1000000

With all the relevant information available, and a trigger-like mechanism to use, the administrator can define what to record and how to process the audit event.

Consider the following commands:

/* create audit event handler */
CREATE PROCEDURE sec.log_id (schema1 varchar2, table1 varchar2, policy1 varchar2) AS
BEGIN
UTIL_ALERT_PAGER(schema1, table1, policy1);      -- send an alert note to my pager
END;

/* add the policy */
DBMS_FGA.ADD_POLICY(
object_schema => 'hr',
object_name   => 'emp',
policy_name   => 'chk_hr_emp',
audit_condition => 'dept = ''SALES'' ', 
audit_column => 'salary',
handler_schema => 'sec',
handler_module => 'log_id',
enable               =>  TRUE);

第一记录被获取后，事件被记录，sec.log_id函数执行，事件被记录在dba_fga_audit_trail，sqlbind和sqltext只有在audit_trail=dbms_fga.db+dbms_fga.extended时才被记录在fga_log$中的lsqltext和lsqlbin列，如果audit_traiL被设置成dbms_fga.xml，审计信息写到xml文件中。

这个功能在cbo下面使用。可以指定dba_fga_audit_trail来分析sql语句和相应的绑定变量的值。这个包按下面4个方面来学习：

1add policy procedure

2disable policy procedure

3drop policy procedure

4enable policy procedure

1add_policy存储过程

DBMS_FGA.ADD_POLICY(
   object_schema   VARCHAR2, 
   object_name     VARCHAR2, 
   policy_name     VARCHAR2, 
   audit_condition VARCHAR2, 
   audit_column    VARCHAR2, 
   handler_schema  VARCHAR2, 
   handler_module  VARCHAR2, 
   enable          BOOLEAN, 
   statement_types VARCHAR2,
   audit_trail     BINARY_INTEGER IN DEFAULT,
   audit_column_opts BINARY_INTEGER IN DEFAULT);

Table 12-3 ADD_POLICY Procedure Parameters

Parameter	Description	Default Value
object_schema	The schema of the object to be audited. (IfNULL, then the current login 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 called only after the first row that matches the audit condition in the query is processed. If the procedure fails with an exception, then the user SQL statement will fail as well.	NULL
enable	Whether the policy is to be enabled:TRUE means enable it.	TRUE
statement_types	The SQL statement types to which this policy is applicable:INSERT, UPDATE, DELETE, or SELECT only.	SELECT
audit_trail	Both where to write the fine-grained audit trail and whether or not to populateLSQLTEXT and LSQLBIND.	DB+EXTENDED
audit_column_opts	Whether a statement is audited when the query referencesany column specified in the audit_column parameter or only whenall such columns are referenced.	ANY_COLUMNS

例子 

DBMS_FGA.ADD_POLICY(object_schema => 'scott', object_name=>'emp', policy_name => 'mypolicy1', audit_condition => 'sal < 100', audit_column =>'comm, credit_card, expirn_date', handler_schema => NULL, handler_module => NULL, enable => TRUE, statement_types=> 'INSERT, UPDATE', audit_trail => DBMS_FGA.DB+DBMS_FGA.EXTENDED, audit_column_opts => DBMS_FGA.ALL_COLUMNS);

如果object_schema没有被指定，那么就是指当前登录的用户

fga不应该用在lob字段上

不管有多少行的满足审计条件的数据返回，每个策略只有一个审计纪律记录返回。

在一个有fga策略定义的表上，如果收到一个快速路径插入或是向量更新的请求，hint会自动失效。

audit_confition一定是一个布尔表达式可以用来评估插入，更新或删除的行的值。可以使null,null被当做true来对待，null会记录所有的操作。不能包含下面的元素

1子查询或sequences

2直接使用sysdate,uid,user或userenv函数，自定义函数和其他的sq函数返回正常的信息。

3伪劣level,prior或rownum

audit_trail参数指定了fga的记录会放在哪里同时也指明了sql文本和绑定变量信息是否被记录

1如果audit_trail包含xmL，那么记录会放在xml格式的操作系统文件中，存放的目录由audit_file_dest参数指定，默认情况下unix在$ORACLE_BASE/admin/$DB_UNIQUE_NAME/adump，windows是在$oracle_bash\admin\$db_unique_name\adump目录下

2如果audit_trail包含了db，那么记录是在sys.fga_log$表中

3如果audit_trail包含extended，那么sql文本和绑定变量信息都记录。

例子：

• For example:

  • Setting audit_trail to DBMS_FGA.DB sends the audit trail to theSYS.FGA_LOG$ table in the database and omits SQL Text and SQL Bind.

  • Setting audit_trail to DBMS_FGA.DB+EXTENDED sends the audit trail to theSYS.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+EXTENDED writes the audit trail in XML files sent to the operating system and includes SQL Text and SQL Bind.

audit_column_opts参数指定下面的情况是否记录

1当查询涉及别的列（audit_column_opts=dbms_fga.any_columns)

2当所有的列都被涉及（audit_column_opts=dbms_fga.all_columns)

可以通过查看动态性能视图v$xml_audit_trail视图来查看放在xml中的记录

disable_policy 过程

DBMS_FGA.DISABLE_POLICY(
   object_schema  VARCHAR2, 
   object_name    VARCHAR2, 
   policy_name    VARCHAR2 ); 

drop_policy过程

DBMS_FGA.DROP_POLICY(
   object_schema  VARCHAR2, 
   object_name    VARCHAR2, 
   policy_name    VARCHAR2 );

查看有哪些策略

select * from dba_audit_policies;