|Oracle7 Server Concepts Manual||
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:
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
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.
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.
Additional Information: For more information about authenticating via your operating system, see your Oracle operating system-specific documentation.
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.
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.
For more information about encrypting passwords in client/server systems, see Oracle7 Server Distributed Systems, Volume I.
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.
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:
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:
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.
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.
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.
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.
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.
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.
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.
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.
The concurrent usage licensing mechanism allows a database administrator to do the following:
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 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.
Copyright © 1996 Oracle Corporation.
All Rights Reserved.