Oracle7 Server Administrator's Guide Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index



Go to previous file in sequence Go to next file in sequence

Managing Users and Resources


This chapter describes how to control access to an Oracle database, and includes the following topics:

See Also: For guidelines on establishing security policies for users and profiles, see Chapter 18.

Privileges and roles control the access a user has to a database and the schema objects within the database. For information on privileges and roles, see Chapter 20.

For databases using Trusted Oracle, see the Trusted Oracle7 Server Administrator's Guide for additional information about user management in that environment.

This chapter contains several references to Oracle Server Manager. For more information about performing specific tasks using Server Manager/GUI or Server Manager/LineMode, see the Oracle Server Manager User's Guide.

Session and User Licensing

This section describes aspects of session and user licensing, and includes the following topics:

Oracle helps you ensure that your site complies with its Oracle Server license agreement. If your site is licensed by concurrent usage, you can track and limit the number of sessions concurrently connected to a database. If your site is licensed by named users, you can limit the number of named users created in a database. In either case, you control the licensing facilities, and must enable the facilities and set the appropriate limits.

To use the licensing facility, you need to know which type of licensing agreement your site has, and what the maximum number of sessions or named users is. Your site may use either type of licensing (concurrent usage or named user), but not both.

Note: In a few cases, a site might have an unlimited license, rather than concurrent usage or named user licensing. In these cases only, leave the licensing mechanism disabled, and omit LICENSE_MAX_SESSIONS, LICENSE_SESSIONS_WARNING, and LICENSE_MAX_USERS from the parameter file, or set the value of all three to 0.

Concurrent Usage Licensing

Concurrent usage licensing limits the number of sessions that can be connected simultaneously to the database on the specified computer. You can set a limit on the number of concurrent sessions before you start an instance. In fact, you should have set this limit as part of the initial installation procedure. You can also change the maximum number of concurrent sessions while the database is running.

See Also: For information about the initial installation procedure, see "Edit New Parameter Files" [*].

Connecting Privileges

After your instance's session limit is reached, only users with RESTRICTED SESSION privilege (usually DBAs) can connect to the database. When a user with RESTRICTED SESSION privileges connects, Oracle sends the user a message indicating that the maximum limit has been reached, and writes a message to the ALERT file. When the maximum is reached, you should connect only to terminate unneeded processes. Do not raise the licensing limits unless you have upgraded your Oracle license agreement.

In addition to setting a maximum concurrent session limit, you can set a warning limit on the number of concurrent sessions. After this limit is reached, additional users can continue to connect (up to the maximum limit); however, Oracle writes an appropriate message to the ALERT file with each connection, and sends each connecting user who has the RESTRICTED SESSION privilege a warning indicating that the maximum is about to be reached.

If a user is connecting with administrator privileges, the limits still apply; however, Oracle enforces the limit after the first statement the user executes.

In addition to enforcing the concurrent usage limits, Oracle tracks the highest number of concurrent sessions for each instance. You can use this "high water mark."

See Also: For information about terminating sessions, see "Terminating Sessions" [*].

For information about Oracle licensing limit upgrades, see "Viewing Licensing Limits and Current Values" [*].

Parallel Server Concurrent Usage Limits

For instances running with the Parallel Server, each instance can have its own concurrent usage limit and warning limit. However, the sum of the instances' limits must not exceed the site's concurrent usage license.

Warning: Sessions that connect to Oracle through multiplexing software or hardware (such as a TP monitor) each contribute individually to the concurrent usage limit. However, the Oracle licensing mechanism cannot distinguish the number of sessions connected this way. If your site uses multiplexing software or hardware, you must consider that and set the maximum concurrent usage limit lower to account for the multiplexed sessions.

See Also: For more information about setting and changing limits in a parallel server environment, see the Oracle7 Parallel Server Concepts & Administration guide.

Setting the Maximum Number of Sessions

To set the maximum number of concurrent sessions for an instance, set the parameter LICENSE_MAX_SESSIONS as follows:

LICENSE_MAX_SESSIONS = 80

If you set this limit, you are not required to set a warning limit (LICENSE_SESSIONS_WARNING). However, using the warning limit makes the maximum limit easier to manage, because it gives you advance notice that your site is nearing maximum use.

Setting the Session Warning Limit

