Janusz Jęczmionka wrote: >Let's execute following SQL statements (in ver. 7.300 build 025-000-085-923 >):
>As user DBA: >CREATE USER USER1 PASSWORD PASSWORD1 DBA NOT EXCLUSIVE >CREATE USER USER2 PASSWORD PASSWORD2 NOT EXCLUSIVE >As user USER1: >CREATE ROLE ROLE1 >GRANT ROLE1 TO USER2 >CREATE TABLE TABLE1 ( T1_ID SERIAL PRIMARY KEY, T1_DATE DATE ) /* just for >test, nothing usefull */ >GRANT SELECT ON TABLE1 TO ROLE1 >CREATE DBPROC DBPROC1( OUT today DATE ) AS SET today = DATE; /* just for >test, nothing usefull */ >GRANT EXECUTE ON DBPROC1 TO ROLE1 >As USER2: >SET ROLE ROLE1 >SELECT * FROM USER1.TABLE1 >---> Statement successfully executed. /* That's OK */ >CALL USER1.DBPROC1( :today ) >---> General error;-4016 POS(1) Unknown procedure name. /* Ooops !!! */ >SET ROLE NONE >SELECT * FROM USER1.TABLE1 >---> Base table not found;-4004 POS(15) Unknown table name:TABLE1. /* That's >OK */ >CALL USER1.DBPROC1( :today ) >---> Statement successfully executed. /* Ooops !!! */ >As USER1: >GRANT EXECUTE ON DBPROC1 TO USER2 >And again as USER2: >SET ROLE ROLE1 >SELECT * FROM USER1.TABLE1 >---> Statement successfully executed. /* That's OK */ >CALL USER1.DBPROC1( :today ) >---> Statement successfully executed. >SET ROLE NONE >SELECT * FROM USER1.TABLE1 >---> Base table not found;-4004 POS(15) Unknown table name:TABLE1. /* That's >OK */ >CALL USER1.DBPROC1( :today ) >---> Statement successfully executed. >Any ideas???? > Janusz Jęczmionka, NOI, RYBNIK There's indeed a bug in the role handling of db-procedures, if you enable a role explicitly. You will not have any problems, if you enable all roles (SET ROLE ALL). The problem will be fixed with one of the next versions. Thanks for reporting the bug. Thomas -- Thomas Anhaus SAP DB, SAP Labs Berlin [EMAIL PROTECTED] http://www.sapdb.org/ _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
