Oracle Security

Chapter 4. The Oracle Data Dictionary

When you want to find out the meaning of a word, how it is spelled, or its derivation, what do you do? Generally, you go to the nearest dictionary and look up the word. In the same way, when you want to find out the contents of your database, you go to the data dictionary to look up the information of interest. From a security perspective, you will use the data dictionary to gain information vital to your security implementation. If you are a DBA, you will spend a great deal of your time interacting with the data dictionary to gain information about the various objects with which you must interact. You will use the data dictionary to examine user accounts and user quota assignments, to look up the location of datafiles on your system, and to obtain the information you need to perform your job effectively. As a developer, you will use a different view of the data dictionary to keep track of the schema objects within your application. If you are a casual application user, you might never even see a data dictionary entry directly.

Oracle’s data dictionary consists of two layers: the tables that make up the real data dictionary and a series of views that allow you to access the information in the data dictionary. Most of the data dictionary views are written to restrict your access to only the data appropriate for your specific level of privilege. The views have meaningful names along with equally meaningful attribute names. In this chapter, we will examine the following information about the Oracle data dictionary:

• What the data dictionary is

• How the data dictionary is created

• How the data dictionary is structured

• What type of information is available

• How that information may be used in a security system

For more detailed information, see the Oracle8 Concepts Manual, Release 8.0, Part A58227-01, Chapter 4, “The Data Dictionary.”

Creating and Maintaining the Data Dictionary

The Oracle data dictionary is primarily an internal record of the state of all objects in the database. These objects include:

• Tables

• Users

• View definitions

• Indexes

• Triggers

• Sequences

• Constraints on tables and columns

• Database links

• Synonyms

• Stored programs

The data dictionary is created by the RDBMS when the database is initially created. The data dictionary is maintained by the RDBMS based on actions performed by the users, application developers, or database administrator. The data dictionary we use is actually a dictionary of the “metadata,” the data that describes the objects in the database. These values are dynamic and are changed by the RDBMS as the objects change. For instance, when you create a table, the table name, along with the names of all the columns in the table and the column characteristics, are recorded in the data dictionary. If you drop the table or rename it, or if a column definition is modified, then the appropriate entry in the data dictionary is updated by the RDBMS on your behalf. In fact, you cannot directly make changes to the data dictionary—regardless of your privilege level.

Note

Modification of a column definition does not mean there is a change in the data stored in the column—only that a change has occurred to some characteristic of the table’s column such as the column name, data type, length, or other characteristic.

There is an audit function supported by the data dictionary. If you set it up properly, the data dictionary will also track, within a collection of audit tables, actions performed by users. From a security perspective, being able to keep track of users who have made modifications to a specific table can be very important. For example, you might want to keep track of who has modified salary information and when the updates were made. (Auditing is discussed more fully in Chapter 5, Chapter 10, and Chapter 11.) Finally, by learning how the views were constructed in the data dictionary, you will see how access to table data can be controlled down to the row level.

Usually a user’s perspective on the data dictionary is somewhat different from its actual implementation. From the user’s perspective, a database is the collection of objects (usually tables) that contain the data used by their applications. This is an acceptable definition from the user viewpoint. However, from the Oracle RDBMS viewpoint, there is only one database and only one data dictionary per instance or, at least, per database cluster. Some pertinent facts about the data dictionary are:

• The dictionary always encompasses all database objects regardless of the owner.

• Users are generally allowed to look at (query) the data dictionary.

• Users are only permitted to see data based on their job requirements or “need to know.”

• Privileges are granted to users on the data dictionary views but not on the underlying tables.

• Users can only access the table data via the views.

• Users can see only information appropriate for their level of privilege, providing a horizontal look into the data.

We’ll discuss some specific examples of how these points are accomplished later in this chapter.