Re: [Info-Ingres] Setting up Ingres Security Auditing for query_text
- From: martin.bowes@xxxxxxxxxxxxx
- Date: Mon, 05 Mar 2007 13:04:16 -0000
Hi Steve,
Just a few comments...
Once you have the package installed do the following to
set-up query_text (i.e. inbound SQL) auditing:
1) In cbf > Security > Auditing; set security_auditing to ON
3) In cbf > Security > Auditing; set on_switch_log to the path
of a shell script to archive the audit files,
e.g. /home/bin/on_sw_log.ksh (See sample script below)
I would also suggest settings:
on_error: STOPAUDIT
on_log_full: SUSPEND (This is for IIR3 and Ingres2006)
These two settings should prevent the DBMS shutting down - and
refusing to restart if there is a problem in the log files.
You may like to consider the following as well:
max_log_size 8192
I've found that having this bigger log file can be helpful when tracking
applications that are just blasting queries into the server. I would also
suggest this is about as high as you want to go as examining the larger
file can be slow.
And as a final step I like to configure extra audit log files as well, ie an
audit.3, audit.4, audit.5. Thats just because I get paranoid. So far the
errlog.log has shown that they haven't been used.
2) In accessdb > users; set "Audit Query Text" to Y for theYou can also just connect to iidbdb and use the command
users that you want to monitor.
alter user x with security_audit=(query_text);
alter user x with nosecurity_audit;
By using the SQL commands you can then create scripts to be fired
from cron to turn the auditing off/on at given 'hot spot' times. This can
help avoid the overhead of logging excessively.
6) To view the audit information, register the audit (i.e. UNIX)
file as a table in iidbdb, the table can then be queried with
normal SQL statements.
Don't forget that the current audit log file is available in iidbdb as the
catalog iiaudit.
Furthermore, unless you are very disciplined, the database will build up
registered security log files - all of which may have had the underlying
file removed or compressed. This can create problems with routine
database maintenance scripts. My suggestion is to either:
* Register into a dummy database excluded from your normal
maintenance cycle.
* Register into a normal database, but immediatly copy the registered
table into a permanent heap table and remove the registered table.
....
# Script to register an audit file as a table
detailinfo varchar(256) not null,
The registration of detailinfo as a varchar creates a weird problem.
Every 256 char block will have lost its last two characters.
Alter your script to use char(256) and you'll get the missing two char
back.
Took me ages to work that one out!
In regards to using the registered log files. Never, Ever put a table
structure on these. Leave them as heap. At least as heap they are
ordered as per the way data was written to them, Once you put a
structure there, even on audittime, this detail may be lost and cause
great confusion.
7) To switch off Security auditing on-the-fly (no restart reqd)
isql iidbdb; and run "disable security_audit all"
Thats a very broad brush statement, probably safer to use:
disable security_audit query_text
Absolutely!
Tips and tricks
~~~~~~~~~~~~~~~
Set the audit files to be small to test file "switching".
One major hole in the handling of Audit files is how theyI've not found this to be the case. The system has always been happy
effect Ingres re-starts:
Audit files are left in-situ when you shutdown Ingres,
but Ingres won't start with them in place,
you have to manually move them out of the way before
restarting Ingres.
to restart with the current audit log. But what is your setting of on_error?
SHUTDOWN?
Script for cbf on_switch_log param i.e /home/bin/on_sw_log.ksh
#!/bin/ksh
# Script to handle audit file switches of Ingres Sec logging
# defined in cbf > Security > Auditing > on_switch_log
LOG=/tmp/on_switch_log.txt
TO_FILE=/tmp/Ingaudit_`date '+%Y%m%d_%H%M%S'`
echo "Move $1 ${TO_FILE}" >> ${LOG} 2>&1
mv $1 ${TO_FILE} >> ${LOG} 2>&1
compress "${TO_FILE}" >> ${LOG} 2>&1
echo "Completed: `date`" >> ${LOG} 2>&1
Unless the audit log is small, I would not suggest compressing the file
inside this script. Auditing can have a huge impact on performance, I'd
suggest that takeing the time to compress a large file may not help the
matter.
Better to get your routine maintenance programs to do the compression
and removal of older log files for you.
Martin Bowes
--
Random Duckman Quote #110:
Charles/Mambo: Dad hasnt got lucky since he convinced a blind
Seinfeld obsessed groupie he was Jason Alexander in a Duck suit.
- References:
- Setting up Ingres Security Auditing for query_text
- From: Steve McElhinney
- Setting up Ingres Security Auditing for query_text
- Prev by Date: Re: [Info-Ingres] Statement aborted
- Next by Date: Date display
- Previous by thread: Setting up Ingres Security Auditing for query_text
- Next by thread: Statement aborted
- Index(es):
Relevant Pages
|
