Re: Auditing / tracing Oracle environment...

The Oracle RDBMS includes an audit command that can be used to perform a wide range of basic auditing activities.

I am against auditing everything as this generates too much data and involves a fair amount of overhead for little practical gain.

First, the basic rule that no Id should have more privileges than the Id needs to work should be followed. Second, nothing should run as a DBA priviled ID. Instead applications run with an application Id that normally only has DML privileges.

What I recommend is that the audit command be used to audit all DDL in the production environment. DDL should be fairly rare and I would expect DDL to be a DBA activity. Since developer access to the production environment should be limited to read only except through use of a special break/fix ID. Audit all actions by this ID on a session basis. Use of the break/fix Id should be documented procedurally. The developer will have to request the password for the Id and it will be changed after each use.

Applications should also provide audit trails for key processes. These are usually in the form of history tables or transaction logs.

Finally, some of the most effective security for the database consists of documented, enforced procedures for promoting changes from test to production and for problem resolution and development practices.

So you have development procedures, application security, and database security. All three need to be designed to work together to form a whole.

IMHO -- Mark D Powell -- Received on Wed Feb 16 2005 - 08:58:54 CST