Re: Why procedure cannot see table?

ORACLE users can be granted privileges directly or indirectly via roles. In order for role privileges to take effect, role needs to be enabled. It happens at user login time. ORACLE checks if granted role is a default role and if it is, enables it (It also happens when user executes SET ROLE statement or DBMS_SESSION.SET_ROLE procedure).
When you execute a stored/packaged procedure/function ORACLE simply switches to procedure/function owner's security domain. Do not confuse it with login. Switching to procedure/function owner's security domain does not enable procedure/function owner's default roles, therefore procedure/function can see and do only what is directly granted to it's owner.
I do not believe it is a bug as someone stated in one of the replies. It would be nice if ORACLE would be enabling default roles while executing stored/packaged procedure/function or even better would allow to use SET ROLE or DBMS_SESSION.SET_ROLE in stored/packaged procedure/function. But I certainly understand that ORACLE would have a lot of difficulties and overhead trying to validate stored/packaged procedures/functions affected (directly and indirectly) every time you change a role.  

Solomon.Yakobson_at_entex.com

In article <32c8685f.1289668_at_news>,
  zlm101_at_psu.edu (Z. Martinez) wrote:
>
> I'd like to thank the people that responded to my original question.
>
> I got my stored procedure to work by granting explicit SELECT access
> to the table to MYUSER. I originally tried to grant SELECT privilege
> to the role MYUSER belongs to, but that did not seem to work.
>
> Now that I got that working, would anybody mind telling me why
> granting SELECT to the ROLE does not work and granting SELECT to
> MYUSER work?
>
> Again, thanks in advance.
>
> Please send your response to zlm101_at_psu.edu
>
> =========================================
> =========================================
>
> I created a stored procedure, LOAD_TOP_CUST, but I'm getting this
> error when I call it.
>
> ERROR at line 1:
> ORA-00942: table or view does not exist
> ORA-06512: at "MYUSER.LOAD_TOP_CUST", line 3
> ORA-06512: at "MYUSER.LOAD_TOP_CUST", line 8
> ORA-06512: at line 2
>
> The procedure looks like this
>
> CREATE OR REPLACE
> PROCEDURE load_top_cust IS
> CURSOR top_cust_cur IS
> SELECT
> customer_id
> FROM customer_sales;
>
> top_cust_rec top_cust_cur%ROWTYPE;
>
> BEGIN /*load_top_cust*/
> OPEN top_cust_cur;
> LOOP
> . . .
> CLOSE top_cust_cur;
>
> END load_top_cust;
>
> One notable thing I'd like to mention is that the CUSTOMER_SALES table
> was created by a different user, i.e. OTHERUSER. I tried declaring it
> as OTHERUSER.customer_sales but then I cannot even compile the
> procedure.
>
> I granted select, made a public synonym, but nothing seems to work.
>
> Any information is very much appreciated.
>
> Thanks in advance.

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet