Oracle FGA细粒度审计

迷南。 2022-08-10 11:42 224阅读 0赞

如果你想要审计表上，在某个时间，哪些人，操作哪些DML语句，用FGA是个不错的选择。

SQL> select \* from v$version;  
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production  
PL/SQL Release 11.2.0.1.0 - Production  
CORE    11.2.0.1.0      Production  
TNS for Linux: Version 11.2.0.1.0 - Production  
NLSRTL Version 11.2.0.1.0 - Production

SQL>drop table test purge;

SQL>create table test as select \* from dba\_objects;

SQL> exec DBMS\_FGA.ADD\_POLICY(object\_schema=>'LCAM\_TEST',object\_name=>'test',policy\_name=>'FGA\_1',enable=>TRUE,statement\_types=>'UPDATE,delete',audit\_trail=>DBMS\_FGA.DB+DBMS\_FGA.EXTENDED,audit\_column\_opts=>DBMS\_FGA.ANY\_COLUMNS);\--statement\_types可以设置select,insert,delete,update

SQL> col SQL\_TEXT format a80 
SQL> select SQL\_TEXT,TIMESTAMP from DBA\_FGA\_AUDIT\_TRAIL order by TIMESTAMP; 
 
SQL> update test set subobject\_name=object\_id where rownum=1; 
SQL> select SQL\_TEXT,TIMESTAMP from DBA\_FGA\_AUDIT\_TRAIL order by TIMESTAMP; 
update test set subobject\_name=object\_id where rownum=1                         01-12月-14 
 
SQL> update test set subobject\_name=object\_id where rownum<100;\--可以看到是按照SQL语句来审计的，不是按照行变动审计 
SQL> select SQL\_TEXT,TIMESTAMP from DBA\_FGA\_AUDIT\_TRAIL order by TIMESTAMP; 
update test set subobject\_name=object\_id where rownum<100                       01-12月-14 
update test set subobject\_name=object\_id where rownum=1                         01-12月-14 
 
SQL> delete from  sys.fga\_log$; 
SQL> commit; 
SQL> select SQL\_TEXT,TIMESTAMP from DBA\_FGA\_AUDIT\_TRAIL order by TIMESTAMP;

查看审计的策略：

select \* from dba\_audit\_policies;

**还有让审计失效、激活、删除的方法：**

exec DBMS\_FGA.DISABLE\_POLICY (object\_schema=>'LCAM\_TEST',object\_name=>'test',policy\_name=>'FGA\_1');  
exec DBMS\_FGA.ENABLE\_POLICY (object\_schema=>'LCAM\_TEST',object\_name=>'test',policy\_name=>'FGA\_1');  
exec DBMS\_FGA.DROP\_POLICY(object\_schema=>'LCAM\_TEST',object\_name=>'test',policy\_name=>'FGA\_1');

**官方文档的位置是：**

Oracle® Database PL/SQL Packages and Types Reference  11*g* Release 2 (11.2)   DBMS\_FGA

Table 66-2 ADD\_POLICY Procedure Parameters

<table> 
 <thead> 
 <tr> 
 <th style="vertical-align:bottom;">Parameter</th> 
 <th style="vertical-align:bottom;">Description</th> 
 <th style="vertical-align:bottom;">Default Value</th> 
 </tr> 
 </thead> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;"> <code>object_schema</code> </td> 
 <td style="vertical-align:top;"> The schema of the object to be audited.&nbsp;<code>(</code><code>If NULL, the current log-on user schema is assumed.)</code> </td> 
 <td style="vertical-align:top;"> <code>NULL</code> </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> <code>object_name</code> </td> 
 <td style="vertical-align:top;"> The name of the object to be audited. </td> 
 <td style="vertical-align:top;"> - </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> <code>policy_name</code> </td> 
 <td style="vertical-align:top;"> The unique name of the policy. </td> 
 <td style="vertical-align:top;"> - </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> <code>audit_condition</code> </td> 
 <td style="vertical-align:top;"> A condition in a row that indicates a monitoring condition.&nbsp;<code>NULL</code>&nbsp;is allowed and acts as&nbsp;<code>TRUE</code>. </td> 
 <td style="vertical-align:top;"> <code>NULL</code> </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> <code>audit_column</code> </td> 
 <td style="vertical-align:top;"> The columns to be checked for access. These can include OLS hidden columns or object type columns. The default,&nbsp;<code>NULL</code>, causes audit if any column is accessed or affected. </td> 
 <td style="vertical-align:top;"> <code>NULL</code> </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> <code>handler_schema</code> </td> 
 <td style="vertical-align:top;"> The schema that contains the event handler. The default,&nbsp;<code>NULL</code>, causes the current schema to be used. </td> 
 <td style="vertical-align:top;"> <code>NULL</code> </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> <code>handler_module</code> </td> 
 <td style="vertical-align:top;"> 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. </td> 
 <td style="vertical-align:top;"> <code>NULL</code> </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> <code>enable</code> </td> 
 <td style="vertical-align:top;"> Enables the policy if TRUE, which is the default. </td> 
 <td style="vertical-align:top;"> <code>TRUE</code> </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> <code>statement_types</code> </td> 
 <td style="vertical-align:top;"> The SQL statement types to which this policy is applicable:&nbsp;<code>INSERT</code>,&nbsp;<code>UPDATE</code>,<code>DELETE</code>, or&nbsp;<code>SELECT</code>&nbsp;only. </td> 
 <td style="vertical-align:top;"> <code>SELECT</code> </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> <code>audit_trail</code> </td> 
 <td style="vertical-align:top;"> Destination (DB or XML) of fine grained audit records. Also specifies whether to populate&nbsp;<code>LSQLTEXT</code>&nbsp;and&nbsp;<code>LSQLBIND</code>&nbsp;in&nbsp;<code>fga_log$</code>. </td> 
 <td style="vertical-align:top;"> <code>DB+</code><code>EXTENDED</code> </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> <code>audit_column_opts</code> </td> 
 <td style="vertical-align:top;"> Establishes whether a statement is audited when the query referencesany&nbsp;column specified in the audit_column parameter or only when&nbsp;allsuch columns are referenced. </td> 
 <td style="vertical-align:top;"> <code>ANY_COLUMNS</code> </td> 
 </tr> 
 </tbody> 
</table>