To set the warning limit for an instance, set the parameter LICENSE_SESSIONS_WARNING in the parameter file used to start the instance.

Set the session warning to a value lower than the concurrent usage maximum limit (LICENSE_MAX_SESSIONS).

Changing Concurrent Usage Limits While the Database is Running

To change either the maximum concurrent usage limit or the warning limit while the database is running, use the ALTER SYSTEM command with the appropriate option. The following statement changes the maximum limit to 100 concurrent sessions:

ALTER SYSTEM SET LICENSE_MAX_SESSIONS = 100;

The following statement changes both the warning limit and the maximum limit:

ALTER SYSTEM
   SET LICENSE_MAX_SESSIONS = 64
      LICENSE_SESSIONS_WARNING = 54;

If you change either limit to a value lower than the current number of sessions, the current sessions remain; however, the new limit is enforced for all future connections until the instance is shut down. To change the limit permanently, change the value of the appropriate parameter in the parameter file.

To change the concurrent usage limits while the database is running, you must have the ALTER SYSTEM privilege. Also, to connect to an instance after the instance's maximum limit has been reached, you must have the RESTRICTED SESSION privilege.

Warning: Do not raise the concurrent usage limits unless you have appropriately upgraded your Oracle Server license. Contact your Oracle representative for more information.

Named User Limits

Named user licensing limits the number of individuals authorized to use Oracle on the specified computer. To enforce this license, you can set a limit on the number of users created in the database before you start an instance. You can also change the maximum number of users while the instance is running, or disable the limit altogether. You cannot create more users after reaching this limit. If you try to do so, Oracle returns an error indicating that the maximum number of users have been created, and writes a message to the ALERT file.

This mechanism operates on the assumption that each person accessing the database has a unique username, and that there are no shared usernames. Do not allow multiple users to connect using the same username.

See Also: For instances running with the Parallel Server, all instances connected to the same database should have the same named user limit. See the Oracle7 Parallel Server Concepts & Administration guide for more information.

Setting User Limits

To limit the number of users created in a database, set the LICENSE_MAX_USERS parameter in the database's parameter file. The following example sets the maximum number of users to 200:

LICENSE_MAX_USERS = 200

If the database contains more than LICENSE_MAX_USERS when you start it, Oracle returns a warning and writes an appropriate message in the ALERT file. You cannot create additional users until the number of users drops below the limit, you should delete users, or upgrade your Oracle license.

Changing User Limits

To change the maximum named users limit, use the ALTER SYSTEM command with the LICENSE_MAX_USERS option. The following statement changes the maximum number of defined users to 300:

ALTER SYSTEM SET LICENSE_MAX_USERS = 300;

If you try to change the limit to a value lower than the current number of users, Oracle returns an error and continues to use the old limit. If you successfully change the limit, the new limit remains in effect until you shut down the instance; to change the limit permanently, change the value of LICENSE_MAX_USERS in the parameter file.

To change the maximum named users limit, you must have the ALTER SYSTEM privilege.

Warning: Do not raise the named user limit unless you have appropriately upgraded your Oracle license. Contact your Oracle representative for more information.

Viewing Licensing Limits and Current Values

You can see the current limits of all of the license settings, the current number of sessions, and the maximum number of concurrent sessions for the instance by querying the V$LICENSE data dictionary view. You can use this information to determine if you need to upgrade your Oracle license to allow more concurrent sessions or named users:

SELECT sessions_max s_max,
   sessions_warning s_warning,
   sessions_current s_current,
   sessions_highwater s_high,
   users_max
   FROM v$license;
S_MAX      S_WARNING  S_CURRENT  S_HIGH     USERS_MAX
-------------------------------------------------------
     100          80         65         88        250

In addition, Oracle writes the session high water mark to the database's ALERT file when the database shuts down, so you can check for it there.

To see the current number of named users defined in the database, use the following query:

SELECT COUNT(*) FROM dba_users;
COUNT(*)
----------
       174

User Authentication

This section describes aspects of authenticating users, and includes the following topics:

Depending on the way you want user identities to be authenticated before they are allowed to create a database session, there are two ways to define users.

1. You can configure Oracle so that it performs both identification and authentication of users.

2. You can configure Oracle so that it performs only the identification of users (leaving authentication up to the operating system or network security service).

You can use the following statement to create a user who is identified and authenticated by Oracle:

CREATE USER scott IDENTIFIED BY tiger;

