Skip Headers
Oracle® Database PL/SQL Packages and Types Reference
10g Release 2 (10.2)

Part Number B14258-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

40 DBMS_FGA

The DBMS_FGA package provides fine-grained security functions.

This chapter contains the following topics:


Using DBMS_FGA


Security Model

Execute privilege on DBMS_FGA is needed for administering audit policies. Because the audit function can potentially capture all user environment and application context values, policy administration should be executable by privileged users only. The policy event handler module will be executed with the module owner's privilege.


Operational Notes

This package is available for only cost-based optimization. The rule-based optimizer may generate unnecessary audit records since audit monitoring can occur before row filtering. For both the rule-based optimizer and the cost-based optimizer, you can refer to DBA_FGA_AUDIT_TRAIL to analyze the SQL text and corresponding bind variables that are issued.


Summary of DBMS_FGA Subprograms

Table 40-1 DBMS_FGA Package Subprograms

Subprogram Description
ADD_POLICY Procedure
Creates an audit policy using the supplied predicate as the audit condition
DISABLE_POLICY Procedure
Disables an audit policy
DROP_POLICY Procedure
Drops an audit policy
ENABLE_POLICY Procedure
Enables an audit policy


ADD_POLICY Procedure

This procedure creates an audit policy using the supplied predicate as the audit condition. The maximum number of FGA policies on any table or view object is 256.

Syntax

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);

Parameters

Table 40-2 ADD_POLICY Procedure Parameters

Parameter Description Default Value
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 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 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 references any column specified in the audit_column parameter or only when all such columns are referenced. ANY_COLUMNS

Usage Notes

V$XML_AUDIT_TRAIL View

The new values for the audit_trail parameter (XML and XML+EXTENDED) cause fine-grained auditing records to be written to operating system files in XML format.

Audit records stored in operating system files can be more secure than database-stored audit records because access can require file permissions that DBAs do not have. Operating system storage for audit records also offers higher availability, since such records remain available even if the database is temporarily inaccessible.

A new dynamic view, V$XML_AUDIT_TRAIL, makes such audit records from XML files available to DBAs through SQL query, providing enhanced usability. Querying this view causes all XML files (all files with an.xml extension) in the AUDIT_FILE_DEST directory to be parsed and presented in relational table format.

The DBA_COMMON_AUDIT_TRAIL view includes the contents of the V$XML_AUDIT_TRAIL dynamic view for standard and fine-grained audit records.

Since the audit XML files are stored in files with extension.xml on all platforms, the dynamic view presents audit information similarly on all platforms, using the following schema:

Table 40-3 Elements in the V$XML_AUDIT_TRAIL Dynamic View

Element Type
AUDIT_TYPE VARCHAR2(18)
SESSION_ID NUMBER
PROXY_SESSIONID NUMBER
STATEMENTID NUMBER
ENTRYID NUMBER
EXTENDED_TIMESTAMP TIMESTAMP(6) WITH TIME ZONE
GLOBAL_UID VARCHAR2(32)
DB_USER VARCHAR2(30)
CLIENT_ID VARCHAR2(64)
EXT_NAME VARCHAR2(4000)
OS_USER VARCHAR2(255)
USERHOST VARCHAR2(128)
OS_PROCESS VARCHAR2(16)
TERMINAL VARCHAR2(255)
INSTANCE_NUMBER NUMBER
OBJECT_SCHEMA VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
POLICY_NAME VARCHAR2(30)
STATEMENT_TYPE VARCHAR2(28)
TRANSACTIONID RAW(8)
SCN NUMBER
COMMENT_TEXT VARCHAR2(4000)
SQL_BIND VARCHAR2(4000)
SQL_TEXT VARCHAR2(4000)

Usage Notes

Examples

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

DISABLE_POLICY Procedure

This procedure disables an audit policy.

Syntax

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

Parameters

Table 40-4 DISABLE_POLICY Procedure Parameters

Parameter Description
object_schema The schema of the object to be audited. (If NULL, the current log-on user schema is assumed.)
object_name The name of the object to be audited.
policy_name The unique name of the policy.

The default value for object_schema is NULL. (If NULL, the current log-on user schema is assumed.)

Examples

DBMS_FGA.DISABLE_POLICY (
object_schema   =>  'scott',
object_name     =>  'emp',
policy_name     =>  'mypolicy1');

DROP_POLICY Procedure

This procedure drops an audit policy.

Syntax

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

Parameters

Table 40-5 DROP_POLICY Procedure Parameters

Parameter Description
object_schema The schema of the object to be audited. (If NULL, the current log-on user schema is assumed.)
object_name The name of the object to be audited.
policy_name The unique name of the policy.

Usage Notes

The DBMS_FGA procedures cause current DML transactions, if any, to commit before the operation unless they are inside a DDL event trigger. With DDL transactions, the DBMS_FGA procedures are part of the DDL transaction. The default value for object_schema is NULL. (If NULL, the current log-on user schema is assumed.)

Examples

DBMS_FGA.DROP_POLICY (
object_schema   =>  'scott',
object_name     =>  'emp',
policy_name     =>  'mypolicy1');

ENABLE_POLICY Procedure

This procedure enables an audit policy.

Syntax

DBMS_FGA.ENABLE_POLICY(
   object_schema  VARCHAR2,
   object_name    VARCHAR2,
   policy_name    VARCHAR2,
   enable         BOOLEAN);

Parameters

Table 40-6 ENABLE_POLICY Procedure Parameters

Parameter Description
object_schema The schema of the object to be audited. (If NULL, the current log-on user schema is assumed.)
object_name The name of the object to be audited.
policy_name The unique name of the policy.
enable Defaults to TRUE to enable the policy.

Examples

DBMS_FGA.ENABLE_POLICY (
object_schema    =>  'scott',
object_name      =>  'emp',
policy_name      =>  'mypolicy1',
enable           =>   TRUE);