Re: Q: How to see another users pkg-bodies without dba-role

From: RSH <RSH_Oracle_at_worldnet.att.net>
Date: Thu, 28 Feb 2002 14:30:10 GMT
Message-ID: <S9rf8.5300$106.336365@bgtnsc05-news.ops.worldnet.att.net>

Thomas, let me say first that that was done just beautifully; I was all set to say something like "geez, you ought to be working for Oracle" when I discovered that issue was moot,

But anyhow, especially since I know you work for da family, I gotta beef here--

Why should anyone with just execute privileges be able to see the code or anything else inside the objects they happened to have been granted execute rights upon? (God, is that actually a sentence? I'm tired...)

With the sometimes overly abundant and highly granular privilege mechanism, a far cry from the old days of:

connect, resource, dba

and of course object level privileges which back in those days didn't have to concern themselves with things like 'execute' unless it meant the DBA, its surprising, if not shocking, to me, to learn that people with merely execute privs can view any part of the code or code skeleton. Why should they?

I mean as primitive as UNIX is with its permission scheme (compared with Oracle), you can allow someone to execute something, but not inspect the code; I should think that amounts to big boo boo somewhere in Oracle land.

I'd further propose {yes yes, I know this isn't the place to do it, etcetera} that while they're at it:

1. Certainly, revise that view and any others so that EXECUTE people can't read the code they are allowed to run; that seems absurd it ever made it out of Q/A etc.
2. An object level privilege should be created called "INSPECT" (or whatever you want to call it, that's a 20 year hangover from PLATO) that allows owners of procedures, triggers, functions, packages, package bodies, views and materialized views, and whatever else in Oracle these days that is a logic type object, that could be granted to a role or user, allowing them to see but not touch the code/sql/etc the owner decides is okay for them to look at. Obviously a GRANT EDIT for same would turn Oracle security upside down and inside out and I'm not suggesting THAT.
3. Something has to be done about TRUNCATE. I've seen grossness that clients came up with that they were actually proud of; I'm sure you know there are people out there creating procedures and packages under SYS to get around the problem of this neither fish nor fowl command. The bright boys in CA ought to be able to cobble together something that supports GRANT TRUNCATE on xxx TO zzz, as an alternative to some of the disasters I've seen that people thought were clever. And like any other object level priv, the owner should be the one able to grant it.

But actually, all this started as a note to compliment you on your excellent write-up here.

RSH. "Thomas Kyte" <tkyte_at_us.oracle.com> wrote in message news:a5j1rs025su_at_drn.newsguy.com...
> In article <178d2795.0202270723.66ac1c69_at_posting.google.com>,
> mark.powell_at_eds.com says...
> >
> >"Reinhard" <Reinhard_nospam.Wahl_at_gmx.de> wrote in message
> >news:<a5ica0$7g33j$1_at_ID-119204.news.dfncis.de>...
> >> Hi,
> >>
> >> what privilege does user A need to see the sources of the package
bodies of
> >> user B if he has no dba-rights?
> >> I tried some of the privileges of the dba-role, but there are to many
and
> >> user A has to reconnect every time again because ORACLE seems to
remember
> >> the rights from the last login.
> >>
> >> Does anyone know the decisively privilege?
> >> Regards
> >> Reinhard
> >> --------------------------------------------------------------
> >> For direct reply please remove _nospam from address
> >
> >If you can execute the package you have select privilege on its source
> >in the all_source dictionary view.
> >

>

> not entirely....

>
> you can see the source of standalone procedures/functions and package
specs.
> But you cannot in general see the package bodies.

>

> The view all_source has:

>


>     o.owner# in (userenv('SCHEMAID'), 1 /* PUBLIC */)

>     or

>     (

>       (

>          (

>           (o.type# = 7 or o.type# = 8 or o.type# = 9)

>           and

>           o.obj# in (select obj# from sys.objauth$

>                      where grantee# in (select kzsrorol from x$kzsro)

>                        and privilege#  = 12 /* EXECUTE */)

>         )

>         or

>         exists

>         (

>           select null from sys.sysauth$

>           where grantee# in (select kzsrorol from x$kzsro)

>           and

>           (

>             (

>               /* procedure */

>               (o.type# = 7 or o.type# = 8 or o.type# = 9)

>               and

>               (

>                 privilege# = -144 /* EXECUTE ANY PROCEDURE */

>                 or

>                 privilege# = -141 /* CREATE ANY PROCEDURE */

>               )

>             )

>         or

>             (

>               /* package body */

>               o.type# = 11 and

>               privilege# = -141 /* CREATE ANY PROCEDURE */

>             )

> ......

>
>

> so you can see if if you are the OWNER or you have the CREATE ANY
PROCEDURE priv
> (which DBA does, hence DBA's could see it)

>
>

> for example:

>

> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create user test identified by test;
> User created.

>

> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> grant create session to test;
> Grant succeeded.

>

> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace package my_pkg
> 2 as
> 3 procedure p;
> 4 end;
> 5 /
> Package created.

>

> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace package body my_pkg
> 2 as
> 3 procedure p
> 4 is
> 5 begin
> 6 null;
> 7 end;
> 8 end;
> 9 /
> Package body created.

>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace procedure p as begin
null;
> end;
> 2 /
> Procedure created.

>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace function f return
number as
> begin return 0; end;
> 2 /
> Function created.

>

> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> grant execute on my_pkg to test;
> Grant succeeded.

>

> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> grant execute on p to test;
> Grant succeeded.

>

> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> grant execute on f to test;
> Grant succeeded.

>

> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> @connect test/test

>

> test_at_ORA817DEV.US.ORACLE.COM> select owner, type, name, text
> 2 from all_source
> 3 where owner = 'OPS$TKYTE'
> 4 and name in ( 'MY_PKG', 'P', 'F' )
> 5 /
> OWNER TYPE NAME TEXT
> --------- ------------ ------------------------------
> ------------------------------
> OPS$TKYTE FUNCTION F function f return
number
> as be
> gin return 0; end;

>


> OPS$TKYTE PACKAGE      MY_PKG                         package my_pkg

> OPS$TKYTE PACKAGE      MY_PKG                         as

> OPS$TKYTE PACKAGE      MY_PKG                           procedure p;

> OPS$TKYTE PACKAGE      MY_PKG                         end;

> OPS$TKYTE PROCEDURE    P                              procedure p as begin

null;


> end

>                                                       ;

>
>

> 6 rows selected.

>

> test_at_ORA817DEV.US.ORACLE.COM> @connect /
> test_at_ORA817DEV.US.ORACLE.COM> set termout off
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> GET afiedt.buf NOLIST
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> set termout on
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> grant create any procedure to test;

>

> Grant succeeded.

>

> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> @connect test/test
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> set termout off
> test_at_ORA817DEV.US.ORACLE.COM> GET afiedt.buf NOLIST
> test_at_ORA817DEV.US.ORACLE.COM> set termout on
> test_at_ORA817DEV.US.ORACLE.COM> select owner, type, name, text
> 2 from all_source
> 3 where owner = 'OPS$TKYTE'
> 4 and name in ( 'MY_PKG', 'P', 'F' )
> 5 /
> OWNER TYPE NAME TEXT
> --------- ------------ ------------------------------
> ------------------------------
> OPS$TKYTE FUNCTION F function f return
number
> as be
> gin return 0; end;

>


> OPS$TKYTE PACKAGE      MY_PKG                         package my_pkg

> OPS$TKYTE PACKAGE      MY_PKG                         as

> OPS$TKYTE PACKAGE      MY_PKG                           procedure p;

> OPS$TKYTE PACKAGE      MY_PKG                         end;

> OPS$TKYTE PACKAGE BODY MY_PKG                         package body my_pkg

> OPS$TKYTE PACKAGE BODY MY_PKG                         as

> OPS$TKYTE PACKAGE BODY MY_PKG                           procedure p

> OPS$TKYTE PACKAGE BODY MY_PKG                           is

> OPS$TKYTE PACKAGE BODY MY_PKG                           begin

> OPS$TKYTE PACKAGE BODY MY_PKG                                   null;

> OPS$TKYTE PACKAGE BODY MY_PKG                           end;

> OPS$TKYTE PACKAGE BODY MY_PKG                         end;

> OPS$TKYTE PROCEDURE    P                              procedure p as begin

null;


> end

>                                                       ;

>
>

> 14 rows selected.

>

> test_at_ORA817DEV.US.ORACLE.COM>

>
>

> NOT THAT I RECOMMEND GIVING 'CREATE ANY PROCEDURE' to your accounts!!!
That
> would be bad.

>
> Perhaps it would be better to grant select on DBA_SOURCE to these selected
users
> when connected as SYS or as SYSDBA (internal). DBA_SOURCE view has no
such
> "filters" on it.

>
>
>


> >If you are not familar with the rdbms dictionary you might want to

> >review the following articles:

> >

> >How do I find information about a database object: table, index,

> >constraint, view, etc... in Oracle ?

> >at url http://www.jlcomp.demon.co.uk/faq/privileges.html

> >

> >And for security related information

> >

> >How do I find out which users have the rights, or privileges, to

> >access a given object ?

> >at url http://www.jlcomp.demon.co.uk/faq/object_info.html

> >

> >HTH
> >-- Mark D Powell --

>


> --

> Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/

> Expert one on one Oracle, programming techniques and solutions for Oracle.
> http://www.amazon.com/exec/obidos/ASIN/1861004826/
> Opinions are mine and do not necessarily reflect those of Oracle Corp
> Received on Thu Feb 28 2002 - 08:30:10 CST