This discussion is archived
5 Replies Latest reply: Apr 15, 2013 6:34 PM by Harvey RSS

View only access to PL/SQL Objects

Harvey Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    >
    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 (Not for general SQL/PLSQL questions)

    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points