31 Value-Based Auditing with Fine-Grained Audit Policies
Fine-grained auditing enables you to perform value-based auditing to audit access to certain rows based on values in specific columns.
- Overview of Fine-Grained Auditing
Before you create fine-grained audit policies, you should understand the overall concepts how of fine-grained auditing works. - Creating Fine-Grained Audit Policies
TheDBMS_FGA.ADD_POLICY
procedure creates a fine-grained audit policy. - Managing Fine-Grained Audit Policies
After you create a fine-grained audit policy, you can alter or drop it. - Tutorial: Adding an Email Alert to a Fine-Grained Audit Policy
This tutorial demonstrates how to create a fine-grained audit policy that generates an email alert when users violate the policy. - Fine-Grained Audit Policy Data Dictionary Views
You can query data dictionary and dynamic views to find detailed auditing information about fine-grained audit policies.
Related Topics
Parent topic: Monitoring Database Activity with Auditing
31.1 Overview of Fine-Grained Auditing
Before you create fine-grained audit policies, you should understand the overall concepts how of fine-grained auditing works.
- About Fine-Grained Auditing
Oracle Database enables you to create customized audit policies using fine-grained auditing (FGA), which is available in Oracle Database Enterprise Edition. - Where Are Fine-Grained Audit Records Stored?
Fine-grained auditing records are stored in the unified audit trail, which you can view by querying theUNIFIED_AUDIT_TRAIL
data dictionary view. - Who Can Perform Fine-Grained Auditing?
Oracle provides roles for privileges needed to create fine-grained audit policies and to view and analyze fine-grained audit policy data. - Fine-Grained Auditing on Tables or Views That Have Oracle VPD Policies
The audit trail captures the VPD predicate for fine-grained audited tables or views that are included in an Oracle VPD policy. - Fine-Grained Auditing in a Multitenant Environment
You can create fine-grained audit policies in the CDB root, application root, CDB PDBs, and application PDBs. - Fine-Grained Audit Policies with Editions
You can createDBMS_FGA
policies for use in an editions environment.
Related Topics
Parent topic: Value-Based Auditing with Fine-Grained Audit Policies
31.1.1 About Fine-Grained Auditing
Oracle Database enables you to create customized audit policies using fine-grained auditing (FGA), which is available in Oracle Database Enterprise Edition.
Use fine-grained auditing if you want to perform value-based auditing to audit access to certain rows based on values in specific columns or if you want to integrate with event handlers within the Oracle database.
Fine-grained auditing enables you to monitor data access based on content of the column values returned. For instance, with fine-grained auditing, you can audit access to a sensitive column such as SALARY
in the EMPLOYEES
table only when record values with SALARY >1500
are retrieved by the query. Fine-grained audit policies also enable you to specify an event handler. Event handlers are PL/SQL functions that Oracle Database calls when an audit condition is triggered. When a SQL query satisfies the fine-grained audit policy conditions (that is, relevant columns and specific data values being accessed), Oracle Database invokes the event handler, which in turn can be configured to message to a database administrator or it can trigger a security alert in an external system. This speeds up the detection of a security violation and enables administrators to respond to the problem sooner.
Two key use-cases where you will want to consider fine-grained audit policies in addition to unified audit policies are:
- When you want to audit access to specific security-relevant columns, and their sensitive data values, such as salaries or Social Security numbers
- Raise alerts on possible security breaches
Fine-grained auditing enables you to monitor data access based on content. It provides granular auditing of queries, and INSERT
, UPDATE
, and DELETE
operations. Some sample instances where you might consider fine-grained auditing includes the following:
-
Accessing a table between 9 p.m. and 6 a.m. or on Saturday and Sunday
-
Using an IP address from outside the corporate network
-
Modifying a sensitive data value above an expected threshold
Fine-grained audit policies are based on simple, user-defined SQL predicates on table objects that act as conditions for selective auditing. The SQL statement is audited during fetching, whenever the policy conditions are met for a row.
Consider using fine-grained audit policies over unified audit policies if you have the following requirements:
- You need row value-based auditing. For instance, you want to audit updates to a salary column when the updated value is higher than a specified threshold, but not otherwise.
- You need to pro-actively notify administrators or other users of specific events in the Oracle database.
- You need to capture differing bind variable values in DML statement for bulk data processing operation using
BULK COLLECT
andFORALL
in PL/SQL.
Note:
-
Fine-grained auditing is supported only with cost-based optimization. For queries using rule-based optimization, fine-grained auditing checks before applying row filtering, which could result in an unnecessary audit event trigger.
-
Policies currently in force on an object involved in a flashback query are applied to the data returned from the specified flashback snapshot based on time or system change number (SCN).
-
If you want to use fine-grained auditing to audit data that is being directly loaded (for example, using Oracle Warehouse Builder to run DML statements), then Oracle Database transparently makes all direct loads that are performed in the database instance into conventional loads. If you want to preserve the direct loading of data, consider using unified audit policies instead.
Parent topic: Overview of Fine-Grained Auditing
31.1.2 Where Are Fine-Grained Audit Records Stored?
Fine-grained auditing records are stored in the unified audit trail, which you can view by querying the UNIFIED_AUDIT_TRAIL
data dictionary view.
Administrators who have the AUDIT_ADMIN
or AUDIT_VIEWER
role can query UNIFIED_AUDIT_TRAIL
data dictionary view.
The audit trail captures an audit record for each reference of a table or a view within a SQL statement. For example, if you run a UNION
statement that references the HR.EMPLOYEES
table twice, then an audit policy for statement generates two audit records, one for each access of the HR.EMPLOYEES
table.
Related Topics
Parent topic: Overview of Fine-Grained Auditing
31.1.3 Who Can Perform Fine-Grained Auditing?
Oracle provides roles for privileges needed to create fine-grained audit policies and to view and analyze fine-grained audit policy data.
The fine-grained audit privileges are as follows:
-
To create and administer fine-grained audit policies, you must be granted the
AUDIT_ADMIN
role or theEXECUTE
privilege on theDBMS_FGA
package. You must also be granted theADMINISTER FINE GRAINED AUDIT POLICY
system privilege to administer other schemas than your own schemas. (A user does not need this privilege to administer fine-grained audit policies in their own schema.) To grant theADMINISTER FINE GRAINED AUDIT POLICY
privilege:- Syntax of the
ADMINISTER FINE GRAINED AUDIT POLICY
privilege grant if the fine-grained audit policy is to apply to all non-SYS
schemas across the database:GRANT ADMINISTER FINE GRAINED AUDIT POLICY TO grantee;
- Syntax of the
ADMINISTER FINE GRAINED AUDIT POLICY
privilege grant if the fine-grained audit policy is to be restricted to a specific schema:GRANT ADMINISTER FINE GRAINED AUDIT POLICY ON SCHEMA schema TO grantee;
- Syntax of the
-
To view and analyze fine-grained audit data, you must be granted the
AUDIT_VIEWER
role.
The PL/SQL package is already granted to AUDIT_ADMIN
role. As with all privileges, an administrator must only grant these roles to trusted users only. You can find the roles that user have been granted by querying the DBA_ROLE_PRIVS
data dictionary view.
Parent topic: Overview of Fine-Grained Auditing
31.1.4 Fine-Grained Auditing on Tables or Views That Have Oracle VPD Policies
The audit trail captures the VPD predicate for fine-grained audited tables or views that are included in an Oracle VPD policy.
This behavior is similar to how the unified audit trail captures the VPD predicate for unified audit policies.
The audit trail also captures internal predicates from Oracle Label Security and Oracle Real Application Security policies.
You do not need to create a special audit policy to capture the VPD predicate audit records. The predicate information is automatically stored in the RLS_INFO
column of the UNIFIED_AUDIT_TRAIL
data dictionary view.
31.1.5 Fine-Grained Auditing in a Multitenant Environment
You can create fine-grained audit policies in the CDB root, application root, CDB PDBs, and application PDBs.
Note the following general rules about fine-grained audit policies:
-
You cannot create fine-grained audit policies on
SYS
objects. -
You cannot create fine-grained audit policies, either local or application common, for extended data link objects.
-
When you create a fine-grained audit policy in the CDB root, the policy cannot be applied to all PDBs. It only applies to objects within the CDB root. (In other words, there is no such thing as a common fine-grained audit policy for the CDB root.) If you want to create a fine-grained audit policy to audit a common object’s access in all the PDBs, then you must explicitly create that policy in each PDB and then enable it on the common objects that is accessible in the PDB.
-
When you create a fine-grained audit policy in a PDB, it applies only to objects within the PDB. You cannot create one policy for the entire multitenant environment. The policy must be specific to objects within a PDB.
-
You can create application common fine-grained audit policies only if you are connected to the application root and only within the
BEGIN
/END
block. If you are connected to the application root and create the fine-grained audit policy outside theBEGIN
/END
block, then the fine-grained audit policy is created in the application root. -
You cannot create application common fine-grained audit policies on local PDB objects.
-
If the application common fine-grained audit policy has a handler, then this handler must be owned by either an application common user or a CDB common user.
-
You can create an application fine-grained audit policy on local (PDB) objects and CDB common objects. Because the policy is local to its container, the object on which the policy is defined is audited only in the particular container where the policy is defined. For example, if you create a fine-grained audit policy in the
hr_pdb
PDB, the object for which you create this policy must exist in thehr_pdb
PDB. -
You cannot create local fine-grained audit policies in an application PDB on object linked and extended data link objects. On metadata-linked objects are allowed in the fine-grained audit policy.
-
Application root local policies are allowed for all application common objects.
-
When you create a fine-grained audit policy as a common audit policy in an application root, it will be effective in each PDB that belongs to this application root. Therefore, any access to the application common object and CDB common object (on which the application common fine-grained audit policy is defined) from the application PDB is audited in the fine-grained audit trail in that application PDB.
-
When you create scripts for application install, upgrade, patch, or uninstall operations, you can include SQL statements within the
ALTER PLUGGABLE DATABASE app_name BEGIN INSTALL
andALTER PLUGGABLE DATABASE app_name END INSTALL
blocks to perform various operations. You can include fine-grained audit policy statements only within these blocks. -
You can only enable, disable, or drop application common fine-grained audit policies from the application root, and from within a
ALTER PLUGGABLE DATABASE app_name BEGIN INSTALL
andALTER PLUGGABLE DATABASE app_name END INSTALL
block in a script.
Parent topic: Overview of Fine-Grained Auditing
31.1.6 Fine-Grained Audit Policies with Editions
You can create DBMS_FGA
policies for use in an editions environment.
Note the following:
-
You can prepare an application for edition-based redefinition, and cover each table that the application uses with an editioning view. If you do this, then you must move the fine-grained audit polices that protect these tables to the editioning view. You can find information about the currently configured editions by querying the DBA_EDITIONS data dictionary view. To find information about fine-grained audit policies, query DBA_AUDIT_POLICIES.
-
If you plan to use the
DBMS_FGA
package policy across different editions, then you can control the results of the policy: whether the results are uniform across all editions, or specific to the edition in which the policy is used.
Parent topic: Overview of Fine-Grained Auditing
31.2 Creating Fine-Grained Audit Policies
The DBMS_FGA.ADD_POLICY
procedure creates a fine-grained audit policy.
- About Creating a Fine-Grained Audit Policy
You can create and manage fine-grained audit policies by using theDBMS_FGA
PL/SQL package. - Syntax for Creating a Fine-Grained Audit Policy
TheDBMS_FGA.ADD_POLICY
procedure includes many settings, such as the ability to use a handler for complex auditing. - Example: Using DBMS_FGA.ADD_POLICY to Create a Fine-Grained Audit Policy
TheDBMS_FGA.ADD_POLICY
procedure can create a fine-grained audit policy using multiple statement types. - Audits of Specific Columns and Rows
You can do value-based auditing to audit access to certain rows based on values in specific columns.
Parent topic: Value-Based Auditing with Fine-Grained Audit Policies
31.2.1 About Creating a Fine-Grained Audit Policy
You can create and manage fine-grained audit policies by using the DBMS_FGA
PL/SQL package.
Consider the following when you create fine-grained audit policies:
- The
DBMS_FGA
PL/SQL package enables you to add all combinations of the following statements into one policy:SELECT
INSERT
UPDATE
DELETE
- For
MERGE
statements:- You can audit
MERGE
statements by configuring fine-grained access on the underlying actions ofINSERT
andUPDATE
. - Only one record is generated for each policy for successful
MERGE
operations.
- You can audit
If you plan to create a materialized view on the base table on which you want to create a fine-grained audit policy, then you must create the fine-grained audit policy on the base table before you create the materialized view on the same table. Otherwise, any refresh operations on the materialized view will fail with an ORA-12008: error in materialized view refresh path
error.
When you create a fine-grained audit policy, be aware that sensitive data, such as credit card information, can be recorded in clear text.
To administer fine-grained audit policies, you must have be granted the AUDIT_ADMIN
role. Note also that the EXECUTE
privilege for the DBMS_FGA
package is mandatorily audited.
The audit policy is bound to the table for which you created it. This simplifies the management of audit policies because the policy only needs to be changed once in the database, not in each application. In addition, no matter how a user connects to the database—from an application, a Web interface, or through SQL*Plus or Oracle SQL Developer—Oracle Database records any actions that affect the policy.
If any rows returned from a query match the audit condition that you define, then Oracle Database inserts an audit entry into the unified audit trail. This entry excludes all the information that is reported in the regular audit trail. In other words, only one row of audit information is inserted into the audit trail for every fine-grained audit policy that evaluates to true.
The DBMS_FGA.ADD_POLICY
procedure creates an audit policy using the supplied predicate as the audit condition.
By default, Oracle Database runs the policy predicate with the privileges of the user who owns 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. The fine grained policy is only created in the local PDB.
You cannot modify a fine-grained audit policy after you have created it. If you must modify the policy, then drop and recreate it.
You can find information about a fine-grained audit policy by querying the ALL_AUDIT_POLICIES
, DBA_AUDIT_POLICIES
, and USER_AUDIT_POLICIES
views. The UNIFIED_AUDIT_TRAIL
view contains a column entitled FGA_POLICY_NAME
, which you can use to filter out rows that were generated using a specific fine-grained audit policy.
Related Topics
Parent topic: Creating Fine-Grained Audit Policies
31.2.2 Syntax for Creating a Fine-Grained Audit Policy
The DBMS_FGA.ADD_POLICY
procedure includes many settings, such as the ability to use a handler for complex auditing.
The DBMS_FGA.ADD_POLICY
procedure syntax is as follows:
DBMS_FGA.ADD_POLICY( object_schema IN VARCHAR2 DEFAULT NULL object_name IN VARCHAR2, policy_name IN VARCHAR2, audit_condition IN VARCHAR2 DEFAULT NULL, audit_column IN VARCHAR2 DEFAULT NULL handler_schema IN VARCHAR2 DEFAULT NULL, handler_module IN VARCHAR2 DEFAULT NULL, enable IN BOOLEAN DEFAULT TRUE, statement_types IN VARCHAR2 DEFAULT SELECT, audit_trail IN BINARY_INTEGER DEFAULT NULL, audit_column_opts IN BINARY_INTEGER DEFAULT ANY_COLUMNS, policy_owner IN VARCHAR2 DEFAULT NULL);
In this specification:
-
object_schema
specifies the schema of the object to be audited. (IfNULL
, 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 asTRUE
. If you specifyNULL
or no audit condition, then any action on a table with that policy creates an audit record, whether or not rows are returned.Follow these guidelines:
-
Do not include functions, which run the auditable statement on the same base table, in the
audit_condition
setting. For example, suppose you create a function that runs anINSERT
statement on theHR.EMPLOYEES
table. The policy'saudit_condition
contains this function and it is forINSERT
statements (as set bystatement_types
). When the policy is used, the function runs recursively until the system has run out of memory. This can raise the errorORA-1000: maximum open cursors exceeded
orORA-00036: maximum number of recursive SQL levels (50) exceeded
. -
Do not issue the
DBMS_FGA.ENABLE_POLICY
orDBMS_FGA.DISABLE_POLICY
statement from a function in a policy's condition.
-
-
audit_column
specifies one or more columns to audit, including hidden columns. If set toNULL
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. -
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 runs an
INSERT
statement on theHR.EMPLOYEES
table. The policy that is associated with this handler is forINSERT
statements (as set by thestatement_types
parameter). When the policy is used, the handler runs recursively until the system has run out of memory. This can raise the errorORA-1000: maximum open cursors exceeded
orORA-00036: maximum number of recursive SQL levels (50) exceeded
. -
Do not issue the
DBMS_FGA.ENABLE_POLICY
orDBMS_FGA.DISABLE_POLICY
statement from a policy handler. Doing so can raise theORA-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 isTRUE
. -
statement_types
: Specifies the SQL statements to be audited:INSERT
,UPDATE
,DELETE
, orSELECT
only. If you want to audit aMERGE
operation, then setstatement_types
to'INSERT,UPDATE'
. The default isSELECT
. -
audit_trail
: If you have migrated to unified auditing, then Oracle Database ignores this parameter and writes the audit records immediately to the unified audit trail. Starting in Oracle Database 23ai, traditional auditing is desupported, so theaudit_trail
is ignored. -
audit_column_opts
: If you specify more than one column in theaudit_column
parameter, then this parameter determines whether to audit all or specific columns. -
policy_owner
is the user who owns the fine-grained auditing policy. However, this setting is not a user-supplied argument. The Oracle Data Pump client uses this setting internally to recreate the fine-grained audit policies appropriately.
Related Topics
Parent topic: Creating Fine-Grained Audit Policies
31.2.3 Example: Using DBMS_FGA.ADD_POLICY to Create a Fine-Grained Audit Policy
The DBMS_FGA.ADD_POLICY
procedure can create a fine-grained audit policy using multiple statement types.
Example 31-1 shows how to audit statements INSERT
, UPDATE
, DELETE
, and SELECT
on table HR.EMPLOYEES
.
Note that this example omits the audit_column_opts
parameter, because it is not a mandatory parameter.
Example 31-1 Using DBMS_FGA.ADD_POLICY to Create a Fine-Grained Audit Policy
BEGIN DBMS_FGA.ADD_POLICY( object_schema => 'HR', object_name => 'EMPLOYEES', policy_name => 'chk_hr_employees', audit_column => 'SALARY', enable => TRUE, statement_types => 'INSERT, UPDATE, SELECT, DELETE'); END; /
After you create the policy, if you query the DBA_AUDIT_POLICIES
view, you will find the new policy listed:
SELECT POLICY_NAME FROM DBA_AUDIT_POLICIES; POLICY_NAME ------------------------------- CHK_HR_EMPLOYEES
Afterwards, any of the following SQL statements log an audit event record.
SELECT COUNT(*) FROM HR.EMPLOYEES WHERE COMMISSION_PCT = 20 AND SALARY > 4500; SELECT SALARY FROM HR.EMPLOYEES WHERE DEPARTMENT_ID = 50; DELETE FROM HR.EMPLOYEES WHERE SALARY > 1000000;
Parent topic: Creating Fine-Grained Audit Policies
31.2.4 Audits of Specific Columns and Rows
You can do value-based auditing to audit access to certain rows based on values in specific columns.
To accomplish this, use the audit_column
parameter of the DBMS_FGA.ADD_POLICY
procedure to specify one or more sensitive columns. Use the audit_condition
boolean parameter to audit data in specific rows. Consider using unified audit policy if you do not have a need to do value-based auditing.
The following settings enable you to perform an audit if anyone in Department 50 (DEPARTMENT_ID = 50
) tries to access the SALARY
and COMMISSION_PCT
columns.
audit_condition => 'DEPARTMENT_ID = 50', audit_column => 'SALARY,COMMISSION_PCT,'
As you can see, this feature is enormously beneficial. It not only enables you to pinpoint particularly important types of data to audit, but it provides increased protection for columns that contain sensitive data, such as Social Security numbers, salaries, patient diagnoses, and so on.
If the audit_column
lists more than one column, then you can use the audit_column_opts
parameter to specify whether a statement is audited when the query references any column specified in the audit_column
parameter or only when all columns are referenced. For example:
audit_column_opts => DBMS_FGA.ANY_COLUMNS, audit_column_opts => DBMS_FGA.ALL_COLUMNS,
If you do not specify a relevant column, then auditing applies to all columns.
31.3 Managing Fine-Grained Audit Policies
After you create a fine-grained audit policy, you can alter or drop it.
- Enabling a Fine-Grained Audit Policy
TheDBMS_FGA.ENABLE_POLICY
procedure enables a fine-grained audit policy. - Disabling a Fine-Grained Audit Policy
TheDBMS_FGA.DISABLE_POLICY
procedure disables a fine-grained audit policy. - Dropping a Fine-Grained Audit Policy
TheDBMS_FGA.DROP_POLICY
procedure drops a fine-grained audit policy.
Parent topic: Value-Based Auditing with Fine-Grained Audit Policies
31.3.1 Enabling a Fine-Grained Audit Policy
The DBMS_FGA.ENABLE_POLICY
procedure enables a fine-grained audit policy.
Related Topics
Parent topic: Managing Fine-Grained Audit Policies
31.3.2 Disabling a Fine-Grained Audit Policy
The DBMS_FGA.DISABLE_POLICY
procedure disables a fine-grained audit policy.
Related Topics
Parent topic: Managing Fine-Grained Audit Policies
31.3.3 Dropping a Fine-Grained Audit Policy
The DBMS_FGA.DROP_POLICY
procedure drops a fine-grained audit policy.
object_name
parameter of the DBMS_FGA.ADD_POLICY
procedure, or if you drop the user who created the audit policy.
Related Topics
Parent topic: Managing Fine-Grained Audit Policies
31.4 Tutorial: Adding an Email Alert to a Fine-Grained Audit Policy
This tutorial demonstrates how to create a fine-grained audit policy that generates an email alert when users violate the policy.
- About This Tutorial
This tutorial shows how you can add an email alert to a fine-grained audit policy that goes into effect when a user (or an intruder) violates the policy. - Step 1: Install and Configure the UTL_MAIL PL/SQL Package
TheUTL_MAIL
PL/SQL manages email that includes commonly used email features, such as attachments, CC, and BCC. - Step 2: Create User Accounts
You must create an administrative account and an auditor user. - Step 3: Configure an Access Control List File for Network Services
An access control list (ACL) file can be used to enable fine-grained access to external network services. - Step 4: Create the Email Security Alert PL/SQL Procedure
The email security alert PL/SQL procedure generates a message describing the violation and then sends this message to the appropriate users. - Step 5: Create and Test the Fine-Grained Audit Policy Settings
The fine-grained audit policy will trigger the alert when the policy is violated. - Step 6: Test the Alert
With the components in place, you are ready to test the alert. - Step 7: Remove the Components of This Tutorial
If you no longer need the components of this tutorial, then you can remove them.
Parent topic: Value-Based Auditing with Fine-Grained Audit Policies
31.4.1 About This Tutorial
This tutorial shows how you can add an email alert to a fine-grained audit policy that goes into effect when a user (or an intruder) violates the policy.
Note:
-
To complete this tutorial, you must use a database that has an SMTP server.
-
This tutorial applies to the current PDB only.
To add an email alert to a fine-grained audit policy, you first must create a procedure that generates the alert, and then use the following DBMS_FGA.ADD_POLICY
parameters to call this function when someone violates this policy:
-
handler_schema
: The schema in which the handler event is stored -
handler_module
: The name of the event handler
The alert can come in any form that best suits your environment: an email or pager notification, updates to a particular file or table, and so on. Creating alerts also helps to meet certain compliance regulations, such as California Senate Bill 1386. In this tutorial, you will create an email alert.
In this tutorial, you create an email alert that notifies a security administrator that a Human Resources representative is trying to select or modify salary information in the HR.EMPLOYEES
table. The representative is permitted to make changes to this table, but to meet compliance regulations, we want to create a record of all salary selections and modifications to the table.
31.4.2 Step 1: Install and Configure the UTL_MAIL PL/SQL Package
The UTL_MAIL
PL/SQL manages email that includes commonly used email features, such as attachments, CC, and BCC.
Related Topics
31.4.3 Step 2: Create User Accounts
You must create an administrative account and an auditor user.
Related Topics
31.4.4 Step 3: Configure an Access Control List File for Network Services
An access control list (ACL) file can be used to enable fine-grained access to external network services.
UTL_MAIL
, you must configure this type of access control list (ACL) file.
31.4.5 Step 4: Create the Email Security Alert PL/SQL Procedure
The email security alert PL/SQL procedure generates a message describing the violation and then sends this message to the appropriate users.
-
As user
fga_admin
, create the following procedure.CREATE OR REPLACE PROCEDURE email_alert (sch varchar2, tab varchar2, pol varchar2) AS msg varchar2(20000) := 'HR.EMPLOYEES table violation. The time is: '; BEGIN msg := msg||TO_CHAR(SYSDATE, 'Day DD MON, YYYY HH24:MI:SS'); UTL_MAIL.SEND ( sender => 'youremail@example.com', recipients => 'recipientemail@example.com', subject => 'Table modification on HR.EMPLOYEES', message => msg); END email_alert; /
In this example:
-
CREATE OR REPLACE PROCEDURE ...AS
: You must include a signature that describes the schema name (sch
), table name (tab
), and the name of the audit procedure (pol
) that you will define in audit policy in the next step. -
sender
andrecipients
: Replaceyouremail@example.com
with your email address, andrecipientemail@example.com
with the email address of the person you want to receive the notification.
-
31.4.6 Step 5: Create and Test the Fine-Grained Audit Policy Settings
The fine-grained audit policy will trigger the alert when the policy is violated.
31.5 Fine-Grained Audit Policy Data Dictionary Views
You can query data dictionary and dynamic views to find detailed auditing information about fine-grained audit policies.
Table 30-20 lists these views.
Tip:
To find error information about audit policies, check the trace files. The USER_DUMP_DEST
initialization parameter sets the location of the trace files.
Table 31-1 Views for Use with Fine-Grained Audit Policies
View | Description |
---|---|
|
Displays information about all fine-grained audit policies |
|
Lists default object-auditing options that are to be applied when objects are created |
|
Maps the auditable system action numbers to the action names |
|
Similar to the |
|
Displays information about fine-grained audit policies |
|
Describes audited Oracle Label Security events performed by users, and indicates if the user's action failed or succeeded |
|
Describes privilege (auditing option) type codes. This table can be used to map privilege (auditing option) type numbers to type names. |
|
Displays information about all fine-grained audit policies on table and views owned by the current user |
|
Displays all audit records |
Related Topics
Parent topic: Value-Based Auditing with Fine-Grained Audit Policies