Audit log records in database??
The poster wanted IDS audit event logs stored in a database table rather than flat files, so they could report on them with SELECTs. Suggestions: use triggers to capture old/new values (impractical for 500+ tables, and useless against DBA actions), the 11.x SQL tracing option, a third-party tool (Lintel InfoTrace), and a developerWorks article on trigger-based auditing. The main answer was the bundled onshowaudit utility, which converts audit logs into pipe-delimited unload files loadable into a documented table schema. Caveats raised: audited inserts into the audit table can snowball, and DBAs/DBSAs shouldn't have access to audit data, so a separate database is advisable. Leffler noted auditing improvements were planned for a future release.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I want to store audit event logs in a database table instead of file system.
This will help me to manage better way and simple to generate reports using
select statements.
Is there any way using onaudit utility or may be some other way to do?
Advice appreciated.. thanks in advance.
DJ...
Hello.
Onaudit won´t give you much informatios, like "old value, and new value
= update of course".
If you need this kind of informations auditted, just make triggers ;)
Regards.
Alexandre Marini
Tecnologia da Informação - DBA
SEFAZ-MS / SGI-UIMP / Sistemas IBM-Informix
IIUG Member
<http://www.iiug.org>;
DHANANJAY KUMAR escreveu:
> I want to store audit event logs in a database table instead of file system.
> This will help me to manage better way and simple to generate reports using
> select statements.>
> Is there any way using onaudit utility or may be some other way to do?
>
> Advice appreciated.. thanks in advance.
>
> DJ...
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
Thanks Marini for the reply. Creating trigger for more than 500 tables and maintaining would be the last choice to do this. What i am considering is that, write a script to load audit logs (records) into a table periodically through the cron job. It will be nice for next IDS release if there is a choice to store audit logs in a database table or in a file system. Someone convey this to IBM..:-) Thanks. DJ...
You could even try the new 11.X sql trace option, but that´s more specific to users, or databases tracing ok? Besides, you didn´t mention what´s your engine version.... Att. Alexandre Marini Tecnologia da Informação - DBA SEFAZ-MS / SGI-UIMP / Sistemas IBM-Informix IIUG Member <http://www.iiug.org>; DHANANJAY KUMAR escreveu: > Thanks Marini for the reply. > > Creating trigger for more than 500 tables and maintaining would be the last > choice to do this. What i am considering is that, write a script to load audit > logs (records) into a table periodically through the cron job. > > It will be nice for next IDS release if there is a choice to store audit logs > in a database table or in a file system. Someone convey this to IBM..:-) > > Thanks. > DJ... > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > >
Hi
Not sure what events you want to audit, but have a look at
www.lintel.co.uk/infotrace. This product will audit updates, inserts and
deletes on logged tables and puts the information into database tables. Let
me know if you would like a trial version.
Regards
David Linthwaite
Lintel Software Consultancy Ltd
IBM Business Partner
Tel: 01244 357250
Fax: 01244 357248
mailto:dlinthwaite@lintel.co.uk
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On
> Behalf Of DHANANJAY KUMAR
> Sent: 05 November 2009 09:24
> To: ids@iiug.org
> Subject: Audit log records in database?? [17937]
>
> I want to store audit event logs in a database table instead
> of file system.
> This will help me to manage better way and simple to generate
> reports using select statements.
>
> Is there any way using onaudit utility or may be some other
> way to do?
>
> Advice appreciated.. thanks in advance.
>
> DJ...
>
>
> **************************************************************
> *****************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
On Thu, Nov 5, 2009 at 03:51, DHANANJAY KUMAR <dhananjay_kumar2k@yahoo.com>wrote: > Thanks Marini for the reply. > > Creating trigger for more than 500 tables and maintaining would be the last > choice to do this. What i am considering is that, write a script to load > audit > logs (records) into a table periodically through the cron job. > > It will be nice for next IDS release if there is a choice to store audit > logs > in a database table or in a file system. Someone convey this to IBM..:-) > > One issue is that the inserts to the audit table would be audited, thus triggering an automatic overflow of all the space in your system. Also, in general, inserting the records into the current database is not likely to be acceptable to the auditors. There are plans afoot to improve auditing in the next release of IDS. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/ "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." NB: Please do not use this email for correspondence. I don't necessarily read it every week, even. Charles de Gaulle<http://www.brainyquote.com/quotes/authors/c/charles_de_gaulle.html>; - "The better I get to know men, the more I find myself loving dogs." --000e0cd0ea9a9d6bbd0477b72cd2
Check this Article... is a bit old (for IDS 9.40), but I believed still valid... http://www.ibm.com/developerworks/data/library/techarticle/dm-0410roy/index.html
onshowaudit
This utiliity is provided with the engine and will transform the audit logs
into a pipe separated file (just like a normal unload file).
Please check the documentation. The table schema you'll need to create is
there as the instructions to use onshowaudit (if I recall correctly there
was a doc bug on the table schema, but I'm not sure, and it was on old
versions...)
Triggers will not work for DBAs...
Insert into the database, while not impossible creates a problem (DBAs tendto have access to it).
Of course it would be possible to use another database with different
privileges... But the DBAs and DBSAs should not have access to the audit
logs!
The article from Jacque Roy that Alexandre provided is very interesting in
case you want to use triggers. It will possibly save you a lot of work.
Regards.
On Thu, Nov 5, 2009 at 9:24 AM, DHANANJAY KUMAR <dhananjay_kumar2k@yahoo.com
> wrote:
> I want to store audit event logs in a database table instead of file
> system.
> This will help me to manage better way and simple to generate reports using
> select statements.>
> Is there any way using onaudit utility or may be some other way to do?
>
> Advice appreciated.. thanks in advance.
>
> DJ...
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--000e0cd58ed02366410477bc1567