Figure 19.2.
Creating a new role
with Security Manager.

Figure 19.3.
Granting the CONNECT
and RESOURCE roles to
the new role.

3. Once you click the Create button, the Department_Administrator role is created, and you should see it in the lists of database roles on the right-hand side of the window.
4. Now, add a user—Terri Greenglass, the English department administrator—to the database.
5. Click the Users folder, and click the + button on the toolbar.
6. In the User field, enter tgreenglass.
7. In the Password and Confirm Password fields, enter the password you wish to give the new user (see Figure 19.4).

Figure 19.4.
Creating the new
database user.

8. For the purpose of this example, ignore the Tablespaces section of the window.
9. To grant the Department_Administrator role to Terri Greenglass, select the
   Privileges tab.
10. Using the mouse, select the Department_Administrator role, and click the Add button (see Figure 19.5).

Figure 19.5.
Granting the
Department_
Administrator role
to the new user.

Page 519

11. To complete the creation of the Oracle account for Terri Greenglass, click the Create button. Security Manager submits the SQL statements to the Oracle database to create the new user and grant the role.

Limiting a User's Resources with a Profile

In addition to providing a quota on the use of storage in a tablespace, Oracle also provides other system-level quotas that can be established for a user. These quotas include the following:

• CONNECT_TIME is the number of elapsed minutes since the user has connected to an Oracle database
• IDLE_TIME is the number of elapsed minutes since the user last performed an action resulting in an Oracle call
• LOGICAL_READS_PER_CALL is the number of Oracle blocks read from memory or disk to satisfy an Oracle call

The mechanism that you use for specifying these quotas is the profile. For example, you might want to create a profile for a casual user that limits his or her connect time to two hours or less. In addition, you might also want to limit a casual user's idle time to 10 minutes. On top of that, you might also fear that a casual user—particularly, one who has access to an ad hoc query tool—could inadvertently construct queries that produce a Cartesian product of several tables. By setting a fairly high limit on LOGICAL_READS_PER_CALL, you can prevent the user's query from consuming memory and CPU time needed by other users.

To enforce the resource limits specified in a user's profile, the DBA must perform either one of the following tasks:

• Set the resource_limit initialization parameter to TRUE. The new value for resource_limit won't take effect until you restart the database.
• Modify the database by issuing the following command in SQL*Plus: ALTER SYSTEM SET RESOURCE LIMIT = TRUE; or by setting the RESOURCE_LIMIT=TRUE in the init.ora file for the database.

Obstacles to Optimal Performance

One of the goals of this lesson is to address performance issues related to an Oracle database. The material in this lesson is an introduction to some of the issues you may encounter during the database development process. Here are some general suggestions.

Keep these guidelines in mind as you read about the performance characteristics that occur in an Oracle client/server environment.

Synonyms

A synonym is another name for a table. Actually, the term table synonym is more accurate. Synonyms come in two flavors: private and public. A private synonym is visible only to the Oracle user who created it. A public synonym is visible to all Oracle users. Any Oracle user who has been granted the RESOURCE role can create a private synonym. On the other hand, only an Oracle user who has been granted the DBA role—or the Create Public Synonym system privilege—can create a public synonym.

NEW TERM
A synonym is another name for a table or view. A synonym that is visible only to its owner is referred to as a private synonym. A synonym that is visible to all Oracle users is referred to as a public synonym.

In one way, a synonym is similar to a view: both objects enable a table to be referenced by a different name. However, a synonym doesn't enable you to restrict columns or rename them.

A synonym provides an additional name for referencing a table. For example, you may not be able to rename a table because existing applications reference the current table name. However, a synonym that provides a more intuitive and meaningful name might be ideal for use in an ad hoc query tool.

Synonym Syntax

You should see how you can manage synonyms with the Personal Oracle Navigator or with Schema Manager. However, there is an advantage to using an SQL script for creating and dropping synonyms.

Therefore, take a look at the syntax for creating a synonym:

CREATE [PUBLIC] SYNONYM synonym-name
FOR owner.object-name;

The variables are defined as follows:

synonym-name is the synonym name and is subject to Oracle database object-naming
requirements.
owner is the name of the Oracle account that owns the referenced table or view.
object-name is the name of the table or view referenced by the synonym.

You might want to create a synonym to reference a table whose name is inappropriate or difficult to remember. In this example, the synonym p_artifact is used to point to the table parthaginian_artifacts, which belongs to the same Oracle user.

SQL> create synonym p_artifact for parthaginian_artifacts;

Synonym created.

A private synonym also can point to a table owned by another Oracle user. Suppose Oracle user RJOHNSON has created the Project table and wants to enable Oracle user KCHOW to read the Project table. First, RJOHNSON grants the select privilege on the Project table to KCHOW. However, each time KCHOW wants to look at the table, she has to remember to qualify the table name with RJOHNSON—the table owner. Consequently, she creates a private synonym that enables her to reference the table by the Project name alone, as shown in Listing 19.1.

Listing 19.1. Using a private synonym.

SQL> select Project_Number
  2  from Project;
from Project
     *
ERROR at line 2:
ORA-00942: table or view does not exist

SQL> create synonym Project for RJOHNSON.Project;

Synonym created.

SQL> select Project_Number
  2  from Project;

PROJECT_NUMBER
--------------
          1201
          2143
          4310