Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / DB2 Topics / December 2006

Tip: Looking for answers? Try searching our database.

Audit Triggers

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Michel Esber - 13 Dec 2006 17:39 GMT
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.