This content has been marked as final. Show 6 replies
grant execute on package will give access to all procedures and functions in the package.1 person found this helpful
You can achieve this by creating a separate function for each function/procedure in package. And give execute permissions on the new function.
Thnaks vlethakula & Osama_mustafa for your reply ..
Vlethakula, the solution you suggested can help but I have a question here, since I need to create a separate function for each function/procedure in package, can I include all the newly created functions in one package?
This is because I need to grant the execute on many functions/procedures from multiple packages, so I’ll include all the required functions in one package and then i’ll grant the execute on the whole package once (it’s much easier that grant on each function individually)!
Thanks once again!
You can, sure.1 person found this helpful
It doesn't sound like it would be particularly wise, though. Assuming that you have different packages in order to properly organize your code, it doesn't make sense to throw away that organization by throwing procedures from many different packages into a single package in order to simplify grants. It would generally make sense to either create a separate wrapper package that has whatever subset of procedures you want to allow from the real package or to break up the original packages into two packages (one with the "safe" procedures that you want to grant and one with the "unsafe" procedures that you want to restrict). It depends, of course, on how you've set up your packages and why you want to grant users access to a subset of the packaged procedures.
Thanks a lot Justin Cave ..
Another way can be to put a little piece of code into critical procedures that checks who is calling them. Better to use a role for such check.
package body MyMixedRightsPkg as function check_role(role_name in string) return boolean as n number:=0; begin select count(*) into n from session_roles where role=role_name; return r>0; end check_role; procedure sensitive_proc_a as begin if not check_role('ACCOUNTANTS') then raise_application_error(-2033,'Error: Must have ACCOUNTANT role'); end if; -- code starts here ... end; procedure sensitive_proc_b as begin if not check_role('CEO') then raise_application_error(-2033,'Error: Must have CEO role'); end if; -- code starts here ... end; end MyMixedRightsPkg;