Need a Help, Access login and sql traces
John Smith wanted to log logins and SQL statements on IDS 9.40 FC9 and later 11. Fernando Nunes suggested the auditing facility, sysdbopen()/sysdbclose() on 11, and SQLTRACE (circular buffer, so may lose statements). Kathy Duchene shared a cron shell script querying syssessions and syssqlstat in sysmaster. For failed connection attempts she suggested grepping online.log for password errors. John had also found a tool that logs sessions.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hello under IDS 9.40 FC9, i want to log access to ids, and trace the sql statements is there a way to do this with IDs commands only ? or IDS options ? by the way, i will migrate to ids 11 but in the summer, how to do the same things on ids 11 please ? Thanks in advance
For logging access you could use the auditing facility. I don't know any way to log the SQLs without external tools: - Brute force tcpdump - Something like IBM Guardium (not sure if it works with 9.x) - Some external party tool for replication... not sure about this one, but it's possible that some may capture the SQLs For version 11, you could use the sysdbopen() and sysdbclose() procedure to capture login information. As for logging the SQL you could try something with SQLTRACE, but there is no guarantee that you'll be able to capture and store all of them (it works by using a circular buffer, so the buffer may be re-used before you're able to save it... Regards. On Mon, Jan 9, 2012 at 3:31 PM, SMITH JOHN <daylight@webmails.com> wrote: > Hello > > under IDS 9.40 FC9, i want to log access to ids, and trace the sql > statements > > is there a way to do this with IDs commands only ? or IDS options ? > > by the way, i will migrate to ids 11 but in the summer, how to do the same > things on ids 11 please ? > > Thanks in advance > > > > ******************************************************************************* > 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... --00235429e164d1b84c04b61a8ec8
I am using Informix 11.50, but the system table should also be available in
your version. I created a shell script and scheduled it every 10 minutes
through the cron, it writes the output to a log which I monitor.
. setdb.sh # Set environment to intended database
echo `date` >>$PROD/log/whoson.$TDAY
dbaccess sysmaster <<!>>$PROD/log/whoson.$TDAY
select sid, connected, uid, pid, username[1,8], hostname[1,35], tty[1,13],sqs_statement
from syssessions, syssqlstat
where sqs_sessionid = sid
order by 2;
You can then use the onstat -g ses or onstat -g sql to gather additional
information on a particular sql that was written to the log.
thanks i also found a way to do it, with server studio, but i still need to log sessions, the successfuls and the unsuccessful ones. your script and also server studio logs only the successfuls sessions. i understrand that there is a way to logs unsuccessful sessions with informix auditing, can someone tell me more ?
If by "unsuccessful sessions" you are referring to failed attempts to connect to Informix, you can check the online.log for these errors. The following statement can be used and redirected to an output file for logging. cat online.log|grep password
Related threads
- Posting from the Informix-list
- Migrating from IDS 9.40.UC6 to 11.50.UC3
- Ip for a network session
- questions onstat -g