472,331 Members | 1,700 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,331 software developers and data experts.

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 6318
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: raulgz | last post by:
I need audit triggers that change columns value in the same record that fire trigger. I need how to do.. Thanks..
2
by: ecastillo | last post by:
i'm in a bit of a bind at work. if anyone could help, i'd greatly appreciate it. i have a web app connecting to a sql server using sql server...
2
by: Zlatko MatiŠ | last post by:
I tried to implement triggers for filling audit-trail table on this way. Everything works fine as long as I don't update the primary key field...
3
by: Zlatko MatiŠ | last post by:
Hello. I tried to implement audit trail, by making an audit trail table with the following fileds:...
1
by: Jeff Magouirk | last post by:
Dear Group, I would like to create an audit table that is created with a trigger that reflects all the changes(insert, update and delete) that...
0
by: JohnO | last post by:
Thanks to Serge and MarkB for recent tips and suggestions. Ive rolled together a few stored procedures to assist with creating audit triggers...
13
by: Jim M | last post by:
I've been playing with Allen Browne's audit code and found it very useful. I need to track record insertions, deletions, and edits for several...
6
by: Parag | last post by:
Hello, I have been assigned the task to design the audit trail for the ASP.NET web application. I don't know what the best practices for such audit...
0
by: JimLad | last post by:
Hi, I've been tasked with reviewing the Authentication and Auditing of an application and database. ASP/ASP.NET 1.1 app with SQL Server 2000...
0
by: tammygombez | last post by:
Hey everyone! I've been researching gaming laptops lately, and I must say, they can get pretty expensive. However, I've come across some great...
0
by: concettolabs | last post by:
In today's business world, businesses are increasingly turning to PowerApps to develop custom business applications. PowerApps is a powerful tool...
0
better678
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
0
by: teenabhardwaj | last post by:
How would one discover a valid source for learning news, comfort, and help for engineering designs? Covering through piles of books takes a lot of...
0
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
0
by: CD Tom | last post by:
This happens in runtime 2013 and 2016. When a report is run and then closed a toolbar shows up and the only way to get it to go away is to right...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
0
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.