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 audited.
Oracle Fine Grained Auditing provides the capability to generate rich audit data for who, how and what access takes place to specific data. 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.
This capability can also generate huge volumes of audit data that can quickly negate any benefits of collecting the audit data in the first place.
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 are these important?
Answering the above questions will help increase the value of the audit data by limiting the fine grained audit data generated. Oracle Fine Grained Auditing 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
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 live audit data is generated and can be 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 setup. This not only adds unnecessary overhead but can introduce problems when it comes to managing the database space consumed by the audit data.
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);
Implement Audit Management
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.