Use the following command to create a user who is identified by Oracle and authenticated by the operating system or a network service:

CREATE USER scott IDENTIFIED EXTERNALLY;

Using CREATE USER IDENTIFIED EXTERNALLY, database administrators can create database accounts that must be authenticated via the operating system and cannot be authenticated using a password. By default, Oracle only allows operating system authenticated logins over secure connections. Setting the initialization parameter REMOTE_OS_AUTHENT to TRUE allows the RDBMS to trust the client's operating system username received over a non-secure connection and use it for account access. When creating a user, you can specify how that user is authenticated by Oracle. Of course, you can alter any user authentication methods later, after creating a user.

See Also: For information about network authentication, see Oracle7 Server Distributed Systems, Volume I.

Operating System Authentication

With operating system authentication your database relies on the underlying operating system to restrict access to database accounts. A database password is not used for this type of login. If your operating system permits, you can have it authenticate users. If you do so, set the parameter OS_AUTHENT_PREFIX, and use this prefix in Oracle usernames. This parameter defines a prefix that Oracle adds to the beginning of every user's operating system account name. Oracle compares the prefixed username with the Oracle usernames in the database when a user attempts to connect.

For example, assume that OS_AUTHENT_PREFIX is set as follows:

OS_AUTHENT_PREFIX=OPS$

If a user with an operating system account named "TSMITH" is to connect to an Oracle database and be authenticated by the operating system, Oracle checks that there is a corresponding database user "OPS$TSMITH" and, if so, allows the user to connect. All references to a user authenticated by the operating system must include the prefix, as seen in "OPS$TSMITH".

The default value of this parameter is "OPS$" for backward compatibility with previous versions of Oracle. However, you might prefer to set the prefix value to some other string or a null string (an empty set of double quotes: ""). Using a null string eliminates the addition of any prefix to operating system account names, so that Oracle usernames exactly match operating system usernames.

After you set OS_AUTHENT_PREFIX, it should remain the same for the life of a database. If you change the prefix, any database username that includes the old prefix cannot be used to establish a connection, unless you alter the username to have it use password authentication.

See Also: The text of the OS_AUTHENT_PREFIX parameter is case-sensitive on some operating systems. See your operating system-specific Oracle documentation for more information about this initialization parameter.

Operating System Authentication and Network Clients

If you want to have the operating system authenticate a user, by default that user cannot connect to the database over SQL*Net. This means the user cannot connect using a multi-threaded server, as this connection uses SQL*Net. This restriction is the default because a remote user could impersonate another operating system user over a network connection.

If you are not concerned with this security risk and want to use operating system user authentication with network clients, set the parameter REMOTE_OS_AUTHENT (default is FALSE)in the database's parameter file to TRUE. The change will take effect the next time you start the instance and mount the database.

Database Authentication

To have Oracle authenticate a user, specify a password for the user when you create or alter the user. Users can change their password at any time. Passwords are stored in an encrypted format. Each password must be made up of single-byte characters, even if your database uses a multi-byte character set.

See Also: For more information about valid passwords, see the Oracle7 Server SQL Reference.

Oracle Users

Each Oracle database has a list of valid database users. To access a database, a user must run a database application and connect to the database instance using a valid username defined in the database. This section explains how to manage users for a database, and includes the following topics:

Creating Users

To create a database user, you must have the CREATE USER system privilege. When creating a new user, tablespace quotas can be specified for any tablespace in the database, even if the creator does not have a quota on a specified tablespace. Due to such privileged power, a security administrator is normally the only type of user that has the CREATE USER system privilege.

You create a user with either the Create User property sheet of Server Manager/GUI, or the SQL command CREATE USER. Using either option, you can also specify the new user's default and temporary segment tablespaces, tablespace quotas, and profile.

The following statement creates a new user named JWARD, identified externally:

CREATE USER OPS$jward
   IDENTIFIED EXTERNALLY
   DEFAULT TABLESPACE data_ts
   TEMPORARY TABLESPACE temp_ts
   QUOTA 100M ON test_ts
   QUOTA 500K ON data_ts
   PROFILE clerk;

See Also: A newly-created user cannot connect to the database until granted the CREATE SESSION system privilege; see page 20 - 12.

Specifying a Name

