Week 3

Day 19

An Overview of Oracle
Database Security and
Tuning

Today's lesson covers two topics that can't be ignored during the development of Oracle database applications—security and tuning. Many of the tasks discussed in this lesson are typically performed by a dedicated database administrator. Even if you're never responsible for the security and tuning of an Oracle database, it's a good idea for you to understand some of the issues that are involved. This lesson begins with a discussion of users, roles, and the privileges granted to them.

Users and Roles

During Day 3, "Logical Database Design," you went through the steps of adding a new user to an Oracle database. In addition to a user, Oracle also enables you to create a database role—a set of Oracle privileges that can correspond with a functional role in an organization. For example, you may want to allow a data

entry clerk to insert and update rows in a particular table but prevent him or her from deleting any rows. To enforce this security scheme, you would create a database role named DATA_ENTRY, which would be granted these privileges.

On the other hand, the data entry manager should be able to insert, update, and delete rows in the tables. Therefore, you would create a separate role named DATA_ENTRY_MANAGER to implement this security policy. Once a database role is created, it can be granted to an individual user as needed. In addition, a role also can be granted to another role. Database roles not only help to ensure appropriate security, they also simplify database administration.

For an application environment with many tables, many users, and distinct functional roles, the implementation and maintenance of a security scheme is a major task. If the database administrator grants privileges directly to individual users, it's very easy for things to fall through the cracks in the long run. The DBA might forget to give some people a privilege they need to do their work or might give others a superfluous privilege. Also, if a change to the database application requires that the privileges of users also change, the DBA will have far more work to accomplish this without roles.

By defining a database role for each functional role that exists in the organization, you significantly reduce the task of managing users and their privileges. For instance, suppose you have 10 database users—three belong to the marketing department and the other seven are in the engineering department. You would create two database roles, Marketer and Engineer, and grant the appropriate privileges to each role; the Marketer role needs access to one set of tables, whereas the Engineer role requires access to a different set. You would then grant the appropriate role to each user. If the database changes—for instance, the addition of a table—you simply update the privileges of the role, not the user.

Pre-Defined Users: SYS and SYSTEM

Every Oracle database has two pre-defined accounts that serve a special purpose—SYS and SYSTEM. The SYS account owns the Oracle data dictionary tables and associated database objects. The SYSTEM account owns tables that are used by Oracle application development tools such as Oracle Forms or Reports. You should not create any database objects such as tables or indexes while connected as SYS or SYSTEM, except for Oracle-supplied SQL scripts that specifically indicate that they should be installed as SYS or SYSTEM.

Pre-Defined Roles

When an Oracle database is installed, the five database roles are created:

• CONNECT has the capability to connect to the Personal Oracle database (by means of the system privilege, CREATE SESSION).
• RESOURCE has the capability to create tables, indexes, views, and other Oracle objects.

• DBA consists of all system privileges needed to create, alter, or drop users and manage the database objects that they own.
• IMP_FULL_DATABASE consists of all system privileges needed to perform a full database import.
• EXP_FULL_DATABASE consists of all system privileges needed to perform a full database export.

System and Object Privileges

A role isn't useful unless it has been granted at least one privilege. Oracle privileges belong in two categories:

• System privileges enable an Oracle user to execute statements such as CREATE TABLE, CREATE USER, or ALTER INDEX.
• Object privileges are associated with a particular operation (such as SELECT or UPDATE) on a specified database object (such as a table or index).

You can grant many different system privileges to a role or user—in fact, Oracle has more than 80 system privileges. One system privilege that almost every Oracle user needs is CREATE SESSION—the capability to establish an Oracle session.

Creating and Using a Role

To make these concepts more concrete, this section walks you through the process of creating a role and assigning it to an individual. You will also grant system privileges to the role. To do this, you'll need an account that has been granted the DBA role. Please realize that what you see on your screen may differ from the figures in this lesson because you may have users and roles already defined.

1. To start, invoke Security Manager from the Oracle Enterprise Manager program group.
2. Enter the username, password, and service as appropriate.

3. Once Security Manager has connected to the Oracle database, click the Roles folder on the left-hand window.
4. In the right-hand window, you should see the existing database roles (see Figure 19.1).

Figure 19.1.
Viewing existing
database roles with
Security Manager.

5. To create a new role, you may either click the + button on the toolbar or select the menu item Role | Create. A dialog window is displayed, prompting you for the new role's name (see Figure 19.2).
6. Enter Department_Administrator.
7. Leave the Authentication radio button set to None.

Next, grant the CONNECT and RESOURCE roles to the new database role.

1. Press the tab folder named Privileges. By default, Security Manager displays the roles that are currently granted to the new database role. At the bottom of the dialog window, you will see the existing roles.
2. Holding down the Control key, select CONNECT and RESOURCE and click Add (see Figure 19.3). You should now see the CONNECT and RESOURCE roles in the upper portion of the dialog window.