34
DBMS_FGA

The DBMS_FGA package provides fine-grained security functions.

This chapter contains the following topics:

• Using DBMS_FGA
  • Security Model
  • Operational Notes
• Summary of DBMS_FGA Subprograms

---

Using DBMS_FGA

• Security Model
• Operational Notes

---

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.

---

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 34-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 34-2 ADD_POLICY Procedure Parameters

Parameter	Description
object_schema	The schema of the object to be audited. Default value: NULL. (If NULL, the current effective user schema is assumed.)
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. Default value: 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. Default value: NULL
handler_schema	The schema that contains the event handler. The default, NULL, causes the current schema to be used. Default value: 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 is processed in the query. If the procedure fails with exception, the user SQL statement will fail as well. Default value: NULL
enable	Enables the policy if TRUE, which is the default. Default value: TRUE
statement_types	The SQL statement types to which this policy is applicable: insert, update, delete, or select only. Default value: SELECT
audit_trail	Whether to populate LSQLTEXT and LSQLBIND in fga_log$. Default value: 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. Default value: ANY_COLUMNS

Usage Notes

• Example: DBMS_FGA.ADD_POLICY(object_schema => 'scott', object_name=>'emp', policy_name => 'mypolicy1', audit_condition => 'sal < 100', audit_column =>'comm, credit_card, expirn_date', handler_schema => NULL, handler_module => NULL, enable => TRUE, statement_types=> 'INSERT, UPDATE');
• An FGA policy should not be applied to out-of-line columns such as LOB columns.
• If object_schema is NULL, the current effective user schema is assumed.
• The audit function (handler_module) is an alerting mechanism for the administrator; it must have the following interface:

  PROCEDURE <fname> ( object_schema VARCHAR2, object_name VARCHAR2, policy_
  name VARCHAR2 )  AS ...
  
  

  where fname is the name of the procedure, object_schema is the name of the schema of the table audited, object_name is the name of the table to be audited, and policy_name is the name of the policy being enforced.

• Each audit policy is applied to the query individually. However, at most one audit record may be generated for each policy, no matter how many rows being returned satisfy that policy's audit_condition. In other words, whenever any number of rows being returned satisfy an audit condition defined on the table, a single audit record will be generated for each such policy.
• If a table with an FGA policy defined on it receives a Fast Path insert or a vectored update, the hint is automatically disabled before any such operations. Disabling the hint allows auditing to occur according to the policy's terms. (One example of a Fast Path insert is the statement INSERT-WITH-APPEND-hint.)
• The audit_condition must be a boolean expression that can be evaluated using the values in the row being inserted, updated, or deleted. This condition can be NULL (or omitted), which is interpreted as TRUE, but it cannot contain the following elements:
  • Subqueries or sequences
  • Any direct use of SYSDATE, UID, USER or USERENV functions. However, a user-defined function and other SQL functions can use these functions to return the desired information.
  • Any use of the pseudocolumns LEVEL, PRIOR, or ROWNUM.

  Specifying an audit condition of "1=1" to force auditing of all specified statements ("statement_types") affecting the specified column ("audit_column") is no longer needed to achieve this purpose. NULL will cause audit even if no rows were processed, so that all actions on a table with this policy are audited.

• The audit_trail parameter specifies whether to record the query's SQL Text and SQL Bind variable information in the FGA audit trail (fga_log$) columns named LSQLTEXT and LSQLBIND:
  • To populate, set to DBMS_FGA.DB_EXTENDED (the default);
  • To leave unpopulated, set to DBMS_FGA.DB.

  The audit_trail parameter appears in the ALL_AUDIT_POLICIES view.

• The audit_column_opts parameter establishes whether a statement is audited
  • when the query references any column specified in the audit_column parameter (audit_column_opts = DBMS_FGA.ANY_COLUMNS), or
  • only when all such columns are referenced (audit_column_opts = DBMS_FGA.ALL_COLUMNS).

  The default is DBMS_FGA.ANY_COLUMNS.

  The ALL_AUDIT_POLICIES view also shows audit_column_opts.

Examples

DBMS_FGA.ADD_POLICY (object_schema => 'scott', object_name=>'emp', policy_name 
=> 'mypolicy1', audit_condition => 'sal < 100', audit_column =>'comm, credit_
card, expirn_date', handler_schema => NULL, handler_module => NULL, enable => 
TRUE, statement_types=> 'INSERT, UPDATE'); 

---

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 34-3 DISABLE_POLICY Procedure Parameters

Parameter	Description
object_schema	The schema of the object to be audited. (If NULL, the current effective 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 effective user schema is assumed.)

---

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 34-4 DROP_POLICY Procedure Parameters

Parameter	Description
object_schema	The schema of the object to be audited. (If NULL, the current effective 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. However, the procedures do not cause a commit first if 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 effective user schema is assumed.)

---

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 34-5 ENABLE_POLICY Procedure Parameters

Parameter	Description
object_schema	The schema of the object to be audited. (If NULL, the current effective 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.