|
Problem IC65704
|
Status: Closed |
INCORRECT GRANT STATEMENT WITH DB2LOOK WHEN TWO FUNCTIONS WITH SAME NAME BUT DIFFERENT SCHEMA |
| product: |
DB2 FOR LUW / DB2FORLUW / 950 - DB2 |
| Problem description: |
Repro Steps
1) create table s1.tab1(c1 smallint NOT NULL, c2 int, c3 bigint)
2) create function s1.func1() specific func1 returns integer
language sql reads sql data deterministic no external action
begin declare i integer; set i = (select c3 from s1.tab1 where
c2 = 22);return i;end
3) grant execute on function s1.func1 to user ZURBIE
4) create function s2.func1(x int) specific func1 returns
integer fenced language java parameter style java external name
'Functions!squire' no external action called on null input
deterministic no sql
5) grant execute on function s2.func1 to user zurbie
6) db2look -d <dbname> -l -e -x
Here is a snippet from the db2look output ...
-----------------------------------------------------
-- Authorization Statements on User Defined Functions
-----------------------------------------------------
GRANT EXECUTE ON FUNCTION "S1 "."FUNC1"(INTEGER) TO USER
"ZURBIE " ;
GRANT EXECUTE ON FUNCTION "S1 "."FUNC1"(INTEGER) TO USER
"ZURBIE " ;
GRANT EXECUTE ON FUNCTION "S2 "."FUNC1"(INTEGER) TO USER
"ZURBIE " ;
GRANT EXECUTE ON FUNCTION "S2 "."FUNC1"(INTEGER) TO USER
"ZURBIE " ;
If I try to execute the grant statement on s1.func1 as provided
by db2look, I get the following error:
GRANT EXECUTE ON FUNCTION "S1 "."FUNC1"(INTEGER) TO USER
"ZURBIE "
DB21034E The command was processed as an SQL statement because
it was not a valid Command Line Processor command. During SQL
processing it returned:
SQL0458N In a reference to routine "S1.FUNC1" by signature, a
matching routine could not be found. SQLSTATE=42883
I believe that the GRANT statement is created incorrectly, as
there should be no (INTEGER). If you drop function s2.func1,
then run db2look again, you will get the correct GRANT DDL stmt
of
GRANT EXECUTE ON FUNCTION "S1 "."FUNC1"() TO USER "ZURBIE
" ; |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* Repro Steps *
* 1) create table s1.tab1(c1 smallint NOT NULL, c2 int, c3 *
* bigint) *
* 2) create function s1.func1() specific func1 returns integer *
* language sql reads sql data deterministic no external action *
* begin declare i integer; set i = (select c3 from s1.tab1 *
* where c2 = 22);return i;end *
* 3) grant execute on function s1.func1 to user ZURBIE *
* 4) create function s2.func1(x int) specific func1 returns *
* integer fenced language java parameter style java external *
* name 'Functions!squire' no external action called on null *
* input deterministic no sql *
* 5) grant execute on function s2.func1 to user zurbie *
* *
* 6) db2look -d <dbname> -l -e -x *
* *
* Here is a snippet from the db2look output ... *
* *
* ----------------------------------------------------- *
* -- Authorization Statements on User Defined Functions *
* ----------------------------------------------------- *
* GRANT EXECUTE ON FUNCTION "S1 "."FUNC1"(INTEGER) TO *
* USER "ZURBIE " ; *
* GRANT EXECUTE ON FUNCTION "S1 "."FUNC1"(INTEGER) TO *
* USER "ZURBIE " ; *
* GRANT EXECUTE ON FUNCTION "S2 "."FUNC1"(INTEGER) TO *
* USER "ZURBIE " ; *
* GRANT EXECUTE ON FUNCTION "S2 "."FUNC1"(INTEGER) TO *
* USER "ZURBIE " ; *
* *
* If I try to execute the grant statement on s1.func1 as *
* provided by db2look, I get the following error: *
* *
* GRANT EXECUTE ON FUNCTION "S1 "."FUNC1"(INTEGER) TO *
* USER "ZURBIE " *
* DB21034E The command was processed as an SQL statement *
* because it was not a valid Command Line Processor command. *
* During SQL processing it returned: *
* SQL0458N In a reference to routine "S1.FUNC1" by signature, *
* a matching routine could not be found. SQLSTATE=42883 *
* *
* I believe that the GRANT statement is created incorrectly, *
* as there should be no (INTEGER). If you drop function *
* s2.func1, then run db2look again, you will get the correct *
* GRANT DDL stmt of *
* GRANT EXECUTE ON FUNCTION "S1 "."FUNC1"() TO USER *
* "ZURBIE " ; *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 v9.5 fp6 *
**************************************************************** |
| Local Fix: |
|
| available fix packs: |
DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
Problem fixed in v9.5 fp6 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 20.01.2010
30.03.2010
30.03.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.5.FP6
|
| Problem solved according to the fixlist(s) of the following version(s) |
