By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,004 Members | 1,296 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,004 IT Pros & Developers. It's quick & easy.

No select privilege on sysibm.sysdummy1

P: n/a
create table ppp
(
t int
)
@

create view v_ppp as
select t from ppp
@

select 1 from sysibm.sysdummy1
@

create trigger q
instead of insert on v_ppp
for each row
begin atomic
declare x int;
set x = (select 1 from sysibm.sysdummy1);
end@

With DB2 8 fp 7b, 8a I have been getting the following error when
creating the trigger in the above code

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0551N "CMM" does not have the privilege to perform operation
"SELECT" on
object "SYSIBM.SYSDUMMY1". LINE NUMBER=7. SQLSTATE=42501

The standalone select from sysibm.sysdummy1 works fine.

I did not have this problem with Fix Pack 7a.

Has anybody encountered this, should I even bother with downloading Fix
pack 9?

Mike

Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
That was ESE on Windows XP.

Nov 12 '05 #2

P: n/a
"Mike Gemmell" <gr*************@hotmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...
create table ppp
(
t int
)
@

create view v_ppp as
select t from ppp
@

select 1 from sysibm.sysdummy1
@

create trigger q
instead of insert on v_ppp
for each row
begin atomic
declare x int;
set x = (select 1 from sysibm.sysdummy1);
end@

With DB2 8 fp 7b, 8a I have been getting the following error when
creating the trigger in the above code

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0551N "CMM" does not have the privilege to perform operation
"SELECT" on
object "SYSIBM.SYSDUMMY1". LINE NUMBER=7. SQLSTATE=42501

The standalone select from sysibm.sysdummy1 works fine.

I did not have this problem with Fix Pack 7a.

Has anybody encountered this, should I even bother with downloading Fix
pack 9?

Mike

I noticed some similarly strange behavior with FP8. I checked the privileges
on the view and noticed select on the view was not granted to public. Select
access is granted to public with databases created with FP4 and FP9 in my
shop.

First check the privileges on the view and grant select to public if not
already done. Also see APAR JR19986, which was supposed to have fixed the
problem in the following fixpacks (FP8 seems to be conspicuously absent from
the list):

Version 8 FixPak 7a
Version 8 FixPak 8a
Version 8 FixPak 9 (also known as Version 8.2 FixPak 2)
Version 8 FixPak 9a

Not sure if an upgrade of an existing db created in FP8 would fix the
problem.
Nov 12 '05 #3

P: n/a
Thanks Mark,

I was aware of that problem.

However, it turns out I didn't RTFM.
It appears that a user must be explicitly granted select (unless they
are a DBADM) on any tables or views referenced in any trigger they
create. No group privileges are considered in this context.

As for me not having the same symptoms on Fix Pack 7a, it turns out I
actually had DBADM privileges on the database I was using (arg).

Mike

Nov 12 '05 #4

P: n/a
This is not an answer, just a suggestion.

Perhaps TABLE() would be better?

set x = (select 1 from TABLE(VALUES(1)) A)

sysibm.sysdummy1 was added for help in migrating Oracle code to DB2.

Nov 12 '05 #5

P: n/a
Actually, it was added to maintain consistency with DB2 for z/OS.

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.