Creating a User in Oracle AI Database 26ai

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.

The CREATE USER Statement

The following example creates the COIN_ADMIN user with a complete set of account attributes:

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;

Note that no quota clause appears for the temporary tablespace. Temporary tablespaces do not use quotas at all; Oracle manages temporary segment allocation automatically regardless of quota settings, so including one would be harmless but misleading, and should be omitted from production scripts.

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 broad categories of authentication for database users, plus a growing set of cloud-identity options layered on top of them:

1. 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.
2. 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.
3. 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.

Layered on top of these, current Oracle releases support genuinely modern cloud-identity authentication: OAuth 2.0 token-based authentication against Microsoft Entra ID, the current name for what was previously called Azure Active Directory, and against Oracle Cloud Infrastructure IAM. Rather than storing a database-specific password at all, the database trusts a token issued by the identity provider your organization already uses elsewhere. For on-premises deployments without that kind of centralized identity infrastructure, 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 container-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:

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

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

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

A precise, easy-to-misstate rule worth stating carefully: CREATE TYPE and CREATE ANY TYPE can be satisfied through a role, granted directly or inherited from a role, no difference. But when a type's own definition references other types, the type's owner must have been granted EXECUTE on those referenced types, or EXECUTE ANY TYPE, directly; a role membership does not satisfy this specific requirement. The same restriction applies to creating a table that uses types: the table owner needs direct EXECUTE access to the referenced types, not access obtained through a role. This is a narrower rule than "role grants of EXECUTE ANY TYPE never work"; it applies specifically to the owner at creation time for these two operations.

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. 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, the only auditing mechanism current Oracle releases support.

Oracle SQL Developer and Oracle SQL Developer Web also provide user management interfaces for DBAs who prefer a lightweight tool over the full OEM stack.

IF NOT EXISTS and Schema Privileges

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 privileges across every object 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. Beyond WITH ADMIN OPTION, which lets the grantee further grant the same privilege to others, schema-level grants also support WITH DELEGATE OPTION, a related but distinct way of allowing the grantee to delegate the privilege onward; the two options serve different administrative delegation models and are worth distinguishing rather than treating as interchangeable.

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. The IF NOT EXISTS clause and schema-level privilege grants simplify provisioning workflows in automated environments, and modern cloud-identity authentication against Microsoft Entra ID or OCI IAM is worth evaluating for organizations that already manage identity centrally elsewhere.

Creating Users - Quiz