Creating Users in Oracle Database

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. This separation becomes important in Oracle 23ai, which introduces schema-level privilege management enhancements that give DBAs finer control over what actions can be performed within a given schema.

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 and global authentication for enterprise environments.

The full syntax for creating a locally authenticated user is:

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

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 Oracle Unified Auditing is enabled — which is the default configuration from Oracle 19c onward — the CREATE USER action is logged automatically. In Oracle 23ai, unified audit policies provide even more granular control over which user management events are recorded.

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;

Note that in Oracle 19c and later, the CONNECT role grants only CREATE SESSION. The RESOURCE role grants a set of object creation privileges 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;

In Oracle 23ai, ALTER USER also supports the new SCHEMA PRIVILEGES clause, which allows granting schema-level privileges directly through the user modification statement rather than as a separate grant operation.

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 values such as OPEN, LOCKED, EXPIRED, and EXPIRED & LOCKED. 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';

Oracle 23ai Enhancements to User Management

Oracle 23ai introduces several improvements relevant to user administration. Schema-level privilege grants allow a DBA to grant all privileges on all objects within a schema with a single statement, simplifying permission management for applications that own many objects. The new IF NOT EXISTS clause on CREATE USER prevents errors when a script runs against a database where the user already exists:

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

Oracle 23ai also enhances the unified audit trail with improved filtering and retention policies, making it easier to maintain compliance audit logs for user creation and modification events without excessive storage overhead.

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