Oracle9i Database Concepts Release 1 (9.0.1) Part Number A88856-02 |
|
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.
See Also:
|
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:
SQLPLUS /
If the operating system is used to authenticate database users, some special considerations arise with respect to distributed database environments and database links.
See Also:
|
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.
See Also:
|
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 24-1.
Oracle supports remote authentication of users through Remote Dial-In User Service (RADIUS), a standard lightweight protocol used for user authentication, authorization, and accounting.
See Also:
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 allows you to 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.
See Also:
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.
The CREATE 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 into 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 CREATE PROFILE
statements.
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 allows you to 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 24-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.
See Also:
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 24-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 SYSDBA
and SYSOPER
privileges. These privileges allow database administrators to perform the following actions.
See Also:
|
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:
SYS
and 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.
The 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 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.
However, to maintain tight security over access rights, grant only privileges and roles that are of interest to all users to PUBLIC.
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 UNLIMITED TABLESPACE
system privilege to PUBLIC.
PUBLIC
(using CREATE PUBLIC DATABASE LINK/SYNONYM),
but no other schema object can be owned by PUBLIC.
For example, the following statement is not legal:
CREATE TABLE public.emp ... ;
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.
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:
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:
Oracle9i Database Administrator's Guide for instructions about enabling and disabling resource limits
See Also:
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_SESSION
and 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 AUDIT SESSION
clause to gather information about the limits CONNECT_TIME, LOGICAL_READS_PER_SESSION,
and LOGICAL_READS_PER_CALL.
You can gather statistics for other limits using the Monitor feature of Oracle Enterprise Manager (or SQL*Plus), specifically the Statistics monitor.
Oracle is usually licensed for use by a maximum number of named users or by a maximum number of concurrently connected users. The database administrator (DBA) is responsible for ensuring that the site complies with its license agreement. Oracle's licensing facility helps the DBA monitor system use by tracking and limiting the number of sessions concurrently connected to an instance or the number of users created in a database.
If the DBA 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 or she can upgrade the Oracle license to raise the appropriate limit.
The following sections explain the two major types of licensing available for Oracle.
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 online users. 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 DBA to:
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 DBA to kill unneeded sessions, allowing other sessions to connect.
LICENSE_SESSIONS_WARNING
parameter. After the warning limit is reached, Oracle allows additional sessions to connect (up to the maximum limit described previously). However, Oracle sends a warning message to any user who connects with RESTRICTED SESSION
privilege and records a warning message in the database's ALERT
file.
The DBA can set these limits in the database's parameter file so that they take effect when the instance starts and can change them while the instance is running (using the ALTER SYSTEM
statement). The latter is useful for databases that cannot be taken offline.
The session licensing mechanism allows a DBA 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 DBA can use this information to evaluate the system's licensing needs and plan for system upgrades.
For instances running with Oracle9i Real Application Clusters, 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.
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 to 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 DBA is responsible for taking these sessions into account.
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 DBA to set a limit on the number of users that are defined in a database, including users connected through database links. After 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 two (or more) people share a user name.
The DBA can set this limit in the database's parameter file so that it takes effect when the instance starts and can change it while the instance is running (using the ALTER SYSTEM
statement). The latter is useful for databases that cannot be taken offline.
If multiple instances connect to the same database in Oracle9i Real Application Clusters, all instances connected to the same database should have the same named user limit.
|
![]() Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|