Audit trigger
Hello,
LUW DB2 V8 FP13
I am trying to create audit triggers in order to find out which
user/application is deleting data from a table, as well as the
statement the user entered.
Here´s what I´ve done:
# create table AUD.T_BACKLOG_AUDIT (EVENT_TIME TIMESTAMP, USERNAME
VARCHAR(20), STATEMENT VARCHAR(251))
# CREATE TRIGGER TRG.TRACE_BACKLOG
AFTER DELETE ON ASSET.TBL_BACKLOG
FOR EACH STATEMENT MODE DB2SQL
BEGIN ATOMIC
insert into AUD.T_BACKLOG_AUDIT
select current timestamp, current user,
SUBSTR(VARCHAR(STMT_TEXT),1,250)
from TABLE(SNAPSHOT_STATEMENT('ASSET',-1)) as A,
TABLE(SNAPSHOT_APPL_INFO('ASSET',-1)) as B
where A.AGENT_ID=B.AGENT_ID and APPL_ID=APPLICATION_ID()
END;
But when regular users connect to the DB and execute a DELETE on
ASSET.TBL_BACKLOG, the following error message indicating lack of
privileges is returned:
SQL0723N An error occurred in a triggered SQL statement in trigger
"TRG.TRACE_BACKLO". Information returned for the error includes
SQLCODE
"-443", SQLSTATE "38553" and message tokens
"SNAPSHOT_APPL_INFO|*_APPL_INFO|SQL1092 Re". SQLSTATE=09000
I do not wish to grant DB_ADM to this user. Event monitors are not an
option due to its overhead.
Any other way to achieve the same result? I want to audit delete
activity on a single table.
Thanks in Advance,
Knut Stolze - 13 Dec 2006 18:03 GMT
> Audit trigger
>
[quoted text clipped - 40 lines]
> Any other way to achieve the same result? I want to audit delete
> activity on a single table.
You could try setting the DB2_SNAPSHOT_NOAUTH registry variable to ON.
Alternatively, have a look here:
http://publib.boulder.ibm.com/infocenter/db2luw/v8/topic/com.ibm.db2.udb.doc/adm
in/r0010018.htm

Signature
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Michel Esber - 13 Dec 2006 18:13 GMT
> You could try setting the DB2_SNAPSHOT_NOAUTH registry variable to ON.
>
[quoted text clipped - 5 lines]
> DB2 z/OS Utilities Development
> IBM Germany
Hello Knut,
Thanks for the quick reply. However, it did not work.
[db2inst1@julianapaes ]$ grep -i dbsite /etc/passwd
dbsite:x:501:501::/home/dbsite:/bin/bash
[db2inst1@julianapaes ]$ grep -i dbsite /etc/group
dbsite:x:501:
[db2inst1@julianapaes ]$ db2 "update dbm cfg using SYSMON_GROUP dbsite"
DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.
db2 connect to asset user dbsite using ***
db2 "select current timestamp, current user,
SUBSTR(VARCHAR(STMT_TEXT),1,250) from
TABLE(SNAPSHOT_STATEMENT('ASSET',-1)) as A,
TABLE(SNAPSHOT_APPL_INFO('ASSET',-1)) as B where A.AGENT_ID=B.AGENT_ID
and APPL_ID=APPLICATION_ID()"
SQL0443N Routine "SNAPSHOT_APPL_INFO" (specific name "*_APPL_INFO")
has
returned an error SQLSTATE with diagnostic text "SQL1092 Reason code
or
token: DBSITE ". SQLSTATE=38553
What I am doing wrong?
I haven´t tried the registry option because it requires an instance
restart ...
Thanks,
Ian - 13 Dec 2006 21:43 GMT
> I haven´t tried the registry option because it requires an instance
> restart ...
Changing SYSMON_GROUP requires an instance restart, too.
That's what the problem is.
Ian
Lennart - 16 Dec 2006 08:52 GMT
> Audit trigger
>
[quoted text clipped - 5 lines]
> user/application is deleting data from a table, as well as the
> statement the user entered.
I noticed you post and the idea looks interesting, however I only get
null for STMT_TEXT in SNAPSHOT_STATEMENT. What does it take to get a
value there? I switched on monitors as:
db2 update monitor switches using statement on BUFFERPOOL on LOCK on
SORT on TABLE on UOW on
But I still dont get any value. Any ideas, anyone?
BTW, all snapshot functions take <dbname> as an argument. Is there a
current_database register or such that can be used instead of an actual
name?
/Lennart
Lennart - 17 Dec 2006 08:36 GMT
[...]
> I noticed you post and the idea looks interesting, however I only get
> null for STMT_TEXT in SNAPSHOT_STATEMENT. What does it take to get a
[quoted text clipped - 4 lines]
>
> But I still dont get any value. Any ideas, anyone?
Updating the dbm cfg seem to help :-)
> BTW, all snapshot functions take <dbname> as an argument. Is there a
> current_database register or such that can be used instead of an actual
> name?
CURRENT_SERVER seem ok for the moment
/Lennart