6 Replies Latest reply: Jan 28, 2013 3:09 PM by Mark Malakanov (user11181920) RSS

    Grant EXCUTE privilege on some functions of a package

    DU
      Hi all,

      I need to grant execute privilege to a user on only some functions of a package, while the other functions, inside the package, should not be accessed by the user.

      Any advice how to do this pls?
        • 1. Re: Grant EXCUTE privilege on some functions of a package
          vlethakula
          grant execute on package will give access to all procedures and functions in the package.

          You can achieve this by creating a separate function for each function/procedure in package. And give execute permissions on the new function.

          Check

          http://dbaforums.org/oracle/index.php?showtopic=8134
          • 2. Re: Grant EXCUTE privilege on some functions of a package
            Osama_Mustafa
            grant execute on a package to a user, that user can execute all of the procedures and functions defined in the package spec.

            Similar thread
            granting execute on a Package Body ?
            • 3. Re: Grant EXCUTE privilege on some functions of a package
              DU
              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!
              • 4. Re: Grant EXCUTE privilege on some functions of a package
                JustinCave
                You can, sure.

                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.

                Justin
                • 5. Re: Grant EXCUTE privilege on some functions of a package
                  DU
                  Thanks a lot Justin Cave ..
                  • 6. Re: Grant EXCUTE privilege on some functions of a package
                    Mark Malakanov (user11181920)
                    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;