Re: DB Security: PL/SQL and Roles

I believe what ORACLE is saying is that you can not COMPILE a procedure unless you have direct access to the objects. If compiled by the objects' owner or by another userid with directly granted access to the objects, you can then grant execute on this procedure to a role, grant table permissions to this role, you can assign this role to another user and it will work. We have this set up in our shop and it works fine.

The only exception is for dynamic SQL - since it is dynamic, you can think of it as not really being compiled as such and therefore when it is executed from within a procedure, ORACLE sort of 'compiles' it and requires directly granted table access.

Wayne Linton
Shell Canada Ltd. Received on Mon Jul 07 1997 - 00:00:00 CEST