Re: package body in all

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 23 Oct 1999 09:21:42 -0400
Message-ID: <NbUROHeJ+Kf8QZ4rchoj+DcVXj5H@4ax.com>

A copy of this was sent to "Ludek Novotny" <novot_at_post.tele.dk> (if that email address didn't require changing) On Sat, 23 Oct 1999 13:16:02 +0200, you wrote:

>Hi,
>
>What privileges/roles does an user need to be able to see another users
>package _body_ in sys.all_source? Or: Is it possible to grant such an access
>to a specific package? When I use "grant execute on package_name to user_a",
>user_a can only see the public part of the package.
>
>Thanks
>
>Ludek Novotny, Roskilde, Denmark
>

scott_at_8i> select text from all_views where view_name = 'ALL_SOURCE';

TEXT

select u.name, o.name,
decode(o.type#, 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',

                11, 'PACKAGE BODY', 13, 'TYPE', 14, 'TYPE BODY',
                28, 'JAVA SOURCE', 'UNDEFINED'),

s.line, s.source
from sys.obj$ o, sys.source$ s, sys.user$ u

where o.obj# = s.obj#
  and o.owner# = u.user#
  and o.type# in (7, 8, 9, 11, 13, 14, 28)

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

      (
         (
          (o.type# = 7 or o.type# = 8 or o.type# = 9 or o.type# = 28)
          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 or o.type# = 28)
              and
              (
                privilege# = -144 /* EXECUTE ANY PROCEDURE */
                or
                privilege# = -141 /* CREATE ANY PROCEDURE */
              )
            )
            or
            (
              /* package body */
              o.type# = 11 and
              privilege# = -141 /* CREATE ANY PROCEDURE */
            )
            or
            (
              /* type */
              o.type# = 13
              and
              (
                privilege# = -184 /* EXECUTE ANY TYPE */
                or
                privilege# = -181 /* CREATE ANY TYPE */
              )
            )
            or
            (
              /* type body */
              o.type# = 14 and
              privilege# = -181 /* CREATE ANY TYPE */
            )
          )
        )
      )

)
)

that shows you can see the body of the package if you have "CREATE ANY PROCEDURE" (very very very powerful priv -- don't just give that one away)....

the view DBA_SOURCE does not have this restriction.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat Oct 23 1999 - 08:21:42 CDT

Re: package body in all_tables