Using SQLTRACE
The poster wanted to log all SQL run by ~50 users for security auditing and found SQLTRACE didn't show user/host. Replies: SQLTRACE isn't designed for auditing, but med/high levels give the user id and session id; the client hostname can be captured via a sysdbopen procedure and joined on session id. Art Kagel corrected the syntax for per-session tracing (set globally, suspend, then enable per sid from syssessions — affecting existing sessions only) and warned the ring buffer can overwrite entries before polling, though Ben Thompson and Doug Lawry said adequate buffer sizing plus frequent polling (e.g. 10,000 entries polled every 10s) captures everything. The poster settled on Server Studio's Sentinel, which Doug noted only samples prepared statements from sysconblock and will miss some SQL.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello to all, I need to have all the sql statements run by about 50 users (it's a matter of security not performance), so i activated sql tracing using server studio with selecting these users, but i noticed it doesnt give the user/hostname informations. My questions : 1- Is SQL Tracing functionnality done for my purpose (security) ? 2- If so how can i have the informations and how can i set the SQLTRACE in the ONCONFIG file ? 3- If not, how can i have these sql statements ? Thanks in advance
SQLTRACE is not INTENDED for security, no. However, IB that if you increase the detail level to medium or high you should see the user and host. Art Art S. Kagel, Principal Consultant ASK Database Management Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Mar 10, 2014 at 9:58 AM, SMITH JOHN <daylight@webmails.com> wrote: > Hello to all, > > I need to have all the sql statements run by about 50 users (it's a matter > of > security not performance), so i activated sql tracing using server studio > with > selecting these users, but i noticed it doesnt give the user/hostname > informations. > > My questions : > > 1- Is SQL Tracing functionnality done for my purpose (security) ? > > 2- If so how can i have the informations and how can i set the SQLTRACE in > the > ONCONFIG file ? > > 3- If not, how can i have these sql statements ? > > Thanks in advance > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e013c61a0b8909204f44197c3
SQLTRACE does have both the uid of the user and the database sid of the user for all trace levels. If you would like to know the hostname then I would suggest installing a sysdbopen procedure which capture this information and then you can join the traced database sid to this procedure which capture login informaiton. To create the sysdbopen procedure see: http://www.ibmnosql.com/2011/11/how-to-track-the-resources-used-by-database-user s/ John F. Miller III STSM, Lead Architect miller3@us.ibm.com 503-747-1366 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 03/10/2014 07:41:07 AM: > From: "Art Kagel" <art.kagel@gmail.com> > To: ids@iiug.org, > Date: 03/10/2014 07:41 AM > Subject: Re: Using SQLTRACE [32685] > Sent by: ids-bounces@iiug.org > > SQLTRACE is not INTENDED for security, no. However, IB that if you > increase the detail level to medium or high you should see the user and > host. > > Art > > Art S. Kagel, Principal Consultant > ASK Database Management > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on the IIUG, nor any other organization with which I am > associated either explicitly, implicitly, or by inference. Neither do > those opinions reflect those of other individuals affiliated with any > entity with which I am affiliated nor those of the entities themselves. > > On Mon, Mar 10, 2014 at 9:58 AM, SMITH JOHN <daylight@webmails.com> wrote: > > > Hello to all, > > > > I need to have all the sql statements run by about 50 users (it's a matter > > of > > security not performance), so i activated sql tracing using server studio > > with > > selecting these users, but i noticed it doesnt give the user/hostname > > informations. > > > > My questions : > > > > 1- Is SQL Tracing functionnality done for my purpose (security) ? > > > > 2- If so how can i have the informations and how can i set the SQLTRACE in > > the > > ONCONFIG file ? > > > > 3- If not, how can i have these sql statements ? > > > > Thanks in advance > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --089e013c61a0b8909204f44197c3 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
I tested the SQL Trace with med and high mode and it gives the user_id but not
the host, i guess it will be ok for the moment :)
SQLTRACE level=med,ntraces=100000,size=2,mode=global
but
1- I want to capture all the statements and i don t know how the configure
ntraces to do so.
2- The user mode does"nt work for me with the below statement found on a ibm
page.
dbaccess sysadmin -<<END
execute function task("set sql tracing on", 1000, 1,"low","user");select task("set sql user tracing on", session_id)
FROM sysmaster:syssessions
WHERE username not in ("root","informix");
END
We have solutions at Oninit Consulting in UK to record permanently all SQL statements executed (without blowing up the database) and log full connection details including the client host name. I'll contact you directly to see if we can help. Regards, Doug Lawry
Hello can anyone answer please :)
First: You may not ever be able to trap every SQL that passes through the server in a permanent place. The sqltrace buffer is a ring buffer of the size you specify, but if your SQLs are arriving too fast they will fall off the end of the buffer and be replaced before you can poll the table and copy them out. Doug claims to be able to do this, but I doubt it. Second: Your use of the user level tracing is not quite right. Do this instead: execute task( "set sql tracing on", 1000, 1, "low", "global" ); -- Set the parameters execute task( "set sql tracing suspend" ); -- Stop global tracing select task( "set sql tracing session", "on", sid ) -- enable tracing for most sessions from sysmaster:syssessions where username not in ("root", "informix" ); Art Art S. Kagel, Principal Consultant ASK Database Management Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Mar 14, 2014 at 5:37 AM, SMITH JOHN <daylight@webmails.com> wrote: > Hello > can anyone answer please :) > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c3474c64fe6504f48ed6c7
So it will trace the current sessions not new ones ?
Yes. Art Art S. Kagel, Principal Consultant ASK Database Management Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Mar 14, 2014 at 7:03 AM, SMITH JOHN <daylight@webmails.com> wrote: > So it will trace the current sessions not new ones ? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0160be548f4a5c04f48f2dc4
So it will trace the current sessions not new ones ?
Hi Art, I guess newly completed SQL statements could be being written to the SQLTRACE buffer faster than you can read them out. However by sizing your SQLTRACE buffer appropriately I've found it's nearly always possible to read it all out of sysmaster:syssqltrace and start from the top again and read from where you left off and not miss anything. Ben.
Correct. Art Art S. Kagel, Principal Consultant ASK Database Management Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Mar 14, 2014 at 9:42 AM, SMITH JOHN <daylight@webmails.com> wrote: > So it will trace the current sessions not new ones ? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c3f0206fe93704f4925f08
That depends on how busy your server is. I have one client where their server processes about 10,000 queries an hour. Creating a trace buffer that big slowed their system down too much, partially because memory was a bit tight and partially because it was a Solaris system which has some problems with shared memory performance under certain circumstances. Another client, also on Solaris hmmm.., tried to allocate 1,000,000 trace buffers of 1MB each before I could stop him. Brought the machine to its knees as it tried to allocate a TB of shared memory. Had to crash the server and reboot the machine to stop it. -- Obviously not normal situation, but... A word to the wise. I think that the point is while you MIGHT catch every SQL, I wouldn't depend on it. Only a sniffer product like iWatch can possibly catch every SQL and make a permanent record of it. Art Art S. Kagel, Principal Consultant ASK Database Management Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Mar 14, 2014 at 10:19 AM, BENJAMIN THOMPSON < benjamin.thompson@bskyb.com> wrote: > Hi Art, > > I guess newly completed SQL statements could be being written to the > SQLTRACE > buffer faster than you can read them out. > > However by sizing your SQLTRACE buffer appropriately I've found it's nearly > always possible to read it all out of sysmaster:syssqltrace and start from > the > top again and read from where you left off and not miss anything. > > Ben. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1134718879ca8704f49294c8
Hi Art. I have this working well on a system which turns over a 10000 statement SQLTRACE buffer every minute, writing out around a 10MB compressed file per hour. Perhaps I should present this at IIUG 2015! Art said: > You may not ever be able to trap every SQL that passes through the > server in a permanent place. The sqltrace buffer is a ring buffer of the > size you specify, but if your SQLs are arriving too fast they will fall off > the end of the buffer and be replaced before you can poll the table and > copy them out. Doug claims to be able to do this, but I doubt it.
Great Doug! How big a trace buffer did you have to set up and how frequently do you poll the systrace* tables? Art Art S. Kagel, Principal Consultant ASK Database Management Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Mar 14, 2014 at 2:02 PM, DOUG LAWRY <douglawry@hotmail.com> wrote: > Hi Art. I have this working well on a system which turns over a 10000 > statement SQLTRACE buffer every minute, writing out around a 10MB > compressed > file per hour. Perhaps I should present this at IIUG 2015! > > Art said: > > > You may not ever be able to trap every SQL that passes through the > > server in a permanent place. The sqltrace buffer is a ring buffer of the > > size you specify, but if your SQLs are arriving too fast they will fall > off > > the end of the buffer and be replaced before you can poll the table and > > copy them out. Doug claims to be able to do this, but I doubt it. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c3735a72901904f49518be
10000 (enough for a minute), polling every 10 seconds, so plenty of margin. There is some aggregation to reduce storage requirements. Art said: How big a trace buffer did you have to set up and how frequently do you poll the systrace* tables?
Hello to all i finally found a way with server studio with sentinel, the sentinel do capture SQL a local repository (local informix instance) Thanks to all
This saves a snapshot of prepared statements from "sysmaster:sysconblock" at specified intervals. It will therefore not show how many times a statement is executed or when, and will miss some statements entirely. It can only show estimated cost and not actual costs or run time. However, it is a good and safe way to sample the worst slow statements over a long period. SMITH JOHN said: i finally found a way with server studio with sentinel, the sentinel do capture SQL a local repository (local informix instance)