Within each database, a username must be unique with respect to other usernames and roles; a user and role cannot have the same name. Furthermore, each user has an associated schema. Within a schema, each schema object must have unique names.

Usernames in Multi-Byte Character Sets In a database that uses a multi-byte character set, each username should contain at least one single-byte character. If a username contains only multi-byte characters, the encrypted username/password combination is considerably less secure.

Setting a User's Authentication

In the previous CREATE USER statement, the new user is to be authenticated using the operating system. The username includes the default prefix "OPS$." If the OS_AUTHENT_PREFIX parameter is set differently (that is, if it specifies either no prefix or some other prefix), modify the username accordingly, by omitting the prefix or substituting the correct prefix.

Alternatively, you can create a user who is authenticated using the database and a password:

CREATE USER jward
   IDENTIFIED BY airplane
   . . . ;

In this case, the connecting user must supply the correct password to the database to connect successfully.

User Passwords in Multi-Byte Character Sets In a database that uses a multi-byte character set, passwords must include only single-byte characters. Multi-byte characters are not accepted in passwords. See Also: For more information about valid passwords, see the Oracle7 Server SQL Reference.

Assigning a Default Tablespace

Each user has a default tablespace. When a user creates a schema object and specifies no tablespace to contain it, Oracle stores the object in the user's default tablespace.

The default setting for every user's default tablespace is the SYSTEM tablespace. If a user does not create objects, this default setting is fine. However, if a user creates any type of object, consider specifically setting the user's default tablespace. You can set a user's default tablespace during user creation, and change it later. Changing the user's default tablespace affects only objects created after the setting is changed.

Consider the following issues when deciding which tablespace to specify:

In the previous CREATE USER statement, JWARD's default tablespace is DATA_TS.

Assigning a Temporary Tablespace

Each user also has a temporary tablespace. When a user executes a SQL statement that requires a temporary segment, Oracle stores the segment in the user's temporary tablespace.

If a user's temporary tablespace is not explicitly set, the default is the SYSTEM tablespace. However, setting each user's temporary tablespace reduces file contention among temporary segments and other types of segments. You can set a user's temporary tablespace at user creation, and change it later.

In the previous CREATE USER statement, JWARD's temporary tablespace is TEMP_TS, a tablespace created explicitly to only contain temporary segments.

Assigning Tablespace Quotas

You can assign each user a tablespace quota for any tablespace. Assigning a quota does two things:

By default, a user has no quota on any tablespace in the database. If the user has the privilege to create a schema object, you must assign a quota to allow the user to create objects. Minimally, assign users a quota for the default tablespace, and additional quotas for other tablespaces in which they will create objects.

You can assign a user either individual quotas for a specific amount of disk space in each tablespace, or an unlimited amount of disk space in all tablespaces. Specific quotas prevent a user's objects from consuming too much space in the database.

You can assign a user's tablespace quotas when you create the user, or add or change quotas later. If a new quota is less than the old one, then the following conditions hold true:

Revoking Tablespace AccessYou can revoke a user's tablespace access by changing the user's current quota to zero. After a quota of zero is assigned, the user's objects in the revoked tablespace remain, but the objects cannot be allocated any new space. UNLIMITED TABLESPACE System PrivilegeTo permit a user to use an unlimited amount of any tablespace in the database, grant the user the UNLIMITED TABLESPACE system privilege. This overrides all explicit tablespace quotas for the user. If you later revoke the privilege, explicit quotas again take effect. You can grant this privilege only to users, not to roles. Before granting the UNLIMITED TABLESPACE system privilege, consider the consequences of doing so:

Advantage

Disadvantages

Setting Default Roles

You cannot set a user's default roles in the CREATE USER statement. When you first create a user, the user's default role setting is ALL, which causes all roles subsequently granted to the user to be default roles. Use the ALTER USER command to change the user's default roles.

Warning: When you create a role, it is granted to you implicitly and added as a default role. You will get an error at login if you have more than MAX_ENABLED_ROLES. You can avoid this error by altering the user's default roles to be less than MAX_ENABLED_ROLES. Thus, you should change the DEFAULT ROLE settings of SYS and SYSTEM before creating user roles.

Altering Users

Users can change their own passwords. However, to change any other option of a user's security domain, you must have the ALTER USER system privilege. Security administrators are normally the only users that have this system privilege, as it allows a modification of any user's security domain. This privilege includes the ability to set tablespace quotas for a user on any tablespace in the database, even if the user performing the modification does not have a quota for a specified tablespace.

