Thursday, March 13, 2008

Your own DB 'Big Brother'

How many times you have been in the unfortunate situation where something catastrophic has happened, caused by an OSI 'Layer-8' error, you've been asked whom did that... and you don't have a clue.

Well, for those cases Oracle provides a very useful auditing feature, that is very simple to enable.

Usually you start enabling auditing on users that have the privilege to do the activity you are interested on, after you may enable auditing on users suspicious of trying to do something they don't have the privilege to.

Let's give a short example, given the case I want to track who drops or modifies any user.

SQL> alter system set audit_trail=DB scope=spfile;

or edit your pfile to reflect above setting
SQL> shutdown
SQL> startup
SQL> audit drop user by admin;

SQL> audit alter user by admin;

SQL> alter user scott identified by tigre;
User Altered
SQL> select count(*) sys.aud$;

How do you check what is fasible to audit or what is beeing audited, you must check these views:

If those views aren't available then you must create them running the cataudit.sql script from $ORACLE_HOME/rdbms/admin.

Further explanation you may find in the Oracle Database Administration Guide.

Subscribe to Oracle Database Disected by Email

No comments:

Custom Search