Missing privilege:ROLE DEFINED PRIVILEGE in CREATE TABLE temp.table1 AS select

[Alexey Gaidukov]

/* from user DBADMIN */

CREATE USER GIS PASSWORD 1 RESOURCE
//
CREATE USER REG PASSWORD 1 RESOURCE
//
CREATE ROLE regRole
//
CREATE USER TU PASSWORD 1
//
GRANT regRole TO TU
//
ALTER user TU DEFAULT ROLE ALL

/* from user GIS*/

create table gis.test (
f1 integer,
f2 integer)
//
grant select on gis.test to reg,regRole


/* from user REG */

CREATE DBPROC reg.testproc AS
VAR
   sqlText varchar(1200);
BEGIN
    TRY
       TRY
           DROP TABLE temp.table1;
        CATCH BEGIN
           IF $rc<>-4004 then
               STOP($rc, 'DROP temp.table1:'||$errmsg);
           SET $rc = 0;
        END;
        SET sqlText = 'CREATE TABLE temp.table1 AS select f1,f2 from 
gis.test';
        TRY
            EXECUTE sqlText;
        CATCH
            IF $rc <> 100 THEN
                STOP($rc, 'EXECUTE:'||$errmsg);
    CATCH
       STOP($rc, $errmsg);
END;
//
grant execute on reg.testproc to regRole


/* from user TU*/

call reg.testproc

---- Error -------------------------------
Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed
Syntax error or access violation;-5001 POS(47) EXECUTE:Missing 
privilege:ROLE DEFINED PRIVILEGE
call reg.testproc



Another variant of DBPROC reg.testproc works well in the same 
environment. Difference is only in "CREATE TABLE temp.table1 AS select"


CREATE DBPROC reg.testproc AS
VAR
   sqlText varchar(1200);
BEGIN
    TRY
       TRY
           DROP TABLE temp.table1;
        CATCH BEGIN
           IF $rc<>-4004 then
               STOP($rc, 'DROP temp.table1:'||$errmsg);
           SET $rc = 0;
        END;
        CREATE TABLE temp.invest_lab (
           f1 integer,
           f2 integer);
        SET sqlText = 'INSERT INTO temp.table1 select f1,f2 from gis.test';
        TRY
            EXECUTE sqlText;
        CATCH
            IF $rc <> 100 THEN
                STOP($rc, 'EXECUTE:'||$errmsg);
    CATCH
       STOP($rc, $errmsg);
END;


--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]