Oracle Fine Grained Auditing Configuration

Why Oracle Fine Grained Auditing

Most critical database systems contain sensitive data that has compliance or regulatory requirements attached to storage and access of that data. Even if the data does not fall into this category, there can be very strong reasons to ensure access to certain data is sufficiently audited. Oracle auditing of sensitive data can be accomplished with Oracle Fine Grained Auditing. Oracle FGA provides the capability to generate rich audit trail for specific objects or columns in the database.

Oracle FGA is an important tool when hardening your database platform, enabling you to see exactly how your sensitive data is being accessed. This not only has compliance benefits but can assist in validating that your security policies are implemented correctly.

We cover in this article what you need to do to get started with Oracle Fine Grained Auditing along with some practical tips to get the best value out of your audit data.

Before You Start

To implement Oracle Fine Grained Auditing effectively several questions need to be answered first:

• What data needs to be audited?
• What access is regarded as “trusted”?
• Does audit need to be enabled for “trusted” access?
• What needs to happen when “untrusted” access occurs?
• How can I enrich the value of the audit data?

We also recommend that the DB audit trail is set to write to the database and the audit tables are located into their own tablespaces and not in the default SYSAUX tablespace before enabling FGA.

Why is this important?

Answering the above questions will help increase the value of your Oracle auditing by limiting the volume of audit data generated. Oracle FGA allows for exclusion rules to be implemented where the audit record will not be generated, if certain conditions are met. We can setup exclusion rules on a wide range of criteria based on client hostnames, operating system accounts, application module names to name a few. The exclusion rules are implemented as PL/SQL code and attached to the fine grained audit policies.

The types of exclusion rules will vary largely depending on your goals and compliance requirements. Compliance requirements in a PCIDSS environment require all access to credit card data is audited regardless of whether the access is “trusted” or not. If you are auditing data for internal purposes only, supplier bank account detail changes etc, then you can look to exclude “trusted” access to the data.

In this context, multiple conditions must be met before access is regarded as trusted. For example we have the following rules in place for one audit policy:

if application_server_name in 'trusted_list'
and
user_client_id = authenticated_application_name
and
os_username = trusted_os_account
and
program_module_name = known_application_program_name
then
  exclude audit
end

Iterative Process

Implementation of Oracle Fine Grained Auditing is an iterative process. After the audit is enabled the audit data needs to be reviewed to check that the exclusion rules are being correctly applied. There may be additional exclusion rules required once the live audit trail is analysed. This can be iterative until you have tuned down to a validated working set of audit data.

Keep in mind that Oracle Fine Grained Auditing has the potential to generate huge volumes of audit data if not correctly implemented. This can add unnecessary overhead by auditing actions that do not need to be audited. Large volumes of audit data can introduce problems when it comes to managing the database space consumed by the audit trail tables.

Implementation Steps

Define & Implement Audit Policy

Owner: HR
Object Name: PER_ALL_PEOPLE_F
Statement Types: SELECT, INSERT, UPDATE, DELETE
Exclusions: YES
Handler Function: AUDIT_EXCLUSION

This is going to be implemented as:

begin
  DBMS_FGA.ADD_POLICY(
    OBJECT_SCHEMA =>'HR',
    object_name =>'PER_ALL_PEOPLE_F',
    policy_name =>'PAPF_FGA_SIDU_POL_1',
    audit_condition=>'AUDIT_EXCLUSION=1',
    statement_types =>'select,insert,delete,update');
end;

The function used for the exclusion rules is implemented as follows:

create or replace NONEDITIONABLE FUNCTION audit_exclusion RETURN NUMBER AS

    lhost     VARCHAR2(32);
    lclient   VARCHAR2(32);
    losuser   VARCHAR2(32);
    lmodule   VARCHAR2(64);
BEGIN
    lhost := sys_context('userenv', 'host');
    lclient := sys_context('userenv', 'client_identifier');
    losuser := sys_context('userenv', 'os_user');
    lmodule := sys_context('userenv', 'module');
    IF lhost = 'trusted_host_name' AND lclient IN (
        'TRUSTED_USER1','TRUSTED_USER2'
    ) AND losuser = 'trusted_os_user' THEN
        RETURN 0;
    ELSIF lhost = 'trusted_host_name' AND losuser = 'TRUSTED_USER1' AND lmodule IN (
        'trusted_program_name1',
        'trusted_program_name2'
    ) THEN
        RETURN 0;
    ELSE
        RETURN 1;
    END IF;

END;

Now enable the audit policy:

begin
  DBMS_FGA.ENABLE_POLICY(
    object_schema  => 'HR',
    object_name    => 'PER_ALL_PEOPLE_F',
    policy_name    => 'PAPF_FGA_SIDU_POL_1',
    enable         => TRUE);
end;

Review Audit Data

Once you have representative workload on the system for the audited objects the audit trail will be written into the DBA_FGA_AUDIT_TRAIL or UNIFIED_AUDIT_TRAIL views.

You can use the below SQL as a start to identifying audit data patterns in your system. Once the patterns are identified and understood you can review the exclusion policies to further refine the audit data collected.

select trunc(timestamp), os_user, object_schema, object_name, policy_name, client_id, count(*) from dba_fga_audit_trail group by trunc(timestamp), os_user, object_schema, object_name, policy_name, client_id order by trunc(timestamp);

Manage the Audit Data

To avoid excessive space consumption in the initial phases of your implementation, you want to ensure appropriate procedures are in place to periodically purge the audit data. The frequency of the purge will be dependent on the volume of audit data generated along with the available storage capacity.

Keep in mind that the easiest solution to resize the audit tablespace is the DBMS_AUDIT_MGMT.set_audit_trail_location procedure. This does require a tablespace to be created that is equal to the size of the current audit tablespace in order to run. Do not let your audit tables and tablespaces become too big – it may require a large amount of storage to release the unused tablespace.

If you need to retain the audit data for compliance reasons there are some great options available for log management tools that we will cover in a follow-up article.

Actioning Audit Data

Now you have refined audit data being collected you need to make it actionable. Depending on your requirements this could be via a daily report summarising the access, or enabling real-time alerting and monitoring of sensitive data access. Either way, you now have a powerful tool to identify how sensitive data is accessed and be proactive when suspicious access occurs.

Book an appointment to talk to our team today on how we can help you implement and optimise your Oracle auditing.