|Oracle9i Database Concepts
Release 2 (9.2)
Part Number A96524-01
This chapter explains how to control access to an Oracle database. It includes the following sections:
Database security entails allowing or disallowing user actions on the database and the objects within it. Oracle uses schemas and security domains to control access to data and to restrict the use of various database resources.
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. Privileges are granted to users at the discretion of other users--hence the term discretionary access control.
A user (sometimes called a username) is a name defined in the database that can connect to and access objects. A schema is a named collection of objects, such as tables, views, clusters, procedures, and packages. Schemas and users help database administrators manage database security.
Enterprise users are managed in a directory and can be given access to multiple schemas and databases without having to create an account or schema in each database. This arrangement is simpler for users and for DBAs and also offers better security because their privileges can be altered in one place.
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:
This chapter describes the first four security domain options listed.
The information in this chapter applies to all user-defined database users. It does not apply to the special database users
To prevent unauthorized use of a database username, Oracle provides user validation through several different methods for normal database users. You can perform authentication by:
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 network authentication.
Oracle requires special authentication procedures for database administrators, because they perform special database operations.
Some operating systems permit Oracle to use information maintained by the operating system to authenticate users. The benefits of authentication by the operating system are:
If the operating system is used to authenticate database users, some special considerations arise with respect to distributed database environments and database links.
Oracle supports the following methods of authentication by the network.
If network authentication services are available to you (such as DCE, Kerberos, or SESAME), Oracle can accept authentication from the network service. To use a network authentication service with Oracle, you need Oracle9i Enterprise Edition with the Oracle Advanced Security option.
Authentication systems based on public key cryptography systems issue digital certificates to user clients, which use them to authenticate directly to servers in the enterprise without direct involvement of an authentication server. Oracle provides a public key infrastructure (PKI) for using public keys and certificates. It consists of the following components:
Oracle's public key infrastructure is illustrated in Figure 22-1.
To use public key infrastructure-based authentication with Oracle, you need Oracle9i Enterprise Edition with the Oracle Advanced Security option.
Oracle supports remote authentication of users through Remote Dial-In User Service (RADIUS), a standard lightweight protocol used for user authentication, authorization, and accounting.
To use remote authentication of users through RADIUS with Oracle, you need Oracle9i Enterprise Edition with the Advanced Security option.
Oracle Advanced Security Administrator's Guide for information about Oracle Advanced Security
Oracle can authenticate users attempting to connect to a database by using information stored in that database.
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 in an encrypted format. A user can change his or her password at any time.
To protect password confidentiality, Oracle lets you encrypt passwords during network (client/server and server/server) connections. If you enable this functionality on the client and server machines, Oracle encrypts passwords using a modified DES (Data Encryption Standard) algorithm before sending them across the network. It is strongly recommended that you enable password encryption for connections to protect your passwords from network intrusion.
Oracle9i Database Administrator's Guide for more information about encrypting passwords in network systems
Oracle can lock a user's account if the user fails to login to the system within a specified number of attempts. Depending on how the account is configured, it can be unlocked automatically after a specified time interval or it must be unlocked by the database administrator.
PROFILE statement configures the number of failed logins a user can attempt and the amount of time the account remains locked before automatic unlock.
The database administrator can also lock accounts manually. When this occurs, the account cannot be unlocked automatically but must be unlocked explicitly by the database administrator.
Password lifetime and expiration options allow the database administrator to specify a lifetime for passwords, after which time they expire and must be changed before a login to the account can be completed. On first attempt to login to the database account after the password expires, the user's account enters the grace period, and a warning message is issued to the user every time the user tries to login until the grace period is over.
The user is expected to change the password within the grace period. If the password is not changed within the grace period, the account is locked and no further logins to that account are allowed without assistance by the database administrator.
The database administrator can also set the password state to expired. When this happens, the user's account status is changed to expired, and the user or the database administrator must change the password before the user can log in to the database.
The password history option checks each newly specified password to ensure that a password is not reused for the specified amount of time or for the specified number of password changes. The database administrator can configure the rules for password reuse with
Complexity verification checks that each password is complex enough to provide reasonable protection against intruders who try to break into the system by guessing passwords.
The Oracle default password complexity verification routine requires that each password:
In a multitier environment, Oracle controls the security of middle-tier applications by limiting their privileges, preserving client identities through all tiers, and auditing actions taken on behalf of clients. In applications that use a heavy middle tier, such as a transaction processing monitor, it is important to be able to preserve the identity of the client connecting to the middle tier. Yet one advantage of a middle tier is connection pooling, which allows multiple users to access a data server without each of them needing a separate connection. In such environments, you need to be able to set up and break down connections very quickly. For these environments, Oracle offers the creation of lightweight sessions through the Oracle Call Interface. These lightweight sessions allow each user to be authenticated by a database password without the overhead of a separate database connection, as well as preserving the identity of the real user through the middle tier.
You can create lightweight sessions with or without passwords. If a middle tier is outside or on a firewall, it would be appropriate to establish the lightweight session with passwords for each lightweight user session. For an internal application server, it might be appropriate to create a lightweight session that does not require passwords.
In a multitier architecture environment, an application server provides data for clients and serves as an interface between clients and one or more database servers.
This architecture lets you use an application server to validate the credentials of a client, such as a web browser. In addition, the database server can audit operations performed by the application server and operations performed by the application server on behalf of the client. For example, an operation performed by the application server on behalf of the client might be a request for information to be displayed on the client whereas an operation performed by the application server might be a request for a connection to the database server.
Authentication in a multitier environment is based on trust regions, including the following:
Application servers can also enable roles for the client on whose behalf it is connecting. The application server can obtain these roles from a directory, which thus serves as an authorization repository. The application server can only request that these roles be enabled. The database verifies that:
Figure 22-2 shows an example of multitier authentication.
There are a number of security issues for middle-tier applications:
Multitier authentication maintains the identify of the client through all tiers of the connection. This is necessary because if the identity of the originating client is lost, it is not possible to maintain useful audit records. In addition, it is not possible to distinguish operations performed by the application server on behalf of the client from those done by the application server for itself.
Privileges in a multitier environment are limited to what is necessary to perform the requested operation.
Client privileges are as limited as possible in a multitier environment. Operations are performed on behalf of the client by the application server.
Application server privileges in a multitier environment are limited so that the application server cannot perform unwanted or unneeded operations while performing a client operation.
Oracle9i Database Administrator's Guide for more information about multitier authentication
The Secure Socket Layer (SSL) protocol is an application layer protocol. It can be used for user authentication to a database, independent of global user management in Oracle Internet Directory. That is, users can use SSL to authenticate to the database without implying anything about their directory access. However, if you wish to use the enterprise user functionality to manage users and their privileges in a directory, the user must use SSL to authenticate to the database. A parameter in the initialization file governs which use of SSL is expected.
Database administrators perform special operations (such as shutting down or starting up a database) that should not be performed by normal database users. Oracle provides a more secure authentication scheme for database administrator usernames.
You can choose between operating system authentication or password files to authenticate database administrators.
Figure 22-3 illustrates the choices you have for database administrator authentication schemes, depending on whether you administer your database locally (on the same machine on which the database resides) or if you administer many different database machines from a single remote client.
On most operating systems, operating system authentication for database administrators involves placing the operating system username of the database administrator in a special group (on UNIX systems, this is the dba group) or giving that operating system username a special process right.
The database uses password files to keep track of database usernames who have been granted the
SYSOPERlets database administrators perform
RECOVER, and includes the
SYSDBAcontains all system privileges with
OPTION, and the
SYSOPERsystem privilege. Permits
DATABASEand time-based recovery.
Oracle Internet Directory is a directory service implemented as an application on the Oracle database. It enables retrieval of information about dispersed users and network resources. Oracle Internet Directory combines Lightweight Directory Access Protocol (LDAP), version 3, the open Internet standard directory access protocol, with the high performance, scalability, robustness, and availability of the Oracle Server.
Oracle Internet Directory includes the following:
As part of every user's security domain, the database administrator can set several options regarding tablespace use:
When a user creates a schema object without specifying a tablespace to contain the object, Oracle places the object in the user's default tablespace. You set a user's default tablespace when the user is created, and you can change it after the user has been created.
When a user executes a SQL statement that requires the creation of a temporary segment, Oracle allocates that segment in the user's temporary tablespace.
You can assign to each user a tablespace quota for any tablespace of the database. Doing so can accomplish two things:
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 or she 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, kilobytes, or megabytes), 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 and temporary segments have no effect on each other:
SYSand therefore are not subject to quotas.
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.
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, but the objects cannot be allocated any new space.
Each database contains a user group called
PUBLIC user group provides public access to specific schema objects, such as tables and views, and provides all users with specific system privileges. Every user automatically belongs to the
PUBLIC user group.
As members of
PUBLIC, users can see (select from) all data dictionary tables prefixed with
ALL. Additionally, a user can grant a privilege or a role to
PUBLIC. All users can use the privileges granted to
You can grant or revoke any system privilege, object privilege, or role to
PUBLIC. However, to maintain tight security over access rights, grant only privileges and roles that are of interest to all users to
Granting and revoking some system and object privileges to and from
PUBLIC can cause every view, procedure, function, package, and trigger in the database to be recompiled.
PUBLIC has the following restrictions:
PUBLIC, although you can assign the
TABLESPACEsystem privilege to
LINK/SYNONYM), but no other schema object can be owned by
PUBLIC. For example, the following statement is not legal:
Rollback segments can be created with the keyword
You can set limits on the amount of various system resources available to each user as part of a user's security domain. By doing so, you can prevent the uncontrolled consumption of valuable system resources such as CPU time.
This resource limit feature is very useful in large, multiuser systems, where system resources are very expensive. 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 important, because users' consumption of system resources is less likely to have detrimental impact.
You manage a user's resource limits and password management preferences with his or her profile--a named set of resource limits that you can assign to that user. Each Oracle database can have an unlimited number of profiles. Oracle allows the security administrator to enable or disable the enforcement of profile resource limits universally.
If you set 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.
Oracle9i Database Administrator's Guide for information about security administrators
Oracle can limit the use of several types of system resources, including CPU time and logical reads. In general, you can control each of these resources at the session level, the call level, or both.
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. You can set several resource limits at the session level.
If a user exceeds a session-level resource limit, Oracle terminates (rolls back) the current statement 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
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 accomplish no more work during the current session.
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 using the system excessively, Oracle lets you set several resource limits 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.
When SQL statements and other types of calls are made to Oracle, an 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 for each call and the total amount of CPU time used for Oracle calls during a session. The limits are set and measured in CPU one-hundredth seconds (0.01 seconds) used by a call or a session.
Input/output (I/O) is one of the most expensive operations in a database system. SQL statements that are I/O intensive can monopolize memory and disk use and cause other database operations to compete for these resources.
To prevent single sources of excessive I/O, Oracle let you limit the logical data block reads for each call and for each 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 during a session.
Oracle also provides for the limitation of several other resources at the session level:
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.
Oracle9i Database Administrator's Guide for instructions about enabling and disabling resource limits
A profile is a named set of specified resource limits that can be assigned to a valid username of an Oracle database. Profiles provide for easy management of resource limits. Profiles are also the way in which you administer password policy.
You need to create and manage user profiles only 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.
Before creating profiles and setting the resource limits associated with them, you should determine appropriate values for each resource limit. You can 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 set the
LOGICAL_READS_PER_CALL limits 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 use the
SESSION clause to gather information about the limits
You can gather statistics for other limits using the Monitor feature of Oracle Enterprise Manager (or SQL*Plus), specifically the Statistics monitor.