A.4. DBMS_FGA This package is used to add, drop, enable, and disable fine-grained auditing policies. Because some program parameters are new or different in Oracle Database 10g, the tables below indicate the differences between the Oracle9i Database and Oracle Database 10g releases.
This procedure adds an FGA policy on a table. | | | | Oracle Database version |
|---|
Parameter name | Datatype | Description | 9i | 10g |
|---|
object_schema | VARCHAR2 | Schema name whose tables are to be under FGA. | Yes | Yes | object_name | VARCHAR2 | Name of the table on which FGA is enabled. | Yes | Yes | policy_name | VARCHAR2 | Name of the policy. | Yes | Yes | audit_condition | VARCHAR2 | Condition under which the audit trail will be generated (e.g., USER='SCOTT'). | Yes | Yes | audit_column | VARCHAR2 | Auditing will be triggered only if columns from this list are selected. | Yes | Yes | handler_schema | VARCHAR2 | If the policy has a handler module, the owner of that module. Handler modules are either procedures or packages that execute automatically when the audit condition is satisfied. | Yes | Yes | handler_module | VARCHAR2 | Name of the handler procedure or package. | Yes | Yes | enable | BOOLEAN | Indicates whether the policy is created as enabled. Default is enabled. | Yes | Yes | statement_types | VARCHAR2 | Which types of statements are audited. Valid values are SELECT, INSERT, DELETE, and UPDATE. | No | Yes | audit_trail | BINARY_INTEGER | If you need to capture bind variables as well as SQL text, set this parameter to DB_EXTENDED (which is the default). Otherwise, set it to DB. In Oracle Database 10g Release 2, you can define another typeXMLthat writes audit trails in XML format in the filesystem. | No | Yes | audit_column_opts | BINARY_INTEGER | When the parameter audit_column is set, auditing is triggered only if those columns are selected. If this parameter is set to ALL_COLUMNS, auditing is triggered only if all of the columns mentioned in the parameter audit_column are selected. If this parameter is set to ANY_COLUMNS, then auditing kicks in if any of the columns are selected. | No | Yes |
This procedure drops an FGA policy that was previously created on a table. | | | | Oracle Database version |
|---|
Parameter name | Datatype | Description | 9i | 10g |
|---|
object_schema | VARCHAR2 | Owner of the table under FGA | Yes | Yes | object_name | VARCHAR2 | Name of the table on which FGA is enabled | Yes | Yes | policy_name | VARCHAR2 | Name of the policy to be dropped | Yes | Yes |
This procedure disables an FGA policy that was previously created on a table. The policy is not dropped, but its effect is disabled. | | | | Oracle Database version |
|---|
Parameter name | Datatype | Description | 9i | 10g |
|---|
object_schema | VARCHAR2 | Owner of the table under FGA | Yes | Yes | object_name | VARCHAR2 | Name of the table on which FGA is enabled | Yes | Yes | policy_name | VARCHAR2 | Name of the policy to be disabled | Yes | Yes |
This procedure enables an FGA policy that was previously created on a table. The specified policy must have already been created. | | | | Oracle Database version |
|---|
Parameter name | Datatype | Description | 9i | 10g |
|---|
object_schema | VARCHAR2 | Owner of the table under FGA | Yes | Yes | object_name | VARCHAR2 | Name of the table on which FGA is established | Yes | Yes | policy_name | VARCHAR2 | Name of the policy to be enabled | Yes | Yes |
A.4.1. FGA Data Dictionary Views This section summarizes the columns in the data dictionary views that are relevant to fine-grained auditing.
This view shows the FGA policies created on the database. | | | Oracle Database version |
|---|
Column name | Description | 9i | 10g |
|---|
OBJECT_SCHEMA | Owner of the table on which the policy is defined. | Yes | Yes | OBJECT_NAME | Name of the table on which the policy is defined. | Yes | Yes | POLICY_NAME | Name of the defined policy. | Yes | Yes | POLICY_TEXT | Condition under which the auditing should be triggered (e.g., "SALARY>1500") is recorded here. | Yes | Yes | POLICY_COLUMN | Columns whose selection triggers the audit. | Yes | Yes | PF_SCHEMA | If the policy has a handler module, its owner is recorded here. The handler module executes automatically when the audit condition is satisfied. | Yes | Yes | PF_PACKAGE | If the handler module is a packaged procedure, the name of package is recorded here. | Yes | Yes | PF_FUNCTION | Name of the handler. If the handler module is a packaged procedure, this column shows the name of the procedure. | Yes | Yes | ENABLED | Indicates whether the policy is currently enabled. | Yes | Yes | SEL | Indicates whether the policy applies to SELECT statements. | No | Yes | INS | Indicates whether the policy applies to INSERT statements | No | Yes | UPD | Indicates whether the policy applies to UPDATE statements. | No | Yes | DEL | Indicates whether the policy applies to DELETE statements. | No | Yes | AUDIT_TRAIL | Type of auditing. If the value is DB_EXTENDED, it records bind variables in the trail. If the value is DB, bind variables are not recorded. In Oracle Database 10g Release 2, this column might also show XML, which indicates that the audit trail is in XML format in the operating system. | No | Yes | POLICY_COLUMN_OPTIONS | Indicates whether auditing is triggered when all of the columns are selected or just any one of them, from the list shown in the column POLICY_COLUMN. | No | Yes |
This view shows the FGA audit trail. | | | Oracle Database version |
|---|
Column name | Description | 9i | 10g |
|---|
SESSION_ID | AUDIT SESSION identifier. This is a unique session ID number; it is not the same as the SID column from the V$SESSION view. | Yes | Yes | TIMESTAMP | Time of the trail entry. | Yes | Yes | DB_USER | Database user that issued this query recorded in the trail. | Yes | Yes | OS_USER | Operating system user. | Yes | Yes | USERHOST | Hostname of the user. | Yes | Yes | CLIENT_ID | Client identifier of the session, if defined. | Yes | Yes | ECONTEXT_ID | Only in Oracle Database 10g Release 2. If a context is defined, the context ID is shown here. | No | Yes (R2) | EXT_NAME | If the user is externally authenticated, the external name is shown here. | Yes | Yes | OBJECT_SCHEMA | Owner of the table referenced by the statement. | Yes | Yes | OBJECT_NAME | Name of the table. | Yes | Yes | POLICY_NAME | Name of the policy that triggered this entry. | Yes | Yes | SCN | System Control Number of the database when this audit trail entry was generated. Used for flashback queries. | Yes | Yes | SQL_TEXT | Text of the SQL statement issued by the user. | Yes | Yes | SQL_BIND | Bind variables and their values in the SQL statement. | Yes | Yes | COMMENT$TEXT | Additional information about the audit trails is recorded here, if available. | Yes | Yes | STATEMENT_TYPE | Indicates the type of statement (e.g., SELECT). | No | Yes | EXTENDED_TIMESTAMP | Extended timestamp of the audit entry. This is recorded in the TIMESTAMP datatype, which offers a precision up to one- thousandth of a second. | No | Yes | PROXY_SESSIONID | If the user has logged in as a proxy user, this indicates the SID of the proxy session. | No | Yes | GLOBAL_UID | If the user is an enterprise user, authenticated by LDAP or some other mechanism, then the global UID is listed here. | No | Yes | INSTANCE_NUMBER | Instance ID, relevant only if the database is a Real Application Cluster (RAC). | No | Yes | OS_PROCESS | Operating system Process ID. | No | Yes | TRANSACTIONID | If the statement is a transaction, then the transaction ID is recorded here. | No | Yes | STATEMENTID | A single session may have several statements (e.g., a user selected from one table, then from another, etc.). Each statement has a unique statement ID. | No | Yes | ENTRYID | A single statement may, in turn, invoke several recursive statements, which are identified by a unique entry ID. | No | Yes |
| FLASHBACK_TRANSACTION_QUERY | |
This view shows the transactions in the system available in the undo segments. You can use this information to find out details on transactions. Available only in Oracle Database 10g. Column name | Description |
|---|
XID | Transaction identifier. | START_SCN | SCN number at the start of the transaction. | START_TIMESTAMP | Start time of the transaction, in TIMESTAMP format. | COMMIT_SCN | SCN when the transaction was committed. | COMMIT_TIMESTAMP | Time when the transaction was committed, in TIMESTAMP format. | LOGON_USER | User who performed the transaction. | UNDO_CHANGE# | SCN number of the undo operation. | OPERATION | Operation performed by the transaction (e.g., SELECT). PL/SQL blocks will show up as either DECLARE or BEGIN. | TABLE_NAME | Name of the table on which the operation was performed. | TABLE_OWNER | Owner of the table. | ROW_ID | Rowid of the row that was modified by this transaction. | UNDO_SQL | SQL that can be used to undo the transaction. |
|
