Sunday, December 14, 2008

why auditing

In general it seems that security is one of the least important routines of the dba. But in my opinion this issue should be one of the most important issues. Not only be able to supply audit records to firm-wide audit documentations but also knowing what is going on our databases to be sure everything goes straight. Auditing is not only blaming someone for a wrong action. This routine is for early clearing up for the wrong events.
I start auditing with "db, extended" AUDIT_TRAIL parameter to track the sql statements with the actions by altering the system, restarting the instance is a must as this parameter cannot be changed online:

SQL> alter system set audit_trail="db, extended" scope=spfile;
SQL> shutdown immediate;
SQL> startup;

After enabling the auditing, i decided what actions should be audited. Here i was not very selective, i simply enabled all DML actions for the individual users.

AUDIT 
  INSERT ANY TABLE, 
  DELETE ANY TABLE, 
  UPDATE ANY TABLE 
BY "username" ;

AUDIT 
  INSERT TABLE, 
  DELETE TABLE, 
  UPDATE TABLE 
BY "username" ;

AUDIT 
  DROP ANY TABLE, 
  DROP ANY INDEX, 
  DROP ANY VIEW, 
  DROP ANY SYNONYM, 
  DROP ANY PROCEDURE, 
  DROP ANY SEQUENCE, 
  DROP ANY TRIGGER, 
  DROP ANY TYPE 
BY "username" ;

AUDIT 
  ALTER ANY TABLE, 
  ALTER ANY INDEX, 
  ALTER ANY PROCEDURE, 
  ALTER ANY SEQUENCE, 
  ALTER ANY TRIGGER, 
  ALTER ANY TYPE 
BY "username" ;

AUDIT 
  CREATE ANY TABLE, 
  DROP ANY INDEX, 
  DROP ANY VIEW, 
  DROP ANY SYNONYM, 
  DROP ANY PROCEDURE, 
  DROP ANY SEQUENCE, 
  DROP ANY TRIGGER, 
  DROP ANY TYPE 
BY "username";

Move sys.aud$ table to data tablespace: We dont want to oversize the SYSTEM tablespace. As far as i read from oracle documentation moving AUD$ table to another tablespace is not supported. Instead of this action writing some control and maintenance scripts are suggested to be used.

select * from sys.AUD$;
alter table sys.AUD$ move tablespace PQ_DATA;

List the auditing privileges and users: To check what is being audited by my database.
select * from dba_priv_audit_opts;
After two weeks of auditing, i nearly captured more than three commonly wrong routines especially using wrong tablespaces and deleting routines.


No comments:

Post a Comment