Forum Stats

  • 3,759,061 Users
  • 2,251,495 Discussions
  • 7,870,477 Comments

Discussions

Restrict view of available schemas

M1k3G
M1k3G Member Posts: 88
edited Aug 19, 2014 5:30AM in General Database Discussions

I am working on a database that has literally hundreds of schema's, and it is tiring to scroll down every time I need something to find the appropriate schema, and then start digging through the tables or triggers I need to work on. This is not a database I have created, and the owner is not interested in tidying up this mess.

My tool of preference is PL/SQL Developer, and on occasion, TOAD and even Excel using ODBC. When I log in with either of these tools, I'd like to see only 2 schema's. Let's assume that out of the schema's available, I want to create a user that can only see BRICS and EMEA, which have unique tables. Is this possible?

Answers

  • The datadictionary has three levels, All datadictionary views starting with user_ allow you to see your own objects.

    All datadictionary views starting with dba_ (provided you have select_dictionary privilege) allow you to see all objects in the database.

    All datadictionary views starting with all_ allow you to see all object so *which you have been GRANTED acces*.

    The tools you mention usually consult all_ views.

    So just make sure only two schemas grant you access.

    Regards,


    Sybrand Bakker

    Senior Oracle DBA

  • everything is forbidden; except that which is explicitly GRANTED

    [PUBLIC objects are always visible to every schema.]

  • My tool of preference is PL/SQL Developer, and on occasion, TOAD and even Excel using ODBC. When I log in with either of these tools, I'd like to see only 2 schema's.

    Then review your documentation for those tools and learn how to set browser filters that limit the objects you will see. You don't need to create a special user.

  • Hemant K Chitale
    Hemant K Chitale Member Posts: 15,759 Blue Diamond

    It is the tool that decides what query it runs against which data dictionary view.  Maybe the tool has documentation that explains this.  Or else you'll have to trace a  session to see what it does.

    Since you can't change the data dictionary view (for example :  dba_objects, all_objects, user_objects), you'd have to see if you can modify the tool's query / filters.

    Hemant K Chitale

  • M1k3G
    M1k3G Member Posts: 88

    I'd like to thank everyone that responded to this question. You gave me some leads that I managed to follow up. The most promising was the code below.

    <span class="kwd" style="color: #00008b;">PROCEDURE</span><span class="pln"> GRANT_TABLES </span><span class="kwd" style="color: #00008b;">IS</span><span class="pln"><br/></span><span class="kwd" style="color: #00008b;">BEGIN</span><span class="pln"><br/><br/>   </span><span class="kwd" style="color: #00008b;">FOR</span><span class="pln"> tab </span><span class="kwd" style="color: #00008b;">IN</span><span class="pln"> </span><span class="pun">(</span><span class="kwd" style="color: #00008b;">SELECT</span><span class="pln"> table_name<br/>               </span><span class="kwd" style="color: #00008b;">FROM</span><span class="pln">  all_tables<br/>              </span><span class="kwd" style="color: #00008b;">WHERE</span><span class="pln">  owner </span><span class="pun">=</span><span class="pln"> this_user</span><span class="pun">)</span><span class="pln"> LOOP<br/>   </span><span class="kwd" style="color: #00008b;">EXECUTE</span><span class="pln"> IMMEDIATE </span><span class="str" style="color: #800000;">'GRANT SELECT, INSERT, UPDATE, DELETE ON '</span><span class="pun">||</span><span class="pln">tab</span><span class="pun">.</span><span class="pln">table_name</span><span class="pun">||</span><span class="str" style="color: #800000;">' TO other_user'</span><span class="pun">;</span><span class="pln"><br/></span><span class="kwd" style="color: #00008b;">END</span><span class="pln"> LOOP</span><span class="pun">;</span><span class="pln"><br/></span><span class="kwd" style="color: #00008b;">END</span><span class="pun">;</span>


    However, it seems that Herman is right. I need to search in the tools to find this solution.

  • How does that code solve the problem that you ask about?


  • M1k3G
    M1k3G Member Posts: 88

    That code is trying to address sybrand_b's suggestion.

    "So just make sure only two schemas grant you access".

    I used the code to provide grants to the schemas that I wanted to see for a new user I created. Since that didnt work, in conjunction with your own question, I take it that I misunderstood sybrand. Can you be more specific about what type of code I need to look at, or what specific actions I need to take?

  • Hemant K Chitale
    Hemant K Chitale Member Posts: 15,759 Blue Diamond

    If you connect with an account that has either DBA or SELECT_CATALOG_ROLE role or SELECT ANY DICTIONARY privilege granted and the tool queries the DBA_TABLES view, you can't avoid having to wade through a list of all schemas.

    If you connect with an account that does NOT have these roles / privilege, the tool might be querying the ALL_TABLES view, thus listing only the schemas that the account has been granted access to.

    So you need to figure out

    a. What account you are using and what privileges it has been granted

    b. What data dictionary view the tool is querying

    Hemant K Chitale

  • Richard Harrison .
    Richard Harrison . Member Posts: 2,065 Gold Trophy

    Hi,

    The version i have of plsqldeveloper (8 - so pretty old) has the option in the object browser to define the query that lists what objects are shown - I'm pretty sure you'll have this in your version.

    In the object browser there is a drop down showing <CURRENT_USER>, <ALL_USERS> etc - the drop down below that is a filter on those objects - you just need to create a new one that does what you want. This can be done by clicking the icon that looks like a filter with a wand - should be self exaplantory from there i hope - just make sure when you run it the drop down above it is set to <ALL_USERS>.

    Cheers,

    Rich

This discussion has been closed.