18 Jul 19 Part 3: Fine-Grained Auditing is Essential
Fine-Grained Auditing is Essential
You have determined that an audit trail is needed to help you improve your internal controls and monitor change. You have decided what types of change you want to track but have you considered fine-grained auditing?
One approach to auditing might be to attempt to audit everything; by everything I mean all tables, all columns and all rows; sure, this would give you a full record of everything that has happened, but this is the wrong approach for many reasons…
- Impractical – Oracle EBS has over 24,000 underlying tables
- Data growth – the resulting audit trail will likely end up larger than your actual database
- Performance – you will likely cause performance issues across the entire system
- Ineffective – the audit trail will be less effective because it will not be easy to use due to the volume of data
Based only on the 4 reasons above, auditing everything is pretty much impossible, but more to the point, doing so, is pointless.
What is Fine-Grained Auditing?
Being fine-grained with auditing simply means that you are being “selective” in terms of what types of change is tracked and thus what goes into the audit trail.
There are essentially 4 steps to being fine-grained…
1. Table Selection
2. Column Selection
3. Row Selection
Here is a brief overview of each of the above 4 steps…
Step 1 – Table Selection
This happens automatically to some degree; this is because most people when considering what to audit are already being selective with what must be included in the audit trail when defining the audit requirements.
For example, the requirements may say that the audit trail should include all changes to key configurations and master data…etc; so already you are being fine-grained by choosing not to attempt to audit everything.
Step 2 – Column Selection
Consider a table in the database that you want to audit has 180 columns, do you really need all 180 columns in your audit trail? Is every column so important that you need a full history of change for it?
The chances are that you probably don’t need all 180 columns and in fact, it may only be a handful that are actually needed to satisfy requirements; if this is the case then there is no point tracking all 180 columns and instead, you should just track only those columns that are required to meet the needs of the business. Here are a few examples of the types of data you might not need to track…
- Date tracking – this kind of information will (or rather should) be recorded automatically by your audit solution anyway so it makes little sense to audit these types of values
- Internal ID’s – Many columns on many tables are internal ID’s that on their own are meaningless; often it is better to pull in additional meta-data to describe these internal ID’s and omit the ID itself (we cover this more in the next article in the series).
- Unused Flexfields – Many EBS tables include generic columns that can be used as Flexfields; there is no point tracking an unused Flexfield column.
Beyond those listed above, check to make sure that any column do you include in the audit trail is useful in some way; does the business need a before and after picture of all change for the column? If not, don’t include it.
But you should be careful not to overdo it; not including enough data is as bad (possibly worse) than including too much.
Step 3 – Row Selection
The next step is to consider if you need all rows in a particular table; consider a table that stores information about Profile Options in Oracle EBS, there maybe be many thousands of rows for many thousands of different Profile Options, do you really need to track change to them all?
Maybe you are auditing a table that stores supplier information, do you need to audit all supplier rows? Perhaps the requirement is to only audit a supplier once it has been enabled.
Another example of being selective with the rows might be that you only need to audit certain type of transaction in exceptional circumstance, i.e. an invoice above a certain value.
This type of selectivity is typically dependant on a column value on the table being audited and can go a long way to reducing the volume of data that goes into the audit trail.
Step 4 – Context-Driven
Step 4 involves determining if you should be auditing only under certain conditions or within a given context.
A few examples of this might be…
- Monitor change outside of normal working hours
- Audit something when done outside of an expected responsibility
- Track change by a specific list of users or by anyone other than an expected list of user
Often when tracking change, you may be more interested in change you did not expect to see.
Only Audit What You Need
So, the golden rule when building an audit trail is to “only audit what you need”; you shouldn’t adopt a “lets audit this just in case” approach and so some thought as to exactly what is needed to satisfy the audit requirements should be given; as the title suggests, fine-grained auditing is essential when building an effective audit trail.
Want to know how we can help you build an effective, fine-grained audit trail for Oracle EBS? Get in touch today and ask us about CS*Audit.
Next Time & Early Access
The next article in this series is “Meta What? Metadata is What“.
If you want to get access to these articles before anyone else, please subscribe to our newsletter.