Auditing ALTER SYSTEM commands

Wonder, who is changing the system date? Who is changing the parameter? Who is giving all those ALTER SYSTEM commands which are causing havoc for others?

The simplest solution of the problem is, "DO NOT PROVIDE PASSWORD OF DBA USER TO A PERSON WHO IS NOT A DBA". Looks simple, right? Wrong. In developement environments it is not possible to restrict the access of DBA user to 1 or 2 persons. And it is very difficult to track someone who is giving ALTER SYSTEM commands. Triggers will not work.

These commands can not be stopped, but at least can be monitored. To monitor this, you need to start the AUDITing. Again, this can hit the performance, so use it when it is really really important.

First step is to start auditing for the database. To start auditing, follow these steps:

1. Set parameter audit_trail=db,extended in the spfile.
2. Connect to SYS user and execute the following script
        $ORACLE_HOME/rdbms/admin/cataudit.sql
3. Auditing can be started for a user
        a.) for a particular command he fires (whenever the command is successful or not successful)
            AUDIT ALTER SYSTEM BY abc WHENEVER SUCCESSFUL;
            AUDIT ALTER SYSTEM BY abc WHENEVER NOT SUCCESSFUL;
        b.) for ALL the commands fired by him
            AUDIT ALL BY abc BY ACCESS;
4. shutdown the database
5. startup the database

That's it. Now you can monitor any command fired by the user. Details can be checked in DBA_AUDIT_STATEMENT view. This view will give you the user name, operating system user name, host name, terminal, timestamp and SQL text with all the other information.

So folks, try it and let me know how many users you have caught red handed when they were doing any harm to your database.