onshowaudit systables

Posted in 2011

Topics: Security, Permissions & Auditing

SERVIO VELASCO — Wed, 11 May 2011 14:07:52 — source: IIUG Forums & Mailing Lists

Hello everybody I have the questions. I turn on audit and I set UPDATE, INSERT and DELETE operations over a user. When I do INSERT I can to return the row. I used the following query. audit file: ONLN|2011-05-11 09:02:52.000|tesoreriadesa|3019|audit_tcp|informix|0:INRW:stores6:113:1048980:25 8 I took the table name with. SELECT tabname FROM stores6:systables WHERE tabid = 113; I took the row with. SELECT * FROM pruebatab WHERE rowid = 258; I want to select the rows when delete or update but I don´t know to use the data in the audit files. Anybody know how to use this data to take a rows? Thanks a lot Regards Servio Velasco

↪ replying to SERVIO VELASCO

KARL OLIVER — Wed, 11 May 2011 21:55:53 — source: IIUG Forums & Mailing Lists

Hello Not sure exactly what your question is. Anyway if I want to look in audit files I usually use Unix vi command on audit file. Or you could use onshowaudit to list audit file and pipe it into grep looking for what you want . For example to see audit records for create procedure I use onshowaudit | grep CRSP returns ONLN|2011-03-1719:53:39.000|wuasp132|18235|asp132_ecms_live_rep|paqis|0:CRSP:ec_ live:4679:paqis:get_ec_cert_track Not sure if this is what you are looking for

↪ replying to KARL OLIVER

SERVIO VELASCO — Thu, 12 May 2011 14:01:10 — source: IIUG Forums & Mailing Lists

Hello Oliver I will try to explain me. I created a mask to audit INSERT, DELETE and UPDATE operations at user "testuser". onaudit -a -u _dml -e +DLRW,INRW,UPRW onaudit -d -u testuser I deleted a row of table tab1. DELETE FROM tab1 WHERE c1=3; In the audit file has the following line. ONLN|2011-05-11 09:06:04.000|testdesa|3019|audit_tcp|testuser|0:DLRW:stores6:113:1048980:259 About the manual "IBM Informix Security guide" in table 12-1 for DLRW event have Code event: DLRW Event: Delete row Variables contents dbname:dbname tabid: tabid extra_1: partno partno:frag_id row_num: row-num dbname: stores6 tabid: 113 partnum:1048980 row_num: 259 (rowid of the delete row) I used the next query to confirm data. And the data is OK SELECT tabname, partnum,tabid FROM stores6:systables WHERE tabid = 113 The row_num: is the rowid before of delete. My questions are. Does exist any form to know the old data? Can I use "partnum" and "row_num" to return the old data? What are tables in "sysmaster" can I use to return data? I updated a row. UPDATE testtab SET c2='ONE' WHERE c1=1; In audit file has the next line. ONLN|2011-05-12 08:38:17.000|testdesa|18450|audit_tcp|testuser|0:UPRW:stores6:113:1048980:257:10 48980:257 Code event: UPRW Event: Update row Variables contents dbname:dbname tabid: tabid extra_1: old partno partno:old rowid flags: new rowid extra_2: new partno dbname: stores6 tabid:113 old partno: 1048980 old rowid: 257 new partnum: 1048980 new rowid: 257 In this case, The old partno is same new partnum and the old rowid is same new rowid. My question is: Does exist any form to know the old data? Thanks a lot Regards Servio Velasco

↪ replying to SERVIO VELASCO

Alexandre Marini — Thu, 12 May 2011 14:09:58 — source: IIUG Forums & Mailing Lists

