We are looking at solution alternatives for a 'customized auditing' requirement, whereby we with to log GRANT/REVOKE statements made. In addition to the columns that out-of-box Oracle auditing would provide through SYS.AUD$, and its associated views (eg: DBA_AUDIT_TRAIL),
we wish to record 2 additional 'custom' attributes -
(1) TicketID and
(2) BusinessReason for making the privilege change.
Our front-end interface that issues the GRANTS and REVOKES provides for collecting the custom attribute values, which we currently 'store' by setting public property values in a package.
Our initial idea was to write an After Insert trigger on SYS.AUD$, but ORA-04089 disallows this, and for good reason !!
We're now considering writing 'AFTER GRANT ON DATABASE' and 'AFTER REVOKE ON DATABASE' database event triggers. This certainly can be done. If possible, in addition to our TicketID and BusinessReason custom attributes, we'd like to record whatever columns are needed to allow for 'linking to' the associated
SYS.AUD$ row that is written if appopriate Oracle auditing is turned on. In
this way, we would have access to the detailed attributes captured in SYS.AUD$.
There are 2 fundamental questions:
1. Would the associated SYS.AUD$ rows be in the table at the time our database
event triggers are being performed ?
2. What set of columns from SYS.AUD$ would support the 'linking' capability ?
Your front-end interface could set the session client_identifier, that would get recorded in the Oracle audit trail
I would think your app would have to record the app info in it's own table, and use the client_identifier as the link between Oracle audit trail and app audit trail
Oracle audit entries are autonomous - they get recorded even if the base transaction rolls back...
"False data can act only as a distraction. Therefore. I shall refuse to perceive you." - Bomb #20
Click Here to Expand Forum to Full Width