New post
Question
· Jan 7, 2021

How to audit data change?

#InterSystems IRIS

Hi guys,

 

In our clients environment, multiple sources will connect to one DB (all through JDBC connections) and perform various operations. Sometimes they found some data was deleted without  reason.  Thus they want some feature like SQL Server Database Audit Specifications that can log who at what time with which IP deleted data in a specific DB.

 

I've checked IRIS Audit but I didn't  find feature about  that . How can we audit and log  delete  of a known DB? The deletions might be performed by delete statament or truncate table stattement.

Thanks.

Product version: IRIS 2020.1
Discussion (5)2
Log in or sign up to continue
Dmitry Maslennikov · Jan 7, 2021

I know, about logging in JDBC on the client-side, but not sure how detailed it.

I think you can find if anybody even did such SQL queries, by looking into SQL Cached Queries for the particular table in SMP.

But this will not help to find who did it.

A more reliable way is to look at Journal files. You will need to do a full scan of all journal files. Depends on how many changes you have it may take quite a long time, and you should do it in time until the journal file is purged. If you will find deleting data there, you can get JobID and time when it happened, and by using System Audit, for instance, you would be able to get the exact user.

0 0
Tani Frankel · Jan 7, 2021

Nicky,

You can use the %System->%SQL family of audit events.

From the docs:

Event Source Event Type and Event Occurs When Event Data Contents Default Status
%System

%SQL/

DynamicStatement

 A dynamic SQL call is executed. The statement text and the values of any host-variable arguments passed to it. If the total length of the statement and its parameters exceeds 3,632,952 characters, the event data is truncated. Off
%System

%SQL/

EmbeddedStatement

 An embedded SQL call is executed. See below for usage details. The statement text and the values of any host-variable arguments passed to it. If the total length of the statement and its parameters exceeds 3,632,952 characters, the event data is truncated. Off
%System

%SQL/

XDBCStatement

 A remote SQL call is executed using ODBC or JDBC. The statement text and the values of any host-variable arguments passed to it. If the total length of the statement and its parameters exceeds 3,632,952 characters, the event data is truncated. Off

If you're interested only with JDBC you can stick with the last event above (XDBCStatement).

Note, while we're on the subject, if you're looking for "fancier" kind of auditing, for example to log changed fields (before and after values), you can check this thread by @Fabio Goncalves.

1 0
Nicky Zhu · Jan 8, 2021

Thanks guys. We are using audit log now to trace the deletions. 

Both truncate table statement and delete statement are logged as SQL DELETE Statement in table %SYS.Audit. Of cause, after the audit of XDBCStatement was enabled, the size of Audit Database increased rapidly (I.E 300MB for 100000 insertions in a table of only 5 fields ).

Developers who want to trace SQL execution need to be aware of much more disk space consumption and prepare that in advance.

0 0
Tani Frankel · Jan 10, 2021

Indeed Nicky, the size of the Audit database is a serious concern.

One option you might want to consider (it entails some coding but might be worth your time, vs. your sizing concerns), is to have some task that would do "house keeping" for your events.

If you have a lot of various SQL events (you mention INSERTs, and probably also SELECTs) that you don't have interest in, and one or two that you do (like DELETE). You could have a task that would run periodically (per the timing that makes sense for you, could even be every hour if that's relevant). This task would go through the SQL Audit events, specifically the SQL ones you just turned on, looking at the Description field returned, it should include the type of statement (e.g. SELECT or DELETE). Then for every event that is not a DELETE, for example, you can delete it (remove it from the Audit Log; or export and delete if you want to save them aside).

You could query the Audit by using the %SYS.Audit relevant class queries, "List" for example, filtering on the related event, though Description is just returned and not filtered in advance.

Or you could use direct SQL, for example (this time for looking for the DELETEs) -

SELECT ID, EventSource, EventType, Event, Description FROM %SYS.Audit 
WHERE (Event='XDBCStatement') AND (Description %STARTSWITH 'SQL DELETE')

Which would return for example:

ID EventSource EventType Event Description
2021-01-10 08:23:24.420||MYMACHINE:MYINSTANCE||2386 %System %SQL XDBCStatement SQL DELETE Statement

Then you could of course also look for what is not DELETEs...

SELECT ID, EventSource, EventType, Event,Description FROM %SYS.Audit 
WHERE (Event='XDBCStatement') AND NOT (Description %STARTSWITH 'SQL DELETE')

which could return for example:

ID EventSource EventType Event Description
2021-01-10 08:32:53.014||MYMACHINE:MYINSTANCE||2388 %System %SQL XDBCStatement SQL SELECT Statement

 Then you could delete it, for example:

DELETE FROM %SYS.Audit 
WHERE ID='2021-01-10 08:32:53.014||MYMACHINE:MYINSTANCE||2388'

[Of course you could combine these last two statements into one - e.g. DELETE FROM ... WHERE ID IN (...)]

Hope this helps...

1 0