Re: Stored procedures ignore grants

In article <3dd294$e26_at_lll-winken.llnl.gov>, 68a869_at_llnl.gov (Jeff Busch) writes:
|> I have found that I cannot compile a stored procedure which selects from
|> another user's table if my grant on that table is role-based. I get an
|> error "Table or view does not exist". If I am given an individual grant
|> on the table, I have no problem.
|>
|> For example:
|> sqlplus user_1/password
|> create table people (name varchar2(5));
|> create role see_people;
|> grant select on people to see_people;
|> grant see_people to user_2;
|>
|> User_2 cannot create a stored procedure accessing user1.people
|>
|> sqlplus user_1/password
|> grant select on people to user_2;
|>
|> User_2 can now create a stored procedure accessing user1.people
|>
|> I am using Oracle 7.1.4 Has anyone else run across this problem?
|>
|>
|> ---
|> Jeff Busch Internet busch1_at_llnl.gov
|> Voice (510) 423-9134 Fax (408) 263-1360
|>
|>

This 'feature' is a result of the fact that PL/SQL uses early binding. All references to Oracle objects are done when the procedure is compiled. This makes running the procedure much faster, since the checks for object existence and permissions don't have to be performed then. This is done by maintaining a link between the procedure and the object. When the object is invalidated, the procedure is also marked as invalid, and thus will need to be recompiled (this will also happen automaticly). The problem is that grants affect the data dictionary, and are valid database-wide. But roles can be enabled and disabled for a particular session. Say you create a procedure using a role in one session. Then in another session, you disable the role. Should the procedure stay valid? How about if another user executes the procedure? To avoid such a paradox, all roles are disabled inside stored procedures and triggers. You can only reference objects to which you have been granted direct access (not via a role). Received on Fri Dec 23 1994 - 19:22:06 CET