We help IT Professionals succeed at work.

oracle permissions to role and packages

Jayesh Acharya
Jayesh Acharya used Ask the Experts™
on
I am trying to grant priviledges to packages via roles rather then the users:

Schema M1 has package abc1
procedure print_message

Schema M2 has a package edf2
procedure hello

procedure hello uses the procedure print_message from package abc1

I want to grant execute privildges to M2 for package abc1 via a role, but when I do this I get a pls-00201 error. If i grant  execute privildges to M2 for package abc1 directly i can get past the error, but I want to really manage this via a role, is there any way i can do this.


CREATE OR REPLACE PUBLIC SYNONYM abc1 FOR M1.abc1;

CREATE ROLE APP_ROLE;

grant execute on abc1 to APP_ROLE;

GRANT APP_ROLE TO M2;

now I try to compile packge edf and get the  pls-00201 error

but if i grant

grant execute on abc1 to M2;

I dont get the error


Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
You cannot.  Execution to packages must be explicitly granted to the user.  It has to do with the context of how Oracle works.

There are many blogs/papers out there that go in the reasons if you are interested.
Most Valuable Expert 2011
Top Expert 2012
Commented:
privileges granted to roles do not apply inside pl/sql  

you must either grant directly, as you have already

you "might" want to explore changing the authid from definer to current user,  but that's rarely what people really want.

Most Valuable Expert 2011
Top Expert 2012
Commented:
Jayesh AcharyaTechnichal Consultant

Author

Commented:
Thanks I am actually using authid current user on all the package definitions...

but I have tried to create levels of packages;

some for admins,

Some for developers / production support

The rest would be calls made by external programs ...

I really wanted a way to reduce the managment of this, But I will probably create a function that will read the packages associted with the role and the users that are associted with the role and let the proceudre then grant execute to the appropriate people ..

just one more peice for the to remember during admin