1 2 Previous Next 15 Replies Latest reply on Jan 18, 2008 8:57 PM by JustinCave

    select permission on packages


      can i give a select access to a package to another schema? I dont want the execute grant given to another schema, they just need to see the code.

      how can i do this?

        • 1. Re: select permission on packages
          Himanshu Kandpal

          you can give that user "CREATE ANY PROCEDURE" previlage which would allow the user to see the the source for the package specification and body, and change that code, but not execute the package. THis is risky also as it gives then to view all the packages.

          • 2. Re: select permission on packages
            If i give this permission, the user can create a procedure or function in that schema right?

            i dont want the user to create any object like procedure, package etc. but i need to grant only select permission on tables , procedures and packages.
            • 3. Re: select permission on packages
              Can someone throw some idea plz??
              • 4. Re: select permission on packages
                why don't you just spool out the code, and email it to them?
                • 5. Re: select permission on packages
                  well, thats pain for me to do everytime if the developers asks. I have to find another way so that everyone can login and see the code instead of they ask me
                  • 6. Re: select permission on packages
                    James Clark
                    Do you have some type of version tracking software available? Many places use software so that changes can be tracked, but it will also make the code available to your developers (as you can use normal file system permissions).

                    Otherwise you can spool it out to file as otherwise mentioned and just keep it on a network drive. Just make sure to come up with a logical naming scheme so the code can be found quickly.
                    • 7. Re: select permission on packages
                      What utility are they using to display the source?

                      In theory, a user that has been granted SELECT access to SYS.DBA_SOURCE or the more generic SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY would be able to query the data dictionary in order to get the source for any piece of code.

                      Theory works very well when you have users logging in via SQL*Plus and directly hitting the data dictionary. Different PL/SQL GUI tools, though, may well impose additional requirements before they'll display the source-- many will only display the source to package bodies you have the ability to execute. Some require the more liberal SELECT ANY DICTIONARY privilege (or SELECT_CATALOG_ROLE role). This is a limitation in those particular GUI tools, not something that can be solved via Oracle. If your organization's GUI of choice prevents you from viewing source to packages you cannot execute, that's something that would need to be addressed to that particular vendor.

                      James's solution of an external CVS repository, of course, is an excellent one and a common solution to this sort of problem. Unfortunately, most PL/SQL GUIs have relatively poor integration with version control systems (certainly compared to Java IDEs) which can make this somewhat tedious.

                      • 8. Re: select permission on packages

                        thats what now i am doing currently. either ask the team to check the code which is deploed in production or copy the code and paste it is shared drive.

                        -- this works fine, i dont want to work this way now on

                        i wanted to give a select permission on the procedures to so that they can login to the schema and see the objects. same time they should not be allowed to create any new objects like tables or procs etc..

                        Just curious is there anyway i can achive this.
                        • 9. Re: select permission on packages
                          Justin, they use Toad and Rapid SQL.

                          currently they have give the table access, now we need to grant the procs and package select access too... I am concerned now :(

                          [ the team is working somewhere away and in a different timings]
                          • 10. Re: select permission on packages
                            I know nothing about Rapid SQL. For Toad, you may try granting SELECT ANY DICTIONARY, but if memory serves, you'll not be able to see the bodies without having execute access. It may be worth opening a ticket with BMC to see if they can tell you what set of privileges their particular tool requires and/or asking them to change their tool to not have this dependency.

                            • 11. Re: select permission on packages
                              Assume that they use Toad. If i ask the DBA to grant 'SELECT ANY DICTIONARY' to the schema, can they view all the dictionary tables and other objects?
                              • 12. Re: select permission on packages
                                I don't use Toad. My recollection, however, is that SELECT ANY DICTIONARY is not sufficient and that Toad imposes the restriction that you have to be able to execute the package to be able to view it. Again, it's likely worth trying it out since my memory may be incorrect and/or your version of Toad may have lifted this restriction. And it may be worthwhile to raise the issue with BMC (I believe they own Toad now) to fix their issue (assuming, again, my recollection is correct).

                                • 13. Re: select permission on packages

                                  Select any dictionary gives the grant access to ALL THE OBJECTS including the dictionary objects in the schema?
                                  • 14. Re: select permission on packages
                                    RTFM - http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/part4.htm

                                    1 2 Previous Next