Oracle7 Server Concepts Manual

Library

Product

Contents

Index

Database Access

Schemas, Database Users, and Security Domains
User Authentication
User Tablespace Settings and Quotas
The User Group PUBLIC
User Resource Limits and Profiles
Licensing

Allow me to congratulate you, sir. You have the most totally closed mind that I've ever encountered!

Jon Pertwee (as the Doctor): Frontier in Space

Database security involves allowing or disallowing users from performing actions on the database and the objects within it. Oracle provides comprehensive discretionary access control. Discretionary access control regulates all user access to named objects through privileges. A privilege is permission to access a named object in a prescribed manner; for example, permission to query a table. Because privileges are granted to users at the discretion of other users, this is called discretionary security.

This chapter explains how access to Oracle is controlled. It includes:

Schemas, Database Users, and Security Domains

User Authentication

User Tablespace Settings and Quotas

The User Group PUBLIC

User Resource Limits and Profiles

Licensing

If you are using Trusted Oracle, see the Trusted Oracle7 Server Administrator's Guide for information on database access in that environment.

Schemas, Database Users, and Security Domains

Schemas and users help database administrators manage database security. A schema is a named collection of schema objects, such as tables, views, clusters, procedures, and packages. A user (sometimes called a username) is a name defined in the database that can connect to and access objects in database schemas.

To access a database, a user must run a database application (such as an Oracle Forms form, SQL*Plus, or a Precompiler program) and connect using a username defined in the database.

When a database user is created, a corresponding schema of the same name is created for the user. By default, once a user connects to a database, the user has access to all objects contained in the corresponding schema. A user is associated only with the schema of the same name; therefore, the terms user and schema are similar.

The access rights of a user are controlled by the different settings of the user's security domain. When creating a new database user or altering an existing one, the security administrator must make several decisions concerning a user's security domain. These include

whether user authentication information is maintained by the database, the operating system, or a network authentication service

settings for the user's default and temporary tablespaces

a list, if any, of tablespaces accessible to the user and the associated quotas for each listed tablespace

the user's resource limit profile; that is, limits that dictate the amount of system resources available to the user

the privileges and roles that provide the user with appropriate access to objects needed to perform database operations

This chapter describes the security domain options listed above, except for privileges and roles, which are discussed in Chapter 18, "Privileges and Roles".

Note: The information in this chapter applies to all user-defined database users. It does not apply to the special database users SYS and SYSTEM. Settings for these users' security domains should never be altered. For more information about these special database users, see the Oracle7 Server Administrator's Guide.

User Authentication

To prevent unauthorized use of a database username, Oracle provides user validation via three different methods for normal database users:

authentication by the operating system

authentication by a network authentication service

authentication by the associated Oracle database

For simplicity, one method is usually used to authenticate all users of a database. However, Oracle allows use of all methods within the same database instance.

Oracle also encrypts passwords during transmission to ensure the security of client/server authentication.

Because database administrators perform special database operations, Oracle requires special authentication procedures for database administrators.

Authenticating Users Using the Operating System

If your operating system permits, Oracle can use information maintained by the operating system to authenticate users. The benefits of operating system authentication are the following:

Users can connect to Oracle more conveniently (without specifying a username or password). For example, a user can invoke SQL*Plus and skip the username and password prompts by entering

		SQLPLUS /

Control over user authorization is centralized in the operating system; Oracle need not store or manage user passwords. However, Oracle still maintains usernames in the database.

Username entries in the database and operating system audit trails correspond.

If the operating system is used to authenticate database users, there are some special considerations with respect to distributed database environments and database links; see Chapter 21, "Distributed Databases", for information on this topic.

Additional Information: For more information about authenticating via your operating system, see your Oracle operating system-specific documentation.

Authenticating Users Using Network Authentication

If network authentication services, such as DCE, Kerberos, or SESAME, are available to you, Oracle can accept authentication from the network service. To use a network authentication service with Oracle, you must also have the Oracle Secure Network Services product.

If you use a network authentication service, there are some special considerations for network roles and database links. See Oracle7 Server Distributed Systems, Volume I for more information about network authentication.

Authenticating Users Using the Oracle Database

Oracle can authenticate users attempting to connect to a database by using information stored in that database. You must use this method when the operating system cannot be used for database user validation.

When Oracle uses database authentication, you create each user with an associated password. A user provides the correct password when establishing a connection to prevent unauthorized use of the database. Oracle stores a user's password in the data dictionary. However, all passwords are stored in an encrypted format to maintain security for the user. A user can change his/her password at any time.

Password Encryption while Connecting

To better protect the confidentiality of your passwords, Oracle allows you to encrypt passwords during client/server and server/server connections. If you enable this functionality on the client and server machines, Oracle will encrypt passwords using a modified DES (Data Encryption Standards) algorithm before sending them across the network.

