Re: Is it just me
- From: Lisa Spory <lspory@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 12 Aug 2004 05:45:14 -0700 (PDT)
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@xxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxx
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
