Re: Auditing...

Triggers are indeed the answer, but be careful of using auditting everywhere. If every insert, update or delete is going to result in an additional write operation, then please, please, please think about performance (begged the DBA!). An audit table is bound to have at least a primary key and probably two or three other indexes. In no time at all it becomes one of the largest (if not THE largest) table in your database. Updating indexes does have a noticeable effect on performance, especially if every damn user is doing it for every transaction!

I'll bet you haven't sized this table, you haven't taken it into account when working out transaction rates, you haven't thought about it when considering back up times - yet it will be the largest table in your database unless you consider its use very carefully. You probably need a bigger RS6000 already! Certainly more disk space and a bit of thought as to i/o balancing, since this will be a very "hot" table. You're IBM salesman is probably already daydreaming about where he's going to spend the commission earned from this piece of design! :-)

Also, how are you going to audit SELECT statements ? Anyone any ideas? Unless you only allow the users to read records via stored procedures (which for database security would need to issue an immediate COMMIT of the audit table update, which might severely complicate the idea of COMMIT units) I don't see how you could audit SELECTS. You could do it in the application if you could swear users were never, ever going to be able to access the database by any other method, e.g. SQL*PLUS, ODBC etc...

Dave O'Keeffe

Phil Hoggins wrote:
>
> You can achive this by having triggers on the table in question. Triggers
> will allow you to manage user access on tables by logging the actions to
> another table. You can record before and after views of the data that is
> subject to an update and much more.
>
> Phil
>
> Jimmy <c6635500_at_comp.polyu.edu.hk> wrote in article
> <353D4FAE.72F0_at_comp.polyu.edu.hk>...
> > Hello all,
> >
> > Is it possible to record who access table A in Oracle server? The
> > access include select, update, delete and insert. I know that Oracle
> > server can do audit in table level. Is it possible to audit in record
> > level? i.e. to record which record in table A has been accessed?
> >
> > Thanks,
> > Jimmy
> >

--
For email replys remove "n0spam." from the above address Received on Tue Apr 21 1998 - 15:57:05 CDT