Lesson 7	Creating a User
Objective	Create a Database User in Oracle 23ai

Creating a User in Oracle

Once you have determined the default tablespace, temporary tablespace, password policy, and quota allocations for a new account, you are ready to issue the CREATE USER command. This lesson walks through a complete user creation example, explains each clause, covers the relationship between users and schemas, and reviews the system privileges that govern user management operations in Oracle 19c and Oracle 23ai.

The CREATE USER Statement

The following example creates the COIN_ADMIN user with a complete set of account attributes. This is the modernized version of the classic COIN database example updated for Oracle 23ai:

CREATE USER coin_admin
  IDENTIFIED BY CoinAdmin#2024
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  PROFILE default
  PASSWORD EXPIRE
  QUOTA 5000K ON users
  QUOTA 10M ON tools;

CREATE USER coin_admin — establishes the new account with the username coin_admin.
IDENTIFIED BY CoinAdmin#2024 — sets the initial password. Oracle 23ai's default password verification function requires mixed case, a digit, and a special character. The password and username must not match.
DEFAULT TABLESPACE users — objects created without an explicit tablespace clause will be stored in the USERS tablespace.
TEMPORARY TABLESPACE temp — sort operations and hash joins that exceed PGA memory will use the TEMP tablespace.
PROFILE default — assigns Oracle's built-in default profile, which governs password complexity, expiry intervals, and session resource limits. Explicit assignment is optional since Oracle assigns the default profile automatically, but stating it here makes the configuration visible.
PASSWORD EXPIRE — forces the user to choose a new password at first login. The account cannot be used until the password is reset, ensuring the DBA-assigned credential is never retained.
QUOTA 5000K ON users — permits the user to consume up to 5,000 kilobytes of storage in the USERS tablespace.
QUOTA 10M ON tools — permits up to 10 megabytes in the TOOLS tablespace.

Note that QUOTA UNLIMITED ON temp has been removed from this example. Temporary tablespaces do not use quotas — Oracle manages temporary segment allocation automatically regardless of quota settings. Including the clause is harmless but misleading, and should be omitted from production scripts.

Oracle Cloud Infrastructure

Users and Schemas

In Oracle, a user account and a schema are two sides of the same concept. A user is the account through which someone or something connects to the database. A schema is the namespace that holds all objects — tables, indexes, sequences, views, procedures — owned by that user. The schema name is always identical to the username. When coin_admin creates a table, that table belongs to the coin_admin schema.

A user can exist without owning any objects, in which case their schema is simply empty. A schema cannot exist without a corresponding user. This one-to-one relationship between user and schema is a fundamental Oracle architecture principle that distinguishes it from some other database platforms where schemas and users are managed independently.

The schema owner has full privileges over all objects in their schema by default. They can grant access to those objects to other users or roles using the GRANT command. Other users can reference schema objects using the owner-qualified notation schema_name.object_name, subject to having been granted the appropriate privilege.

User Authentication Methods

Oracle supports three authentication methods for database users:

Database authentication — the most common method. The user's encrypted password is stored in the Oracle data dictionary. Oracle verifies the supplied password against the stored hash at login time. This is the method used in the IDENTIFIED BY clause shown above.
Operating system authentication — Oracle trusts the operating system's authentication of the connecting user. If the OS username matches an Oracle account name (adjusted by the OS_AUTHENT_PREFIX initialization parameter), Oracle grants access without requiring a separate password. This method is commonly used for DBA accounts that connect with / as sysdba on the database server itself.
Network authentication — uses external identity services such as Kerberos, RADIUS, or Public Key Infrastructure (PKI) for credential verification. PKI-based authentication uses digital certificates to verify identity without transmitting passwords. Network authentication methods require Oracle Advanced Security, available in Enterprise Edition.

Oracle 23ai extends authentication options with support for token-based authentication integrations for cloud-hosted databases, including OAuth 2.0 token validation for connections from application tiers. For on-premises deployments, database authentication with strong password policies remains the standard approach.

Always Assign a Default Tablespace

Even for users you do not expect to create objects — reporting users, read-only application accounts, monitoring users — always assign an explicit default tablespace at creation time. If the user's role changes later and they need to create objects, the default tablespace is already configured correctly. Without it, any object creation attempt will either fail or, worse, land in the SYSTEM tablespace if no database-level default has been set.

Quota Options in CREATE USER

The CREATE USER statement accepts as many QUOTA clauses as needed — one per tablespace. Three quota strategies are available:

Fixed quota — restricts the user to a specific amount of space on a tablespace. Appropriate for most users to prevent unchecked storage growth.
```
QUOTA 500M ON app_data
```
Unlimited quota on a specific tablespace — allows the user to consume all available space in that tablespace. Appropriate for schema owners.
```
QUOTA UNLIMITED ON app_data
```
UNLIMITED TABLESPACE system privilege — grants unrestricted space across every tablespace in the database, overriding all individual quota settings. This is the broadest possible storage grant and should be used sparingly. It cannot be granted to a role — only directly to a user.
```
GRANT UNLIMITED TABLESPACE TO coin_admin;
```

