25 Jul 19 Part 4: Audit Trails – Why You Need Metadata
Audit Trails – Why You Need Metadata
What exactly is metadata?
Metadata is simply some data that acts as a means of describing some other data.
Is this important in the context of building an audit trail?
Why Metadata is Important
Metadata is extremely important when it comes to building an audit trail; let me explain by way of an example…
We have an audit requirement to capture change for all supplier bank accounts. We use our audit tool to define an audit policy that captures inserts, updates, and deletes on the table in the database that stores supplier bank accounts. Now, whenever a supplier bank account is created, changed or removed, we have a record of it in our audit trail.
Great! We are done, right?
Data Referencing Data
Oracle EBS is a very large and complex suite of enterprise applications with 10’s of thousands of underlying tables where the data is stored. As with all well designed relational databases, Oracle EBS has been extensively normalized to reduce data redundancy and improve data integrity.
What this means is that much of the data in a given table is simply a reference to some data in another table.
For example, on our supplier bank account table, there is no supplier name or bank name but instead, there are just some internal ID’s that reference this information elsewhere.
Here is a simple example of a supplier bank account row (this is not how the supplier bank account table looks in EBS)…
[table id=1 /]
If a bank account was created for supplier ACME Corp at Big Bucks Bank then we might have a row inserted into the supplier bank account table that looks like this…
[table id=2 /]
Now imagine you are looking back at the above data in the audit trail, sure you can see that a new account was created but it shows the supplier as 15010 and the bank as 2396; not very helpful; in fact, close to useless really.
Metadata to the Rescue
What is needed here is metadata.
In our example, instead of seeing the internal ID’s for supplier and bank what we really need to see if the supplier name and bank name. This is achieved by looking on the supplier and bank tables to pull in this information when needed.
So here is how our example might look once we bring the metadata into the picture…
[table id=3 /]
Capture Metadata at the Right Time
Some audit solutions will pull in this information at the time you “report on the audit trail” and whilst this will, for the most part, be good enough, it is not the best approach to take because between the time the change was captured in the audit trail and the time you report on the audit trail, something may have changed, i.e. the supplier or bank name may have changed.
So, the best solution is to ensure that the appropriate metadata is pulled into the audit trail at the time that the audit trail entry is stored.
Can your audit solution do this?
Want to know how we can help you build an effective, fine-grained, metadata-driven audit trail for Oracle EBS? Get in touch today and ask us about CS*Audit.
Next Time & Early Access
The final article in this series is “Audit Trails – Its all in the reporting“.
If you want to get access to these articles before anyone else, please subscribe to our newsletter.