SQL Stored Proc - Security issue

[peteh]

Hi All;
I am having trouble with a very simple SQL stored procedure. I have
written several that are running with no problems. This one is leaving
me scratching my head. I suspect I'm missing something very simple and
appreciate your help. Here goes:
* This SQL runs fine from command line:
select APPLY_QUAL, rtrim(SET_NAME) as SET_NAME
, SOURCE_OWNER, SOURCE_TABLE, TARGET_OWNER
, TARGET_TABLE, TARGET_CONDENSED, TARGET_COMPLETE
from ASN.IBMSNAP_SUBS_MEMBR;
* When put into the body of a CREATE PROCEDURE statement like so:
CREATE PROCEDURE SPPCH.DPROP_SET_ACTIVITY
(
)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
READS SQL DATA
INHERIT SPECIAL REGISTERS
BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0 ;

-- use WITH RETURN TO CLIENT in DECLARE CURSOR to always
-- return a result set to the client application
DECLARE c1 CURSOR WITH RETURN TO CALLER FOR
select APPLY_QUAL, rtrim(SET_NAME) as SET_NAME
, SOURCE_OWNER, SOURCE_TABLE, TARGET_OWNER
, TARGET_TABLE, TARGET_CONDENSED, TARGET_COMPLETE
from ASN.IBMSNAP_SUBS_MEMBR;

-- to return result set, do not CLOSE cursor
OPEN c1;

-- the EXIT handler ensures that we will not reach this point unless
the
-- result set has results, so we can return a success code
END;
* The CREATE PROCEDURE statement fails with:
[DB2/6000] SQL0551N "SPXXX" does not have the privilege to perform
operation "SELECT" on object "ASN.IBMSNAP_SUBS_MEMBR". LINE NUMBER=26.
SQLSTATE=42501
* SPXXX is a member of a group which has select on the specified object
and SPXXX can run the SQL SELECT statement and return results from the
command line.

Any ideas on what I'm missing? I can find no solution in the App Dev
Guide for Server Apps. Thanks in advance.

Pete H

[Serge Rielau]

peteh wrote:

Hi All;
I am having trouble with a very simple SQL stored procedure. I have
written several that are running with no problems. This one is leaving
me scratching my head. I suspect I'm missing something very simple and
appreciate your help. Here goes:
* This SQL runs fine from command line:
select APPLY_QUAL, rtrim(SET_NAME) as SET_NAME
, SOURCE_OWNER, SOURCE_TABLE, TARGET_OWNER
, TARGET_TABLE, TARGET_CONDENSED, TARGET_COMPLETE
from ASN.IBMSNAP_SUBS_MEMBR;
* When put into the body of a CREATE PROCEDURE statement like so:
CREATE PROCEDURE SPPCH.DPROP_SET_ACTIVITY
(
)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
READS SQL DATA
INHERIT SPECIAL REGISTERS
BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0 ;

-- use WITH RETURN TO CLIENT in DECLARE CURSOR to always
-- return a result set to the client application
DECLARE c1 CURSOR WITH RETURN TO CALLER FOR
select APPLY_QUAL, rtrim(SET_NAME) as SET_NAME
, SOURCE_OWNER, SOURCE_TABLE, TARGET_OWNER
, TARGET_TABLE, TARGET_CONDENSED, TARGET_COMPLETE
from ASN.IBMSNAP_SUBS_MEMBR;

-- to return result set, do not CLOSE cursor
OPEN c1;

-- the EXIT handler ensures that we will not reach this point unless
the
-- result set has results, so we can return a success code
END;
* The CREATE PROCEDURE statement fails with:
[DB2/6000] SQL0551N "SPXXX" does not have the privilege to perform
operation "SELECT" on object "ASN.IBMSNAP_SUBS_MEMBR". LINE NUMBER=26.
SQLSTATE=42501
* SPXXX is a member of a group which has select on the specified object
and SPXXX can run the SQL SELECT statement and return results from the
command line.

Any ideas on what I'm missing? I can find no solution in the App Dev
Guide for Server Apps. Thanks in advance.

Pete H

GROUP privileges are only considered for dynamic SQL.
For static SQL (such as that used in an SQL Procedure) the user must
have explicit privileges.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[peteh]

As usual - Thanks!

Pete H