Prev: Change column Data Type from Integer to Decimal
Next: problem starting db with version 9.1
From: Michel Esber on 13 Dec 2006 12:39
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,

From: Knut Stolze on 13 Dec 2006 13:03
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/infocenter/db2luw/v8/topic/com.ibm.db2.udb.doc/admin/r0010018.htm

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
From: Michel Esber on 13 Dec 2006 13:13
> 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/admin/r0010018.htm
>
> --
> Knut Stolze
> DB2 z/OS Utilities Development
> IBM Germany



Hello Knut,

Thanks for the quick reply. However, it did not work.

[db2inst1(a)julianapaes ]$ grep -i dbsite /etc/passwd
dbsite:x:501:501::/home/dbsite:/bin/bash
[db2inst1(a)julianapaes ]$ grep -i dbsite /etc/group
dbsite:x:501:
[db2inst1(a)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,

From: Ian on 13 Dec 2006 16:43
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

From: Lennart on 16 Dec 2006 03:52

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 <dbname> as an argument. Is there a
current_database register or such that can be used instead of an actual
name?

/Lennart

 |  Next  |  Last
Pages: 1 2
Prev: Change column Data Type from Integer to Decimal
Next: problem starting db with version 9.1