This content has been marked as final. Show 12 replies
The only way is to grant EXECUTE permissions on the package (but do you really want to do that, since that opens up being able to change to package along with it. ESPECIALLY IF THEY WANT TO VIEW THE PACKAGE IN PRODUCTION).
You could grant SELECT on DBA_SOURCE and query for the package code.
Having the execute privilege on a package should not give anyone the ability to see the source for the package body in all_source and if definitely does not give the user the ability to change the package. The privilege only allows them to execute the package.
By default only the owner and DBA privileged users can read both the package specification and the body from all_source. Having execute privilege will allow a user to read the specification.
One way to provide access is to create your own version of the all_source views and grant this to whoever needs the access. You can either write a very specific tailored view for the one user or create a user security table that you use to control who can see what via this special view. Plus you have to grant select access to the special view before anyone can use it.
HTH -- Mark D Powell --
It's a little bit tricky.
If you want User 'B' to be able to view package body of a package owned by User 'A' but not able to alter/compile it. Grant select on dba_source to the user 'B'.
If you've PL/SQL developer, check tools>preferences>options>use DBA views if available.
Otherwise, create your own report based on dba_source table.
Granting select on dba_source to user B is fine as long as you want to allow user B to see the source for all users. If you only want to grant access to user A's stored procedures then this will not work.
Duplicating the Oracle view and removing the filtering line replacing it with a filter for the target user's objects on the other hand is not difficult.
The best approach will depend on the specific need.
HTH -- Mark D Powell --
To view another user Package Body using Toad, you will need to do two things:
1. Have yourself granted system privilege "select any dictionary", (sole access to dba_source dictionary view might work as well but I didn't test it)
2. Change default settings under View -> Options -> StartUp to "Check for Access to DBA Views"
This worked for me.
Thanks amihajlovic, #2 (Change default settings under View -> Options -> StartUp to "Check for Access to DBA Views") worked for me.
To view package body from toad, I had to do both
1. grant select any dictionary to <user>
2. In TOAD , went to View -> Options -> Startup , and checked "Check for Access to DBA Views"
Then only I was ablt to see the package bodies of other schema.
Thanks to prior posts.
I found equivalent setting in PL/SQL Developer: Tools --> Preferences --> Oracle --> Options --> Use DBA Views if available.
User was granted SELECT ANY DICTIONARY
select any dictionary is not needed. two tables dba_objects and dba_source is enough.
On 10gR2 (and possibly earlier databases) I find it is enough for the owning user to ...
where other_role is a role whose users should see the code. Presumably, granting to other_user would also work.
GRANT DEBUG ON my_pkg TO other_role
I'm not sure, though, whether this works when the other_role does not have EXECUTE privilege.
This probably won't work for PL/SQL or TOAD access, but it certainly makes the source visible via ALL_SOURCE.
I had to do this as well for PL/SQL Developer
1.) Grant select on dba_source to whatever role (user doesnt need to be granted select any dictionary)
2.) Grant debug on individual package or grant debug all procedures to whatever role
2.) Ensure the following is checked Tools -> Preferences -> Oracle -> Options -> Use DBA Views when available
Without granting debug, all my users could see were the headers
Edited by: sean_seannery on May 12, 2010 11:09 AM
I believe that A is application schema owner and B is a support user or role that shouldn't be able to execute or modify anything within that schema.
Some colleagues suggest to grant the user B with 'select any dictionary' or with 'select on dba_' tables. This is obsolete, I believe, since 10g.
GRANT DEBUG ON A.PACKAGE_NAME TO B
This should be enough for B to run: DESC A.PACKAGE_NAME
However, SELECT A.PACKAGE_NAME.FUNCTION(X) FROM DUAL should return ORA-01031: insufficient privileges.