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