Re: Question On authid current_user
- From: Norman Dunbar <oracle@xxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 21 Jan 2013 15:26:56 +0000
Hi Scott,
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
