5 Replies Latest reply: Apr 15, 2013 8:34 PM by Harvey RSS

    View only access to PL/SQL Objects

    Harvey
      I want user XYZ to be able to view the procedures, functions, packages etc. I have done this with

      grant select on sys.DBA_SOURCE to XYZ;

      this is giving the user XYZ to select the source.

      PROBLEM:

      When I log in to SQL Developer with that user I can not see procedures, packages and functions in left navigation. How can this be achieved. Note that I still can run the select on DBA_source in SQL Developer and get the results. But I need to see the packages, procedures and function in left navigation and when I click on them I should be able to see the source (Read Only access).

      I do not want to do following

      alter system set O7_DICTIONARY_ACCESSIBILITY=TRUE scope=spfile;


      Please advise what access should be granted to XYZ so that this user can achieve what I have explained above.

      Thank you.

      Edited by: Harvey on Apr 16, 2013 10:52 AM
        • 1. Re: View only access to PL/SQL Objects
          rp0428
          >
          I want user XYZ to be able to view the procedures, functions, packages etc. I have done this with

          grant select on sys.DBA_SOURCE to XYZ;

          this is giving the user XYZ to select the source.

          PROBLEM:

          When I log in to SQL Developer with that user I can not see procedures, packages and functions in left navigation. How can this be achieved. Note that I still can run the select on DBA_source in SQL Developer and get the results. But I need to see the packages, procedures and function in left navigation and when I click on them I should be able to see the source (Read Only access).

          I do not want to do following

          alter system set O7_DICTIONARY_ACCESSIBILITY=TRUE scope=spfile;

          Please advise what access should be granted to XYZ so that this user can achieve what I have explained above.
          >
          You won't be able to see those objects in the navigation tree in Sql developer unless user XYZ has execute privilege on the objects.

          You can test this yourself by granting EXECUTE on a procedure (or other) and then you will see that procedure show up in the tree.

          Please mark this thread ANSWERED and do not continue to use it.

          If you need more information or help you will need to post your question in the Sql Developer forum
          SQL Developer

          If you decide to post in that forum you will need to provide your full sql developer version and the full name and version of the database.
          • 2. Re: View only access to PL/SQL Objects
            Harvey
            Hi rp0428,

            I am very sorry to post this and ask, but I have found the solution. When you have found it post it as you are a guru :)

            There should be a button to say unhelpful and very rude.

            --Harvey.                                                                                                                                                                                                                                                                                                                                                                                                                               
            • 3. Re: View only access to PL/SQL Objects
              sb92075
              Handle:     Harvey
              Status Level:     Newbie
              Registered:     Dec 6, 2007
              Total Posts:     37
              Total Questions:     15 (9 unresolved)


              I extend my condolences to you since you rarely get answers to your questions here.
              • 4. Re: View only access to PL/SQL Objects
                rp0428
                When people post in the wrong forum they need to be told that. That isn't being rude that is being helpful.

                You need to post in the correct forum if you want to get the help you need. Most volunteers in this forum do not have expertise in sql developer and, even if they, do there is a forum dedicated to that software.

                The sql developer forum is moderated by the Oracle developers that actually write and support that product. Those developers will NEVER see your question if you post it here. So you only hurt yourself when you don't post in the correct forum.
                >
                I have found the solution
                >
                If you found the solution you wanted then good for you. But if you want to help others that might have the same problem (which is what these forums are all about) you should post your question and the solution you found in the sql developer forum. It would actually be YOU who is being inconsiderate and rude if you seek help from others on the forums but then aren't willing to tell them what the solution is when you resolve your problem.
                • 5. Re: View only access to PL/SQL Objects
                  Harvey
                  Thanks, I will create the post in correct form and present the solution.

                  The solution is under
                  View only access to PL/SQL Objects

                  Edited by: Harvey on Apr 16, 2013 11:33 AM