Jayesh Acharya
asked on
oracle permissions to role and packages
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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