Re: Need HELP on GRANT and ROLE

Hi Assoy,
Roles are never enabled during PL/SQL. This is because roles are floating and PL/SQL is compiled.
So Oracle tried to make sure PL/SQL would execute always, and ignored roles. There are two ways to circumvent this. One of them you already discovered. You could also have made USER1 owner of the procedure and grant execute to USER2,
as procedures always run with the rights of the owners. In Oracle 8i that has changed and you can choose between invokers rights (new) and definers rights (like it is now).

Hth,

--
Sybrand Bakker, Oracle DBA

<assoy_at_my-deja.com> wrote in message news:7rjlnl$69e$1_at_nnrp1.deja.com...
> Hi all,
> I have problem about GRANT and ROLE, and I do not know whether this is
> a bug or what I am doing is not right.
>
> I have 9 users.
>
> USER1 -- USER9
>
> USER1 has a table called TABLE_A. DBA Created a Role called READ_TBLS.
> USER1 --> Grant select on table_A to READ_TBLS;
> DBA --> Grant READ_TBLS TO USER2;
>
> USER2 is able to do a --> DESC TABLE_A;
> also, USER2 is able to do a --> SELECT * FROM TABLE_A;
> However, when USER2 try to Compile a Stored-Procedure
> SQLWKS> CREATE OR REPLACE PROCEDURE ST_1
> 2> IS
> 3>
> 4>
> 5> CURSOR C1 is SELECT * FROM USER1.TABLE_A;
> 6>
> 7> BEGIN
> 8>
> 9> FOR i in C1
> 10> LOOP
> 11>
> 12> NULL;
> 13>
> 14> END LOOP;
> 15>
> 16> END;
> 17>
> MGR-00072: Warning: PROCEDURE ST_1 created with compilation errors.
> SQLWKS> show errors
> Errors for PROCEDURE ST_1:
> LINE/COL ERROR
> ------------------------------------------------------------------------
> 3/18 PLS-00201: identifier 'USER1.TABLE_A' must be
> declared
> 3/18 PL/SQL: Item
> ignored
> 5/30 PLS-00201: identifier 'USER1.TABLE_A' must be
> declared
> 5/16 PL/SQL: SQL Statement
> ignored
>
>
> But, if USER1 do >> Grant SELECT ON TABLE_A TO USER2;
> Then compile the same exact Stored-procedure, It WORKS.
> SQLWKS> CREATE OR REPLACE PROCEDURE ST_1
> 2> IS
> 3>
> 4> CURSOR C1 is SELECT * FROM USER1.TABLE_A;
> 5>
> 6> BEGIN
> 7>
> 8> FOR i in C1
> 9> LOOP
> 10>
> 11> NULL;
> 12>
> 13> END LOOP;
> 14>
> 15> END;
> 16>
> Statement processed.
>
> Why role READ_TBLS is cannot take care of this ?
>
> Any help would be appreciated
>
> Assoy
>
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Mon Sep 13 1999 - 15:48:21 CDT