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

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, ezio.

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

Last updated: October 31, 2022 - 4:07 am UTC

Version: 9i

Viewed 50K+ 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 Connor 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.

Rating

  (3 ratings)

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

Comments

grant debug?

A reader, November 19, 2015 - 3:27 pm UTC

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
November 20, 2015 - 2:13 am UTC

Thanks for the information. Neat solution

SELECT_CATALOG_ROLE

kulauk, September 12, 2018 - 2:00 pm UTC

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
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.


DEBUG privilege on package

A reader, October 29, 2022 - 7:38 pm UTC

Greetings !

We have Developers asking for DEBUG on package for them to see the code for verification after the database deployment in production. Dev connects to database via their read-only account from SQL Developer.

Can you please clarify and provide your thoughts ?

1. Is it safe to grant DEBUG on package to Read-only users . just for reading the package body ?

Read-only users who got DEBUG package privilege,, Can they do
more than just reading the package and package body. like compilation etc.. ?

We want to limit them to see the package/package body only.

Thanks for your time!
Connor McDonald
October 31, 2022 - 4:07 am UTC

One method would be to have the DBA create a view of DBA_SOURCE for you:

create view your_view
as
select * from dba_source
where owner = 'YOUR_ACCOUNT';

Then no additional privs needed.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library