Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Viewing PACKAGE BODY

    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.

    -Chuck

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    The body is in all_source also. For example to see the source for a package, simply type:

    select text
    from all_source
    where type = 'PACKAGE BODY'
    and name = 'MY_PACKAGE'
    ORDER BY LINE;


    Using wrap is secure and easy. You only have to make sure that you maintain an unwrapped copy of your package source.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    But only for privileged users (I'm guessing the SELECT_CATALOG_ROLE is supplying this privilege):

    Code:
    SQL> connect forbesc@dev
    Enter password: 123456
    Connected.
    SQL> select distinct name,type
      2  from all_source
      3  where name = 'SHARED_PG';
    
    NAME                           TYPE
    ------------------------------ ------------
    SHARED_PG                      PACKAGE
    SHARED_PG                      PACKAGE BODY
    
    SQL> connect suzieq@dev
    Enter password: 12345
    Connected.
    SQL> /
    
    NAME                           TYPE
    ------------------------------ ------------
    SHARED_PG                      PACKAGE
    -Chuck

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    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.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    That's still on the table. Mgmt wants to know:

    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 wrap utility answers #1 & #2.

    -Chuck

  6. #6
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    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.
    Last edited by WilliamR; 05-04-06 at 04:12.

  7. #7
    Join Date
    Dec 2003
    Posts
    1,074
    Thanks WilliamR.

    Could you explain your concept of using a VERSION() function in wrapped code a bit more?

    Thx
    -cf

  8. #8
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    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.
    Last edited by WilliamR; 05-04-06 at 13:09.

  9. #9
    Join Date
    Dec 2003
    Posts
    1,074
    That's a good idea, thanks for the explanation.
    -cf

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •