Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Is it just me

Re: Is it just me

From: Lisa Spory <lspory_at_yahoo.com>
Date: Thu, 12 Aug 2004 05:45:14 -0700 (PDT)
Message-ID: <20040812124514.97131.qmail@web53203.mail.yahoo.com>


In response to Nuno's reply, on my timestamp Thu, 12 Aug 2004 19:49:34:  

> If all you have to do is log that someone has touched a table,
then maybe default Oracle auditing is enough? No, I need to audit insert, update and delete operations on subset of tables and columns, tracking identity of the user that made the change and the old and new values.  

> Note that Tom seems to indicate only light logging. Not data
audit logging.

Actually, Tom's example is data audit logging, his table structure defines who, table_name, column_name, and new and old values.  

> really don't like the idea of *extensive* logging into a single
table: there is a bottleneck-waiting-to-happen, if I ever heard of 1...

I see your point here, however, can't this be addressed via freelists and initrans settings?  

> If you need more detailed logging, I'd look into fine-grained auditing
in 9i.  

Unfortunately, FGA in 9i only monitors Select statements, support for Insert/Update/Delete is not added until 10g. (We can't upgrade yet)  

> In that case, look at auditing via archived redo logs (log miner)?
And make the customer requesting it pay for the time involved? I mean why pay for the audit overhead ALL the time when it is only going to be looked at ONCE or twice a year? Just pay for it then? Just keep your schema exports and archive logs handy.

Good point, however, I am required to track WHO made the change. Which means at the time of the triggering event I will have to grab the user ID from the application context (presumably set by the middle tier - another problem altogether).  

Thanks for you comments and ideas. I also looked into Workspace Manager, but we have a legacy application that requires that an empty, non-Oracle database schema structure "lay on top" - bottom line I can't rename tables and use views to "trick" the legacy code.  

Regards,  

Lisa Spory
In Humid, Rainy Washington DC

Nuno Souto <dbvision_at_optusnet.com.au> wrote: Lisa Spory apparently said,on my timestamp of 12/08/2004 3:22 AM:

Usual disclaimers apply: YMMV, IMHO, NWMD, NAWHTT, etcetc.

> Justification for a single table:

If all you have to do is log that someone has touched a table, then maybe default Oracle auditing is enough?

More detailed data activity logging (trigger-based) is only needed if you need to keep track of actual data moved into the table(s).

I really don't like the idea of *extensive* logging into a single table: there is a bottleneck-waiting-to-happen, if I ever heard of 1... Note that Tom seems to indicate only light logging. Not data audit logging.

> I can create a configuration table with a list of tables/columns to be audited.
> Based upon changes to this table, I can dynamically generated new triggers.

If you need more detailed logging, I'd look into fine-grained auditing in 9i. It does all that plus much more, and is presumably internalised (translate: fast, lesser locking issues).

> Our system aggressively pushes out new releases, most of which will introduce
>new data elements to be audited (and new columns to already audited tables).
> Therefore, an approach that logs each table history is a custom table will
> impose a huge sustainment burden.

You'd be surprised at how easy fine-grained auditing really is nowadays... and it can be toggled on/off, changed, etc. Word of caution: try before using. I hit a few bugs a while ago in 8ir3 with FG access control and had to use views plus some contrived authorisations to get around obvious breaks in security. The same might apply to FG auditing! I do recall restrictions.

> The reading of the system audit log will be very infrequent (1-2 times a year)
> and only supported through a formal customer request.

In that case, look at auditing via archived redo logs (log miner)? And make the customer requesting it pay for the time involved? I mean why pay for the audit overhead ALL the time when it is only going to be looked at ONCE or twice a year? Just pay for it then? Just keep your schema exports and archive logs handy.

> Thoughts? Comments?

You got them.

> Also - Enjoy a Violet Crumble and some Chicken Twisties for me :-)

NOW you tawkin! :)

-- 
Cheers
Nuno Souto
in sunny Sydney, Australia
dbvision_at_optusnet.com.au
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Thu Aug 12 2004 - 07:46:50 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US