Re: Question On authid current_user

On 21/01/13 14:57, Scott Canaan wrote:
> I created a stored procedure that analyzes the tables in a small schema. The procedure is owned by one user and was created with authid current_user, so it can be run by another user. It runs fine as the other user, as long as it is called directly by that user. When it is put inside a package, an ORA-01031: insufficient privileges error is raised. The customer is asking me to grant "execute any" privilege to the second user so that it will run. I'm not convinced that will solve the problem and I don't want to do this grant. What other options are there for getting this procedure to run inside the package?

If the procedure is owned by schema_a then if schema_b wants to run it as part of a schema_b package, then schema_b should be granted execute on schema_a's procedure directly to the user and not indeirectly to a role.

If the grant is via a role, then when the pl/sql code is being executed, all roles are off - so no privs to execute the code.

Execute any procedure, as you are aware, will allow your other schema the ability to execute code owned by SYS, for example, which is most likely a bad thing!

schema_a needs to GRANT EXECUTE ON MY_PROCEDURE TO SCHEMA_B;

HTH Cheers,
Norm.

-- 
Norman Dunbar
Dunbar IT Consultants Ltd

Registered address:
Thorpe House
61 Richardshaw Lane
Pudsey
West Yorkshire
United Kingdom
LS28 7EL

Company Number: 05132767
--
http://www.freelists.org/webpage/oracle-l