Hello all!
DB version: 11gr2 SE /12cR2 SE
I'm trying to audit SELECT statements from a schema that its sole purpose is to query information through synonyms.
The purpose of this is to be able to show in my auditing report what statement was used (hoping it'd appear in the SQL_TEXT or SQL_BIND columns of DBA_COMMON_AUDIT_TRAIL).
Since I don't have an Enterprise licence, I can't develop FGA policies; however, I've been managing using standard (traditional) audits.
I ran a couple tests @ a testing instance; created a schema, tables, populated those tables and then created a synonym. Then I queried “ Select * from syn1 ” and my audits report showed correctly the user, the object schema, the object name and then in the SQL_TEXT field, it showed my Select statement.
However, when I tried to replicate this on a productive instance, the SQL_TEXT column is (null) on each row.
I'm querying it as follows:
SELECT DBMS_LOB.SUBSTR(SQL_TEXT, 4000, 1) FROM DBA_COMMON_AUDIT_TRAIL
Searching over internet I've only found responses where it is said that obtaining data from that field while auditing is an exclusive feature of FGA. However I'm still not sure about it since I have done it before (but didn't work when replicating the process).
QUESTION: Does it have anything to do with any configuration issue of my productive instance/schema?
Is there another way to conduct this kind of audits?
Thanks in advance!
Cheers!