-
Does one need to grant SELECT on a table referenced in dynamic SQL in a stored proc. in a package? I'm using a standard "definers' rights" type of package in Oracle 8.1.6. I dynamically build a SELECT statement an execute it with an
OPEN cursor FOR sql_string;
sql_string is my dynamically-built SQL command. I'm returning this to ADO in a VB 6 app., using an OLE-DB connect string that specifies the MS OLE-DB provider for Oracle. I grant EXECUTE on the package to scott, connect with scott, and execute the stored proc. in the package. I get an error saying table or view not found. If I grant SELECT on the table to scott, he can execute it without errors. I've also tested this using a role, where I grant EXECUTE on the package to the role, and grant the role to scott - same error! My conclusion is that tables in dynamic SQL must be granted explicitly to all users/ roles using them.
-
Yes.
You need to give SELECT privilege directly to user or role.
-
The 'problem' is the package, not dynamic SQL.
Roles are disabled/invisible in stored procedures/packages. You cannot use privileges granted to roles in packages, only privileges granted directly to the user.
-
I've succeeded in giving scott the right priviledges but with roles, and not by granting permissions directly to scott. I gave EXECUTE permission on the packages, and stand-alone stored procs. and functions to scott's role. I noticed however, that scott still didn't have access to certain tables, which turned out to be those that were used with REF CURSORs to SELECT. After giving SELECT permission to scott's role, on those particular tables, it works. I would have preferred though, not to have to give SELECT persmission to anyone on my tables.
