Creating Users in Oracle AI Database 26ai

The Role of the Database User

One of the most routine responsibilities of an Oracle DBA is managing database user accounts. Every person or application that connects to an Oracle database does so through a named user account. Each account is assigned a unique username, and users authenticate with that username to establish a session. Once connected, users can issue SQL statements to create objects, query data, and, depending on their granted privileges, perform administrative operations.

Oracle separates the concept of a user account from the concept of a schema, though in practice they share the same name. When a user creates a table, that table belongs to the user's schema. Since Oracle AI Database 26ai, and indeed since Oracle Database 21c, every database is a multitenant container database, which adds a real question worth asking about any user you create: which container does this account actually belong to? A common user, created in the CDB root, is visible across every pluggable database in the container; a local user, created inside a specific PDB, exists only there. This lesson works with local users in a single PDB unless stated otherwise, but the distinction matters the moment you start managing more than one PDB.

This module covers the full lifecycle of a database user account: creation, modification, password management, temporary suspension, and deletion. It also covers how to retrieve user information from the Oracle data dictionary.

The CREATE USER Command

The CREATE USER command establishes a new database account. At minimum, you must supply a username and an authentication method. In most cases that means an identified-by password clause, though Oracle also supports external authentication, global authentication, and a genuinely expanded set of cloud-identity options in current releases, covered below.

The full syntax for creating a locally authenticated user is:

CREATE USER [IF NOT EXISTS] username IDENTIFIED BY password
 DEFAULT TABLESPACE tablespace_name
 TEMPORARY TABLESPACE temp_tablespace
 QUOTA size ON tablespace_name
 PROFILE profile_name
 ACCOUNT UNLOCK;

A practical example:

CREATE USER myuser IDENTIFIED BY SecurePass#2024
 DEFAULT TABLESPACE users
 TEMPORARY TABLESPACE temp
 QUOTA UNLIMITED ON users
 QUOTA 100M ON my_data;

Each clause in this statement serves a specific purpose. The DEFAULT TABLESPACE clause determines where the user's objects, tables, indexes, and other schema objects, are stored by default. If omitted, Oracle assigns the database default tablespace, which is typically USERS in a standard installation. The TEMPORARY TABLESPACE clause specifies where Oracle writes sort and hash join operations for this user's sessions. The QUOTA clause controls how much space the user may consume in a given tablespace. Setting QUOTA 0 on a tablespace prevents the user from creating any objects there even if they hold the CREATE TABLE privilege.

The IF NOT EXISTS Clause

Notice the optional IF NOT EXISTS clause in the syntax above. When included, Oracle skips the statement without raising an error if a user with that name already exists, rather than failing the script outright:

CREATE USER IF NOT EXISTS myuser IDENTIFIED BY SecurePass#2024
 DEFAULT TABLESPACE users
 TEMPORARY TABLESPACE temp;

This is genuinely useful for setup scripts you expect to run more than once, for instance a provisioning script run again after a partial failure, where you want the script to proceed past user creation rather than stop on an ORA-01920 "user name conflicts with another user or role name" error.

Authentication Beyond a Local Password

Current Oracle Database supports a considerably broader set of authentication methods than a simple password. Beyond IDENTIFIED BY password, IDENTIFIED EXTERNALLY, and IDENTIFIED GLOBALLY, the current IDENTIFIED clause also supports authenticating a user against cloud identity providers directly: AZURE_USER and AZURE_ROLE for Microsoft Azure Active Directory integration, and IAM_GROUP_NAME, IAM_PRINCIPAL_NAME, and IAM_PRINCIPAL_OCID for Oracle Cloud Infrastructure IAM-based authentication. A NO AUTHENTICATION option also exists for accounts that should never authenticate directly at all. This reflects a real shift in how modern Oracle deployments handle identity: rather than managing a separate password for every database account, many organizations now authenticate users against an identity provider they already maintain elsewhere, with the database trusting that provider's decision rather than storing credentials of its own.

Creating a User in a Specific Container

Since every 26ai database is a CDB, CREATE USER also supports an explicit CONTAINER clause when you need to be precise about scope. To specify CONTAINER = ALL, creating a common user visible across every PDB in the container, you must be connected to the CDB root. To specify CONTAINER = CURRENT, restricting the user to the PDB you are currently connected to, you must be connected to a PDB, not the root. For most day-to-day application work, connecting directly to the relevant PDB and creating a local user without an explicit CONTAINER clause is the simpler, more common approach; the explicit clause matters most when you are deliberately creating a common user meant to work identically across every PDB in the container.

What CREATE USER Does Beyond Account Creation

The CREATE USER command does more than register a username and password. Several implicit actions occur at creation time:

1. Default profile assignment: Unless you specify a PROFILE clause, Oracle assigns the DEFAULT profile to the new user. This profile governs password complexity rules, password expiry intervals, failed login limits, and session resource limits such as CPU time and idle timeout.
2. No privileges granted by default: A newly created user has no privileges whatsoever. They cannot log in until CREATE SESSION is granted, and they cannot create objects until the appropriate system privileges or roles are granted. This is by design; Oracle follows the principle of least privilege at account creation.
3. Schema creation: Oracle implicitly creates a schema with the same name as the user. The schema is empty at creation but will hold all objects the user creates. The schema name and the username are always identical in Oracle local authentication.
4. Audit trail entry: If unified auditing is configured to capture user management events, the CREATE USER action is logged automatically. This matters more than it might sound: starting with Oracle AI Database 26ai, traditional auditing is fully desupported. You can no longer create new traditional audit settings or update existing ones at all; unified auditing is not merely the recommended default anymore, it is the only auditing mechanism available for any new configuration work. If you are working with a database upgraded from an earlier release that still has traditional audit settings in place, those continue generating records, but only unified audit policies can be created or modified going forward.

