Thanks for the question, Mette.
Asked: September 03, 2020 - 7:31 am UTC
Last updated: September 04, 2020 - 6:05 am UTC
Version: 9.2.0.6
Viewed 1000+ times
You Asked
Hi Tom,
My case is, that a client (in the top 10 of biggest organisations in Denmark) need to audit the usage of database-links in their Oracle 9-database in a AIX-environment.
More specific they want to know which database links are in use and what SQL is executed through the links. Also who is executing the code at what time is of interest.
AS we in Oracle 9 does not have SQL executed in comment$text in the aud$-view when auditing, I think a alternative way could be:
1) identifying the codeobjects (procedures/packages) using dblinks
2)scan the v$sqlarea for these codeobjects over time.
That method has serious limitations, among other things it lacks of possibilty of identifying codeobjects that is not stored in the database but resides in javascripts, c++, python, etc.
Could you suggest or recommend another approach it would be most welcome.
Thanks,
Best regards
Mette Evert-Nielsen
and Connor said...
A connection to the database from a database link is in essence a connection from a client, ie, the server sees that request as a client coming in from a remote node.
If you audit connections with AUDIT SESSION, then you'll capture the host in DBA_AUDIT_TRAIL - that tells you from *where* and with what username the connections are coming from.
To track every SQL that is then issued, I would have database links come in on a dedicate username, and then simply audit every query from that username with
AUDIT SELECT ANY TABLE BY [username] BY ACCESS;
Is this answer out of date? If it is, please let us know via a Comment