Re: Restricting procedure calls to a package

This on on 10.1.0.4

create or replace package jwtest
as
   function f1 return varchar2;
   procedure p1;
end;
/

show errors package jwtest


create or replace package body jwtest
as
   function f1 return varchar2
   is
   begin
      return ('F1');
   end;

   procedure p1
   is
   begin
      dbms_output.put_line('P1');
   end;

end;
/

show errors package body jwtest


15:44:44 SQL>grant execute on jwtest to scott;

Grant succeeded.

15:44:44 SQL>
15:44:44 SQL>grant execute on jwtest.f1 to scott;
grant execute on jwtest.f1 to scott
                        *
ERROR at line 1:
ORA-04042: procedure, function, package, or package body does not exist


15:44:44 SQL>grant execute on jwtest.p1 to scott;
grant execute on jwtest.p1 to scott
                        *
ERROR at line 1:
ORA-04042: procedure, function, package, or package body does not exist

So, it appears the answer is 'No'.

Can you say 'write some wrappers'?  ;)


On 4/5/06, John P Weatherman <asahoshi@xxxxxxxxxxxxxx> wrote:
>
> All,
>
> I may well have been reading the Wrong FM, but I haven't seen this
> explicitly ruled out anywhere either.  Is it possible to grant execute
> on only selected procedures within a package or is it an all or nothing
> deal.  My developers have managed to code functional units so that all
> the select/insert/update/delete code is in the same package and they
> won't break it out...but the end users who can perform these functions
> very.  I know I could control it with table level grants, but as PL/SQL
> ignores roles, that means I have to maintain very large permissions
> lists on every user.  There has to be a better way (shy of my writing a
> bunch of additional code to handle user adds and mods).
>
> Thanks,
>
> John
>
>
> --
>
> John P Weatherman
> Oracle DBA
> Madison River Communications
> jweatherman91@xxxxxxxxxxxxxx
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>


--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

Other related posts: