Oracle Security

The Data Dictionary Views

Data dictionary views may be grouped into four general categories:

• 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_”

• 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:

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:

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:

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 */)
                  ))
/

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:

1. Users with different access privileges can see different objects.

2. 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:

/* 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;

Note

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.

Are there other ways to do this? Absolutely, provided the structure is present in the database to support the approach.