2 Managing Security for Oracle Database Users

You can manage the security for Oracle Database users in a variety of ways, such as enforcing restrictions on the way that passwords are created, creating user profiles, and using user resource limits to further secure user accounts.

Topics:

About User Security

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 user name defined in the database. Oracle Database enables you to set up security for your users in a variety of ways.

When you create user accounts, you can specify limits to the user account. You can also set limits on the amount of various system resources available to each user as part of the security domain of that user. Oracle Database provides a set of database views that you can query to find information such as resource and session information. This chapter also describes profiles. A profile is collection of attributes that apply to a user. It enables a single point of reference for any of multiple users that share those exact attributes.

Another way to manage user security is to assign users privileges and roles. Configuring Privilege and Role Authorization, provides detailed information.

Creating User Accounts

When you create a user account, in addition to the user name and password, you can specify a default tablespace, a tablespace quota, a temporary tablespace, a profile, and a default role for the user.

Topics:

For guidelines about creating and managing user accounts and passwords, see the following sections:

About Common Users and Local Users

In a multitenant environment, you can create users called common users who have access to the entire CDB, or you can create local users who are specific to a PDB.

Topics:

See Also:

About Common Users

In a multitenant environment, a common user is a database user whose identity and password are known in the root and in every existing and future pluggable database (PDB).

A common user can connect to the root and perform operations. If the common user has the appropriate privileges, then this user can perform operations in PDBs as well, such as granting privileges to local users.

A common user can perform administrative tasks specific to the root or PDBs, such as plugging and unplugging PDBs, changing their state, or specifying the temporary tablespace for the multitenant container database (CDB). Only common users who have the appropriate privileges can navigate between containers that belong to a CDB.

For example, common users can perform the following operations across multiple PDBs:

  • Granting privileges to common users or common roles

  • Running an ALTER DATABASE statement that specifies the recovery clauses that apply to the entire CDB

  • Running an ALTER PLUGGABLE DATABASE statement to change the state of a given PDB while connected to the root. A local user connected to a PDB can also change its state, given appropriate privileges.

All Oracle-supplied administrative user accounts, such as SYS and SYSTEM, are common users and can navigate across the CDB. Common users can have different privileges in different PDBs. For example, the common user SYSTEM can switch between PDBs and use the privileges that are granted to SYSTEM in the current PDB. However, if one of the PDBs is Oracle Database Vault-enabled, then the Database Vault restrictions, such as SYSTEM not being allowed to create user accounts, apply to the common SYSTEM user when he is connected to that PDB. (Oracle does not recommend that you change the privileges of the Oracle-supplied common users. You can, however, locally grant user-created common users different privileges in each container.)

See Also:

How Plugging in PDBs Affects Common Users

Plugging a non-CDB into a CDB as a PDB affects the Oracle-supplied administrative accounts, the passwords of these common user accounts, and privileges of all accounts.

The following actions take place:

  • The Oracle-supplied administrative accounts are merged with the existing common user accounts.

  • The passwords of the existing common user accounts take precedence over the passwords for the accounts from the non-CDB.

  • If you had modified the privileges of a user account in its original non-CDB, then these privileges are saved, but they only apply to the PDB that was created when the PDB was plugged into the CDB, as locally granted privileges. For example, suppose you had granted the user SYSTEM a role called hr_mgr in the non-CDB db1. After the db1 database has been added to a CDB, then SYSTEM can only use the hr_mgr role in the db1 PDB, and not in any other PDBs.

If you plug a PDB that contains a common user into a CDB, then the following actions take place:

  • The common user accounts in this PDB lose commonly granted privileges that they may have had, including the SET CONTAINER privilege.

  • If the target CDB has a common user with the same name as a common user in a newly plugged-in PDB, then the new common user is merged with the target CDB common user. The password of the target CDB common user takes precedence. Otherwise, a common user in a newly plugged in PDB becomes a locked account. In this case, you can do one of the following:

    • Leave the user account locked and use the objects of its schema.

    • Use Oracle Data Pump to copy these objects to another schema, and then drop the locked user account.

    • Close the PDB, connect to the root, and then create a common user with the same name as the locked account. When you re-open the PDB, Oracle Database resolves the differences in the roles and privileges that were commonly granted to the locked user. Afterward, you can unlock this user account in the PDB. Privileges and roles that were locally granted to the user will remain unchanged.

About Local Users

In a multitenant environment, a local user is a database user that exists only in a single PDB. Local users can have administrative privileges, but these privileges apply only in the PDB in which the local user account was created.

A local user account has the following characteristics, which distinguishes it from common user accounts:

  • Local user accounts cannot create common user accounts or commonly grant them privileges. A common user with the appropriate privileges can create and modify common or local user accounts and grant and revoke privileges, commonly or locally. A local user can create and modify local user accounts or locally grant privileges to common or local users in a given PDB.

  • You can grant local user accounts common roles. However, the privileges associated with the common role only apply to the local user's PDB.

  • The local user account must be unique only within its PDB.

  • With the appropriate privileges, a local user can access objects in a common user's schema. For example, a local user can access a table within the schema of a common user if the common user has granted the local user privileges to access it.

  • You can editions-enable a local user account but not a common user account.

See Also:

Who Can Create User Accounts?

Any user who has been granted the CREATE USER system privilege can create user accounts, including user accounts that will be used as proxy users.

Because the CREATE USER system privilege is a powerful privilege, a database administrator or security administrator is usually the only user who has this system privilege.

If you want to grant the new user account to be able to grant other users privileges, then you should alter this user's privileges to use the ADMIN OPTION so that he or she can grant the privilege to their users. For example, if user lbrown, to whom you have granted the CREATE USER system privilege, also wants to grant her users the CREATE SESSION privilege, then run the following statement:

GRANT CREATE SESSION TO lbrown WITH ADMIN OPTION;

As with all user accounts to whom you grant privileges, grant these privileges to trusted users only.

In a multitenant environment, you must have the commonly granted CREATE USER system privilege to create common user accounts. To create local user accounts, you must have a commonly granted CREATE USER privilege or a locally granted CREATE USER privilege in the PDB in which the local user account will be created.

Note:

