Oracle8i Administrator's Guide
Release 2 (8.1.6)

Part Number A76956-01





Go to previous page Go to next page

Managing Users and Resources

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

For guidelines on establishing security policies for users and profiles, see Chapter 21, "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 23, "Managing User Privileges and Roles".

Session and User Licensing

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. You do this by setting the following initialization parameters in your initialization parameter file:

These initialization parameters are discussed in succeeding sections.

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.


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 initialization parameter file, or set the value of all three to 0.  

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

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 as described in Chapter 2, "Creating an Oracle Database". You can also change the maximum number of concurrent sessions while the database is running.

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. For information about terminating sessions, see "Terminating Sessions".

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"; it may help you determine if your Oracle license needs to be reviewed. For information about Oracle licensing limit upgrades, see "Viewing Licensing Limits and Current Values".

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.


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 Oracle8i Parallel Server Administration, Deployment, and Performance. 

Setting the Maximum Number of Sessions

To set the maximum number of concurrent sessions for an instance, set the LICENSE_MAX_SESSIONS initialization parameter. This example sets the maximum number of concurrent sessions to 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 LICENSE_SESSIONS_WARNING initialization parameter 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 statement with the appropriate option. The following statement changes the maximum limit to 100 concurrent sessions:


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


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


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

See Also:

For more information about using the ALTER SYSTEM statement to change the value of initialization parameters see Oracle8i SQL Reference. 

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.

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:


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.

See Also:

For instances running with the Parallel Server, all instances connected to the same database should have the same named user limit. See Oracle8i Parallel Server Administration, Deployment, and Performance. 

Changing User Limits

To change the maximum named users limit, use the ALTER SYSTEM statement with the LICENSE_MAX_USERS option. The following statement changes the maximum number of defined users to 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.


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,
   FROM v$license;

-------  ----------  -----------  --------  ----------
    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;


See Also:

For a complete description of the V$LICENSE view, see the Oracle8i Reference. 

User Authentication

Depending on how you want user identities to be authenticated, there are several ways to define users before they are allowed to create a database session:

  1. You can define users such that the database performs both identification and authentication of users. This is called database authentication.

  2. You can define users such that authentication is performed by the operating system or network service. This is called external authentication.

  3. You can define users such that they are authenticated by SSL (Secure Sockets Layer). These users are called global users. For global users, an enterprise directory can be used to authorize their access to the database through global roles.

  4. You can specify users who are allowed to connect through a proxy server. This is called multi-tier authentication and authorization.

These types of authentication are discussed in the following sections:

Other attributes to consider when creating users for your database are discussed later in "Oracle Users".

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. Oracle password management is discussed in Chapter 21, "Establishing Security Policies".

Creating a User Who is Authenticated by the Database

The following statement creates a user who is identified and authenticated by Oracle. User SCOTT must specify the password TIGER whenever connecting to Oracle.


See Also:

For more information about valid passwords, and how to specify the IDENTIFIED BY clause, in the CREATE USER and ALTER USER statements, see Oracle8i SQL Reference. 

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

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 initialization parameter OS_AUTHENT_PREFIX, and use this prefix in Oracle usernames. The OS_AUTHENT_PREFIX 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:


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.

Creating a User Who is Authenticated Externally

The following statement creates a user who is identified by Oracle and authenticated by the operating system or a network service. This example assumes that OS_AUTHENT_PREFIX = "".


Using CREATE USER...IDENTIFIED EXTERNALLY, you create database accounts that must be authenticated via the operating system or network service. Oracle relies on this external login authentication to ensure that a specific operating system user has access to a specific database user.

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.

For more information on external authentication, see Oracle Advanced Security Administrator's Guide and Oracle8i Distributed Database Systems. 

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

Generally, user authentication via the host operating system offers the following benefits:

Network Authentication

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

Advantages of External Authentication

Following are advantages of external authentication:

Global Authentication and Authorization

