Link to home
Start Free TrialLog in
Avatar of NaderYacoub

asked on

Oracle Auditing: need sql statement along with user in sys.dba_audit_trail

The title pretty much says it all...

I want to be able to see the sql statement that the particular user ran along with the regular audit info.
Avatar of pettmans
Flag of Australia image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of peterside7

You can't because you don't have the where clause and the columns in the audit table.

You have this info in the sga, in

SQL> desc v$sqlarea
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -----------------
 SQL_TEXT                                                                          VARCHAR2(1000)

But it's impossible to join these 2 tables with the session id because it's NOT in  v$sqlarea.

Best thing is to audit SELECT, INSERT, DELETE, UPDATE on major or all objects (big overhead)
and or columns.

But auditing with oracle the syntax of a statement is NOT there yet.
if you can capture the sql-statement including all used bind variables if you are using oracle 9i or 10g.
you can query this information from dba_fga_audit_trail. "sql_text" and "sql_bind" contain the information you are looking for.
the tables you audit must be analyzed. to implement fine grained auditing you need to use the DBMS_FGA package (as mentioned above)

there are some discussions aboute fine grained auditing on

(if found this thread searching for "dbms_fga")

Avatar of NaderYacoub



The solution I was looking at was to put an insert trigger on the table of V$sqltext. This is your first suggestion. I was just wondering if there was a better way. Do you think this is the best solution?

P.S. I am running on Oracle 7.3... I know, it's old. I have never messed with Oracle until I got this job. Thank God, it's only a fraction of what I do. The rest is on SQL Server 2000.
Well my first suggestion was to put a trigger on the audit trail (eg sys.dba_audit_trail). I'm not sure if you can put a trigger on a in-memory view such as V$sqltext. Even if you could,  I don't think it's a good idea. The result would be that every SQL statement executed would be logged. That would include the SQL that extracts the sqltext when the trigger fires. Can you see how that results in infinite recursion? A better approach would be to use a scheduled job to select all data from the v$sqltext on a regular basis.

If you want to capture all sql then I think you should check use of sql trace flags to log the detail to a text file.

As for your version of Oracle... upgrade!!!. From what I've read (haven't used it myself), the fine grained auditing package would meet many of your requirements but you need to move forward to take advantage of it. Plus there are many other advantages to running an up to date version including the level of support available, performance, ease of management, etc.

Also it may be time to step back and review your requirements. What exactly do you want to capture and how do you plan to use it? Owing the overheads associated with Oracle audit trails, you need to aim to minimise the capture of data while still meeting your needs.

I went back and reread the question. You say you want to capture the SQL from a particular person. Is that a specific particular person or just the level of detail? Ie do you you want to track user with usename X or do you want to report at the username level for all users? Can we do that without capturing absolutely everything?
Do you want to capture all activity or is it only particular types of activities against specfic tables?

Narrowing down your requirements can significantly reduce the overhead.

If you are simply tracking one user, then if the user works from a particluar location, you can turn tracing on at the client end and then grab the logfiles from the client workstation.
If the user works from a variety of locations but always on the LAN, then you could include the enable of tracing in the network login for that user.

If you need to trace SQL after the fact. (What did user X do last week?) then, you need to:
- upgrade to 9i,
- enable archive logging
- use log minor to query the transaction logs.

pettmans... the requirements are such:

I need to know everything our DBA does. This means what SQL statement he ran and when. This is for auditing purposes to prove that our DBA is not acting in any malicious way. So I want to see every sql statement he ran and when.

- Nader Yacoub
Nothing can be done for this in 9i.

That is why 10g has the new DBA AUDIT FEATURE.

If you are trying to track your DBA's activities then you have very little you can achieve using your current version of Oracle. For most versions of oracle, activity by any user with sysdba privileges is unaudited. Earlier this year I assisted a customer in meeting Sarbanes Oxley audit requirements and came up against this issue. Oracle  have recognised this gaping hole in their audit capability and "trusted users" can now be audited as from Oracle 9i release 2.

Even then there are the difficulties previously discussed in linking an audited action to a particular SQL statement.

However, also available with 9i is LogMiner. This is a utility that allows the archive logs to be queried and it can be used to show all logged DML/DDL for a particular user. Even that has holes though as your DBA can potentially make changes with logging disabled.

You can require your DBA to turn on SQL tracing for all of his/her work, but once again, the DBA could disable this feature if intending to do harm.

As peterside7 mentions, 10G further increases the audit capability. In particular the flashback facility provides enhanced capabilty to view SQL that has been executed. I haven't used 10G yet so I'm not going to offer any detailed info on that.

Scott Pettman
pettman... I am sorry... I meant our DBA who has an account that is actually showing up in sysaudit. I did not the mean the oracle dba account.