Why would you want to know about the
internal structure of the data dictionary of the database? Not only
because some of the best examples of basic security implementation
may be found within the Oracle data dictionary, but also because the
more thoroughly you understand how the database is put together, the
better you can protect it.
The data dictionary uses base tables for which nobody has implicit
privileges, and many views, most of which are qualified so the user
can see only those rows that are appropriate. Source code for the
data dictionary is found in the
which is read when the database instance is created. The views (with
which many users are familiar) are created by
CATALOG.SQL. There are other views created by
other programs that are executed at database creation time, but
we’ll focus on these two scripts in our examples here.
In general, unless a user has been granted the DBA role, or
comparable privileges, that user cannot access these base tables.
These base tables are created when the
file is executed at the time of database initialization
and owned by a user known as
sys. All of the
views, including those that will be addressed later, are based on the
base tables created from the statements in this file.
You must not modify the
SQL.BSQ file or the
CATALOG.SQL file, but you’ll find the
scripts within the files very educational. Therefore, we suggest that
you copy the files to other file names and then walk through the
copies. If either of these files is modified and something goes wrong
with your database, Oracle Support may not be willing to assist in
the repair efforts based on the modified files.
XX is the version of the RDBMS you have
installed, such as
SQL.BSQ file is read by the instance and is
used to create the database system tablespace and all of the tables,
clusters, indexes, sequences, and initial data required by the kernel
code to store and maintain the metadata for user-created objects. No
user, not even
system, ever directly manipulates the data in
these tables. They are maintained as a result of Data Definition
Language (DDL) commands. For example, executing the statement:
CREATE TABLE x (col1 NUMBER);
will result in an entry in the tab$ table and will modify the contents of the fet$ table (free extent table), uet$ (used extent table), and so on. These “$” tables are a part of the internal Oracle data dictionary.
While it’s true that the data dictionary offers a fascinating object study for data normalization, it has no inherent security aspects. What it does have is all of the characteristics that may be used to implement a security system. A security system that implements all aspects of the security plan will have several layers and will incorporate several components and features of the Oracle database, as we’ll discuss in the sections that follow.
User and role names must always be
unique, not only within their individual category, but within the
combined user and role set on your system. The reason can be
explained by looking at the CREATE statement for the user$ table
SQL.BSQ. A portion of the CREATE
statement is reproduced here:
create table user$ /* user table */ ( user# number not null, /* user identified number */ name varchar2("M_IDEN") not null, /* name of user */ type number not null, /* 0 = role, 1 = user */ ...
Note the last attribute line, which defines the type of user. The comment indicates that there are only two options: zero (0) for a role, and one (1) for a user. Both user and role definitions will be stored in this table. Keep the following two facts in mind:
Later in the
SQL.BSQ file, there is a relevant
create unique index i_user1 on user$(name);
which further illustrates the duality of the use of the user$ table. Because this is a UNIQUE index, there cannot be duplicate entries in the user$ table that have the same value for name. Since this table is used to store both usernames and role names, each entry, regardless of type, must be unique.
rem Create database script for the production database. CREATE DATABASE prod1 DATAFILE 'c:\orawin95\dataprod1\prod1sys01.dbf' SIZE 20m REUSE CONTROLFILE
REUSELOGFILE 'c:\orawin95\dataprod1\prod1log1.dbf' SIZE 500k REUSE, 'c:\orawin95\dataprod1\prod1log2.dbf' SIZE 500k REUSE, 'c:\orawin95\dataprod1\prod1log3.dbf' SIZE 500k REUSE ; CREATE ROLLBACK SEGMENT tmprb TABLESPACE system STORAGE ( INITIAL 10K NEXT 10K MINEXTENTS 2 MAXEXTENTS UNLIMITED OPTIMAL 30K ) ; ALTER ROLLBACK SEGMENT tmprb ONLINE;
This is a real statement that was used to create a real database. It
is the first statement executed after performing a CONNECT INTERNAL
command and issuing the STARTUP NOMOUNT command. Of course there are
additional statements that create the remaining tablespaces that will
be used for the data, indexes, and so on, but at this point, there is
a functioning database. Note that in this statement, there is no
explicit reference to
SQL.BSQ. Note also that
there is no reference to creating the system
tablespace. In the initialization file (named
initprod1.ora in this system) it will be seen
that there is also no mention of the system
tablespace, nor any mention of
SQL.BSQ. The file
is searched for by the kernel whenever the CREATE DATABASE statement
is used. This file is used to create the internal objects of the data
There are some rather unusual variable references in the
SQL.BSQ file. For instance, the initial
create tablespace SYSTEM datafile "D_DBFN" default storage (initial 10K next 10K) online.
On the same page within the “create table tab$” is the column definition:
audit$ varchar2("S_OPFL") not null ...
The D_DBFN and S_OPFL terms again suggest the special nature of the
SQL.BSQ file. Even though the file contains
primarily SQL statements, it is clear it cannot be executed from a
standard SQL*Plus or
svmgr session. There are
simply no provisions in the SQL syntax for these unusual parameters.
They are replaced by the kernel at execution time with appropriate
values and, if any of these statements are altered in any way, the
results are unpredictable and there is a strong possibility that the
database create will fail.
The objects created by
SQL.BSQ are the internal
data dictionary objects. Immediately after database creation, the
status of the dictionary is as follows (comments are added for