FGA Notes @11G
To create a
fine-grained audit policy, you must have
EXECUTE privileges on the
DBMS_FGA PL/SQL package. The package is owned by the
SYS user.
To
create a fine-grained audit policy, use the
DBMS_FGA.ADD_POLICY
procedure. This procedure creates an audit policy using the supplied
predicate as the audit condition. Oracle Database executes the policy
predicate with the privileges of the user who created the policy. The
maximum number of fine-grained policies on any table or view object is
256. Oracle Database stores the policy in the data dictionary table, but
you can create the policy on any table or view that is not in the
SYS schema.
After
you create the
fine-grained audit policy, it does not reside in any specific schema,
although the definition for the policy is stored in the
SYS.FGA$ data dictionary table.
You cannot modify a fine-grained audit policy after you have created
it. If you need to modify the policy, drop it and then recreate it.
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);
In this specification:
-
object_schema: Specifies the schema of the object to be audited. (If NULL, the current log-on user schema is assumed.)
-
object_name: Specifies the name of the object to be audited.
-
policy_name: Specifies the name of the policy to be created. Ensure that this name is unique.
-
audit_condition: Specifies a Boolean condition in a row. NULL is allowed and acts as TRUE. See "Auditing Specific Columns and Rows" for more information. If you specify NULL or no audit condition, then any action on a table with that policy creates an audit record, whether or not rows are returned
-
audit_column: Specifies one or more columns to audit, including hidden columns. If set to NULL
or omitted, all columns are audited. These can include Oracle Label
Security hidden columns or object type columns. The default, NULL,
causes audit if any column is accessed or affected.
-
handler_schema: If an alert is used to trigger a
response when the policy is violated, specifies the name of the schema
that contains the event handler. The default, NULL, uses the current schema. See also "Tutorial: Adding an Email Alert to a Fine-Grained Audit Policy".
-
handler_module: Specifies the name of the event handler.
Include the package the event handler is in. This function is invoked
only after the first row that matches the audit condition in the query
is processed.
Follow these guidelines:
-
Do not create recursive fine-grained audit handlers. For example, suppose you create a handler that executes an
INSERT statement on the HR.EMPLOYEES table. The policy that is associated with this handler is for INSERT statements (as set by the statement_types
parameter). When the policy is used, the handler executes recursively
until the system has run out of memory. This can raise the error ORA-1000: maximum open cursors exceeded or ORA-00036: maximum number of recursive SQL levels (50) exceeded.
-
Do not issue the
DBMS_FGA.ENABLE_POLICY or DBMS_FGA.DISABLE_POLICY statement from a policy handler. Doing so can raise the ORA-28144: Failed to execute fine-grained audit handler error.
-
enable: Enables or disables the policy using true or false. If omitted, the policy is enabled. The default is TRUE.
-
statement_types: Specifies the SQL statements to be audited: INSERT, UPDATE, DELETE, or SELECT only.
-
audit_trail: Specifies the destination (DB or XML) of fine-grained audit records. Also specifies whether to populate LSQLTEXT and LSQLBIND in FGA_LOG$. However, be aware that sensitive data, such as credit card information, can be recorded in clear text. See "Auditing Sensitive Information" for how you can handle this scenario.
If you set the audit_trail parameter to XML, then the XML files are written to the directory specified by the AUDIT_FILE_DEST initialization parameter.
For read-only databases, Oracle Database writes the fine-grained audit trail to XML files, regardless of the audit_trail setting.
-
audit_column_opts: If you specify more than one column in the audit_column parameter, then this parameter determines whether to audit all or specific columns. See "Auditing Specific Columns and Rows" for more information.
BEGIN
DBMS_FGA.ADD_POLICY(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'chk_hr_employees',
enable => TRUE,
statement_types => 'INSERT, UPDATE, SELECT, DELETE',
audit_trail => DBMS_FGA.DB+DBMS_FGA.EXTENDED);
END;
/
DBMS_FGA.DISABLE_POLICY(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'chk_hr_employees');
DBMS_FGA.DROP_POLICY(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'chk_hr_employees');
SELECT * FROM DBA_AUDIT_POLICIES;
SYS@DEV1> show parameter audit_trail;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string DB
SYS@DEV1> SET SERVEROUTPUT ON
BEGIN
IF
DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(
DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD)
THEN
DBMS_OUTPUT.PUT_LINE('AUD$ is initialized for cleanup');
ELSE
DBMS_OUTPUT.PUT_LINE('AUD$ is not initialized for cleanup.');
END IF;
END;
SYS@DEV1>
AUD$ is not initialized for cleanup.
PL/SQL procedure successfully completed.
SYS@DEV1>
Here is an excerpt form the documentation:
DEFAULT_CLEANUP_INTERVAL: Specify the desired default hourly purge interval (for example,
12 for every 12 hours). The
DBMS_AUDIT_MGMT procedures use this value to determine how to purge audit records. The timing begins when you run the
DBMS_AUDIT_MGMT.INIT_CLEANUP procedure. To update this value later, set the
DBMS_AUDIT_MGMT.CLEAN_UP_INTERVAL property of the
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY procedure.
The
DEFAULT_CLEANUP_INTERVAL setting must indicate the frequency in which
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL
is called. If you are uncertain about the frequency, set it to an
approximate value. You can change this value later on by using the
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY procedure.
BEGIN
DBMS_AUDIT_MGMT.INIT_CLEANUP(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
DEFAULT_CLEANUP_INTERVAL => 48 );
END;
/
You can
cancel the
DBMS_AUDIT_MGMT.INIT_CLEANUP settings, that is, the default cleanup interval, by invoking the
DBMS_AUDIT_MGMT.DEINIT_CLEANUP procedure.
For example, to cancel all purge settings for the standard audit trail:
BEGIN
DBMS_AUDIT_MGMT.DEINIT_CLEANUP(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD);
END;
/
Views:
SELECT * FROM DBA_AUDIT_POLICIES;
SELECT * FROM DBA_FGA_AUDIT_TRAIL;
SELECT * FROM DBA_AUDIT_MGMT_CLEAN_EVENTS;
SELECT * FROM DBA_AUDIT_MGMT_CLEANUP_JOBS;
SELECT * FROM DBA_AUDIT_MGMT_CONFIG_PARAMS;
SELECT * FROM DBA_AUDIT_MGMT_LAST_ARCH_TS;
Documentation Pointers
- Oracle® Database Security Guide 11g Release 2 (11.2)
