2 Securing the Database Installation and Configuration

About the Oracle Data Dictionary

The Oracle data dictionary is a set of database tables that provides information about the database. A data dictionary has the following contents:

• The names of Oracle Database users

• Privileges and roles granted to each user

• The definitions of all schema objects in the database (tables, views, indexes, clusters, synonyms, sequences, procedures, functions, packages, triggers, and so on)

• The amount of space allocated for, and is currently used by, the schema objects

• Default values for columns

• Integrity constraint information

• Auditing information, such as who has accessed or updated various schema objects

• Other general database information

The data dictionary tables and views for a given database are stored in the SYSTEM tablespace for that database. All the data dictionary tables and views for a given database are owned by the user SYS. Connecting to the database with the SYSDBA privilege gives full access to the data dictionary. Oracle strongly recommends limiting access to the SYSDBA privilege to only those operations necessary such as patching and other administrative operations. The data dictionary is central to every Oracle database.

You can view the contents of the data dictionary by querying data dictionary views, which are described in Oracle Database Reference. Be aware that not all objects in the data dictionary are exposed to users. A subset of data dictionary objects, such as those beginning with USER_% are exposed as read only to all database users.

Example 2-1 shows how you can find a list of database views specific to the data dictionary by querying the DICTIONARY view.

sqlplus system
Enter password: password

SQL> SELECT TABLE_NAME FROM DICTIONARY;

Enabling Data Dictionary Protection

You can protect the data dictionary by setting the O7_DICTIONARY_ACCESSIBILITY initialization parameter to FALSE. This parameter prevents users who have the ANY system privilege from using those privileges on the data dictionary, that is, on objects in the SYS schema.

Oracle Database provides highly granular privileges. One such privilege, commonly referred to as the ANY privilege, is typically granted to only application owners and individual database administrators. For example, you could grant the DROP ANY TABLE privilege to an application owner. You can protect the Oracle data dictionary from accidental or malicious use of the ANY privilege by turning on or off the 07_DICTIONARY_ACCESSIBILITY initialization parameter.

To enable data dictionary protection: 

1. Start Oracle Enterprise Manager Database Control (Database Control).

   See Oracle Database 2 Day DBA for instructions about how to start Database Control.

2. Log in as SYS and connect with the SYSDBA privilege.

   • User Name: Enter the name of a user who has administrative privileges. In this case, you enter SYS.

   • Password: Enter the SYS user's password.

   • Connect As: From the list, select SYSDBA.

   The Oracle Enterprise Manager Database Home page (Database Home page) appears.

3. Click Server to display the Server subpage.

4. In the Database Configuration section, click Initialization Parameters.

   The Initialization Parameters page appears.

5. In the list, search for O7_DICTIONARY_ACCESSIBILITY.

   In the Name field, enter O7_ (the letter O), and then click Go. You can enter the first few characters of a parameter name. In this case, O7_ displays the O7_DICTIONARY_ACCESSIBILTY parameter.

   Depending on the parameter, you may have to modify the value from the SPFile subpage. Click the SPFile tab to display the SPFile subpage.

6. Set the value for O7_DICTIONARY_ACCESSIBILTY to FALSE.

7. Click Apply.

8. Restart the Oracle Database instance.

   1. Click the Database Instance link.

   2. Click Home to display the Database Control home page.

   3. Under General, click Shutdown.

   4. In the Startup/Shutdown Credentials page, enter your credentials.

      See Oracle Database 2 Day DBA for more information.

   5. After the shutdown completes, click Startup.

Modifying the Value of an Initialization Parameter

This section explains how to use Database Control to modify the value of an initialization parameter. To find detailed information about the initialization parameters available, see Oracle Database Reference.

To modify the value of an initialization parameter: 

1. Start Database Control.

2. Log in as user SYS with the SYSDBA privilege.

   • User Name: SYS

   • Password: Enter your password.

   • Connect As: SYSDBA

3. Click Server to display the Server subpage.

4. In the Database Configuration section, click Initialization Parameters.

   The Initialization Parameters page appears.

5. In the Name field, enter the name of the parameter to change, and then click Go.

   You can enter the first few letters of the parameter, for example, SEC_RETURN if you are searching for the SEC_RETURN_SERVER_RELEASE_NUMBER parameter. Alternatively, you can scroll down the list of parameters to find the parameter you want to change.

   Depending on the parameter, you might have to modify the value from the SPFile subpage. Click the SPFile tab to display the SPFile subpage.

6. In the Value field, either enter the new value or if a list is presented, select from the list.

7. Click Apply.

8. If the parameter is static, restart the Oracle Database instance.

   To find out if an initialization parameter is static, check its description in Oracle Database Reference. If the Modifiable setting in its summary table shows No, then you must restart the database instance.

   1. Click the Database Instance link.

   2. Click Home to display the Database Control home page.

   3. Under General, click Shutdown.

   4. In the Startup/Shutdown Credentials page, enter your credentials.

      See Oracle Database 2 Day DBA for more information.

   5. After the shutdown completes, click Startup.