For more information about encrypting passwords in client/server systems, see Oracle7 Server Distributed Systems, Volume I.

Database Administrator Authentication

Database administrators must often perform special operations such as shutting down or starting up a database. Because these operations should not be performed by normal database users, the database administrator usernames need a more secure authentication scheme. Oracle provides a few methods for authenticating database administrators.

Depending on whether you wish to administer your database locally on the same machine on which the database resides or if you wish to administer many different database machines from a single remote client, you can choose between operating system authentication or password files to authenticate database administrators. Figure 17 - 1 illustrates the choices you have for database administrator authentication schemes.

Figure 17 - 1. Database Administrator Authentication Methods

On most operating systems, OS authentication for database administrators involves placing the OS username of the database administrator in a special group (on UNIX systems, this is the dba group) or giving that OS username a special process right.

Additional Information: For information about OS authentication of database administrators, see your Oracle operating system-specific documentation.

Password files are files used by the database to keep track of database usernames who have been granted the SYSDBA and SYSOPER privileges. These privileges allow database administrators to perform the following actions:

SYSOPER Permits you to perform STARTUP, SHUTDOWN, ALTER DATABASE OPEN/MOUNT, ALTER DATABASE BACKUP, ARCHIVE LOG, and RECOVER, and includes the RESTRICTED SESSION privilege.

SYSDBA Contains all system privileges with ADMIN OPTION, and the SYSOPER system privilege; permits CREATE DATABASE and time-based recovery.

For information about password files, see the Oracle7 Server Administrator's Guide.

User Tablespace Settings and Quotas

As part of every user's security domain, the database administrator can set several options regarding tablespace usage:

the user's default tablespace

the user's temporary tablespace

space usage quotas on tablespaces of the database for the user

The user can use the specified tablespace to create objects, provided that the user has the appropriate privileges.

The amount of space that can be allocated for storage of a user's objects within the specified tablespace can be limited.

By default, each user has no quota on any tablespace in the database. Therefore, if the user has the privilege to create some type of schema object, he must also have been either assigned a tablespace quota in which to create the object or been given the privilege to create that object in the schema of another user who was assigned a sufficient tablespace quota.

You can assign two types of tablespace quotas to a user: a quota for a specific amount of disk space in the tablespace, specified in bytes, Kb, or Mb, or a quota for an unlimited amount of disk space in the tablespace. You should assign specific quotas to prevent a user's objects from consuming too much space in a tablespace.

Tablespace quotas are not considered during temporary segment creation:

Temporary segments do not consume any quota that a user might possess.

Temporary segments can be created in a tablespace for which a user does not have a quota.

You can assign a tablespace quota to a user when you create that user, and you can change that quota or add a different quota later.

Revoking Tablespace Access

Revoke a user's tablespace access by altering the user's current quota to zero. With a quota of zero, the user's objects in the revoked tablespace remain, yet the objects cannot be allocated any new space.

The User Group PUBLIC

Each database contains a user group called PUBLIC. The PUBLIC user group provides public access to specific schema objects (tables, views, and so on) and provides all users with specific system privileges. Every user automatically belongs to the PUBLIC user group.

As members of PUBLIC, users may see (select from) all data dictionary tables prefixed with USER and ALL. Additionally, a user can grant a privilege or a role to PUBLIC. All users can use the privileges granted to PUBLIC.

You can grant (or revoke) any system privilege, object privilege, or role to PUBLIC. See Chapter 18, "Privileges and Roles," for more information on privileges and roles. However, to maintain tight security over access rights, grant only privileges and roles of interest to all users to PUBLIC.

Granting and revoking certain system and object privileges to and from PUBLIC can cause every view, procedure, function, package, and trigger in the database to be recompiled.

Restrictions for PUBLIC include the following:

You cannot assign tablespace quotas to PUBLIC, although you can assign the UNLIMITED TABLESPACE system privilege to PUBLIC.

You can create only database links and synonyms as PUBLIC objects, using CREATE PUBLIC DATABASE LINK/SYNONYM. No other object can be owned by PUBLIC. For example, the following statement is not legal:

	    CREATE TABLE public.emp . . . ;

Note: Rollback segments can be created with the keyword PUBLIC, but these are not owned by public. All rollback segments are owned by SYS. See Chapter 3, "Data Blocks, Extents, and Segments"; for more information about rollback segments.

User Resource Limits and Profiles

As part of a user's security domain, you can set limits on the amount of various system resources available to the user. By explicitly setting resource limits for each user, the security administrator can prevent the uncontrolled consumption of valuable system resources such as CPU time.

The resource limit feature of Oracle is very useful in large, multiuser systems. In such environments, system resources are very expensive; therefore, the excessive consumption of these resources by one or more users can detrimentally affect the other users of the database. In single user or small scale multiuser database systems, the system resource feature is not as useful because users are less likely to consume system resources with detrimental impact.

