Tuesday, May 24, 2011

Auditing the Oracle DBMS for executed SQL statements


  • The issue is this:


We got ourselves into deep trouble in some data-migration project in which an application generates a very important report and makes insertions into several tables, in a  very ill designed database . The application in question is very bad written and documented as well for that matter (I'm talking about the first version of both database and application guys...please don't get upset =-D)  , and the people operating over it weren't sure about its information sources (as far as I understand it's no longer the case).

  • So what's the easiest way to visualize what tables and what fields are being affected and queried, without looking at the code, so we can migrate only the important data being affected and read by this application?


I proposed one approach for this issue: auditing the SQL statements executed by the application.

Let's follow the steps described in here (in a summarized fashion):

http://www.oracle-base.com/articles/10g/Auditing_10gR2.php

Log in as sysdba, with something like:

sqlplus "/ as sysdba"

from an account with proper credentials.

  1. Enable auditing

  2. Select an existing a user to audit: SQL-> AUDIT ALL BY ichbinrene BY access.


This audits everything including DDL (create table), DML (inserts, updates, deletes) and login/logoff events.

3. Querying the dba_audit_trail table, to monitor this user's activity.

Obviously, there's so much more to this, (for example: how the hell do I clear this audit log?) but given that is kind of late (and I'm kind of tired) , I'll refer the reader to the link provided above.

Cheers!

No comments:

Post a Comment