How can I give to user, execute another users's functions?

Question

I want to give privileges to a user to call a function another users function.

I write this : GRANT EXECUTE ANY FUNCTION TO user;

but it doesn't work.

user need to call this:

call XXX.YYY.AlterAllInvalidObjects(NULL,'PACKAGE BODY');

but how can I give grant ?

NOTE : I don't want to use : GRANT EXECUTE ON FUNCTION AlterAllInvalidObjects TO user; I need general solution not specific function name.

Raphaël Althaus · Accepted Answer · 2012-09-13T13:47:29.373

GRANT EXECUTE ANY PROCEDURE TO user;

will manage both functions and procedures. You can't grant that way to functions only...

EDIT

the other way (but you'll have to run it every time the other user will create a new function to get all the grants):

Run

select 'GRANT EXECUTE ON '||owner||'.'||object_name||' TO user;'
from all_objects
where owner = 'xxx'
and object_type='FUNCTION';

and copy-paste-execute the result...

or use a SP doing the same thing (cursor on the query and execute immediate in loop)

score 0 · Answer 2 · answered Sep 13 '12 at 11:26

To be honest it looks like you're asking about synonyms, not grants because grants doesn't help you to invoke call AlterAllInvalidObjects without mentioning the schema. Please consider about http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7001.htm

2 Answers2