You manage resource limits with user profiles. A profile is a named set of resource limits that you can assign to a user. Each Oracle database can have an unlimited number of profiles. Additionally, Oracle provides the security administrator the option to universally enable or disable the enforcement of profile resource limits.

If you use resource limits, a slight degradation in performance occurs when users create sessions. This is because Oracle loads all resource limit data for the user when a user connects to a database.

Types of System Resources and Limits

Oracle can limit the use of several types of system resources. In general, you can control each of these resources at the session level, the call level, or both:

Session Level

Each time a user connects to a database, a session is created. Each session consumes CPU time and memory on the computer that executes Oracle. Several resource limits for Oracle can be set at the session level.

If a user exceeds a session-level resource limit, Oracle terminates the current statement (rolled back), and returns a message indicating the session limit has been reached. At this point, all previous statements in the current transaction are intact, and the only operations the user can perform are COMMIT, ROLLBACK, or disconnect (in this case, the current transaction is committed); all other operations produce an error. Even after the transaction is committed or rolled back, the user can effectively accomplish no more work during the current session.

Call Level

Each time a SQL statement is executed, several steps are taken to process the statement. During this processing, several calls are made to the database as part of the different execution phases. To prevent any one call from excessively using the system, Oracle allows several resource limits to be set at the call level.

If a user exceeds a call-level resource limit, Oracle halts the processing of the statement, rolls back the statement, and returns an error. However, all previous statements of the current transaction remain intact, and the user's session remains connected.

CPU Time

When SQL statements and other types of calls are made to Oracle, a certain amount of CPU time is necessary to process the call. Average calls require a small amount of CPU time. However, a SQL statement involving a large amount of data or a runaway query can potentially consume a large amount of CPU time, reducing CPU time available for other processing.

To prevent uncontrolled use of CPU time, you can limit the CPU time per call and the total amount of CPU time used for Oracle calls in the duration of a session. The limits are set and measured in CPU one-hundredth seconds (0.01 seconds) used by a call or a session.

Logical Reads

Input/output is one of the most expensive operations in a database system. I/O intensive statements can monopolize memory and disk usage and cause other database operations to compete for these resources.

To prevent single sources of excessive I/O, Oracle can limit the logical data block reads per call and per session. Logical data block reads include data block reads from both memory and disk. The limits are set and measured in number of block reads performed by a call or a session.

Other Resources

Oracle also provides for the limitation of several other resources at the session level:

You can limit the number of concurrent sessions per user. Each user can create only up to a predefined number of concurrent sessions.

You can limit the idle time for a session. If the time between Oracle calls for a session reaches the idle time limit, the current transaction is rolled back, the session is aborted, and the resources of the session are returned to the system. The next call receives an error that indicates the user is no longer connected to the instance. This limit is set as a number of elapsed minutes.

Note: Shortly after a session is aborted because it has exceeded an idle time limit, PMON cleans up after the aborted session. Until PMON completes this process, the killed session is still counted as one of the sessions for the sessions/user resource limit.

You can limit the elapsed connect time per session. If a session's duration exceeds the elapsed time limit, the current transaction is rolled back, the session is dropped, and the resources of the session are returned to the system. This limit is set as a number of elapsed minutes.

Note: Oracle does not constantly monitor the elapsed idle time or elapsed connection time. Doing so would reduce system performance. Instead, it checks every few minutes. Therefore, a session can exceed this limit slightly (for example, by five minutes) before Oracle enforces the limit and aborts the session.

You can limit the amount of private SGA space (used for private SQL areas) for a session. This limit is only important in systems that use the multi-threaded server configuration; otherwise, private SQL areas are located in the PGA. This limit is set as a number of bytes of memory in an instance's SGA. The characters "K" or "M" can be used to specify Kilobytes or Megabytes.

Instructions on enabling and disabling resource limits are included in the Oracle7 Server Administrator's Guide.

Profiles

A profile is a named set of specified resource limits that can be assigned to valid usernames of an Oracle database. Profiles provide for easy management of resource limits.

When to Use Profiles

You only need to create and manage user profiles if resource limits are a requirement of your database security policy. To use profiles, first categorize the related types of users in a database. Just as roles are used to manage the privileges of related users, profiles are used to manage the resource limits of related users. Determine how many profiles are needed to encompass all types of users in a database and then determine appropriate resource limits for each profile.

Determining Values for Resource Limits of a Profile

Before creating profiles and setting the resource limits associated with them, you should determine appropriate values for each resource limit. Base these values on the type of operations a typical user performs. For example, if one class of user does not normally perform a high number of logical data block reads, then the LOGICAL_READS_PER_SESSION and LOGICAL_READS_PER_CALL limits should be set conservatively.

