Oracle Security

About SQL.BSQ

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 SQL.BSQ file, 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 SQL.BSQ 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.

Warning

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.

SQL.BSQ and CATALOG.SQL Locations

The Oracle-delivered scripts for these files can be found in the.../rdbms/admin directory on most systems. For a Windows NT database, the files will be found in:

<drive_designation>:\orant\rdbmsXX\admin\sql.bsq

<drive_designation>:\orant\rdbmsXX\admin\catalog.sql

where XX is the version of the RDBMS you have installed, such as rdbms80.

For UNIX installations, the files will be found in:

$ORACLE_HOME/dbs/sql.bsq or $ORACLE_HOME/rdbms/admin/sql.bsq

$ORACLE_HOME/dbs/catalog.sql or $ORACLE_HOME/rdbms/admin/catalog.sql

For an OpenVMS system, the files will be found in:

$ORACLE_HOME:[RDBMS]sql.bsq

$ORACLE_HOME:[RDBMS]catalog.sql

How SQL.BSQ Is Used

The 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 sys or 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

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

1. There is no command to create a role or user which specifies a value for “type”. The value for “type” is inserted by the kernel when the appropriate DDL command is executed.

2. This table has an entry made when either of the following two DDL commands is used:

   CREATE USER ...
   CREATE ROLE ...

Later in the SQL.BSQ file, there is a relevant DDL command:

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.

SQL.BSQ and Database Creation

The SQL.BSQ file is never mentioned in any SQL statement. It is used by inference only once, at database creation time, when a script such as the following is executed:

rem  Create database script for the production database.
CREATE DATABASE prod1
     DATAFILE 'c:\orawin95\dataprod1\prod1sys01.dbf' SIZE 20m REUSE
  CONTROLFILE REUSE
      LOGFILE '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 dictionary.

Inside SQL.BSQ

There are some rather unusual variable references in the SQL.BSQ file. For instance, the initial statement is:

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 clarity):

SQL> SELECT type, count(*)
  2    FROM obj$ 
  3   GROUP BY type;
TYPE       COUNT(*)
---------- ----------
         0          1      -- Reference record for next object number
         1         65      -- Indexes
         2         68      -- Tables
         3          8      -- Clusters
         5          4      -- Synonyms
         6          8      -- Sequences

                  

Note

The values shown here are for version 7 of the RDBMS and may change from version to version.