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 --
I stand corrected. I should have looked closer at my test case and
test id privileges.
Received on Thu Feb 28 2002 - 10:02:35 CST