Usually, the best way to determine the appropriate resource limit values for a given user profile is to gather historical information about each type of resource usage. For example, the database or security administrator can gather information about the limits CONNECT_TIME, LOGICAL_READS_PER_SESSION, and LOGICAL_READS_PER_CALL using the audit feature of Oracle. By using the AUDIT SESSION option, the audit trail gathers helpful information that you can used to determine appropriate values for the previously mentioned limits. You can gather statistics for other limits using the Monitor feature of Server Manager, specifically the Statistics monitor. The Monitor feature of Server Manager is described in the Oracle Server Manager User's Guide.

Licensing

Usually, Oracle is licensed for use by a maximum number of named users, or by a maximum number of concurrently connected users. The database administrator is responsible for making sure that the site complies with its license agreement. Oracle's licensing facility helps database administrators track and limit the number of sessions concurrently connected to an instance, or to limit the number of users created in a database, and thereby ensure that the site complies with the Oracle license agreement.

The database administrator controls the licensing facilities and can enable the facility and set the limits. He/she can also monitor the system's use. If the database administrator discovers that more than the licensed number of sessions need to connect, or more than the licensed number of users need to be created, he/she can upgrade the Oracle license to raise the appropriate limit. (To upgrade an Oracle license, you must contact your Oracle representative.)

Note: In some cases, Oracle is not licensed for either a set number of sessions or a set group of users. For example, when Oracle is embedded in an Oracle application (such as Oracle Office), run on some older operating systems, or purchased for use in some countries, it is licensed differently. In such cases only, the Oracle licensing mechanisms do not apply and should remain disabled.

The following sections explain the two major types of licensing available for Oracle.

Concurrent Usage Licensing

In concurrent usage licensing, the license specifies a number of concurrent users, which are sessions that can be connected concurrently to the database on the specified computer at any time. This number includes all batch processes and on-line users. Also, if a single user has multiple concurrent sessions, each session counts separately in the total number of sessions. If multiplexing software (such as a TP monitor) is used to reduce the number of sessions directly connected to the database, the number of concurrent users is the number of distinct inputs to the multiplexing front-end.

The concurrent usage licensing mechanism allows a database administrator to do the following:

An administrator can set a limit on the number of concurrent sessions that can connect to an instance by setting the LICENSE_MAX_SESSIONS parameter. Once this limit is reached, only users who have the RESTRICTED SESSION system privilege can connect to the instance; this allows database administrators to kill unneeded sessions, allowing other sessions to connect.

An administrator can set a warning limit on the number of concurrent sessions that can connect to an instance by setting the LICENSE_SESSIONS_WARNING parameter. Once the warning limit is reached, Oracle allows additional sessions to connect (up to the maximum limit described above), but sends a warning message to any user with RESTRICTED SESSION privilege who connects and records a warning message in the database's ALERT file.

The database administrator can set these limits in the database's parameter file so that they take effect when the instance starts. The administrator alternatively can change them while the instance is running by using the ALTER SYSTEM command. This is useful for databases that cannot be taken offline.

In addition, the session licensing mechanism allows a database administrator to check the current number of connected sessions and the maximum number of concurrent sessions since the instance started. The V$LICENSE view shows the current settings for the license limits, the current number of sessions, and the highest number of concurrent sessions since the instance started (the session "high water mark"). The administrator can use this information to evaluate the system's licensing needs and plan for system upgrades.

For instances running with the Parallel Server, each instance can have its own concurrent usage limit and warning limit. The sum of the instances' limits must not exceed the site's concurrent usage license. See the Oracle7 Server Administrator's Guide for more information.

The concurrent usage limits apply to all user sessions, including sessions created for incoming database links. They do not apply to sessions created by Oracle or recursive sessions. Sessions that connect through external multiplexing software are not counted separately by the Oracle licensing mechanism, although each contributes individually to the Oracle license total The database administrator is responsible for taking these sessions into account.

Named User Licensing

In named user licensing, the license specifies a number of named users, where a named user is an individual who is authorized to use Oracle on the specified computer. No limit is set on the number of sessions each user can have concurrently, or on the number of concurrent sessions for the database.

Named user licensing allows a database administrator to set a limit on the number of users that are defined in a database, including users connected via database links. Once this limit is reached, no one can create a new user. This mechanism assumes that each person accessing the database has a unique user name in the database and that no people share a user name.

The database administrator can set this limit in the database's parameter file so that it takes effect when the instance starts. The administrator can also change it while the instance is running by using the ALTER SYSTEM command. This is useful for databases that cannot be taken offline.

If multiple instances connect to the same database with the Parallel Server, all instances connected to the same database should have the same named user limit. See Oracle7 Parallel Server Concepts & Administration for more information.