Oracle Advanced Security allows you to centralize management of user-related information, including authorizations, in an LDAP-based directory service. Users can be identified in the database as global users, meaning that they are authenticated by SSL and that the management of these users is done outside of the database by the centralized directory service. Global roles are defined in a database and are known only to that database, but authorizations for such roles is done by the directory service.


You can also have users authenticated by SSL, whose authorizations are not managed in a directory; that is, they have local database roles only. See the Oracle Advanced Security Administrator's Guide for details. 

This centralized management enables the creation of enterprise users and enterprise roles. Enterprise users are defined and managed in the directory. They have unique identities across the enterprise, and can be assigned enterprise roles that determine their access privileges across multiple databases. An enterprise role consists of one or more global roles, and might be thought of as a container for global roles.

Creating a User Who is Authorized by a Directory Service

You have a couple of options as to how you specify users who are authorized by a directory service.

Creating a Global User

The following statement illustrates the creation of a global user, who is authenticated by SSL and authorized by the enterprise directory service:

    IDENTIFIED GLOBALLY AS 'CN=scott,OU=division1,O=oracle,C=US'

The string provided in the AS clause provides an identifier meaningful to the enterprise directory (DN, or distinquished name).

In this case, SCOTT is truly a global user. But, the downside here, is that user SCOTT must then be created in every database that he must access, plus the directory.

Creating a Schema-Independent User

Creating schema-independent users allows multiple enterprise users to access a shared schema in the database. A schema-independent user is:

The process of creating a schema-independent user is as follows:

  1. Create a shared schema in the database as follows.

  2. In the directory, you now create multiple enterprise users, and a mapping object.

    The mapping object tells the database how you want to map users' DNs to the shared schema. You can either do a full DN mapping (one directory entry for each unique DN), or you can map, for example, every user containing the following DN components to the APPSCHEMA:


    See the Oracle Internet Directory Administrator's Guide for an explanation of these mappings.

Most users do not need their own schemas, and implementing schema-independent users divorces users from databases. You create multiple users who share the same schema in a database, and as enterprise users, they can access shared schemas in other databases as well.

Advantages of Global Authentication and Global Authorization

Some of the advantages of global user authentication and authorization are the following:

Multi-Tier Authentication and Authorization

It is possible to design an application server (middle tier) to proxy clients in a secure fashion. It is not necessary for the application server to know a clients's password in order to connect on behalf of the client. As long as the client authenticates itself with the middle tier and the middle tier authenticates itself with the database, and the middle tier is authorized to act on behalf of the client by the administrator, client identities can be maintained all the way into the database without compromising the security of the client.

The application server must be designed for this functionality. OCI (Oracle Call Interface) provides this capability, which is discussed in Oracle Call Interface Programmer's Guide.

To authorize an application server to proxy a client you use the GRANT CONNECT THROUGH clause of the ALTER USER statement. You can also specify roles that the application server is permitted to activate when connecting as the client. The following example authorizes the application server APPSERVE to connect as user BILL, and allows APPSERVE to activate all roles associated with BILL, except PAYROLL.


You use the REVOKE CONNECT THROUGH clause to disallow a proxy connection.

The PROXY_USERS view can be queried to see which users are currently authorized to connect through a proxy.

Operations done on behalf of a client by an application server can be audited. See "Auditing in a Multi-Tier Environment".

See Also:

For more information on multi-tier authentication and authorization, see Oracle8i Concepts and Oracle8i Application Developer's Guide - Fundamentals.

For a description and syntax of the proxy clause of ALTER USER, see Oracle8i 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 tablespaces in the database, even if the creator does not have a quota on a specified tablespace. Because it is a powerful privilege, a security administrator is normally the only user who has the CREATE USER system privilege.

You create a user with the SQL statement CREATE USER. The following example creates a user and specifies that user's password, default tablespace, temporary tablespace where temporary segments are created, tablespace quota, and profile.

    QUOTA 100M ON test_ts
    QUOTA 500K ON data_ts
    PROFILE clerk;
