5.1 What You Can See

Objects that we create can always be extracted from views that begin with "USER_." If we create 10 tables in our schema, then a query of USER_TABLES will return 10 rows ”each row returning attribute information about a table.

Suppose that an Oracle account user named SMITH creates a table called PAYROLL and grants all privileges on PAYROLL to the user SCOTT. Can SCOTT see PAYROLL in USER_TABLES? No, but SCOTT can see PAYROLL from ALL_TABLES. The ALL_TABLES view shows not just tables we create but also other tables to which we have been granted access.

The views we are concerned with are named with the following prefixes:

• USER

• ALL

• DBA

These prefixes are an aid in limiting the scope of what we want to see, need to see, or should be allowed to see. To see all tables we create in our schema, we query USER_TABLES; to see all tables we create plus tables to which we have received privileges from other users, we query ALL_TABLES. A user with the DBA role or SELECT_CATALOG_ROLE can query DBA_TABLES to select a list of all tables in the database. The scope defined by each prefix is the following:

USER_	Views with this prefix return a result set describing those objects created in your schema. To see a list of all the user views in this group : [View full width] SELECT view_name FROM all_views WHERE view_name like 'USER%';
ALL_	Views with this prefix return objects created in your schema plus those objects that have been created in other accounts and to which you have been granted privileges. The ALL views frequently include a column for OWNER ”to reflect the owner of the object to which you have access. You won't see OWNER as a column in USER_TABLES because you are the owner of all table names in this view; however, OWNER is a column in ALL_TABLES.
DBA_	Views with this prefix provide information about the entire database. A query of OWNER and TABLE_NAME from DBA_TABLES returns the table owner and name of all tables in the database ”including the base tables owned by SYS.

Not every view has a USER, ALL, and DBA prefix. There are some views that only exist in the DBA view scope. One example is the view DBA_DATA_FILES. This view is used in Chapter 3 to illustrate index space usage from a primary key index. In general, a view in USER scope has a counterpart in the ALL and DBA scope.

Access to the DBA view is sometimes given in a development environment. There is no harm allowing developers to explore the Oracle dictionary; however, you can achieve this access without releasing the DBA role. By default, with the typical CONNECT and RESOURCE roles ”usually the default given to an applications developer ”one cannot access these tables. You can grant the SELECT ANY TABLE privilege or SELECT_CATALOG role to a user and this will allow one to access the full data dictionary. The more one understands the data dictionary, the more in touch they are with the complex operations of the database and the more they are sensitive to such things as SQL statement tuning.