As a security administrator, you should create your own roles and assign only those privileges that are needed. For example, many users formerly granted the CONNECT privilege did not need the additional privileges CONNECT used to provide. Instead, only CREATE SESSION was actually needed, and in fact, that is the only privilege CONNECT presently retains.

Creating organization-specific roles gives an organization detailed control of the privileges it assigns, and protects it in case Oracle Database changes the roles that it defines in future releases. Configuring Privilege and Role Authorization, discusses how to create and manage roles.

Creating a New User Account and Granting the User the Minimum Database Privileges

When you create a new user account, typically, you should also enable this user to access the database.

  1. Use the CREATE USER statement to create a new user account.

    For example:

    CREATE USER jward 
     IDENTIFIED BY password 
     DEFAULT TABLESPACE example 
     QUOTA 10M ON example 
     TEMPORARY TABLESPACE temp
     QUOTA 5M ON system 
     PASSWORD EXPIRE;
    

    Replace password with a password that is secure. See "Minimum Requirements for Passwords" for more information.

    This example creates a local user account and specifies the user password, default tablespace, temporary tablespace where temporary segments are created, tablespace quotas, and profile.

  2. At minimum, grant the user the CREATE SESSION privilege so that the user can access the database instance.
    GRANT CREATE SESSION TO jward;
    

    A newly created user cannot connect to the database until he or she has the CREATE SESSION privilege. If the user must access Oracle Enterprise Manager, then you should also grant the user the SELECT ANY DICTIONARY privilege.

Restrictions on Creating the User Name for a New Account

When you specify a name for a user account, you must be aware of restrictions such as whether the name is unique and naming conventions.

Topics:

Uniqueness of User Names

Each user has an associated schema. Within a schema, each schema object must have a unique name.

Oracle Database will prevent you from creating a user name if it is already exists. You can check existing names by querying the USERNAME column of the DBA_USERS data dictionary view.

User Names in a Multitenant Environment

Within each PDB, a user name must be unique with respect to other user names and roles.

Note the following restrictions:

  • A user and a role cannot have the same name.

  • The user name cannot start with c## (or C##) unless this user is a common user account.

Case Sensitivity for User Names

The CREATE USER statement enables you to specify a user name. How you specify the user name enables you to control the case sensitivity in which the user name is stored in the database.

For example:

CREATE USER jward
 IDENTIFIED BY password
 DEFAULT TABLESPACE data_ts
 QUOTA 100M ON temp_ts
 QUOTA 500K ON data_ts
 TEMPORARY TABLESPACE temp_ts
 PROFILE clerk
 CONTAINER = CURRENT;

User jward is stored in the database in upper-case letters. For example:

SELECT USERNAME FROM ALL_USERS;

USERNAME
---------
JWARD
...

However, if you enclose the user name in double quotation marks, then the name is stored using the case sensitivity that you used for the name. For example:

CREATE USER "jward" IDENTIFIED BY password;

So, when you query the ALL_USERS data dictionary view, you will find that the user account is stored using the case that you used to create it.

SELECT USERNAME FROM ALL_USERS;

USERNAME
---------
jward
...

User JWARD and user jward are both stored in the database as separate user accounts. Later on, if you must modify or drop the user that you had created using double quotation marks, then you must enclose the user name in double quotation marks.

For example:

DROP USER "jward";

Assignment of User Passwords

To assign the user a password, you can use the IDENTIFIED BY clause of the CREATE USER statement. Ensure that you create a secure password.

In the example in "Creating a New User Account and Granting the User the Minimum Database Privileges", the new local user is authenticated using the database. In this case, the connecting user must supply the correct password to the database to connect successfully. To specify a password for the user, use the IDENTIFIED BY clause in the CREATE USER statement.

CREATE USER jward
 IDENTIFIED BY password
 DEFAULT TABLESPACE data_ts
 QUOTA 100M ON temp_ts
 QUOTA 500K ON data_ts
 TEMPORARY TABLESPACE temp_ts
 PROFILE clerk
 CONTAINER = CURRENT;

See Also:

Default Tablespace for the User

A default tablespace stores objects that users create. Tablespaces enable you to separate user data from system data, such as the data that is stored in the SYSTEM tablespace. You use the CREATE USER or ALTER USER statement to assign a default tablespace to a user.

Topics:

About Assigning a Default Tablespace for a User

Each user should have a default tablespace. When a schema object is created in the user's schema and the DDL statement does not specify a tablespace to contain the object, Oracle Database stores the object in the default user's tablespace.

The default setting for the default tablespaces of all users is the SYSTEM tablespace. If a user does not create objects, and has no privileges to do so, then this default setting is fine. However, if a user is likely to create any type of object, then you should specifically assign the user a default tablespace, such as the USERS tablespace. Using a tablespace other than SYSTEM reduces contention between data dictionary objects and user objects for the same data files. In general, do not store user data in the SYSTEM tablespace.

You can use the CREATE TABLESPACE SQL statement to create a permanent default tablespace other than SYSTEM at the time of database creation, to be used as the database default for permanent objects. By separating the user data from the system data, you reduce the likelihood of problems with the SYSTEM tablespace, which can in some circumstances cause the entire database to become nonfunctional. This default permanent tablespace is not used by system users, that is, SYS, SYSTEM, and OUTLN, whose default permanent tablespace is SYSTEM. A tablespace designated as the default permanent tablespace cannot be dropped. To accomplish this goal, you must first designate another tablespace as the default permanent tablespace. You can use the ALTER TABLESPACE SQL statement to alter the default permanent tablespace to another tablespace. Be aware that this will affect all users or objects created after the ALTER DDL statement commits.

You can also set a user default tablespace during user creation, and change it later with the ALTER USER statement. Changing the user default tablespace affects only objects created after the setting is changed.

When you specify the default tablespace for a user, also specify a quota on that tablespace.

DEFAULT TABLESPACE Clause for Assigning a Default Tablespace

To assign a default tablespace to a user, you can include the DEFAULT TABLESPACE clause in the CREATE USER statement.

In the following CREATE USER statement, the default tablespace for local user jward is data_ts:

CREATE USER jward
 IDENTIFIED BY password
 DEFAULT TABLESPACE data_ts
 QUOTA 100M ON temp_ts
 QUOTA 500K ON data_ts
 TEMPORARY TABLESPACE temp_ts
 PROFILE clerk
 CONTAINER = CURRENT;

