如果你想要審計表上,在某個時間,哪些人,操作哪些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> 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);--
SQL> col SQL_TEXT format a80SQL>
還有讓審計失效、激活、刪除的方法:
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 11g Release 2 (11.2) DBMS_FGA
Table 66-2 ADD_POLICY Procedure Parameters
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 OLS hidden columns or object type 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$.
DB+EXTENDED
audit_column_opts
Establishes whether a statement is audited when the query referencesany column specified in the audit_column parameter or only when allsuch columns are referenced.
ANY_COLUMNS