Granting the Minimum Required Privileges

After creating a user, the first grant is almost always CREATE SESSION, which allows the user to establish a database connection:

GRANT CREATE SESSION TO myuser;

For a developer account that needs to create tables and run queries:

GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE TO myuser;

For environments where many users share a common set of privileges, Oracle roles simplify administration. Rather than granting individual privileges to each user, you grant the role:

GRANT connect, resource TO myuser;

The CONNECT role has carried far fewer privileges than its name once implied for a long time now, and it is worth being precise about exactly what it grants today: the CONNECT role currently retains only the CREATE SESSION and SET CONTAINER privileges. That second privilege is directly relevant in a mandatory-CDB world: SET CONTAINER is what lets a common user switch between containers within a CDB at all, so its inclusion in CONNECT is not an accident, it reflects how routinely that operation comes up now that every database is multitenant. The RESOURCE role separately grants a set of object creation privileges, including CREATE TYPE, but not unlimited tablespace quota; that must be granted separately if required.

Modifying a User with ALTER USER

After creation, user account attributes are managed with ALTER USER. Common modifications include changing the password, updating tablespace quotas, switching profiles, and locking or unlocking the account.

Change a user's password:

ALTER USER myuser IDENTIFIED BY NewSecurePass#2025;

Change the default tablespace:

ALTER USER myuser DEFAULT TABLESPACE new_tablespace;

Assign a different profile:

ALTER USER myuser PROFILE developer_profile;

One correction worth making plainly: schema-level privileges, which let a DBA grant a broad set of privileges across every object in a schema with one statement, are real and current, but they are granted through the GRANT statement, not through any special clause of ALTER USER. If you have seen or written material describing a SCHEMA PRIVILEGES clause as part of ALTER USER, that description does not match current syntax; the actual mechanism looks like this instead:

GRANT SELECT ANY TABLE ON SCHEMA hr TO myuser;

That statement grants the SELECT privilege across every table in the HR schema to myuser in a single operation, which is the genuine capability this feature provides, just reached through GRANT rather than ALTER USER.

Temporarily Disabling a User

When a user needs to be prevented from logging in without permanently removing their account, the cleanest approach is to lock the account:

ALTER USER myuser ACCOUNT LOCK;

This immediately blocks new login attempts. All existing privileges, password settings, and schema objects remain completely intact. When access should be restored:

ALTER USER myuser ACCOUNT UNLOCK;

Locking is preferable to dropping and recreating the account because it avoids the overhead of reassigning privileges and restoring schema objects. It is the standard approach for handling employee leaves, security investigations, and maintenance windows.

Changing a User's Password

A DBA can reset any user's password at any time using ALTER USER:

ALTER USER myuser IDENTIFIED BY NewPassword#99;

To force the user to choose a new password at their next login without specifying the new password yourself, expire the current password:

ALTER USER myuser PASSWORD EXPIRE;

When a password is expired, Oracle prompts the user to set a new one the next time they connect. Until they do, they cannot access the database. This technique is commonly used during account provisioning to ensure users set their own credentials before first use.

Retrieving User Information from the Data Dictionary

Oracle maintains detailed user account information in several data dictionary views. The primary view for user management is DBA_USERS:

SELECT
 username,
 account_status,
 lock_date,
 expiry_date,
 default_tablespace,
 temporary_tablespace,
 profile,
 created
FROM dba_users
ORDER BY username;

The ACCOUNT_STATUS column reports considerably more detail than a simple open-or-locked flag. Beyond the common values OPEN, LOCKED, EXPIRED, and EXPIRED & LOCKED, you may also see LOCKED(TIMED) for an account locked automatically after too many failed login attempts, EXPIRED(GRACE) for an account past its password expiry date but still within a configured grace period, and combined states such as EXPIRED(GRACE) & LOCKED(TIMED). The LOCK_DATE and EXPIRY_DATE columns record when those states were applied, which is valuable for auditing and compliance reporting.

To view the privileges granted directly to a user:

SELECT privilege, admin_option
FROM dba_sys_privs
WHERE grantee = 'MYUSER';

To view role assignments:

SELECT granted_role, admin_option, default_role
FROM dba_role_privs
WHERE grantee = 'MYUSER';

Summary

Creating and managing Oracle database users is a foundational DBA skill. The CREATE USER command establishes the account, assigns a default profile, and creates the associated schema, with the option to scope that account explicitly to a container in a multitenant environment and to authenticate it against a cloud identity provider rather than a local password. After creation, the user requires at minimum a CREATE SESSION grant to connect. All subsequent modifications, password changes, tablespace quotas, profile assignments, and account locking, are handled through ALTER USER, while schema-level privilege grants are handled through GRANT specifically. The data dictionary views DBA_USERS, DBA_SYS_PRIVS, and DBA_ROLE_PRIVS provide full visibility into account status and privilege assignments. The remaining lessons in this module cover each of these operations in detail.