See Also:

"Tablespace Quotas for a User" for information about assigning a space quota for the tablespace

Tablespace Quotas for a User

The tablespace quota defines how much space to provide for a user's tablespace.

Topics:

About Assigning a Tablespace Quota for a User

You can assign each user a tablespace quota for any tablespace (except a temporary tablespace).

Assigning a quota accomplishes the following:

  • Users with privileges to create certain types of objects can create those objects in the specified tablespace.

  • Oracle Database limits the amount of space that can be allocated for storage of a user's objects within the specified tablespace to the amount of the quota.

By default, a user has no quota on any tablespace in the database. If the user has the privilege to create a schema object, then you must assign a quota to allow the user to create objects. At a minimum, assign users a quota for the default tablespace, and additional quotas for other tablespaces in which they can create objects. The maximum amount of space that you can assign for a tablespace is 2 TB. If you need more space, then specify UNLIMITED for the QUOTA clause.

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 using too much space in the database.

You can assign quotas to a user tablespace when you create the user, or add or change quotas later. (You can find existing user quotas by querying the USER_TS_QUOTAS view.) If a new quota is less than the old one, then the following conditions remain true:

  • If a user has already exceeded a new tablespace quota, then the objects of a user in the tablespace cannot be allocated more space until the combined space of these objects is less than the new quota.

  • If a user has not exceeded a new tablespace quota, or if the space used by the objects of the user in the tablespace falls under a new tablespace quota, then the user's objects can be allocated space up to the new quota.

CREATE USER Statement for Assigning a Tablespace Quota

The QUOTA clause of the CREATE USER statement assigns the quotas for a tablespace.

The following CREATE USER statement assigns quotas for the temp_ts and data_ts tablespaces:

CREATE USER jward
 IDENTIFIED BY password
 DEFAULT TABLESPACE data_ts
 QUOTA 100M ON temp_ts
 QUOTA 500K ON data_ts
 TEMPORARY TABLESPACE temp_ts
 PROFILE clerk
 CONTAINER = CURRENT;

Restriction of the Quota Limits for User Objects in a Tablespace

You can restrict the quota limits for user objects in a tablespace by using the ALTER USER SQL statement to change the current quota of the user to zero.

After a quota of zero is assigned, the objects of the user in the tablespace remain, and the user can still create new objects, but the existing objects will not be allocated any new space. For example, you could not insert data into one of this user's existing tables. The operation will fail with an ORA-1536 space quota exceeded for tables error.

Grants to Users for the 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.

The UNLIMITED TABLESPACE privilege overrides all explicit tablespace quotas for the user. If you later revoke the privilege, then you must explicitly grant quotas to individual tablespaces. You can grant this privilege only to users, not to roles.

Before granting the UNLIMITED TABLESPACE system privilege, you must consider the consequences of doing so.

Advantage:

  • You can grant a user unlimited access to all tablespaces of a database with one statement.

Disadvantages:

  • The privilege overrides all explicit tablespace quotas for the user.

  • You cannot selectively revoke tablespace access from a user with the UNLIMITED TABLESPACE privilege. You can grant selective or restricted access only after revoking the privilege.

Temporary Tablespaces for the User

A temporary tablespace contains transient data that persists only for the duration of a user session.

Topics:

About Assigning a Temporary Tablespace for a User

You should assign each user a temporary tablespace.

When a user executes a SQL statement that requires a temporary segment, Oracle Database stores the segment in the temporary tablespace of the user. These temporary segments are created by the system when performing sort or join operations. Temporary segments are owned by SYS, which has resource privileges in all tablespaces.

To create a temporary tablespace, you can use the CREATE TEMPORARY TABLESPACE SQL statement.

If you do not explicitly assign the user a temporary tablespace, then Oracle Database assigns the user the default temporary tablespace that was specified at database creation, or by an ALTER DATABASE statement at a later time. If there is no default temporary tablespace explicitly assigned, then the default is the SYSTEM tablespace or another permanent default established by the system administrator. Do not store user data in the SYSTEM tablespace. Assigning a tablespace to be used specifically as a temporary tablespace eliminates file contention among temporary segments and other types of segments.

Note:

If your SYSTEM tablespace is locally managed, then users must be assigned a specific default (locally managed) temporary tablespace. They may not be allowed to default to using the SYSTEM tablespace because temporary objects cannot be placed in locally managed permanent tablespaces.

You can set the temporary tablespace for a user at user creation, and change it later using the ALTER USER statement. You can also establish tablespace groups instead of assigning individual temporary tablespaces.

See Also:

TEMPORARY TABLESPACE Clause for Assigning a Temporary Tablespace

You can use the TEMPORARY TABLESPACE clause in the CREATE USER statement to assign a user a temporary tablespace.

In the following example, the temporary tablespace of jward is temp_ts, a tablespace created explicitly to contain only temporary segments.

CREATE USER jward
 IDENTIFIED BY password
 DEFAULT TABLESPACE data_ts
 QUOTA 100M ON temp_ts
 QUOTA 500K ON data_ts
 TEMPORARY TABLESPACE temp_ts
 PROFILE clerk
 CONTAINER = CURRENT;

Profiles for the User

A profile is a set of limits on database resources and password access to the database.

You can specify a profile when you create a user. The PROFILE clause of the CREATE USER statement assigns a user a profile. If you do not specify a profile, then Oracle Database assigns the user a default profile.

For example:

CREATE USER jward
 IDENTIFIED BY password
 DEFAULT TABLESPACE data_ts
 QUOTA 100M ON temp_ts
 QUOTA 500K ON data_ts
 TEMPORARY TABLESPACE temp_ts
 PROFILE clerk
 CONTAINER = CURRENT;

Creation of a Common User or a Local User

In a multitenant environment, user accounts are either common, that is, available throughout the CDB, or they are local, that is, available only within a specific PDB.

Topics:

About Creating Common User Accounts

When you create a common user account, be aware of restrictions such as where you can create these accounts, their naming conventions, and the kinds of objects that can be stored in their schemas.