Hello. Historical auditing is easier enabled by using triggers. Otherwise, you would only know the "before delete" or "before update" data, tracing logical-log files. Best regards. Em 12/05/2011 10:01, SERVIO VELASCO escreveu: > Hello Oliver > > I will try to explain me. > > I created a mask to audit INSERT, DELETE and UPDATE operations at user > "testuser". > > onaudit -a -u _dml -e +DLRW,INRW,UPRW> > onaudit -d -u testuser> > I deleted a row of table tab1. > > DELETE FROM tab1 WHERE c1=3;> > In the audit file has the following line. > > ONLN|2011-05-11 > 09:06:04.000|testdesa|3019|audit_tcp|testuser|0:DLRW:stores6:113:1048980:259 > > About the manual "IBM Informix Security guide" in table 12-1 for DLRW event > have > > Code event: DLRW > Event: Delete row > Variables contents > > dbname:dbname > > tabid: tabid > > extra_1: partno > > partno:frag_id > > row_num: row-num > > dbname: stores6 > tabid: 113 > partnum:1048980 > row_num: 259 (rowid of the delete row) > > I used the next query to confirm data. And the data is OK > > SELECT tabname, partnum,tabid FROM stores6:systables > WHERE tabid = 113> > The row_num: is the rowid before of delete. > > My questions are. Does exist any form to know the old data? Can I use > "partnum" and "row_num" to return the old data? What are tables in "sysmaster" > can I use to return data? > > I updated a row. > > UPDATE testtab SET c2='ONE' WHERE c1=1;> > In audit file has the next line. > > ONLN|2011-05-12 > 08:38:17.000|testdesa|18450|audit_tcp|testuser|0:UPRW:stores6:113:1048980:257:10 48980:257 > > Code event: UPRW > Event: Update row > Variables contents > > dbname:dbname > > tabid: tabid > > extra_1: old partno > > partno:old rowid > > flags: new rowid > > extra_2: new partno > > dbname: stores6 > tabid:113 > old partno: 1048980 > old rowid: 257 > new partnum: 1048980 > new rowid: 257 > > In this case, The old partno is same new partnum and the old rowid is same new > rowid. > > My question is: Does exist any form to know the old data? > > Thanks a lot > Regards > > Servio Velasco > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Alexandre Marini Tecnologia da Informação - DBA msn: alexandre_marini@hotmail.com SEFAZ-MS / SGI-UGSR / Sistemas IBM-Informix Cert-Info-Mgmt_color <Cert-Info-Mgmt_color.jpg> IBM Certified System Administrator - Informix Dynamic Server V10 / V11 See you at <http://www.iiug.org/conf/2011/iiug/>;

↪ replying to Alexandre Marini

Art Kagel — Thu, 12 May 2011 15:28:29 — source: IIUG Forums & Mailing Lists

Or you can use the new Change Data Capture (CDC) facility which is lower overhead than triggers (a couple of existing data audit packages that used to use triggers in Informix have been or are in the process of being recoded to use CDC). Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, 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 Thu, May 12, 2011 at 10:09 AM, Alexandre Marini < amarini@fazenda.ms.gov.br> wrote: > Hello. > Historical auditing is easier enabled by using triggers. > Otherwise, you would only know the "before delete" or "before update" > data, tracing logical-log files. > > Best regards. > > Em 12/05/2011 10:01, SERVIO VELASCO escreveu: > > Hello Oliver > > > > I will try to explain me. > > > > I created a mask to audit INSERT, DELETE and UPDATE operations at user > > "testuser". > > > > onaudit -a -u _dml -e +DLRW,INRW,UPRW> > > > onaudit -d -u testuser> > > > I deleted a row of table tab1. > > > > DELETE FROM tab1 WHERE c1=3;> > > > In the audit file has the following line. > > > > ONLN|2011-05-11 > > > 09:06:04.000|testdesa|3019|audit_tcp|testuser|0:DLRW:stores6:113:1048980:259 > > > > About the manual "IBM Informix Security guide" in table 12-1 for DLRW > event > > have > > > > Code event: DLRW > > Event: Delete row > > Variables contents > > > > dbname:dbname > > > > tabid: tabid > > > > extra_1: partno > > > > partno:frag_id > > > > row_num: row-num > > > > dbname: stores6 > > tabid: 113 > > partnum:1048980 > > row_num: 259 (rowid of the delete row) > > > > I used the next query to confirm data. And the data is OK > > > > SELECT tabname, partnum,tabid FROM stores6:systables > > WHERE tabid = 113> > > > The row_num: is the rowid before of delete. > > > > My questions are. Does exist any form to know the old data? Can I use > > "partnum" and "row_num" to return the old data? What are tables in > "sysmaster" > > can I use to return data? > > > > I updated a row. > > > > UPDATE testtab SET c2='ONE' WHERE c1=1;> > > > In audit file has the next line. > > > > ONLN|2011-05-12 > > > > 08:38:17.000|testdesa|18450|audit_tcp|testuser|0:UPRW:stores6:113:1048980:257:10 48980:257 > > > > Code event: UPRW > > Event: Update row > > Variables contents > > > > dbname:dbname > > > > tabid: tabid > > > > extra_1: old partno > > > > partno:old rowid > > > > flags: new rowid > > > > extra_2: new partno > > > > dbname: stores6 > > tabid:113 > > old partno: 1048980 > > old rowid: 257 > > new partnum: 1048980 > > new rowid: 257 > > > > In this case, The old partno is same new partnum and the old rowid is > same > new > > rowid. > > > > My question is: Does exist any form to know the old data? > > > > Thanks a lot > > Regards > > > > Servio Velasco > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > > Alexandre Marini > > Tecnologia da Informação - DBA > > msn: alexandre_marini@hotmail.com > > SEFAZ-MS / SGI-UGSR / Sistemas IBM-Informix > > Cert-Info-Mgmt_color <Cert-Info-Mgmt_color.jpg> > > IBM Certified System Administrator - Informix Dynamic Server V10 / V11 > > See you at > > <http://www.iiug.org/conf/2011/iiug/>; > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec547ca0f26716204a315d7fa