Read Access to Package Bodies
I’ve recently been asked to grant a user permission to read a package body owned by another user. It’s surprisingly difficult to do.
There’s no object privilege that you can grant that will do this, instead you have to grant read access on DBA_SOURCE to the second user for them to see the first user’s source code. One problem with this is that you’ll grant them the right to read everybody’s code. If this is an issue (and it may well be) you’ll have to create a view which limits the rows that can be returned:
CREATE VIEW some_source AS SELECT * FROM sys.dba_source WHERE owner = 'X' AND name = 'Y'
Remember to follow my earlier tip with regard to granting permissions if you take this approach.
If you’re happy to grant the straight DBA view to the other user, and they want to view the package source using TOAD, there’s a bit more to do. Firstly, they’ll need access to DBA_OBJECTS as well. Secondly, they’ll need to tweak their TOAD setup as follows:
- Select View/Options from the menu.
- Select “StartUp” from the tree view on the left.
- Tick the “Check for access to DBA views” checkbox.
Next time they log on they’ll be able to see package bodies.
Incidentally, there’s an important moral to this story: Comment your packages in the specification, not the body (well, OK, you should comment the body too). Most of the time you won’t want to do the above hacking, so people need to be able to work out what a packaged program unit does from looking at the part of the package they can see!