view package body source code 2

[huchen]

Hello,

I have schema A and B

A has packages for all applications.

B as a developer can create package in his own schema, and can view source code of A's packages(A.pkg_1).
B is not allowed to compile A.pkg_1

what kind of privilege I should give to developer B so he can view source code from A.pkg_1?

Thank you.

 

[SantaMufasa]

B...can view source code of A's packages (A.pkg_1)...what kind of privilege I should give to developer B so he can view source code from A.pkg_1?

Huchen, I am puzzled...if B can already view source code of A's packages, why are you asking what kind of privilege you should give to B to view source code from A?


If a user cannot view another user's user-defined function/procedure/package source code, and if you want them to see the other user's object's source code, you have the owning user issue this command:

GRANT EXECUTE ON <object_name> TO <target username>;

Following the GRANT, the target user can issue this command to see the source user's object code:

select text
from all_source
where owner = '<source owner name>'
  and name = '<object name>'
order by line;

Please let us know if this answers your question.






Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[karluk]

To the best of my knowledge there is no special privilege needed for B to get the source code for A's packages. B should be able to do this through the dbms_metadata package. All B needs is execute on dbms_metadata, which is by default granted to public.

set long 50000
set pagesiz 50
select dbms_metadata.get_ddl('PACKAGE', 'PKG_1', 'A') from dual;
select dbms_metadata.get_ddl('PACKAGE_BODY', 'PKG_1', 'A') from dual;

 

[huchen]

Thank you Santa and Karluk.

Sorry for confusion. I want B to view A.pkg_1 source code.
after "GRANT EXECUTE", B can only view the header of A.pkg_1, B needs to see the package body of A.pkg_1

Karluk's solution is good, but the linesize from the result is 80.
I did "set linesize 400", and it does not work.
How can I change the line size?

Thank you.

 

[huchen]

Hello, I am all set with this.
I just grant select any dictionary to B, and B can view all packages now.

Is there any drawback if I grant select any dictionary to a big user group?

Thanks