What is the privilege that allows a user to be able to see the contents of a PACKAGE BODY? Currently, a user with EXECUTE rights can only see the PACKAGE SPEC from ALL_SOURCE. Are there other views which might display this information?
We're investigating what kind of security to put in place on PACKAGES that generate a default pin based on a user's name. We're wondering if using the wrap utility is overkill, if we're comfortable with a situation where only Analysts and Developers can view the source code.
But only for privileged users (I'm guessing the SELECT_CATALOG_ROLE is supplying this privilege):
SQL> connect forbesc@dev
Enter password: 123456
SQL> select distinct name,type
2 from all_source
3 where name = 'SHARED_PG';
SHARED_PG PACKAGE BODY
SQL> connect suzieq@dev
Enter password: 12345
Sorry, I misunderstood your question. If you have a package that generates PIN numbers based on an internal permutation using the users name, then I would wrap the package. Even within the developer group (except the pin generation writer), this should not be accessible.
You do not need a parachute to skydive. You only need a parachute to skydive twice.
1) Can a user that can execute the PACKAGE see the BODY
2) If we want to go a step further, how do we conceal the BODY from developers and analysts as well.
My simple test against ALL_SOURCE shows that the BODY is concealed from suzieq. I was looking to the forum to see if there were any DBMS_x utilities or other VIEWs that may still give access to the PACKAGE BODY. So I considered #1 only topically researched.
The documentation for ALL_SOURCE simply mentions "stored objects accessible to the current user" without describing how this is derived, but it seems to require CREATE ANY PROCEDURE to view another schema's procedure/function/package body source, and CREATE ANY TYPE for type bodies, or just DEBUG ANY PROCEDURE (check the source of the ALL_SOURCE view). These are included in the default DBA role. The EXECUTE ANY xxx privileges only let you see header (specification) source.
If wrapping code, the practice of including a VERSION() function in every package spec and body (using tags automatically updated by your source control system) will make version control a lot easier.
Source code control systems such as Subversion, CVS and PVCS have a system of tags such as $LastChangedRevision$ that you can embed in version-controlled text files and that are automatically updated with the current information on checkout. What I have done whenever possible is to ensure that all packages contain a function called VERSION() that returns this information, so that a package can report its own version information (ideally keeping spec and body versions separate). Standalone procedures and functions can't do this but could at least contain the tags in a header block.
If you don't do this and you wrap source code, you could end up trying to support a system where you do not know what source code is in use, although of course this depends on the rest of your version control procedures - for example Oracle knows the version of, say, DBMS_STATS included in any given release without needing a VERSION() function.