Skip to Main Content
  • Questions
  • What privilege to view package body

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, ezio.

Asked: November 18, 2015 - 9:23 am UTC

Answered by: Connor McDonald - Last updated: September 17, 2018 - 5:24 am UTC

Category: Database - Version: 9i

Viewed 10K+ times! This question is

You Asked

Hi Tom:
I have a problem when i grant the package privilege to the other user.
A is a normal user which used in factory environment. user B is for app team which can not create anything.
First I grant create any procedure ,execute any procedure and alter any procedure to user B.then user B can view the package body which owned by A.
When I revoke create any procedure privilege from B,Then B still can view the package Spec,but can not view the package body.
Is there any idea about this?

and we said...

That is the whole concept behind the package system - expose the API (the spec) and keep the contents (the body) private.

See https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:52284726269554 for workarounds.

and you rated our response

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

grant debug?

November 19, 2015 - 3:27 pm UTC

Reviewer: A reader

I believe grant debug can solve the OP's issue.

GRANT DEBUG ON <name of package> to <name of user>;

This should make the package body source be visible in ALL_SOURCE, which any user is likely to have access to.

Note SQL Developer 2.1 (which is a really old version) had a bug where it wouldn't show the package body even with grant debug. Only a problem with that product and that version.
Chris Saxon

Followup  

November 20, 2015 - 2:13 am UTC

Thanks for the information. Neat solution

SELECT_CATALOG_ROLE

September 12, 2018 - 2:00 pm UTC

Reviewer: kulauk from UK

Would granting SELECT_CATALOG_ROLE to the user give them the ability to see the package source code?
As i understand it SELECT_CATALOG_ROLE gives read only access to DBA_% views which will include DBA_SOURCE.

What do you think?
Connor McDonald

Followup  

September 17, 2018 - 5:24 am UTC

That is a solution but it seems overkill to me. Yes they get DBA_SOURCE but they get DBA_ everything else as well.