This discussion is archived
6 Replies Latest reply: Jan 28, 2013 1:09 PM by Mark Malakanov (user11181920) RSS

Grant EXCUTE privilege on some functions of a package

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points