Is there a privilege in Oracle 10gR2, which can be granted to users via role to execute any procedure/ function of all packages of one specific schema in their own procedures and functions? For example schemas "user1", "user2", "user3" should be granted a role, which enables calling a procedure of schema "produser" under a procedure that has been created for that schema.
There is a "GRANT EXECUTE ON ANY PROCEDURE" system level privilege, but this is too powerful. If a privilege is granted explicitly to the user for one package (i.e. "GRANT EXECUTE ON produser.package_1 TO user1"), it works, but it's not dynamic and would like to have a role based privilege.
I hope someone can help me. If something or everything is unclear, please let me know.
Thank you in advance:)
There isn't any way to do that with built-in roles or privileges, no. You'd need to create your own role and grant that to your users:
create role prod_access;
grant prod_access to user1;
grant prod_access to user2;
...
Then grant privileges to that role for all your existing packages, or at least those you wan to be exposed:
grant execute on produser.package_1 to prod_access;
Whenever you add a new package part of the deployment would be to grant execute on that to the role. And if/when you add a new user, grant them your role as part if their creation.