Re: Restricting procedure calls to a package

From: Jared Still <jkstill_at_gmail.com>
Date: Wed, 5 Apr 2006 15:42:38 -0700
Message-ID: <bf46380604051542n7a7569ccs1aab5b12c76dedb4@mail.gmail.com>

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_at_infionline.net> 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_at_alumni.wfu.edu
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Apr 05 2006 - 17:42:38 CDT