GRANT connect TO jward;

A newly created user cannot connect to the database until granted the CREATE SESSION system privilege. Usually, a newly created user is granted a role, similar to the predefined roll CONNECT, as shown in this example, that specifies the CREATE SESSION and other basic privileges required to access a database. See "Granting System Privileges and Roles" for specific information.

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 a unique name.

Setting a User's Authentication

In the previous CREATE USER statement, the new user is to be authenticated using the database. In this case, the connecting user must supply the correct password to the database to connect successfully.

The methods of specifying the type of user authentication were discussed earlier in "User Authentication".

Assigning a Default Tablespace

Each user should have 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 should be assigned 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. You do not set a quota for temporary tablespaces. These temporary segments are created by the system when doing sorts or joins and are owned by SYS, which has resource privileges in all tablespaces.

If a user's temporary tablespace is not explicitly set, the default is the SYSTEM tablespace. Setting each user's temporary tablespace reduces file contention among temporary segments and other types of segments, as the temporary tablespace is used exclusively for temporary 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, but quotas are not necessary for a temporary 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.


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:



Specifying a Profile

You also specify a profile when you create a user. A profile is a set of limits on database resources. If no profile is specified, the user is assigned a default profile. For information on profiles see "Managing Resources with Profiles" and "Password Management Policy".

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 statement to change the user's default roles. See "Specifying Default Roles".


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 the SQL statement 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
    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

Most non-DBA users can still change their own passwords with the ALTER USER statement, as follows:

   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 or a form of external authentication. Usually, only DBAs should have this privilege.

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. For more information on changing users' default roles, see Chapter 23, "Managing User Privileges and Roles".

Dropping Users

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


If a user's schema and associated objects must remain but the user must be denied 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 the SQL statement ALTER SYSTEM with the KILL SESSION clause. For more information about terminating sessions, see "Terminating Sessions".

You can drop a user from a database using the DROP USER statement. 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.

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 user JONES and all associated objects and foreign keys that depend on the tables owned by JONES.


Managing Resources with Profiles

A profile is a named set of resource limits. A user's profile limits database usage and instance resources as defined in the 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:

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 initialization 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 statement 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:



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.

Creating Profiles

To create a profile, you must have the CREATE PROFILE system privilege. You can create profiles using the SQL statement CREATE PROFILE. At the same time, you can explicitly set particular resource limits.

The following statement creates the profile CLERK:

    CPU_PER_SESSION unlimited
    CPU_PER_CALL 6000
    IDLE_TIME 30

All unspecified resource limits for a new profile take the limit set by a 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 statement:


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 SQL statements CREATE USER or ALTER USER. 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 the SQL statement 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:

    CPU_PER_CALL default

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 resource limits in a profile. You can set a profile's composite limit using the COMPOSITE_LIMIT clause of the SQL statements CREATE PROFILE or ALTER PROFILE. A composite limit is set via service units, which are weighted amounts of each resource.

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

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

See Also:

For information on how to calculate the composite limit, see the Oracle8i SQL Reference. 

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: CPU_PER_ SESSION, LOGICAL_READS_PER_SESSION, CONNECT_TIME, and PRIVATE_SGA. Set costs for a database using the SQL statement ALTER RESOURCE COST:


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 and the Oracle8i SQL Reference

Dropping Profiles

To drop a profile, you must have the DROP PROFILE system privilege. You can drop a profile using the SQL statement 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:


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.

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:

View  Description 


Lists all users of the database visible to the current user. This view does not describe the users. See the related views. 


Describes all users of the database. 


Same columns as DBA_USERS, but only describes the current user.  


Describes tablespace quotas for all users. 


Same columns as DBA_TA_QUOTAS, but contains information about tablespace quotas for the current user only. 


Describes the password profile parameters that are assigned to the user. 


Displays the resource limits for the current user. 


