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

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question.

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

Answered by: Tom Kyte - Last updated: November 28, 2005 - 7:34 am UTC

Category: Database - 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 we 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.



and you rated our response

  (4 ratings)

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

Reviews

Standard solution

November 28, 2005 - 2:10 am UTC

Reviewer: A reader

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

Followup  

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

November 28, 2005 - 4:24 pm UTC

Reviewer: Alan from Washington DC

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

November 28, 2005 - 5:54 pm UTC

Reviewer: Gary from Sydney, Aus

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

March 17, 2006 - 5:18 am UTC

Reviewer: Ganesh from Chennai,Tamil Nadu, India

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