To create a common user account, follow these rules:

  • To create a common user, you must be connected to the root and have the commonly granted CREATE USER system privilege.

  • The session's current container must be CDB$ROOT.

  • The name that you give the common user must start with C## or c## and contain only ASCII or EDCDIC characters. (This requirement does not apply to the names of existing Oracle-supplied user accounts, such as SYS or SYSTEM.) To find the names of existing user accounts, query the ALL_USERS, CDB_USERS, DBA_USERS, and USER_USERS data dictionary views.

  • To explicitly designate a user account as a common user, in the CREATE USER statement, specify CONTAINER=ALL. If you are logged into the root, and if you omit the CONTAINER clause from your CREATE USER statement, then the CONTAINER=ALL clause is implied.

  • Do not create objects in the schemas of common users. Doing so can cause problems during plug-in and unplug operations.

  • If you specify the DEFAULT TABLESPACE, TEMPORARY TABLESPACE, QUOTA...ON, and PROFILE clauses in the CREATE USER statement for a common user account, then you must ensure that these objects—tablespaces, tablespace groups, and profiles—exist in all containers of the CDB.

  • User-created schema objects owned by common users cannot be shared across PDB boundaries. Schema objects owned by Oracle-created common users are shared throughout the entire CDB.

CREATE USER Statement for Creating a Common User Account

You can use the CREATE USER statement with the CONTAINER clause to create a common user account. You must be in the root to create a common user account.

The following example shows how to create a common user account by using the CONTAINER clause, and then grant the user the SET CONTAINER and CREATE SESSION privilege. Common users must have these privileges to navigate between containers.

CONNECT SYSTEM@root
Enter password: password
Connected.

CREATE USER c##hr_admin
 IDENTIFIED BY password
 DEFAULT TABLESPACE data_ts
 QUOTA 100M ON temp_ts
 QUOTA 500K ON data_ts
 TEMPORARY TABLESPACE temp_ts
 CONTAINER = ALL;

GRANT SET CONTAINER, CREATE SESSION TO c##hr_admin CONTAINER = ALL;

About Creating Local User Accounts

When you create a local user account, be aware of restrictions such as where you can create these accounts, their naming conventions, and the kinds of objects that can be stored in their schemas.

To create a local user account, follow these rules:

  • To create a local user account, you must be connected to the PDB in which you want to create the account, and have the CREATE USER privilege.

  • The name that you give the local user must not start with C## or c##.

  • You can include CONTAINER=CURRENT in the CREATE USER statement to specify the user as a local user. If you are connected to a PDB and omit this clause, then the CONTAINER=CURRENT clause is implied.

  • You cannot have common users and local users with the same name. However, you can use the same name for local users in different PDBs. To find the names of existing user accounts, query the ALL_USERS, CDB_USERS, DBA_USERS, and USER_USERS data dictionary views.

  • Both common and local users connected to a PDB can create local user accounts, as long as they have the appropriate privileges.

CREATE USER Statement for Creating a Local User Account

You can use the CREATE USER statement with the CONTAINER clause to create a local user account. You must create the local user account in the PDB where you want this account to reside.

The following example shows how to create a local user account using the CONTAINER clause.

CONNECT SYSTEM@hrpdb
Enter password: password
Connected.

CREATE USER kmurray
 IDENTIFIED BY password
 DEFAULT TABLESPACE data_ts
 QUOTA 100M ON temp_ts
 QUOTA 500K ON data_ts
 TEMPORARY TABLESPACE temp_ts
 PROFILE hr_profile
 CONTAINER = CURRENT;

Creating a Default Role for the User

A role is a named group of related privileges that you grant as a group to users or other roles. A default role is automatically enabled for a user when the user creates a session. You can assign a user zero or more default roles.

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

  • Use the ALTER USER statement to change the default roles for the user.

For example:

GRANT USER rdale clerk_mgr;

ALTER USER rdale DEFAULT ROLE clerk_mgr;

Before a role can be made the default role for a user, that user must have been already granted the role.

See Also:

"Managing User Roles"

Altering User Accounts

You can use the ALTER USER statement to modify user accounts, such as changing their default tablespace or profile. In addition, you or the user can change the user's password.

Topics:

About Altering User Accounts

In most cases, you can alter user security settings with the ALTER USER SQL statement. Changing user security settings affects the future user sessions, not current sessions.

Users can change their own passwords. However, to change any other option of a user security domain, you must have the ALTER USER system privilege. Security administrators are typically the only users that have this system privilege, as it allows a modification of any user 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.

In a multitenant environment, you must have the commonly granted ALTER USER system privilege to alter common user accounts. To alter local user accounts, you must have a commonly granted ALTER USER privilege or a locally granted ALTER USER privilege in the PDB in which the local user account resides.

ALTER USER Statement for Altering Common or Local User Accounts

In a multitenant environment, you can use the ALTER USER statement to alter both common and local user accounts.

You cannot change an existing common user account to be a local user account, or vice versa. In this case, you must create a new account, as either a common user account or a local user account.

The following example shows how to use the ALTER USER statement to alter the common user c##hr_admin so that this user can access the container data objects in the EM_DB and HR_DB PDBs when connected to the root.

CONNECT SYSTEM@root
Enter password: password
Connected.

ALTER USER c##hr_admin
 DEFAULT TABLESPACE data_ts
 TEMPORARY TABLESPACE temp_ts
 QUOTA 100M ON data_ts
 QUOTA 0 ON temp_ts
 SET CONTAINER_DATA = (EMP_DB, HR_DB) FOR V$SESSION CONTAINER = CURRENT;

The ALTER USER statement here changes the security settings for the user c##hr_admin as follows:

  • DEFAULT TABLESPACE and TEMPORARY TABLESPACE are set explicitly to data_ts and temp_ts, respectively.

  • QUOTA 100M gives the data_ts tablespace 100 MB.

  • QUOTA 0 revokes the quota on the temp_ts tablespace.

  • SET CONTAINER_DATA enables user c##hr_admin to have access to data related to the EMP_DB and HR_DB PDBs as well as the root when he queries the V$SESSION view from the root.

Changing Non-SYS User Passwords

Users can change their own passwords but to change other users' passwords, they must have the correct privileges.

Topics:

About Changing Non-SYS User Passwords

Users can use either the PASSWORD command or ALTER USER statement to change a password.

