0492-0494

Previous Table of Contents Next

Page 492

UNIX (LANCE) and checks to see whether an operating system authenticated account exists within the database (OPS$LANCE). If the account is found, the user is granted access; if the account is not found, the request for access is denied .

You or the user can invoke this type of logon by passing a slash (/) from the command line, as in this example:

 % sqlplus / SQL> show user User is "OPS$LANCE" 

Simply put, a slash causes a null to be passed in the place of the username and the password. This type of authentication is enough to allow access to the database.

User Configuration Setup

After deciding what type of authentication method to use, you still are responsible for making determinations about setting up the user within the database. Just as creating a UNIX user requires the UNIX system administrator to define certain items, such as the user's shell and home directory, similar elements must be defined in the database environment.

This process is known as setting up the user configuration. Although determining the number and types of privileges within the database can encompass a whole separate analysis process, the setup of the user configuration is relatively straightforward. (For more details on security, see Chapter 24.) User configuration has three basic elements:

• Profiles
• Default tablespace
• Temporary tablespace

Each of these elements serves a specific function and is discussed in more detail in the following sections.

Profiles

The database profile is Oracle's attempt to enable you to exercise some method of resource management on the database. According to the Oracle7 Server Administrator's Guide (which also applies to Oracle8), a profile is "a named set of resource limits." To better understand this term , take a step back and try to understand where it came from.

Most of the power-user tools available with Oracle7 and Oracle8 were not around in Oracle6 and earlier versions. To a certain extent, many sites still were trapped in a mainframe mode that precluded the type of access now considered common. Even SQL*Plus was considered primarily a developer's tool and was not something available to users. Then, as they say, someone let the genie out of the bottle . Products were introduced that allowed for client/server access to the database data by using graphical tools, and these types of tools became commonplace. The tools presented a problem for DBAs, however: how to restrict them.

Page 493

Because Oracle6 uses a rule-based optimizer, a change in the table order of a FROM clause or the statement order of a WHERE clause could double or triple (or more) the amount of time required to run a query. Most users were unwilling (or unable) to learn how to properly build queries, so they often were guilty of releasing queries that could bring a production system to its knees.

Oracle7 introduced profiles ”part of two tools to help the DBA administer an RDBMS in an ad-hoc environment (the other tool is the cost-based optimizer). By using profiles, you can designate issues such as how much CPU time a user can receive during a single database session or per SQL statement, how much idle time a user can accumulate, or how long a user can be connected to the database. You give each profile a name (such as CLERK, MANAGER, ACCOUNTANT, and so on), and certain fixed resource limits are associated with the name . You then assign this profile to a user, who must function within the designated profile limits. These same profiles still apply with Oracle8, and you can manage them through Oracle Enterprise Manager.

By default, a user with no profile assigned receives a profile named DEFAULT. The DEFAULT profile is mandatory and must be present within the database. A more complete discussion of profiles can be found in one of the following sections in this chapter titled "Using Profiles."

Default Tablespace

If you think of the Oracle database as its own operating system (as some academics have argued), you probably consider the default tablespace the home directory of the database world. As shown in earlier chapters of this book, you can create a table, an index, or another database object by using the TABLESPACE option:

 CREATE TABLE ORDER     (     orderno    NUMBER(6),     partno     NUMBER(10),     qty        NUMBER(3),     cost       NUMBER(7,2)     ) TABLESPACE USERS; 

Here, the table ORDER is created in the USERS tablespace. However, if no tablespace is designated, as in the following example, the table is created in the tablespace designated as that user's default tablespace:

 CREATE TABLE ORDER     (     orderno    NUMBER(6),     partno     NUMBER(10),     qty        NUMBER(3),     cost       NUMBER(7,2)     ); 

The default tablespace is simply the tablespace where a database object is created if no other tablespace is specified.

Page 494

WARNING

If you do not specify a default tablespace, a user's default tablespace is the SYSTEM tablespace. This can cause the SYSTEM tablespace, which contains the data dictionary and other information crucial to database operation, to become fragmented or full. In some cases, the only way to correct problems is to re-create the entire database.

Temporary Tablespace

The temporary tablespace also is a tablespace, but its function is different from the default tablespace. Continuing with the analogy of the database as an operating system, if the default tablespace is the home directory, the temporary tablespace is the UNIX /tmp directory.

Fundamentally, the temporary tablespace functions as a holding area for SQL commands that require making sorts to the disk (as opposed to sorts in memory). Common examples of this type of operation are GROUP BY, SORT BY, and UNION ALL. When these types of operations are performed, the Oracle RDBMS takes contiguous extents on the temporary tablespace (segments) and uses this space to perform the required sorting and/or joining operations. After the operation is completed, the database releases the segments held within the tablespace.

Although a temporary tablespace is not required to be on a separate tablespace from other database objects, it is recommended. Not only does a separate tablespace reduce contention , but it also avoids fragmentation (for details on both problems, see Chapter 23, "Performance Tuning and Optimization"). Separation also helps keep the tablespace from unexpectedly reaching capacity. Any user who does not have a temporary tablespace set by the DBA has a default temporary tablespace of SYSTEM.

Resource Management

As discussed earlier in this chapter, Oracle7 provides more than just the capability to create user accounts within the database and to constantly monitor their activity; it also enables you to restrict activity by managing resources. To do this, Oracle7 offers two distinct features: profiles and tablespace quotas.

The primary difference between these two features is the type of resources they manage: profiles control process/memory use, and quotas control disk space. When implemented effectively, both features can help curtail problems such as rampant, runaway queries and excessively large, unnecessary tables. These features provide proactive tools to help efficiently maintain the database.

Previous Table of Contents Next