Those showing data about a user’s own objects, labeled “USER_”
Those showing data available to any user in the database, labeled “ALL_”
Those showing data available to any DBA, labeled “DBA_”
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:
SQL> COLUMN table_name FORMAT A20 SQL> COLUMN comments FORMAT A50 WORD SQL> SELECT * FROM DICTIONARY; TABLE_NAME COMMENTS -------------------- -------------------------------------------------- ALL_ALL_TABLES Description of all object and relational tables accessible to the user ALL_ARGUMENTS Arguments in object accessible to the user ALL_CATALOG All tables, views, synonyms, sequences accessible to the user
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:
Description of the user’s own tables
Description of all tables accessible to the user
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.
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.
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
, to give you an idea of how you can
implement control over who sees what in your database:
remark VIEW "DICTIONARY" remark Online documentation for data dictionary tables and views. remark This view exists outside of the family schema. remark /* Find the names of public synonyms for views owned by SYS that have names different from the synonym name. This allows the user to see the short-hand synonyms we have created. */ create or replace view DICTIONARY (TABLE_NAME, COMMENTS) as select o.name, c.comment$ from sys.obj$ o, sys.com$ c where o.obj# = c.obj#(+) and c.col# is null and o.owner# = 0 and o.type = 4 and (o.name like 'USER%' or o.name like 'ALL%' or (o.name like 'DBA%' and exists (select null from sys.v$enabledprivs where priv_number = -47 /* SELECT ANY TABLE */) ) ) union all select o.name, c.comment$ from sys.obj$ o, sys.com$ c where o.obj# = c.obj#(+) and o.owner# = 0 and o.name in ('AUDIT_ACTIONS', 'COLUMN_PRIVILEGES', 'DICTIONARY', 'DICT_COLUMNS', 'DUAL', 'GLOBAL_NAME', 'INDEX_HISTOGRAM', 'INDEX_STATS', 'RESOURCE_COST', 'ROLE_ROLE_PRIVS', 'ROLE_SYS_PRIVS', 'ROLE_TAB_PRIVS', 'SESSION_PRIVS', 'SESSION_ROLES', 'TABLE_PRIVILEGES','NLS_SESSION_PARAMETERS', 'NLS_INSTANCE_PARAMETERS', 'NLS_DATABASE_PARAMETERS') and c.col# is null union all select so.name, 'Synonym for ' || sy.name from sys.obj$ ro, sys.syn$ sy, sys.obj$ so where so.type = 5 and ro.linkname is null and so.owner# = 1 and so.obj# = sy.obj# and so.name <> sy.name and sy.owner = 'SYS' and sy.name = ro.name and ro.owner# = 0 and ro.type = 4 and (ro.owner# = userenv('SCHEMAID') or ro.obj# in (select oa.obj# from sys.objauth$ oa where grantee# in (select kzsrorol from x$kzsro)) or exists (select null from v$enabledprivs where priv_number in (-45 /* LOCK ANY TABLE */, -47 /* SELECT ANY TABLE */, -48 /* INSERT ANY TABLE */, -49 /* UPDATE ANY TABLE */, -50 /* DELETE ANY TABLE */) )) /
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
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.
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.
On a Windows NT system, the Oracle version 7
file is 234 Kbytes. On the same system,
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.
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:
/* return rows for division chiefs. */ SELECT * FROM employee a, employee e WHERE a.division = e.division AND e.job = 'DIVCHIEF' /* return rows for department heads. */ UNION SELECT * FROM employee a, employee e WHERE a.division = e.division AND a.department = e.department AND e.job = 'DEPTHEAD' /* return the employee's own record. */ UNION SELECT * FROM employee a WHERE emp_dbname = user;
The last query will return a duplicate row when either a division chief or department head uses this view. The UNION operator, however, automatically eliminates duplicate rows.