No special privileges (other than those to connect to the database and create a session) are required for a user to change his or her own password. Encourage users to change their passwords frequently. "Guidelines for Securing Passwords" provides advice on the best ways to secure passwords. You can find existing users for the current database instance by querying the ALL_USERS view.

For better security, use the PASSWORD statement to change the account's password. The ALTER USER statement displays the new password on the screen, where it can be seen by any overly curious coworkers. The PASSWORD command does not display the new password, so it is only known to you, not to your co-workers. In both cases, the password is encrypted on the network.

Users must have the PASSWORD and ALTER USER privilege to switch between methods of authentication. Usually, only an administrator has this privilege.

See Also:

Using the PASSWORD Command or ALTER USER Statement to Change a Password

Most users can change their own passwords with the SQL*Plus PASSWORD command or the ALTER USER SQL statement.

  • Use one of the following methods to change a user’s passwords:

    • To use the SQL*Plus PASSWORD command to change a password, supply the user's name, and when prompted, enter the new password.

      For example:

      PASSWORD andy
      Changing password for andy
      New password: password
      Retype new password: password
      
    • To use the ALTER USER SQL statement change a password, include the IDENTIFIED BY clause.

      For example:

      ALTER USER andy IDENTIFIED BY password;
      

Changing the SYS User Password

To change the SYS user password, you must use the ORAPWD command line utility.

Topics:

About Changing the SYS User Password

If you must change the SYS user password, then you should use the ORAPWD command line utility to create a new password file that contains the password that you want to use.