You can alter a user's security settings with either the Alter User property sheet of Server Manager/GUI, or the SQL command ALTER USER. Changing a user's security settings affects the user's future sessions, not current sessions.

The following statement alters the security settings for user AVYRROS:

ALTER USER avyrros
   IDENTIFIED EXTERNALLY
   DEFAULT TABLESPACE data_ts
   TEMPORARY TABLESPACE temp_ts
   QUOTA 100M ON data_ts
   QUOTA 0 ON test_ts
   PROFILE clerk;

The ALTER USER statement here changes AVYRROS's security settings as follows:

Changing a User's Password, for Non-DBAs

While most non-DBA users do not use Server Manager, they can still change their own passwords with the ALTER USER command, as follows:

ALTER USER andy
   IDENTIFIED BY swordfish;

Users can change their own passwords this way, without any special privileges (other than those to connect to the database). Users should be encouraged to change their passwords frequently.

A user must have the ALTER USER privilege to change between Oracle authorization and operating system authorization; usually only DBAs should have this privilege.

Passwords in Multi-Byte Character Sets In a database that uses a multi-byte character set, passwords must include only single-byte characters. Multi-byte characters are not accepted in passwords. See Also: For more information about valid passwords, see the Oracle7 Server SQL Reference.

Changing a User's Default Roles

A default role is one that is automatically enabled for a user when the user creates a session. You can assign a user zero or more default roles. Any role directly granted to a user can potentially be a default role of the user; you cannot specify an indirectly granted role when listing default roles in an ALTER USER DEFAULT ROLE command. However, if the role that the indirectly granted role is granted to is a default role, then all indirectly granted roles of that role are enabled by default. The number of default roles for a user should not exceed the maximum number of enabled roles that are allowed per user; if it does, when the user tries to connect, errors are returned and the connection is not allowed.

Note: Oracle automatically enables a user's default roles when the user creates a session. Placing a role in a user's list of default roles bypasses authentication for the role, whether the role is defined to be authorized using a password or the operating system.

If you specify a list of roles, all other roles granted to that user are removed from the user's default role list.

Suppose user AVYRROS has been granted the roles DEVELOPER and CLERK, and CLERK is his only default role. The following statement removes CLERK from his default role list and adds DEVELOPER:

ALTER USER avyrros
   DEFAULT ROLE DEVELOPER;

In this case, any roles subsequently granted to AVYRROS will not be default roles, and will be disabled on connection.

If you specify ALL for the user's list of default roles, every role granted directly to the user is automatically added to the user's list of default roles. Subsequent modification of a user's default role list can remove newly granted roles from a user's list of default roles. The following example causes all roles currently granted to AVYRROS to be added to his list of default roles, as well as all roles granted in the future:

ALTER USER avyrros
   DEFAULT ROLE ALL;

Furthermore, you can specify ALL EXCEPT with a list of roles, and those roles will be the only roles granted to the user not on the default role list. For example, the following statement adds all roles currently granted to AVYRROS (except the role PAYROLL) to the user's default role list. Any roles granted to AVYRROS in the future are also added to the default role list:

ALTER USER avyrros
   DEFAULT ROLE ALL EXCEPT payroll;

To ensure a user has no default roles, specify NONE for the user's list of default roles:

ALTER USER avyrros
   DEFAULT ROLE NONE;

Changing a user's default role list affects subsequent sessions; it does not affect any session in progress at the time.

Revoking a role from a user automatically removes the role from the user's default role list.

Dropping Users

When a user is dropped, the user and associated schema is removed from the data dictionary and all schema objects contained in the user's schema, if any, are immediately dropped.

Note: If a user's schema and associated objects must remain but the user must be revoked access to the database, revoke the CREATE SESSION privilege from the user.

A user that is currently connected to a database cannot be dropped. To drop a connected user, you must first terminate the user's sessions using either Server Manager/GUI, or the SQL command ALTER SYSTEM with the KILL SESSION clause.

To drop a user and all the user's schema objects (if any), you must have the DROP USER system privilege. Because the DROP USER system privilege is so powerful, a security administrator is typically the only type of user that has this privilege.

You can drop a user from a database using either the Drop menu item of Server Manager/GUI, or the SQL command DROP USER.

If the user's schema contains any schema objects, use the CASCADE option to drop the user and all associated objects and foreign keys that depend on the tables of the user successfully. If you do not specify CASCADE and the user's schema contains objects, an error message is returned and the user is not dropped. Before dropping a user whose schema contains objects, thoroughly investigate which objects the user's schema contains and the implications of dropping them before the user is dropped. Pay attention to any unknown cascading effects. For example, if you intend to drop a user who owns a table, check whether any views or procedures depend on that particular table.

The following statement drops the user JONES, all objects in JONES' schema, and any dependent foreign keys:

DROP USER jones CASCADE;

See Also: For more information about terminating sessions, see "Terminating Sessions" [*].

Managing Resources with Profiles

A profile is a named set of resource limits. If resource limits are turned on, Oracle limits database usage and instance resources to whatever is defined in the user's profile. You can assign a profile to each user, and a default profile to all users who do not have specific profiles. For profiles to take effect, resource limits must be turned on for the database as a whole.

This section describes aspects of profile management, and includes the following topics:

Creating Profiles

To create a profile, you must have the CREATE PROFILE system privilege. You can create profiles using either the Create Profile property sheet of Server Manager/GUI, or the SQL command CREATE PROFILE. At the same time, you can explicitly set particular resource limits.

The following statement creates the profile CLERK:

CREATE PROFILE clerk LIMIT
   SESSIONS_PER_USER 2
   CPU_PER_SESSION unlimited
   CPU_PER_CALL 6000
   LOGICAL_READS_PER_SESSION unlimited
   LOGICAL_READS_PER_CALL 100
   IDLE_TIME 30
   CONNECT_TIME 480;

All unspecified resource limits for a new profile take the limit set by the DEFAULT profile. You can also specify limits for the DEFAULT profile.

Using the DEFAULT Profile

Each database has a DEFAULT profile, and its limits are used in two cases:

Initially, all limits of the DEFAULT profile are set to UNLIMITED. However, to prevent unlimited resource consumption by users of the DEFAULT profile, the security administrator should change the default limits using the Alter Profile dialog box of Server Manager, or a typical ALTER PROFILE statement:

ALTER PROFILE default LIMIT
   . . . ;

Any user with the ALTER PROFILE system privilege can adjust the limits in the DEFAULT profile. The DEFAULT profile cannot be dropped.

Assigning Profiles

After a profile has been created, you can assign it to database users. Each user can be assigned only one profile at any given time. If a profile is assigned to a user who already has a profile, the new profile assignment overrides the previously assigned profile. Profile assignments do not affect current sessions. Profiles can be assigned only to users and not to roles or other profiles.

Profiles can be assigned to users using the Assign Profile dialog box of Server Manager/GUI, or the SQL commands CREATE USER or ALTER USER.

See Also: For more information about assigning a profile to a user, see page 19 - 9 and page 19 - 12.

Altering Profiles

You can alter the resource limit settings of any profile using either the Alter Profile property sheet of Server Manager/GUI, or the SQL command ALTER PROFILE. To alter a profile, you must have the ALTER PROFILE system privilege.

Any adjusted profile limit overrides the previous setting for that profile limit. By adjusting a limit with a value of DEFAULT, the resource limit reverts to the default limit set for the database. All profiles not adjusted when altering a profile retain the previous settings. Any changes to a profile do not affect current sessions. New profile settings are used only for sessions created after a profile is modified.

The following statement alters the CLERK profile:

ALTER PROFILE clerk LIMIT
   CPU_PER_CALL default
   LOGICAL_READS_PER_SESSION 20000;

See Also: For information about default profiles, see "Using the Default Profile" [*].

Using Composite Limits

You can limit the total resource cost for a session via composite limits. In addition to setting specific resource limits explicitly for a profile, you can set a single composite limit that accounts for all resource limits in a profile. You can set a profile's composite limit using the Composite Limit checkbox of the Create Profile and Alter Profile property sheets of Server Manager/GUI, or the COMPOSITE_LIMIT parameter of the SQL commands CREATE PROFILE or ALTER PROFILE. A composite limit is set via a service unit, which is a weighted sum of all resources used.

The following CREATE PROFILE statement is defined using the COMPOSITE_LIMIT parameter:

CREATE PROFILE clerk LIMIT
   COMPOSITE_LIMIT 20000
   SESSIONS_PER_USER 2
   CPU_PER_CALL 1000;

Notice that both explicit resource limits and a composite limit can exist concurrently for a profile. The limit that is reached first stops the activity in a session. Composite limits allow additional flexibility when limiting the use of system resources.

Determining the Value of the Composite Limit

The correct service unit setting for a composite limit depends on the total amount of resource used by an average profile user. As with each specific resource limit, historical information should be gathered to determine the normal range of composite resource usage for a typical profile user.

Setting Resource Costs

Each system has its own characteristics; some system resources may be more valuable than others. Oracle enables you to give each system resource a cost. Costs weight each system resource at the database level. Costs are only applied to the composite limit of a profile; costs do not apply to set individual resource limits explicitly.

To set resource costs, you must have the ALTER RESOURCE system privilege.

Only certain resources can be given a cost, including CPU_PER_- SESSION, LOGICAL_READS_PER_SESSION, CONNECT_TIME, and PRIVATE_SGA. Set costs for a database using the SQL command ALTER RESOURCE COST:

ALTER RESOURCE COST
   CPU_PER_SESSION 1
   LOGICAL_READS_PER_SESSION 50;

A large cost means that the resource is very expensive, while a small cost means that the resource is not expensive. By default, each resource is initially given a cost of 0. A cost of 0 means that the resource should not be considered in the composite limit (that is, it does not cost anything to use this resource). No resource can be given a cost of NULL.

See Also: For additional information and recommendations on setting resource costs, see your operating system-specific Oracle documentation.

Dropping Profiles

To drop a profile, you must have the DROP PROFILE system privilege. You can drop a profile using either Server Manager/GUI, or the SQL command DROP PROFILE. To successfully drop a profile currently assigned to a user, use the CASCADE option.

The following statement drops the profile CLERK, even though it is assigned to a user:

DROP PROFILE clerk CASCADE;

Any user currently assigned to a profile that is dropped is automatically assigned to the DEFAULT profile. The DEFAULT profile cannot be dropped. Note that when a profile is dropped, the drop does not affect currently active sessions; only sessions created after a profile is dropped abide by any modified profile assignments.

Enabling and Disabling Resource Limits

A profile can be created, assigned to users, altered, and dropped at any time by any authorized database user, but the resource limits set for a profile are enforced only when you enable resource limitation for the associated database. Resource limitation enforcement can be enabled or disabled by two different methods, as described in the next two sections.

To alter the enforcement of resource limitation while the database remains open, you must have the ALTER SYSTEM system privilege.

Enabling and Disabling Resource Limits Before Startup

If a database can be temporarily shut down, resource limitation can be enabled or disabled by the RESOURCE_LIMIT initialization parameter in the database's parameter file. Valid values for the parameter are TRUE (enables enforcement) and FALSE; by default, this parameter's value is set to FALSE. Once the parameter file has been edited, the database instance must be restarted to take effect. Every time an instance is started, the new parameter value enables or disables the enforcement of resource limitation.

Enabling and Disabling Resource Limits While the Database is Open

If a database cannot be temporarily shut down or the resource limitation feature must be altered temporarily, you can enable or disable the enforcement of resource limitation using the SQL command ALTER SYSTEM. After an instance is started, an ALTER SYSTEM statement overrides the value set by the RESOURCE_LIMIT parameter. For example, the following statement enables the enforcement of resource limitation for a database:

ALTER SYSTEM
   SET RESOURCE_LIMIT = TRUE;

An ALTER SYSTEM statement does not permanently determine the enforcement of resource limitation. If the database is shut down and restarted, the enforcement of resource limits is determined by the value set for the RESOURCE_LIMIT parameter.

Listing Information About Database Users and Profiles

The data dictionary stores information about every user and profile, including the following:

The following data dictionary views may be of interest when you work with database users and profiles:

See Also: See the Oracle7 Server Reference for detailed information about each view.

Listing Information about Users and Profiles: Examples

The examples in this section assume a database in which the following statements have been executed:

CREATE PROFILE clerk LIMIT
   SESSIONS_PER_USER 1
   IDLE_TIME 30
   CONNECT_TIME 600;
CREATE USER jfee
   IDENTIFIED BY wildcat
   DEFAULT TABLESPACE users
   TEMPORARY TABLESPACE temp_ts
   QUOTA 500K ON users
   PROFILE clerk;
CREATE USER tsmith
   IDENTIFIED BY bedrock
   DEFAULT TABLESPACE users
   TEMPORARY TABLESPACE temp_ts
   QUOTA unlimited ON users;

Listing All Users and Associated Information

The following query lists all users defined in the database:

SELECT * FROM sys.dba_users;
USERNA USER_ID PASSWORD         DEFAUL TEMPOR  CREATED   PROFILE
------ -------- ---------------- ------ ------  --------- --------
SYS          % 522D06CDE017CF93 SYSTEM SYSTEM  31-JUL-90 PUBLIC...
SYSTEM       % 9B30B3EB7A7EE46A SYSTEM SYSTEM  31-JUL-90 PUBLIC...
JFEE         % DEE4F647381D62C4 USERS  TEMP_TS 12-SEP-90 CLERK
TSMITH       % 4791F162172E7834 USERS  TEMP_TS 12-SEP-90 PUBLIC...

All passwords are encrypted to preserve security.

Listing Users' Roles

The following query lists, for each user, the roles granted to that user, and indicates whether each role is granted with the ADMIN OPTION and is a default role:

SELECT * FROM sys.dba_role_privs where grantee = 'JFEE';
GRANTEE                    GRANTED_ROLE              ADM DEF
-------------------------- ------------------------- --- ---
JFEE                       CLERK                     YES YES
JFEE                       PAYROLL                   NO  NO
JFEE                       WEEKLY_ADMIN              NO  NO

Listing All Tablespace Quotas

The following query lists all tablespace quotas specifically assigned to each user:

SELECT * FROM sys.dba_ts_quotas;
TABLESPACE USERNAME        BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS
---------- ---------- ---------- ---------- ---------- ----------
SYSTEM     SYSTEM              0          0          0          0
SYSTEM     JFEE                0     512000          0        250
SYSTEM     TSMITH              0         -1          0         -1

When specific quotas are assigned, the exact number is indicated in the MAX_BYTES column. Unlimited quotas are indicated by "-1".

Listing All Profiles and Assigned Limits

The following query lists all profiles in the database and associated settings for each limit in each profile:

SELECT * FROM sys.dba_profiles
   ORDER BY profile;
PROFILE          RESOURCE_NAME                    LIMIT
---------------  -------------------------------- --------------
CLERK            COMPOSITE_LIMIT                  UNLIMITED
CLERK            SESSIONS_PER_USER                1
CLERK            CPU_PER_SESSION                  UNLIMITED
CLERK            CPU_PER_CALL                     UNLIMITED
CLERK            LOGICAL_READS_PER_SESSION        UNLIMITED
CLERK            LOGICAL_READS_PER_CALL           UNLIMITED
CLERK            IDLE_TIME                        30
CLERK            CONNECT_TIME                     600
CLERK            PRIVATE_SGA                      UNLIMITED
DEFAULT          COMPOSITE_LIMIT                  UNLIMITED
DEFAULT          SESSIONS_PER_USER                UNLIMITED
DEFAULT          CPU_PER_SESSION                  UNLIMITED
DEFAULT          CPU_PER_CALL                     UNLIMITED
DEFAULT          LOGICAL_READS_PER_SESSION        UNLIMITED
DEFAULT          LOGICAL_READS_PER_CALL           UNLIMITED
DEFAULT          IDLE_TIME                        UNLIMITED
DEFAULT          CONNECT_TIME                     UNLIMITED
DEFAULT          PRIVATE_SGA                      UNLIMITED

Viewing Memory Use Per User Session

The following query lists all current sessions, showing the Oracle user and current memory use per session:

SELECT username, value || 'bytes' "Current session memory"
   FROM v$session sess, v$sesstat stat, v$statname name
WHERE sess.sid = stat.sid
   AND stat.statistic# = name.statistic#
   AND name.name = 'session memory';

The amount of space indicated in "Current session memory" is allocated in the shared pool for each session connected through the multi-threaded server. You can limit the amount of memory allocated per user with the PRIVATE_SGA resource limit.

To see the maximum memory ever allocated to each session since the instance started, replace 'session memory' in the query above with 'max session memory'.




Go to previous file in sequence Go to next file in sequence
Prev Next
Oracle
Copyright © 1996 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index