Displays all profiles and their limits. 


Lists the cost for each resource. 


Lists session information for each current session. Includes user name. 


Lists user session statistics. 


Displays decoded statistic names for the statistics shown in the V$SESSTAT view.  


This view describes users who can assume the identity of other users. 

See Also:

See the Oracle8i 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:

    IDLE_TIME 30

    IDENTIFIED BY wildcat
    QUOTA 500K ON users
    PROFILE clerk;

CREATE USER dcranney
    IDENTIFIED BY bedrock
    QUOTA unlimited ON users;

CREATE USER userscott
     IDENTIFIED BY scott1

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; 
--------------- --------------- --------------- 
SYS             DEFAULT         OPEN            
SYSTEM          DEFAULT         OPEN            
USERSCOTT       DEFAULT         OPEN            
JFEE            CLERK           OPEN            
DCRANNEY        DEFAULT         OPEN            

All passwords are encrypted to preserve security. If a user queries the PASSWORD column, that user will not be able to determine another user's password.

Listing All Tablespace Quotas

The following query lists all tablespace quotas specifically assigned to each 
SELECT * FROM sys.dba_ts_quotas;
----------    ---------  --------   ----------   -------   ----------
USERS         JFEE              0       512000         0          250
USERS         DCRANNEY          0           -1         0           -1

When specific quotas are assigned, the exact number is indicated in the MAX_BYTES column. Note that this number will always be a multiple of the database block size, so if you specify a tablespace quota that is not a multiple of the database block size, it will be rounded up accordingly. 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             
-----------------   ---------------            ---------- --------------
CLERK               COMPOSITE_LIMIT            KERNEL     DEFAULT
CLERK               PRIVATE_SGA                KERNEL     DEFAULT
CLERK               CONNECT_TIME               KERNEL     600    
CLERK               IDLE_TIME                  KERNEL     30     
CLERK               CPU_PER_CALL               KERNEL     DEFAULT
CLERK               CPU_PER_SESSION            KERNEL     DEFAULT
CLERK               SESSIONS_PER_USER          KERNEL     1      
DEFAULT             PRIVATE_SGA                KERNEL     UNLIMITED
DEFAULT             CPU_PER_CALL               KERNEL     UNLIMITED
DEFAULT             CONNECT_TIME               KERNEL     UNLIMITED
DEFAULT             IDLE_TIME                  KERNEL     UNLIMITED
32 rows selected. 

Viewing Memory Use Per User Session

The following query lists all current sessions, showing the Oracle user and current UGA (user global area) memory use per session:

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

USERNAME                       Current UGA memory
------------------------------ ---------------------------------------------
USERSCOTT                      42244bytes
SYS                            98196bytes
SYSTEM                         30648bytes

11 rows selected.

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


The following are examples that further demonstrate the use of SQL statements and views discussed in this chapter.

  1. The following statement creates the profile PROF:

       PASSWORD_VERIFY_FUNCTION verify_function
  2. The following statement attempts to creates a user with the same password as the username.

    CREATE USER userscott IDENTIFIED BY userscott PROFILE prof; 
    ORA-28003: Password verification for the specified password failed 
    ORA-20001: Password same as user
  3. The following statement creates user userscott identified by SCOTT% with profile PROF;

    CREATE USER userscott IDENTIFIED BY "scott%" PROFILE prof; 
  4. The following statement attempts to change the user's password to SCOTT% again:

    ALTER USER userscott IDENTIFIED BY "scott%"; 
    ORA-28007: The password cannot be reused 
  5. The following statement locks the user account:

  6. The following statement checks the user account status:

    SELECT username, user_id, account_status, lock_date  
       FROM dba_users 
       WHERE username='USERSCOTT';
    ------------------- ------- ------------------------ ---------
    USERSCOTT                38 LOCKED                   11-OCT-99
  7. The following statement unlocks the user:

  8. The following statement expires the password:


Go to previous page Go to next page
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.