Re: HELP on GRANT and ROLE

From: Ben Ryan <benryan_at_my-deja.com>
Date: Mon, 13 Sep 1999 20:54:45 GMT
Message-ID: <7rjoae$8b1$1@nnrp1.deja.com>

This is the normal behaviour in Oracle. I would imagine most people who write stored procedures run foul of this sooner or later.

I quote "The owner of the package or procedure must have been explicitly granted the necessary object privileges for all objects referenced within the body of the code; the owner cannot have obtained required privileges through roles." (See Oracle7.3 Application Developer's Guide. Page 7-10. The relevant sentence is shown in bold.)

You may wish to have user1 create the store procedure ST_1 and then grant execute on st_1 to user2. When user2 invokes ST_1, ST_1 will run with the privileges of user1. Since user1 has SELECT privilege on the TABLE_A the stored procedure will compile and run.

There have been some changes in Oracle 8.1 to store procedure privileges, which may in part address this "issue".

In article <7rjm84$6lh$1_at_nnrp1.deja.com>,   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.
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Mon Sep 13 1999 - 15:54:45 CDT