With Safari, you learn the way you learn best. Get unlimited access to videos, live online training,
learning paths, books, tutorials, and more.

The Data Dictionary Views

Those showing data available to any user in the database, labeled
“ALL_”

Those showing data available to any DBA, labeled “DBA_”

Everything else

The DICTIONARY View

Oracle provides, within the data dictionary, a view that you can
access to see the composition of all of the data dictionary views. If
you query the DICTIONARY view, you will find the specific
names of data dictionary objects you have the privilege to access.
You will not see any other objects. The DICTIONARY view is very
simple, containing only two columns: table_name and comments. The
column “table_name” is somewhat misleading because all of
the “table” names are really views, synonyms, or other
objects. None of the entities listed are actually tables. Here is a
very small sample section of the DICTIONARY view, with minor
formatting, so you can see what we are talking about:

If you create a user in a version 8.0.4 database and give that user
just the ability to connect to the database (the CREATE SESSION
privilege), the list of table names available to that user, as
obtained from the DICTIONARY view, would total 242, distributed as
follows:

Personal object data with the prefix “USER_”: 101

All object data with the prefix “ALL_”: 85

DBA objects with the prefix “DBA_”: 0

Everything else: 56

As you can see from this list, there is no exact correspondence in
terms of numbers among the “ALL,” “DBA,” and
“USER” views. For each category of users, separate views
are available. For example, USER_TABLES, ALL_TABLES, and DBA_TABLES
each stores information about the tables in the database, but at
different levels of privilege; despite their names, all of these
entities are actually views on the same tables! The view names were
selected to convey their general purpose. Here are comments from the
data dictionary for these three views:

USER_TABLES

Description of the user’s own tables

ALL_TABLES

Description of all tables accessible to the user

DBA_TABLES

Description of all tables in the database

If you look at the code the Oracle RDBMS uses to enable you to see
the three types of TABLES views listed here, you will notice some
interesting things:

All three views contain the same SELECT list, except that the
“owner” column is omitted from the USER_TABLES view. This
makes sense since this view is expected to return only the names of
tables owned by the user.

All three views use the same list of data dictionary tables, except
for the user$ table, which the USER_TABLES view does not use.

The tables all three views use are: ts$, seg$, obj$, tab$, and obj$,
which are all owned by sys.

We’ll explain these internal ($) tables later in this chapter.

So, if the SELECT lists are essentially the same, and the FROM list
of tables are essentially the same, what is the difference? The
difference is in the WHERE clause, which contains the limiting
conditions for the data to be retrieved. You will find many examples
of Oracle using the same table with different restrictions in the
data dictionary view creation statements.

A view’s definition is stored in the data dictionary and is
treated, in most cases, exactly like a table, but a view does not
store any data. A view is merely a definition of what, and sometimes
how, certain data should be retrieved. There is no distinction made
in SQL DML statements between a “table” and a
“view.” For practical purposes, these terms are
interchangeable. For example, in the following statement:

SELECT *
FROM all_tables;

there is no qualifier to identify the object ALL_TABLES as either a
view or a table; in fact, ALL_TABLES is a view.

Warning

Although you can think of tables and views as being interchangeable,
don’t overlook the potential impact on performance of using a
view, as discussed in Chapter 3.

About Row-Level Security

The DICTIONARY view is a good example
of how row-level security may be implemented.
Data returned from the query will be controlled at the row level by
the condition clause (WHERE ...) on the view definition. The
DICTIONARY view is a three-part union query. We show the text of this
view, as it appears in
CATALOG.SQL, to give you an idea of how you can
implement control over who sees what in your database:

Examining the code for the first query

Let’s take a closer look at the mechanism Oracle uses to
control information access. In the first query of this three-part
union, the two lines:

and o.owner#=0
and o.type = 4

ensure that only views (type = 4) owned by SYS (owner = 0) will be
returned. The rows are further refined by the “and (o.name like
`USER%' ...” section so only the familiar data
dictionary views will be returned. The nested SELECT ensures that
this part of the union query will only return a row if the user has
the specific system privilege, SELECT ANY TABLE. You can test this
portion of the DICTIONARY view creation statement easily. Create a
user (i.e., mary) and grant the user the CREATE
SESSION privilege only. This is the minimum privilege required to
access the database. Connect as mary and enter
the following command:

SELECT COUNT(*)
FROM dictionary
WHERE table_name like 'DBA%';

On a Windows 95 Personal Oracle7 system, the count returned was 2.
Connected as system to the same database with the DBA
role enabled, the count was 93. On a Windows NT system running Oracle
8.0.3, the user mary with only the CREATE
SESSION privilege showed no available rows in the DICTIONARY view for
tables beginning with a “DBA” suffix, while the
system user showed 117 available. From this exercise,
you can learn two important points:

Users with different access privileges can see different objects.

With each new release of the Oracle RDBMS, new or different views are
available.

When developing a security system, keep the second point in mind so
you don’t rely on objects that may change or disappear in later
releases of the Oracle software.

Examining the code for the second and third queries

The second query of the DICTIONARY view returns descriptions of the
specific views listed in the “in (...)” clause, and the
third query returns synonyms owned by system. The
DICTIONARY view will return to the user executing the query only all
object names where the user created the object and has been granted
some type of privilege on the object.

About CATALOG.SQL

On a Windows NT system, the Oracle version 7
CATALOG.SQL file is 234 Kbytes. On the same system,
the Oracle8 CATALOG.SQL is 416 Kbytes. The
differences in size are predominantly caused by the creation of
“GV” global views and the new disaster recovery approach
provided in Oracle8. In either version 7 or version 8, virtually
every object defined in this script is either a view or a synonym.
Additionally, most views are qualified in a manner similar to the
DICTIONARY example to limit the rows returned to only those the user
has the right to see. In order to accomplish this, the kernel must
have two specific pieces of information about the user: the username
and the user id. In addition, that information has to be available in
the data dictionary tables. Since the username and user id are known
from the login process, and since that data was verified against
entries in the data dictionary, the kernel has the information
available at all times to determine the levels of access that should
be made available to each user.

Applying the Concepts

If you want to apply the approach Oracle uses within the data
dictionary code, you will need some method of associating the users
with data in the application tables. Typically, the information you
will need is organizational in nature. A user whose real name is Mary
Jane may be in division AB, department 4. If her userid of
mjane is stored as part of her record in the employee
table, along with her division and department, then you have captured
the minimum amount of information your security system would need to
limit data access.

Typically, when access to personnel data is allowed, there is a
restriction that each user should only be able to see his or her own
data unless that user is a department head or division chief. Here is
an example of code you could use to implement the necessary
restriction on the employee table: