Oracle8 Administrator's Guide
Release 8.0

A58397-01

Library

Product

Contents

Index

Prev Next

20
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 19, Establishing Security Policies.

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 21, Managing User Privileges and Roles.

This chapter contains several references to Oracle Enterprise Manager. For more information about performing specific tasks using Enterprise Manager/GUI or Server Manager/LineMode, see the Oracle Server Manager User's Guide or Oracle Enterprise Manager Administrator'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 Chapter 2, "Creating an Oracle Database".

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 Oracle8 Parallel Server Concepts and Administration.

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 Oracle8 Parallel Server Concepts and Administration 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 or until you 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          82        50

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 how you want user identities to be authenticated, there are three ways to define users before they are allowed to create a database session:

  1. You can configure Oracle so that it performs both identification and authentication of users. This is called database authentication.
  2. You can configure Oracle so that it performs only the identification of users (leaving authentication up to the operating system or network service). This is called external authentication.
  3. You can configure Oracle so that it performs only the identification of users (leaving authentication up to the Oracle Security Service). This is called enterprise authentication.

Database Authentication

If you choose database authentication for a user, administration of the user account, password, and authentication of that user is performed entirely by Oracle. 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.

To enhance security when using database authentication, Oracle recommends the use of password management, including account locking, password aging and expiration, password history, and password complexity verification.

The following statement creates a user who is identified and authenticated by Oracle:

CREATE USER scott IDENTIFIED BY tiger;

See Also: For more information about the CREATE USER and ALTER USER commands, see Oracle8 SQL Reference.

For more information about valid passwords, see Oracle8 SQL Reference.

For more information about Oracle password management, see Chapter 19, "Establishing Security Policies".

Advantages of Database Authentication

Following are advantages of database authentication:

External Authentication

When you choose external authentication for a user, the user account is maintained by Oracle, but password administration and user authentication is performed by an external service. This external service can be the operating system or a network service, such as the Oracle Advanced Networking Option (ANO).

With external authentication, your database relies on the underlying operating system or network authentication service to restrict access to database accounts. A database password is not used for this type of login. If your operating system or network service 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.

The following command creates 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, you can create database accounts that must be authenticated via the operating system or network service and cannot be authenticated using a password.

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

By default, Oracle only allows operating system authenticated logins over secure connections. Therefore, if you want the operating system to authenticate a user, by default that user cannot connect to the database over Net8. This means the user cannot connect using a multi-threaded server, since this connection uses Net8. This default restriction prevents a remote user from impersonating another operating system user over a network connection.

If you are not concerned about remote users impersonating another operating system user over a network connection, and you want to use operating system user authentication with network clients, set the parameter REMOTE_OS_AUTHENT (default is FALSE) to TRUE in the database's parameter file. Setting the initialization parameter REMOTE_OS_AUTHENT to TRUE allows the RDBMS to accept the client operating system username received over a non-secure connection and use it for account access. The change will take effect the next time you start the instance and mount the database.

Network Authentication

Network authentication is performed via the Oracle Advanced Networking Option (ANO), which may be configured to use a third party service such as Kerberos. If you are using ANO as the only external authentication service, the setting of the parameter REMOTE_OS_AUTHENT is irrelevant, since ANO only allows secure connections.

See Also: For information about network authentication, see Oracle8 Distributed Database Systems, and Oracle Security Server Guide.

Advantages of External Authentication

Following are advantages of external authentication:

Enterprise Authentication

If you choose enterprise authentication for a user, the user account is maintained by Oracle, but password administration and user authentication is performed by the Oracle Security Service (OSS). This authentication service can be shared among multiple Oracle database servers and allows user's authentication and authorization information to be managed centrally.

Use the following command to create a user (known as a global user) who is identified by Oracle and authenticated by the Oracle Security Service:

CREATE USER scott IDENTIFIED GLOBALLY as '<external name>';

See Also: For information about the contents of the <EXTERNAL NAME> string, see Oracle8 Distributed Database Systems, and Oracle Security Server Guide.

Advantages of Enterprise Authentication

Following are advantages of enterprise authentication:

See Also: For information about enterprise authentication, see Oracle8 Distributed Database Systems, and Oracle Security Server Guide.

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 Enterprise 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.

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 "Granting System Privileges and Roles".

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 Oracle8 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 Access

You 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 Privilege

To 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 (other than a user 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 Enterprise 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 Authentication Mechanism

While most non-DBA users do not use Enterprise 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.

Users must have the ALTER USER privilege to switch between Oracle database authentication, external authentication, and enterprise authentication; 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 Oracle8 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.

See Also: For more information on changing users' default roles, see Chapter 21, "Managing User Privileges and Roles".

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 Enterprise 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 Enterprise 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.

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 Enterprise 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 Enterprise 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 Enterprise Manager/GUI, or the SQL commands CREATE USER or ALTER USER.

See Also: For more information about assigning a profile to a user, see "Creating Users" and "Altering Users".

Altering Profiles

You can alter the resource limit settings of any profile using either the Alter Profile property sheet of Enterprise 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 Enterprise 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 Enterprise 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;


Note:

This does not apply to password resources.

 

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 Oracle8 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
    DENTIFIED BY wildcat
    DEFAULT TABLESPACE users
    TEMPORARY TABLESPACE temp_ts
    QUOTA 500K ON users
    PROFILE clerk;

CREATE USER dcranney
    IDENTIFIED BY bedrock
    DEFAULT TABLESPACE users
    TEMPORARY TABLESPACE temp_ts
    QUOTA unlimited ON users;

CREATE USER userscott
     IDENTIFIED BY "scott1"
     PASSWORD_LIFETIME 60
     PASSWORD_GRACE_TIME 10;

Listing All Users and Associated Information

The following query lists users and their associated information as defined in the database:

SELECT username, profile, account_status from dba_users; 
USERNAME        PROFILE         ACCOUNT_STATUS  
--------------- --------------- ----------------  
SYS             DEFAULT         OPEN            
SYSTEM          DEFAULT         OPEN            
BLAKE           DEFAULT         OPEN            
SCOTT           DEFAULT         OPEN            
ADAMS           DEFAULT         OPEN            
JONES           DEFAULT         OPEN            
CLARK           DEFAULT         OPEN            
U               DEFAULT         LOCKED          
USERSCOTT       PROF            EXPIRED         

All passwords are encrypted to preserve security.

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        DCRANNEY      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          RESOURCE    LIMIT             
        
-------------------------   ---------------        ----------  --------------
DEFAULT                     COMPOSITE_LIMIT        KERNEL     UNLIMITED
DEFAULT                     SESSIONS_PER_USER      KERNEL             1
DEFAULT                     CPU_PER_CALL           KERNEL     UNLIMITED
DEFAULT                     LOGICAL_READS_PER_CALL KERNEL     UNLIMITED
DEFAULT                     CONNECT_TIME           KERNEL            30
DEFAULT                     IDLE_TIME              KERNEL           600
DEFAULT                     LOGICAL_READS_PER_SESSION KERNEL  UNLIMITED
DEFAULT                     CPU_PER_SESSION        KERNEL     UNLIMITED
DEFAULT                     PRIVATE_SGA            KERNEL     UNLIMITED
DEFAULT                     FAILED_LOGIN_ATTEMPTS  PASSWORD   UNLIMITED
DEFAULT                     PASSWORD_LIFE_TIME     PASSWORD   UNLIMITED
DEFAULT                     PASSWORD_REUSE_MAX     PASSWORD   UNLIMITED
DEFAULT                     PASSWORD_LOCK_TIME     PASSWORD   UNLIMITED
DEFAULT                     PASSWORD_GRACE_TIME    PASSWORD   UNLIMITED
DEFAULT                     PASSWORD_VERIFY_FUNCTION PASSWORD UNLIMITED
DEFAULT                     PASSWORD_REUSE_TIME    PASSWORD   UNLIMITED
PROF                        COMPOSITE_LIMIT        KERNEL       DEFAULT
PROF                        PRIVATE_SGA            KERNEL       DEFAULT
PROF                        CONNECT_TIME           KERNEL       DEFAULT
PROF                        IDLE_TIME              KERNEL       DEFAULT
PROF                        LOGICAL_READS_PER_CALL KERNEL       DEFAULT
PROF                        LOGICAL_READS_PER_SESSION KERNEL    DEFAULT
PROF                        SESSIONS_PER_USER      KERNEL       DEFAULT
PROF                        CPU_PER_CALL           KERNEL       DEFAULT
PROF                        CPU_PER_SESSION        KERNEL       DEFAULT
PROF                        FAILED_LOGIN_ATTEMPTS  PASSWORD           5
PROF                        PASSWORD_LIFE_TIME     PASSWORD          60
PROF                        PASSWORD_REUSE_MAX     PASSWORD   UNLIMITED
PROF                        PASSWORD_LOCK_TIME     PASSWORD           1
PROF                        PASSWORD_GRACE_TIME    PASSWORD          10
PROF                        PASSWORD_VERIFY_FUNCTION PASSWORD UNLIMITED
PROF                        PASSWORD_REUSE_TIME    PASSWORD          60
32 rows selected. 

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'.

Examples

This section contains examples that use functions described throughout this chapter.

  1. The following statement creates the profile prof:
      CREATE PROFILE prof limit 
        FAILED_LOGIN_ATTEMPTS 5 
        PASSWORD_LIFE_TIME 60 
        PASSWORD_REUSE_MAX 60 
        PASSWORD_REUSE_MAX UNLIMITED 
        PASSWORD_VERIFY_FUNCTION verify_function
        PASSWORD_LOCK_TIME 1 
        PASSWORD_GRACE_TIME 10; 


  • The following statement creates a user with the same password as the username with profile prof; CREATE USER userscott IDENTIFIED BY userscott PROFILE prof; ORA-28003: Password verification for the specified password failed ORA-20001: Password same as user
  • The following statement creates user userscott identified by "scott1%" with profile prof; CREATE USER userscott IDENTIFIED BY "scott%" PROFILE prof;
  • The following statement changes the user's password to "scott%" again and returns an error: ALTER USER userscott IDENTIFIED BY "scott%"; ORA-28007: The password cannot be reused
  • The following statement locks the user account: ALTER USER userscott ACCOUNT LOCK;
  • The following statement checks the user account status: SELECT username, user_id, account_status, lock_date FROM dba_users WHERE username='USERSCOTT';
  • The following statement expires the password: ALTER USER userscott PASSWORD EXPIRE;
  • The following statement checks the user account status: SELECT username, user_id, account_status, expiry_date FROM dba_users WHERE username='USERSCOTT';
  • The following statement unlocks the user: ALTER USER userscott ACCOUNT UNLOCK;
  • The following statement checks the account status: SELECT username, user_id, account_status, expiry_date
    FROM dba_users WHERE username='USERSCOTT';



  • Prev

    Next
    Oracle
    Copyright © 1997 Oracle Corporation.

    All Rights Reserved.

    Library

    Product

    Contents

    Index