Note the following:

  • The SYS user account is used by most of the internal recursive SQL. Therefore, if you try to use the ALTER USER statement to change this password while the database is open, then there is a chance that deadlocks will result.

  • If you try to use ALTER USER to change the SYS user password, and if the instance initialization parameter REMOTE_LOGIN_PASSWORDFILE has been set to SHARED, then you cannot change the SYS password. The ALTER USER statement fails with an ORA-28046: Password change for SYS disallowed error.

  • New accounts are created with created with the SHA-2 (SHA-512) verifier. You can identify these accounts by querying the PASSWORD_VERSIONS column of the DBA_USERS data dictionary view. (These password versions are listed as 12C in the view's output.) Because this verifier is too large to fit in the original password file format, the password file must be created in the extended format, by using the format=12 argument in the ORAPWD command. Otherwise, if you try to use the PASSWORD command to change the SYS password, then an ORA-28017: The password file is not in the extended format error will be raised.

  • In an Oracle Real Application Clusters (Oracle RAC) environment, store the password in the ASM disk group so that it can be shared by multiple Oracle RAC instances.

See Also:

ORAPWD Utility for Changing the SYS User Password

The ORAPWD utility enables you to change the SYS user password.

You can use the ORAPWD utility with the FILE parameter to change the SYS user password.The following example shows how create a new SYS password that will be stored in a password file that will be called orapworcl. (If the password file already exists, then an OPW-00005: File with same name exists - please delete or rename error warns you so that you can choose another name. If you want to overwrite the existing password file, then append the force=y argument to the ORAPWD command.)

orapwd file='orapworcl'
Enter password for SYS: new_password

See Also:

Oracle Database Administrator's Guide for more information about the ORAPWD utility

Configuring User Resource Limits

A resource limit defines the amount of system resources that are available for a user.

Topics:

About User Resource Limits

You can set limits on the amount of various system resources available to each user as part of the security domain of that user.

By doing so, you can prevent the uncontrolled consumption of valuable system resources such as CPU time. To set resource limits, you can use Database Resource Manager, which is described in Oracle Database Administrator's Guide.

This resource limit feature is very useful in large, multiuser systems, where system resources are very expensive. Excessive consumption of these resources by one or more users can detrimentally affect the other users of the database. In single-user or small-scale multiuser database systems, the system resource feature is not as important, because user consumption of system resources is less likely to have a detrimental impact.

You manage user resource limits by using Database Resource Manager. You can set password management preferences using profiles, either set individually or using a default profile for many users. Each Oracle database can have an unlimited number of profiles. Oracle Database allows the security administrator to enable or disable the enforcement of profile resource limits universally.

Setting resource limits causes a slight performance degradation when users create sessions, because Oracle Database loads all resource limit data for each user upon each connection to the database.

See Also:

Oracle Database Administrator's Guide for detailed information about managing resources

Types of System Resources and Limits

Oracle Database can limit the use of several types of system resources, including CPU time and logical reads. In general, you can control each of these resources at the session level, call level, or both.

Topics:

Limits to the User Session Level

Each time a user connects to a database, a session is created. Each session uses CPU time and memory on the computer that runs Oracle Database.

You can set several resource limits at the session level. If a user exceeds a session-level resource limit, then Oracle Database terminates (rolls back) the current statement and returns a message indicating that the session limit has been reached. At this point, all previous statements in the current transaction are intact, and the only operations the user can perform are COMMIT, ROLLBACK, or disconnect (in this case, the current transaction is committed). All other operations produce an error. Even after the transaction is committed or rolled back, the user cannot accomplish any more work during the current session.

Limits to Database Call Levels

Each time a user runs a SQL statement, Oracle Database performs several steps to process the statement.

During the SQL statement processing, several calls are made to the database as a part of the different execution phases. To prevent any one call from using the system excessively, Oracle Database lets you set several resource limits at the call level.

If a user exceeds a call-level resource limit, then Oracle Database halts the processing of the statement, rolls back the statement, and returns an error. However, all previous statements of the current transaction remain intact, and the user session remains connected.

Limits to CPU Time

When SQL statements and other types of calls are made to Oracle Database, a certain amount of CPU time is necessary to process the call.

Average calls require a small amount of CPU time. However, a SQL statement involving a large amount of data or a runaway query can potentially use a large amount of CPU time, reducing CPU time available for other processing.

To prevent uncontrolled use of CPU time, you can set fixed or dynamic limits on the CPU time for each call and the total amount of CPU time used for Oracle Database calls during a session. The limits are set and measured in CPU one-hundredth seconds (0.01 seconds) used by a call or a session.

Limits to Logical Reads

Input/output (I/O) is one of the most expensive operations in a database system.

SQL statements that are I/O-intensive can monopolize memory and disk use and cause other database operations to compete for these resources.

To prevent single sources of excessive I/O, you can limit the logical data block reads for each call and for each session. Logical data block reads include data block reads from both memory and disk. The limits are set and measured in number of block reads performed by a call or during a session.

Limits to Other Resources

Oracle Database provides for limiting several other resources at the session level.

Limits to other resources are as follows:

  • You can limit the number of concurrent sessions for each user. Each user can create only up to a predefined number of concurrent sessions.

  • You can limit the idle time for a session. If the time between calls in a session reaches the idle time limit, then the current transaction is rolled back, the session is terminated, and the resources of the session are returned to the system. The next call receives an error that indicates that the user is no longer connected to the instance. This limit is set as a number of elapsed minutes.

    Note:

    Shortly after a session is terminated because it has exceeded an idle time limit, the process monitor (PMON) background process cleans up after the terminated session. Until PMON completes this process, the terminated session is still counted in any session or user resource limit.

  • You can limit the elapsed connect time for each session. If the duration of a session exceeds the elapsed time limit, then the current transaction is rolled back, the session is dropped, and the resources of the session are returned to the system. This limit is set as a number of elapsed minutes.

    Note:

    Oracle Database does not constantly monitor the elapsed idle time or elapsed connection time. Doing so reduces system performance. Instead, it checks every few minutes. Therefore, a session can exceed this limit slightly (for example, by 5 minutes) before Oracle Database enforces the limit and terminates the session.

  • You can limit the amount of private System Global Area (SGA) space (used for private SQL areas) for a session. This limit is only important in systems that use the shared server configuration. Otherwise, private SQL areas are located in the Program Global Area (PGA). This limit is set as a number of bytes of memory in the SGA of an instance. Use the characters K or M to specify kilobytes or megabytes.

    See Also:

    For instructions about enabling or disabling resource limits:

Values for Resource Limits of Profiles

Before creating profiles and setting the resource limits associated with them, you should determine appropriate values for each resource limit.

You can base the resource limit values on the type of operations a typical user performs. For example, if one class of user does not usually perform a high number of logical data block reads, then use the ALTER RESOURCE COST SQL statement to set the LOGICAL_READS_PER_SESSION setting conservatively.

Usually, the best way to determine the appropriate resource limit values for a given user profile is to gather historical information about each type of resource usage. For example, the database or security administrator can use the AUDIT SESSION clause to gather information about the limits CONNECT_TIME, LOGICAL_READS_PER_SESSION.

You can gather statistics for other limits using the Monitor feature of Oracle Enterprise Manager (or SQL*Plus), specifically the Statistics monitor.

See Also:

Managing Resources with Profiles

A profile is a named set of resource limits and password parameters that restrict database usage and instance resources for a user.

Topics:

About Profiles

A profile is a collection of attributes that apply to a user, enabling a single point of reference for any of multiple users that share those exact attributes.

User profiles in Oracle Internet Directory contain attributes pertinent to directory usage and authentication for each user. Similarly, profiles in Oracle Label Security contain attributes useful in label security user administration and operations management. Profile attributes can include restrictions on system resources. You can use Database Resource Manager to set these types of resource limits.

You should assign a profile to each user, and a default profile to all others. Each user can have only one profile, and creating a new one supersedes an earlier version. You must create and manage user profiles only if resource limits are a requirement of your database security policy. To use profiles, first categorize the related types of users in a database. Just as roles are used to manage the privileges of related users, profiles are used to manage the resource limits of related users. Determine how many profiles are needed to encompass all types of users in a database and then determine appropriate resource limits for each profile.

Profile resource limits are enforced only when you enable resource limitation for the associated database. Enabling this limitation can occur either before starting the database (using the RESOURCE_LIMIT initialization parameter) or while it is open (using the ALTER SYSTEM statement).

Though password parameters reside in profiles, they are unaffected by RESOURCE_LIMIT or ALTER SYSTEM and password management is always enabled. In Oracle Database, Database Resource Manager primarily handles resource allocations and restrictions.

Any authorized database user can create, assign to users, alter, and drop a profile at any time (using the CREATE USER or ALTER USER statement). Profiles can be assigned only to users and not to roles or other profiles. Profile assignments do not affect current sessions; instead, they take effect only in subsequent sessions.

To find information about current profiles, query the DBA_PROFILES view.

See Also:

Creating a Profile

A profile can encompass limits for a specific category, such as limits on passwords or limits on resources.

To create a profile, you must have the CREATE PROFILE system privilege. To find all existing profiles, you can query the DBA_PROFILES view.

  • Use the CREATE PROFILE statement to create a profile.

For example, to create a profile that defines password limits:

CREATE PROFILE password_prof LIMIT
  FAILED_LOGIN_ATTEMPTS 6
  PASSWORD_LIFE_TIME 60
  PASSWORD_REUSE_TIME 60
  PASSWORD_REUSE_MAX 5
  PASSWORD_LOCK_TIME 1/24
  PASSWORD_GRACE_TIME 10
  PASSWORD_VERIFY_FUNCTION DEFAULT;

The following example shows how to create a resource limits profile.

CREATE PROFILE app_user LIMIT 
  SESSIONS_PER_USER          UNLIMITED 
  CPU_PER_SESSION            UNLIMITED 
  CPU_PER_CALL               3500 
  CONNECT_TIME               50 
  LOGICAL_READS_PER_SESSION  DEFAULT 
  LOGICAL_READS_PER_CALL     1200 
  PRIVATE_SGA                20K
  COMPOSITE_LIMIT            7500000;

See Also:

Oracle Database SQL Language Reference for more information about the CREATE PROFILE SQL statement

Assigning a Profile to a User

After you create a profile, you can assign it to users.

You can assign a profile to a user who has already been assigned a profile, but the most recently assigned profile takes precedence. When you assign a profile to an external user or a global user, the password parameters do not take effect for that user.

To find the profiles that are currently assigned to users, you can query the DBA_USERS view.

  • Use the ALTER USER statement to assign the profile to a user.

For example:

ALTER USER psmith PROFILE app_user;

Dropping Profiles

You can drop a profile, even if it is currently assigned to a user.

When you drop a profile, the drop does not affect currently active sessions. Only sessions that were created after a profile is dropped use the modified profile assignments. To drop a profile, you must have the DROP PROFILE system privilege. You cannot drop the default profile.

  • Use the SQL statement DROP PROFILE to drop a profile. To drop a profile that is currently assigned to a user, use the CASCADE option.

For example:

DROP PROFILE clerk CASCADE;

Any user currently assigned to a profile that is dropped is automatically is assigned to the DEFAULT profile. The DEFAULT profile cannot be dropped.

See Also:

Oracle Database SQL Language Reference for more information about the DROP PROFILE SQL statement

Dropping User Accounts

You can drop user accounts as long as the user is not logged into a a user session. When you drop a user, the associates objects in that user's schema are dropped as well.

Topics:

About Dropping User Accounts

Before you drop (delete) a user account, you must ensure that you have the appropriate privileges for doing so.

To drop a user account in any environment, you must have the DROP USER system privilege. In a multitenant environment, you must have the commonly granted DROP USER system privilege to drop common user accounts. To drop local user accounts, you must have a commonly granted DROP USER privilege or a locally granted DROP USER privilege in the PDB in which the local user account resides.

When you drop a user account, Oracle Database removes the user account and associated schema from the data dictionary. It also immediately drops all schema objects contained in the user schema, if any.

Note:

  • If a user schema and associated objects must remain but the user must be denied access to the database, then revoke the CREATE SESSION privilege from the user.

  • Do not attempt to drop the SYS or SYSTEM user. Doing so corrupts your database.

Terminating a User Session

A user who is currently connected to a database cannot be dropped. You must first terminate the user session (or the user can exit the session) before you can drop the user.

  1. Query the V$SESSION dynamic view to find the session ID of the user whose session you want to terminate.

    For example:

    SELECT SID, SERIAL#, USERNAME FROM V$SESSION;
    
        SID         SERIAL# USERNAME
    ------- --------------- ----------------------
        127          55234  ANDY
    ...
    
  2. Use the ALTER SYSTEM SQL statement to stop the session for the user, based on the SID and SERIAL# settings of the V$SESSION view.

    For example:

    ALTER SYSTEM KILL SESSION '127, 55234';
    

See Also:

Oracle Database Administrator's Guide for more information about terminating sessions

About Dropping a User After the User Is No Longer Connected to the Database

After a user is no longer connected to the database, you can drop this user by using the DROP USER statement.

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

If the schema of the user contains any dependent schema objects, then 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 schema contains dependent objects, then an error message is returned and the user is not dropped.

Dropping a User Whose Schema Contains Objects

Before dropping a user whose schema contains objects, thoroughly investigate which objects the schema contains and the implications of dropping them.

  1. Query the DBA_OBJECTS data dictionary view to find the objects that are owned by the user.

    For example:

    SELECT OWNER, OBJECT_NAME FROM DBA_OBJECTS WHERE OWNER LIKE 'ANDY';
    

    Enter the user name in capital letters..Pay attention to any unknown cascading effects. For example, if you intend to drop a user who owns a table, then check whether any views or procedures depend on that particular table.

  2. Use the DROP USER SQL statement to drop the user and all associated objects and foreign keys that depend on the tables that the user owns.

    For example:

    DROP USER andy CASCADE;
    

Database User and Profile Data Dictionary Views

Oracle Database provides a set of data dictionary views that provide information about the settings that you used to create users and profiles.

Topics:

Data Dictionary Views That List Information About Users and Profiles

Oracle Database provides a set of data dictionary views that contain information about database users and profiles.

Table 2-1 lists these data dictionary views. For detailed information about these views, see Oracle Database Reference.


Table 2-1 Data Dictionary Views That Display Information about Users and Profiles

View Description

ALL_OBJECTS

Describes all objects accessible to the current user

ALL_USERS

Lists users visible to the current user, but does not describe them

DBA_PROFILES

Displays all profiles and their limits

DBA_TS_QUOTAS

Describes tablespace quotas for users

DBA_OBJECTS

Describes all objects in the database

DBA_USERS

Describes all users of the database

DBA_USERS_WITH_DEFPWD

Lists all user accounts that have default passwords

PROXY_USERS

Describes users who can assume the identity of other users

RESOURCE_COST

Lists the cost for each resource in terms of CPUs for each session, reads for each session, connection times, and SGA

USER_PASSWORD_LIMITS

Describes the password profile parameters that are assigned to the user

USER_RESOURCE_LIMITS

Displays the resource limits for the current user

USER_TS_QUOTAS

Describes tablespace quotas for users

USER_OBJECTS

Describes all objects owned by the current user

USER_USERS

Describes only the current user

V$SESSION

Lists session information for the current database session

V$SESSTAT

Displays user session statistics

V$STATNAME

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


The following sections present examples of using these views. These examples assume that the following statements have been run. The users are all local users.

CREATE PROFILE clerk LIMIT
    SESSIONS_PER_USER 1
    IDLE_TIME 30
    CONNECT_TIME 600;

CREATE USER jfee
    IDENTIFIED BY password
    DEFAULT TABLESPACE example
    TEMPORARY TABLESPACE temp
    QUOTA 500K ON example
    PROFILE clerk
    CONTAINER = CURRENT;

CREATE USER dcranney
    IDENTIFIED BY password
    DEFAULT TABLESPACE example
    TEMPORARY TABLESPACE temp
    QUOTA unlimited ON example
    CONTAINER = CURRENT;

CREATE USER userscott
     IDENTIFIED BY password
     CONTAINER = CURRENT;

Query to Find All Users and Associated Information

To find all users and their associated information as defined in the database, you can query the DBA_USERS view.

For detailed information about the DBA_USERS view, see Oracle Database Reference.

For example:

col username format a11
col profile format a10
col account_status format a19
col authentication_type format a29

SELECT USERNAME, PROFILE, ACCOUNT_STATUS, AUTHENTICATION_TYPE FROM DBA_USERS;
 
USERNAME        PROFILE         ACCOUNT_STATUS   AUTHENTICATION_TYPE
--------------- --------------- ---------------  -------------------
SYS             DEFAULT         OPEN             PASSWORD
SYSTEM          DEFAULT         OPEN             PASSWORD
USERSCOTT       DEFAULT         OPEN             PASSWORD
JFEE            CLERK           OPEN             GLOBAL
DCRANNEY        DEFAULT         OPEN             EXTERNAL 

Query to List All Tablespace Quotas

You can query the DBA_TS_QUOTAS view to list all tablespace quotas specifically assigned to each user.

For detailed information about this view, see Oracle Database Reference.

For example:

SELECT * FROM DBA_TS_QUOTAS;

TABLESPACE    USERNAME    BYTES     MAX_BYTES    BLOCKS    MAX_BLOCKS
----------    ---------  --------   ----------   -------   ----------
EXAMPLE       JFEE              0       512000         0          250
EXAMPLE       DCRANNEY          0           -1         0           -1

When specific quotas are assigned, the exact number is indicated in the MAX_BYTES column. This number is always a multiple of the database block size, so if you specify a tablespace quota that is not a multiple of the database block size, then it is rounded up accordingly. Unlimited quotas are indicated by -1.

Query to List All Profiles and Assigned Limits

The DBA_PROFILE view lists all profiles in the database and associated settings for each limit in each profile.

For detailed information about this view, see Oracle Database Reference.

For example:

SELECT * FROM DBA_PROFILES
   ORDER BY PROFILE;

PROFILE             RESOURCE_NAME              RESOURCE_TYPE   LIMIT 
-----------------   -----------------------    -------------  --------------
CLERK               COMPOSITE_LIMIT            KERNEL         DEFAULT
CLERK               FAILED_LOGIN_ATTEMPTS      PASSWORD       DEFAULT
CLERK               PASSWORD_LIFE_TIME         PASSWORD       DEFAULT
CLERK               PASSWORD_REUSE_TIME        PASSWORD       DEFAULT
CLERK               PASSWORD_REUSE_MAX         PASSWORD       DEFAULT
CLERK               PASSWORD_VERIFY_FUNCTION   PASSWORD       DEFAULT
CLERK               PASSWORD_LOCK_TIME         PASSWORD       DEFAULT
CLERK               PASSWORD_GRACE_TIME        PASSWORD       DEFAULT
CLERK               PRIVATE_SGA                KERNEL         DEFAULT
CLERK               CONNECT_TIME               KERNEL         600    
CLERK               IDLE_TIME                  KERNEL         30     
CLERK               LOGICAL_READS_PER_CALL     KERNEL         DEFAULT
CLERK               LOGICAL_READS_PER_SESSION  KERNEL         DEFAULT
CLERK               CPU_PER_CALL               KERNEL         DEFAULT
CLERK               CPU_PER_SESSION            KERNEL         DEFAULT
CLERK               SESSIONS_PER_USER          KERNEL         1      
DEFAULT             COMPOSITE_LIMIT            KERNEL         UNLIMITED
DEFAULT             PRIVATE_SGA                KERNEL         UNLIMITED
DEFAULT             SESSIONS_PER_USER          KERNEL         UNLIMITED
DEFAULT             CPU_PER_CALL               KERNEL         UNLIMITED
DEFAULT             LOGICAL_READS_PER_CALL     KERNEL         UNLIMITED
DEFAULT             CONNECT_TIME               KERNEL         UNLIMITED
DEFAULT             IDLE_TIME                  KERNEL         UNLIMITED
DEFAULT             LOGICAL_READS_PER_SESSION  KERNEL         UNLIMITED
DEFAULT             CPU_PER_SESSION            KERNEL         UNLIMITED
DEFAULT             FAILED_LOGIN_ATTEMPTS      PASSWORD       10
DEFAULT             PASSWORD_LIFE_TIME         PASSWORD       180
DEFAULT             PASSWORD_REUSE_MAX         PASSWORD       UNLIMITED
DEFAULT             PASSWORD_LOCK_TIME         PASSWORD       1
DEFAULT             PASSWORD_GRACE_TIME        PASSWORD       7
DEFAULT             PASSWORD_VERIFY_FUNCTION   PASSWORD       UNLIMITED
DEFAULT             PASSWORD_REUSE_TIME        PASSWORD       UNLIMITED
32 rows selected. 

To find the default profile values, you can run the following query:

SELECT * FROM DBA_PROFILES WHERE PROFILE = 'DEFAULT';

PROFILE             RESOURCE_NAME              RESOURCE_TYPE  LIMIT  
-----------------   -------------------------  -------------  --------------
DEFAULT             COMPOSITE_LIMIT            KERNEL         UNLIMITED
DEFAULT             SESSIONS_PER_USER          KERNEL         UNLIMITED
DEFAULT             CPU_PER_SESSION            KERNEL         UNLIMITED
DEFAULT             CPU_PER_CALL               KERNEL         UNLIMITED
DEFAULT             LOGICAL_READS_PER_SESSION  KERNEL         UNLIMITED
DEFAULT             LOGICAL_READS_PER_CALL     KERNEL         UNLIMITED
DEFAULT             IDLE_TIME                  KERNEL         UNLIMITED
DEFAULT             CONNECT_TIME               KERNEL         UNLIMITED
DEFAULT             PRIVATE_SGA                KERNEL         UNLIMITED
DEFAULT             FAILED_LOGIN_ATTEMPTS      PASSWORD       10
DEFAULT             PASSWORD_LIFE_TIME         PASSWORD       180
DEFAULT             PASSWORD_REUSE_TIME        PASSWORD       UNLIMITED
DEFAULT             PASSWORD_REUSE_MAX         PASSWORD       UNLIMITED
DEFAULT             PASSWORD_VERIFY_FUNCTION   PASSWORD       NULL
DEFAULT             PASSWORD_LOCK_TIME         PASSWORD       1
DEFAULT             PASSWORD_GRACE_TIME        PASSWORD       7

16 rows selected.

Query to View Memory Use for Each User Session

To find the memory use for each user session, you can query the V$SESSION view.

For detailed information on this view, see Oracle Database Reference.

The following query lists all current sessions, showing the Oracle Database user and current User Global Area (UGA) memory use for each 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 name.NAME = 'session uga memory';

USERNAME                       Current UGA memory
------------------------------ ---------------------------------------------
                               18636bytes
                               17464bytes
                               19180bytes
                               18364bytes
                               39384bytes
                               35292bytes
                               17696bytes
                               15868bytes
USERSCOTT                      42244bytes
SYS                            98196bytes
SYSTEM                         30648bytes

11 rows selected.

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