Creating User Accounts

A user account can have restrictions such as profiles, a default role, and tablespace restrictions.

About Common Users and Local Users

In a multitenant environment, CDB common users and application common have access to their respective containers, and local users are specific to a PDB.

About Common Users

Oracle provides two types of common users: CDB common users and application common users.

A CDB common user is a database user whose single identity and password are known in the CDB root and in every existing and future pluggable database (PDB), including any application roots. All Oracle-supplied administrative user accounts, such as SYS and SYSTEM, are CDB common users and can navigate across the system container. CDB common users can have different privileges in different PDBs. For example, the 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 SYSTEM when this user is connected to that PDB. Oracle does not recommend that you change the privileges of the Oracle-supplied CDB common users.

A CDB common user can perform all tasks that an application common user can perform, provided that appropriate privileges have been granted to that user.

An application common user is a user account that is created in an application root, and is common only within this application container. In other words, the application common user does not have access to the entire CDB environment like CDB common users. An application common user is responsible for activities such as creating (which includes plugging), opening, closing, unplugging, and dropping application PDBs. This user can create application common objects in the application root. You can create an application common user only when you are connected to an application root. The ability for users to access the application common objects is subject to the same privileges as local and CDB common objects. For example, a local user in a PDB that is associated with an application root has access to only the objects in that PDB for which the user has privileges. In the application root itself, you can commonly grant a privilege on a CDB common object that will apply across the application container.

Both of these types of common users are responsible for managing the common objects in their respective roots. If the CDB common user or the application common user has the appropriate privileges, then this user can perform operations in PDBs as well, such as granting privileges to local users. These users can also locally grant common users different privileges in each container.

Both CDB and application common users can perform the following activities:

One difference between CDB common users and application common users is that only a CDB common user can run an ALTER DATABASE statement that specifies the recovery clauses that apply to the entire CDB.

How Plugging in PDBs Affects CDB Common Users

Plugging a non-CDB into a CDB as a PDB affects both Oracle-supplied administrative and user-created accounts and privileges.

This affects the passwords of these CDB common user accounts, and privileges of all accounts in the newly plugged-in database.

The following actions take place:

The following two scenarios are possible when you plug a PDB (for example, pdb_1) from one CDB (cdb_1) to a another CDB (cdb_2):

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:

Who Can Create User Accounts?

Users who has been granted the CREATE USER system privilege can create user accounts, including user accounts to 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 create users who themselves have the privilege to create users, then include the WITH ADMIN OPTION clause in the GRANT statement. For example:

GRANT CREATE USER 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. By default, the SET CONTAINER privilege is granted to CONNECT role.

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.

Creating a New User Account That Has Minimum Database Privileges

When you create a new user account, you should 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;
Follow the guidelines in [Minimum Requirements for Passwords](minimum-requirements-passwords.html#GUID-AA1AA635-1CD5-422E-B8CA-681ED7C253CA) to replace *password* with a password that is secure.

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.
  1. 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, be aware of restrictions such as naming conventions and whether the name is unique.

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 in that PDB.

Note the following restrictions:

Case Sensitivity for User Names

How you create a user name controls 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 test_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

The IDENTIFIED BY clause of the CREATE USER statement assigns the user a password.

Ensure that you create a secure password.

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

Default Tablespace for the User

A default tablespace stores objects that users create.

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, the Oracle Database stores the object in the user’s default tablespace.

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

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

The DEFAULT TABLESPACE clause in the CREATE USER statement assigns a default tablespace to the user.

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 test_ts
 QUOTA 500K ON data_ts
 TEMPORARY TABLESPACE temp_ts
 PROFILE clerk
 CONTAINER = CURRENT;

Tablespace Quotas for a User

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

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:

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:

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 test_ts and data_ts tablespaces:

CREATE USER jward
 IDENTIFIED BY password
 DEFAULT TABLESPACE data_ts
 **QUOTA 500K ON data_ts**
 **QUOTA 100M ON test_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 so that the current quota is zero.

To restrict the quote limits, use the ALTER USER SQL statement.

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 tablespace %s 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, consider the consequences of doing so.

Advantage:

Disadvantages:

Temporary Tablespaces for the User

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

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

TEMPORARY TABLESPACE Clause for Assigning a Temporary Tablespace

The TEMPORARY TABLESPACE clause in the CREATE USER statement assigns 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 test_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, defined by attributes, on database resources and password access to the database.

The profile can be applied to multiple users, enabling them to share these attributes.

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 test_ts
 QUOTA 500K ON data_ts
 TEMPORARY TABLESPACE temp_ts
**PROFILE clerk**
 CONTAINER = CURRENT;

In a multitenant environment, different profiles can be assigned to a common user in the root and in a PDB. When the common user logs in to the PDB, a profile whose setting applies to the session depends on whether the settings are password-related or resource-related.

Creation of a Common User or a Local User

The CREATE USER SQL statement can be used to create both common (CDB and application) users and local users.

About Creating Common User Accounts

Be aware of common user account restrictions such as where they can be created, naming conventions, and objects stored in their schemas.

To create a common user account, follow these rules:

ALTER PLUGGABLE DATABASE APPLICATION saas_sales_app SYNC;

CREATE USER Statement for Creating a Common User Account

The CREATE USER statement CONTAINER=ALL clause can be used to create a common user account.

You must be in the CDB root to create a CDB common user account and the application root to create an application common user account.

The following example shows how to create a CDB common user account from the CDB root by using the CONTAINER clause, and then granting the user the SET CONTAINER and CREATE SESSION privileges. Common users must have the SET CONTAINER system privilege to navigate between containers. When you create the account, there is a single common password for this common user across all containers.

CONNECT SYSTEM
Enter password: password
Connected.

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

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

The next example shows how to create an application common user in the application root (app_root) by using the CONTAINER clause, and then granting the user the SET CONTAINER, and CREATE SESSION system privileges. Finally, to synchronize this user so that it is visible in the application PDBs, the ALTER PLUGGABLE DATABASE APPLICATION APP$CON SYNC statement is run.

CONNECT SYSTEM@app_root
Enter password: password
Connected.

CREATE USER app_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 app_admin CONTAINER = ALL;

CONNECT SYSTEM@app_hr_pdb
Enter password: password
Connected.

ALTER PLUGGABLE DATABASE APPLICATION APP$CON SYNC;

About Creating Local User Accounts

Be aware of local user account restrictions such as where they can be created, naming conventions, and objects stored in their schemas.

To create a local user account, follow these rules:

CREATE USER Statement for Creating a Local User Account

The CREATE USER statement CONTAINER clause can be used 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@pdb_name
Enter password: password
Connected.

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

Creating a Default Role for the User

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.

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.