4.1 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:
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 dictionaryregardless of your privilege level.
| || |
Modification of a column definition does not mean there is a change in the data stored in the columnonly 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.