Skip to Main Content
  • Questions
  • Rights to view packages (procedures) source code

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question.

Asked: November 27, 2005 - 2:34 pm UTC

Last updated: November 28, 2005 - 7:34 am UTC

Version: 9.0.1

Viewed 10K+ times! This question is

You Asked

Hi,

is there a way how to grant some database user right to view the source code of my packages and procedures, but not to execute them?
In documentation I found, then I can only grant

GRANT EXECUTE ON mypackage TO any_user;

But I need to enable some other users to view the source code of my packages, but not to execute them.

Thanks,

R. Kazimir

and Tom said...

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';

and grant you select on that view with the grant option:

grant select on your_view to your_account with grant option;


In that manner, you would have a view with all of your "code" visible in it (dba_source does not have any restrictions as to what code may be seen). You can then grant select on that view to whomever you want - or create further views to release on that code you want to release.



Rating

  (4 ratings)

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

Comments

Standard solution

A reader, November 28, 2005 - 2:10 am UTC

Thanks for your answer, but I need "standard" solution - the solution, which works for user using standard tools for viewing package source code, like TOAD.

R. Kazimir

Tom Kyte
November 28, 2005 - 7:34 am UTC

There isn't one, the way security works with the DBA, ALL and USER views predates the existance of proprietary tools like Toad.

TOAD has a 'feature' to help

Alan, November 28, 2005 - 4:24 pm UTC

A while back TOAD put in a feature that helps this package body display problem if you're willing to let the target user see *any* package source. If you go to view/options/startup (or some similar place depending on your TOAD version) there is a checkbox "check for access to DBA views". If this box is checked, and you have access to DBA_SOURCE, then you can display any package spec and body. This works because DBA_SOURCE doesn't have to restrictive predicate that ALL_SOURCE does.

You have to decide whether you're willing to grant access to DBA_SOURCE or not, though.

TOAD doesn't explictly use the schema prefix

Gary, November 28, 2005 - 5:54 pm UTC

As an added note, the versions of TOAD I've used seem to look at DBA_SOURCE rather than specifically at SYS.DBA_SOURCE etc.
If you don't want to show the real DBA_SOURCE, you can create a synonym called DBA_SOURCE in that user that points to your filtered view.
If they do something like ALTER SESSION SET CURRENT_SCHEMA... then it could break.

Problem Fixed

Ganesh, March 17, 2006 - 5:18 am UTC

it was really useful. Got my problem fixed through this review :) Thanks a lot

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