12 Replies Latest reply on Oct 15, 2010 3:46 PM by krzyhu

    Privilege to view package body

    450238
      Hi all,
      I have two users... user 'A' and user 'B' (real user names changed to protect the innocent). User A creates a package spec and package body.

      I want user B to be able to see the code in the package body that user A owns. Ideally I would like user B to be able to view the body code in TOAD via the schema browser or via a DESC. Is this possible?

      Thanks,
      Scott

      PS user B can see the package spec just fine...
        • 1. Re: Privilege to view package body
          48387
          Scott,

          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.
          • 2. Re: Privilege to view package body
            181444
            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 --
            • 3. Re: Privilege to view package body
              417914
              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.
              • 4. Re: Privilege to view package body
                181444
                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 --
                • 5. Re: Privilege to view package body
                  21621
                  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.
                  • 6. Re: Privilege to view package body
                    523295
                    Thanks amihajlovic, #2 (Change default settings under View -> Options -> StartUp to "Check for Access to DBA Views") worked for me.
                    • 7. Re: Privilege to view package body
                      748567
                      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.
                      • 8. Re: Privilege to view package body
                        88321
                        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
                        • 9. Re: Privilege to view package body
                          jnrjian
                          select any dictionary is not needed. two tables dba_objects and dba_source is enough.
                          • 10. Re: Privilege to view package body
                            705081
                            On 10gR2 (and possibly earlier databases) I find it is enough for the owning user to ...
                            GRANT DEBUG ON my_pkg TO other_role
                            where other_role is a role whose users should see the code. Presumably, granting to other_user would also work.

                            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.

                            Nick
                            • 11. Re: Privilege to view package body
                              741551
                              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
                              • 12. Re: Privilege to view package body
                                krzyhu
                                Hi,

                                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.

                                Kind regards,
                                Krzysztof