469,608 Members | 1,515 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,608 developers. It's quick & easy.

Audit Triggers

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,

Dec 13 '06 #1
5 6186
Michel Esber wrote:
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.
You could try setting the DB2_SNAPSHOT_NOAUTH registry variable to ON.

Alternatively, have a look here:
http://publib.boulder.ibm.com/infoce...n/r0010018.htm

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Dec 13 '06 #2
You could try setting the DB2_SNAPSHOT_NOAUTH registry variable to ON.
>
Alternatively, have a look here:
http://publib.boulder.ibm.com/infoce...n/r0010018.htm

--
Knut Stolze
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,

Dec 13 '06 #3
Ian
Michel Esber wrote:
>
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

Dec 14 '06 #4

Michel Esber wrote:
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.
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 <dbnameas an argument. Is there a
current_database register or such that can be used instead of an actual
name?

/Lennart

Dec 16 '06 #5

Lennart wrote:
[...]
>
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?
Updating the dbm cfg seem to help :-)

BTW, all snapshot functions take <dbnameas 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

Dec 17 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by raulgz | last post: by
2 posts views Thread by Zlatko Matić | last post: by
3 posts views Thread by Zlatko Matić | last post: by
1 post views Thread by Jeff Magouirk | last post: by
reply views Thread by JohnO | last post: by
6 posts views Thread by Parag | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.