Re: HELP on GRANT and ROLE

Privileges granted via Role are not visible inside PL/SQL . You need to asssign them Directly..

-Thiru

assoy_at_my-deja.com wrote:

> 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
>
> System Environment: SunOS 5.6, ORACLE 7.3.4
>
> Thanks for your attention
>
> 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:42:05 CDT