If the UNLIMITED TABLESPACE privilege is later revoked, the user's existing objects remain intact but no further storage allocation is permitted unless specific tablespace quotas are then assigned.

System Privileges for User Management

Creating and managing users requires specific system privileges. The following tables summarize the privileges relevant to user management and related object types.

User Management Privileges:

Privilege	Description
`CREATE USER`	Create new user accounts. Also authorizes the creator to assign tablespace quotas, set default and temporary tablespaces, and assign a profile within the same statement.
`ALTER USER`	Modify any user account. Authorizes changing another user's password or authentication method, adjusting tablespace quotas, changing default and temporary tablespaces, and assigning profiles and default roles.
`DROP USER`	Remove user accounts from the database.
`BECOME USER`	Take on the identity of another user. Required for full database import operations.

Trigger Privileges:

Privilege	Description
`CREATE TRIGGER`	Create a database trigger in the grantee's schema.
`CREATE ANY TRIGGER`	Create database triggers in any schema except SYS.
`ALTER ANY TRIGGER`	Enable, disable, or compile database triggers in any schema except SYS.
`DROP ANY TRIGGER`	Drop database triggers in any schema except SYS.
`ADMINISTER DATABASE TRIGGER`	Create a trigger on the DATABASE event. Requires `CREATE TRIGGER` or `CREATE ANY TRIGGER` in addition.

Type Privileges:

Privilege	Description
`CREATE TYPE`	Create object types and object type bodies in the grantee's schema.
`CREATE ANY TYPE`	Create object types and object type bodies in any schema except SYS.
`ALTER ANY TYPE`	Alter object types in any schema except SYS.
`DROP ANY TYPE`	Drop object types and object type bodies in any schema except SYS.
`EXECUTE ANY TYPE`	Use and reference object types and collection types in any schema except SYS, and invoke methods of an object type in any schema when granted to a specific user. When granted to a role, users holding that role cannot invoke object type methods.

View Privileges:

Privilege	Description
`CREATE VIEW`	Create views in the grantee's schema.
`CREATE ANY VIEW`	Create views in any schema except SYS.
`DROP ANY VIEW`	Drop views in any schema except SYS.

Oracle Enterprise Manager and User Creation

Oracle Enterprise Manager (OEM) Cloud Control provides a graphical interface for creating and managing users without writing SQL directly. In modern Oracle environments, the Security Management section of OEM Cloud Control exposes all CREATE USER options — tablespace assignments, quota allocations, profile selection, and password expiry — through form-based dialogs. Changes made through OEM execute the equivalent CREATE USER or ALTER USER SQL internally and are captured in the unified audit trail.

Oracle SQL Developer and Oracle SQL Developer Web (available in Oracle 23ai cloud environments) also provide user management interfaces for DBAs who prefer a lightweight tool over the full OEM stack.

Oracle 23ai: IF NOT EXISTS and Schema Privileges

Oracle 23ai introduces two enhancements relevant to user creation. The IF NOT EXISTS clause prevents errors when provisioning scripts run against databases where the account already exists:

CREATE USER IF NOT EXISTS coin_admin
  IDENTIFIED BY CoinAdmin#2024
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  PROFILE default
  PASSWORD EXPIRE
  QUOTA 5000K ON users
  QUOTA 10M ON tools;

Schema-level privilege grants allow a DBA to grant all current and future object privileges within a schema to another user in a single statement, eliminating the need to re-grant privileges each time a new table or view is added to the schema:

GRANT ALL PRIVILEGES ON SCHEMA coin_admin TO reporting_user;

This is particularly useful for read-only application users and reporting accounts that need access to all objects in a schema but should never create objects themselves.

Summary

Creating a database user in Oracle is a multi-step process that combines account definition, storage assignment, and security configuration into a single CREATE USER statement. Every production user account should specify an explicit default tablespace, temporary tablespace, profile, and appropriate quotas. Use PASSWORD EXPIRE to force credential reset at first login. Avoid granting UNLIMITED TABLESPACE as a system privilege unless absolutely necessary — prefer per-tablespace QUOTA UNLIMITED for schema owners. In Oracle 23ai, the IF NOT EXISTS clause and schema-level privilege grants simplify provisioning workflows in automated environments.

Creating Users - Quiz

Click the Quiz link below to answer a few questions about creating users.
Creating Users - Quiz

[1] Public Key Infrastructure (PKI): A framework for secure online communication and identity verification that creates and manages digital certificates, uses public-key cryptography to enable secure communication over untrusted networks, and ensures data confidentiality and integrity for authenticated connections.