Need HELP on GRANT and ROLE

From: <assoy_at_my-deja.com>
Date: Mon, 13 Sep 1999 20:10:40 GMT
Message-ID: <7rjlnl$69e$1@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:10:40 CDT