Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Synonym auditing help!

Francisco Saúl Hernández RomeroFeb 7 2025 — edited Feb 7 2025

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!

Comments

SiatGesi Feb 14 2025

THE FIRST problem howto wrap I have resolved…

SiatGesi Feb 14 2025

now I'm trying to move the unzipped files (pictures) from db to AS… my real problem… best regards

1 - 2

Post Details

Added on Feb 7 2025
0 comments
29 views