Audit Triggers

[Michel Esber]

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]

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

[Michel Esber]

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,

[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

[Lennart]

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

[Lennart]

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