Is O7_DICTIONARY_ACCESSIBILITY required to be TRUE for using TOAD?
What would this impact in Toad, if it was set to false?
O7_DICTIONARY_ACCESSIBILITY controls restrictions on SYSTEM privileges. If the parameter is set to true, access to objects in the SYS schema is allowed (Oracle7 behavior). The default setting of false ensures that system privileges that allow access to objects in "any schema" do not allow access to objects in the SYS schema.
For example, if O7_DICTIONARY_ACCESSIBILITY is set to false, then the SELECT ANY TABLE privilege allows access to views or tables in any schema except the SYS schema (data dictionary tables cannot be accessed). The system privilege EXECUTE ANY PROCEDURE allows access on the procedures in any schema except the SYS schema.
If this parameter is set to false and you need to access objects in the SYS schema, then you must be granted explicit object privileges. The following roles, which can be granted to the database administrator, also allow access to dictionary objects:
SELECT_CATALOG_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
With that said, setting this to false in Toad would only have block some users from DBA views, however Toad checks a few other items before:
- First: {If user has DBA role or they connected as SYSDBA then assume they have access to DBA_ views}
- Second: {SELECT_CATALOG_ROLE gives access to DBA_ views}
- Third: {SELECT ANY DICTIONARY priv gives access to DBA_ views}
- Fourth: {Oracle 7 users with SELECT ANY TABLE have access to DBA_ views and Oracle 8+ users with SELECT ANY TABLE have access to DBA_ views only if O7_DICTIONARY_ACCESSIBILITY = TRUE}
- Fifth and Last: {The above checks did not yield access to DBA_ views. Let's see if there are any individual views that they have access to the DBA_ equivalent}
At this point we query all_tab_privs to see if they have specific access to individual DBA_ views. The user may have access to DBA_OBJECTS for instance, but only access to ALL_TABLES. We will store that internally so that we query the DBA_ view when appropriate.
