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

oracle privileges grant

Ersin Gülbahar · Sep 13, 2012 · Viewed 57.1k times · Source

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.

Answer

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)