FGA Notes

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

1. Oracle® Database Security Guide 11g Release 2 (11.2)
• Verifying Security Access with Auditing
•  Auditing Specific Activities with Fine-Grained Auditing
•  Managing the Database Audit Trail