Verifying Security Access with Auditing
What Is Auditing?
Auditing is the monitoring and recording of selected user database actions, from both database users and non database users. You can base auditing on individual actions, such as the type of SQL statement executed, or on combinations of data that can include the user name, application, time, and so on. You can audit both successful and failed activities. To use auditing, you enable it, and then configure what must be audited. The actions that you audit are recorded in either data dictionary tables or in operating system files.
Oracle recommends that you enable and configure auditing. Auditing is an effective method of enforcing strong internal controls so that your site can meet its regulatory compliance requirements, as defined in the Sarbanes-Oxley Act. This enables you to monitor business operations, and find any activities that may deviate from company policy. Doing so translates into tightly controlled access to your database and the application software, ensuring that patches are applied on schedule and preventing ad hoc changes. By enabling auditing by default, you can generate an audit record for audit and compliance personnel. Be selective with auditing and ensure that it meets your business compliance needs.
Why Is Auditing Used?
You typically use auditing to perform the following activities:
■Enable accountability for actions. These include actions taken in a particular schema, table, or row, or affecting specific content.
■Deter users (or others, such as intruders) from inappropriate actions based on their accountability.
■Investigate suspicious activity. For example, if a user is deleting data from tables, then a security administrator might decide to audit all connections to the database and all successful and unsuccessful deletions of rows from all tables in the database.
■Notify an auditor of the actions of an unauthorized user. For example, an unauthorized user could be changing or deleting data, or the user has more privileges than expected, which can lead to reassessing user authorizations.
■Monitor and gather data about specific database activities. For example, the database administrator can gather statistics about which tables are being updated, how many logical I/Os are performed, or how many concurrent users connect at peak times.
■Detect problems with an authorization or access control implementation. For example, you can create audit policies that you expect will never generate an audit record because the data is protected in other ways. However, if these policies generate audit records, then you will know the other security controls are not properly implemented.
■Address auditing requirements for compliance. Regulations such as the following have common auditing-related requirements:
– Sarbanes-Oxley Act
– Health Insurance Portability and Accountability Act (HIPAA)
Protecting the Database Audit Trail
When auditing for suspicious database activity, you should protect the integrity of the audit trail records to guarantee the accuracy and completeness of the auditing information. Oracle Database writes the database audit trail to the SYS.AUD $ and SYS. FGA_LOG $ tables. Audit records generated as a result of object audit options set for the SYS. AUD $ and SYS.FGA_LOG$ tables can only be deleted from the audit trail by someone who has connected with administrator privileges. Remember that administrators are also audited for unauthorized use.
Other ways to protect the database audit trail are as follows:
■Set the O7_DICTIONARY_ACCESSIBILITY initialization parameter to FALSE
(the default). This way, only users who have the SYSDBA privilege can perform DML actions on the audit data in the SYS.AUD$ and SYS.FGA_LOG$ tables. In a default installation, O7_DICTIONARY_ACCESSIBILITY is set to FALSE.
Activities That Are Always Written to the Standard and Fine-Grained Audit Records
When standard auditing is enabled (that is, you set AUDIT_TRAIL to DB or DB, EXTENDED), Oracle Database audits all data manipulation language (DML) operations, such as INSERT, UPDATE, MERGE, and DELETE on the SYS.AUD$ and SYS.FGA_LOG$ tables by non-SYS users. (It performs this audit even if you have not set audit options for the AUD$ and FGA_LOGS$ tables.) Typically, non-SYS users do not have access to these tables, except if they have been explicitly granted access. If anon-SYS user tampers with the data in the SYS.FGA_LOG$ and SYS.AUD$ tables, then Oracle Database writes an audit record for each action.
Activities That Are Always Audited for All Platforms
Oracle Database always audits certain database-related operations and writes them to the operating system audit files. It includes the actions of any user who is logged in with the SYSDBA or SYSOPER privilege. This is called mandatory auditing. Even if you have enabled the database audit trail (that is, setting the AUDIT_TRAIL parameter to DB), Oracle Database still writes mandatory records to operating system files. By default, the operating system files are in the $ORACLE_HOME/admin/$ORACLE_SID/a dump directory on UNIX systems. On Windows systems, Oracle Database writes this information to the Windows Event Viewer. You can change the location of this directory by setting the AUDIT_FILE_DEST initialization parameter.
Mandatory auditing includes the following operations:
■SYSDBA and SYS OPER logins
Best Practices for Auditing
Follow these best practices guidelines:
■As a general rule, design your auditing strategy to collect the amount of information that you need to meet compliance requirements, but being sure to focus on activities that cause the greatest security concerns. For example, auditing every table in the database is not practical, but auditing table columns that contain sensitive data, such as salaries, is. With both standard and fine-grained auditing, there are mechanisms you can use to design audit policies that focus on specific activities to audit.
■Periodically archive and purge the audit trail data.
Note: If you set the AUDIT_SYSLOG_LEVEL initialization parameter, mandatory actions are written the to the UNIX sys log.
Fine-grained auditing enables you to create policies that define specific conditions that must take place for the audit to occur. This enables you to monitor data access based on content. It provides granular auditing of queries, and INSERT, UPDATE, and DELETE operations. For example, a central tax authority must track access to tax returns to guard against employee snooping, with enough detail to determine what data was accessed. It is not enough to know that SELECT privilege was used by a specific user on a particular table. Fine-grained auditing provides this deeper functionality.
In general, fine-grained audit policies are based on simple, user-defined SQL predicates on table objects as conditions for selective auditing. During fetching, whenever policy conditions are met for a row, the query is audited.
You can use fine-grained auditing to audit the following types of actions:
■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
■Selecting or updating a table column
■Modifying a value in a table column
Fine-grained auditing records are stored in the SYS.FGA_LOG$ table. To find the records have been generated for the audit policies that are in effect, you can query the DBA_FGA_AUDIT_TRAIL view. The DBA_COMMON_AUDIT_TRAIL view combines both standard and fine-grained audit log records. In addition, you can use the V$XML_AUDIT_TRAIL view to find fine-grained audit records that were written in XML formatted files.
Advantages of Fine-Grained Auditing
Fine-grained auditing creates a more meaningful audit trail, one that includes only very specific actions that you want to audit. It excludes unnecessary information that occurs if each table access was recorded. Fine-grained auditing has the following advantages over standard auditing:
■It performs a Boolean condition check. If the Boolean condition you specify is met, for example, a table being accessed on a Saturday, then the audit takes place.
■It captures the SQL that triggered the audit. You can capture both the SQL statement that caused the audit, and any associated bind variables. Be aware that you can only capture data from scalar column types. You cannot capture data from object columns, LOBs, or user-defined column types. For example, suppose you have the following query:
SELECT NAME FROM EMPLOYEE WHERE SSN = :1
If :1is of integer type and the value for SSN is 987654321, then the audit trail can capture this information. However, the audit trail cannot capture this informationif:1 is a BLOB, CLOB, object, or user-defined type. This feature is available if you create the fine grained audit policy with the audit _trail parameter of the DBMS_FGA.ADD_POLICY PL/SQL procedure to DB+EXTENDED or XML+EXTENDED.
■It adds extra protection to sensitive columns. You can audit specific relevant columns that may hold sensitive information, such as salaries or Social Security numbers.
■It provides an event handler feature. For example, you can write a function that sends an e-mail alert to a security administrator when an audited column that should not be changed at midnight is updated.
■You do not need to set initialization parameters to enable fine-grained auditing.
Instead of setting initialization parameters such as AUDIT_TRAIL, you use the DBMS_FGA PL/SQL package to add and remove fine-grained auditing policies as necessary applying them to the specific operations or objects you want to monitor.
What Permissions Are Needed to Create a Fine-Grained Audit Policy?
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.
■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).
Creating an Audit Trail for Fine-Grained Audit Records
You designate the audit trail format for fine-grained auditing by setting the audit _trail parameter for the DBMS_FGA.ADD_POLICY policy (not to be confused with the UDIT_TRAIL initialization parameter) when you create the audit policy. Setting this parameter to XML or XML+EXTENDED writes the records to the operating system files in XML format. If you prefer to write the fine-grained audit records to the SYS.FGA_LOG$ table, then set the audit_ trail parameter for the DBMS _FGA. ADD_POLICY parameter to DB or DB+EXTENDED. You can use the V$XML_AUDIT_TRAIL data dictionary view to make audit records from XML files available to DBAs through a 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. Because the audit XML files are stored in files with the .xml extension on all plat forms, the dynamic view presents audit information similarly on all platforms.
Using the DBMS_FGA Package to Manage Fine-Grained Audit Policies
To manage a fine-grained audit policy, you use the DBMS_FGA PL/SQL package. This package enables you to add all combinations of SELECT, INSERT, UPDATE, and DELETE statements to one policy. You also can audit MERGE statements, by auditing the underlying actions of INSERT and UPDATE. To audit MERGE statements, configure fine-grained access on the INSERT and UPDATE statements. Only one record is generated for each policy for successful MERGE operations. To administer fine-grained audit policies, you must have the EXECUTE privilege on the DBMS_FGA package.
The audit policy is bound to the table for which you created it. This simplifies the management of audit policies because the policy only must 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 fine-grained 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.
Creating a Fine-Grained Audit Policy
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.
The syntax for the ADD_POLICY procedure is:
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. 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.
■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. If the procedure fails with an exception, then the user SQL statement fails as well.
■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$.
■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.
Example: Using DBMS_FGA.ADD_POLICY to Create a Fine-Grained Audit Policy
object_schema => ‘HR’,
object_name => ‘EMPLOYEES’,
policy_name => ‘chk_hr_employees’,
policy_owner => ‘SEC_MGR’,
Enable => TRUE,
statement_types => ‘INSERT, UPDATE, SELECT, DELETE’,
audit_trail => DBMS_FGA.DB);