| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Q: How to see another users pkg-bodies without dba-role
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;
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, text2 from all_source
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 CorpReceived on Wed Feb 27 2002 - 10:35:08 CST
 |
 |