Creating and Managing Users in Oracle — Module Summary

This module covered the complete lifecycle of an Oracle database user account — from the planning decisions that precede account creation through the SQL commands that create, modify, and remove accounts, to the data dictionary views that provide ongoing visibility into account status and privileges. The following sections summarize the key concepts from each lesson.

Planning Before You Create a User

User creation is a design decision, not a clerical task. Before issuing CREATE USER, a DBA should decide on the account's authentication method, default and temporary tablespaces, profile assignment, storage quotas, initial account status, and the privilege model that will govern what the user can do. Accounts designed correctly from the outset are easier to administer, more secure, and less likely to accumulate privilege sprawl over time.

Oracle 23ai reinforces this principle through schema-only accounts — accounts created with NO AUTHENTICATION that own objects but can never be used for interactive login. This pattern separates object ownership from interactive access, a distinction that simplifies both security architecture and auditing.

The Three Core User Management Commands

Oracle manages user accounts through three DDL statements that cover the full account lifecycle:

-- Create a new account
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;

-- Modify an existing account
ALTER USER coin_admin
  QUOTA 101M ON users
  ACCOUNT UNLOCK;

-- Remove an account and all its objects
DROP USER coin_admin CASCADE;

CREATE USER establishes the account, assigns the default profile, and creates the associated schema. ALTER USER handles all post-creation modifications without affecting schema objects or privilege assignments. DROP USER CASCADE permanently removes the account and all owned objects — this operation is irreversible and requires a schema backup beforehand in production environments.

Tablespaces and Quotas

Every user requires two tablespace assignments: a default permanent tablespace for schema objects and a temporary tablespace for sort and hash join operations. Neither should be left to the SYSTEM tablespace fallback — placing user data or temporary operations on SYSTEM creates disk contention that degrades performance for all concurrent sessions.

Assigning a default tablespace does not grant any storage within it. Quotas must be granted separately. Use fixed quotas for most users, QUOTA UNLIMITED on specific tablespaces for schema owners, and QUOTA 0 to freeze storage growth without removing existing objects. Temporary tablespaces require no quota — Oracle manages temporary segment allocation automatically.

Authentication and the OPS$ Deprecation

Operating system authentication through OPS$ accounts was deprecated in Oracle 12c Release 2 and is unavailable in Oracle 19c and 23ai. Modern replacements include Oracle Wallet for automated script authentication, password files for privileged remote connections, Centrally Managed Users for Active Directory integration, Kerberos for enterprise SSO, and schema-only accounts for application schemas that should never accept interactive logins. Each replacement is more secure than the mechanism it supersedes.

Granting Privileges and Managing Access

A newly created user has an empty privilege domain. The minimum grant required for any database interaction is CREATE SESSION:

GRANT CREATE SESSION TO coin_admin;

Beyond connection access, privileges should be granted based on the principle of least privilege — only what the user's role genuinely requires. Oracle roles bundle related privileges into reusable collections, reducing administrative overhead and the risk of privilege sprawl. The WITH ADMIN OPTION and WITH GRANT OPTION clauses allow privilege delegation and should be assigned sparingly. Privileges are removed with REVOKE. Note that revoking a system privilege does not cascade to downstream grants made by the revoked user — each must be revoked individually.

Oracle 23ai schema-level privilege grants allow all current and future object privileges within a schema to be granted in a single statement, eliminating the need to re-grant privileges each time a new object is added to the schema.

Post-Creation Account Management

ALTER USER is the correct tool for all post-creation modifications. Password resets, tablespace reassignments, quota adjustments, profile changes, account locking, and password expiry are all handled through this single command without disrupting the user's schema objects or privilege assignments. Dropping and recreating an account to change one attribute is destructive and unnecessary — ALTER USER modifies only what is specified and leaves everything else intact.

For password management, three methods cover different scenarios: ALTER USER IDENTIFIED BY for DBA-driven resets, ALTER USER PASSWORD EXPIRE to force user-driven resets, and orapwd for the SYS account specifically. Profile parameters PASSWORD_LIFE_TIME, PASSWORD_GRACE_TIME, PASSWORD_REUSE_TIME, and PASSWORD_REUSE_MAX automate enforcement. Use proxy authentication rather than temporary password changes when connecting as another user for troubleshooting.

Account locking is the standard approach for temporary access suspension. It is immediate, fully reversible, and preserves all account attributes. Locking does not terminate active sessions — those must be killed explicitly via ALTER SYSTEM KILL SESSION if immediate disconnection is required.

Removing Users Safely

Before dropping a user in production, verify three things: the schema has been exported with Oracle Data Pump, no other schemas have dependencies on the user's objects via DBA_DEPENDENCIES, and the user has no active sessions in V$SESSION. DROP USER CASCADE removes the account and all owned objects in a single irreversible DDL operation. Schema owners of large partitioned objects or LOB-heavy tables may require extended processing time during cascade drops.

Monitoring Users Through the Data Dictionary

Four data dictionary views provide complete visibility into user account configuration:

-- Account status, tablespace assignments, profile, creation date
SELECT username, account_status, default_tablespace,
       temporary_tablespace, profile, created, expiry_date
FROM dba_users
WHERE username = 'COIN_ADMIN';

-- Direct system privilege grants
SELECT privilege, admin_option
FROM dba_sys_privs
WHERE grantee = 'COIN_ADMIN';

-- Role assignments
SELECT granted_role, default_role
FROM dba_role_privs
WHERE grantee = 'COIN_ADMIN';

-- Tablespace quota allocations and consumption
SELECT tablespace_name,
  bytes / 1024 AS used_kb,
  DECODE(max_bytes, -1, 'UNLIMITED', TO_CHAR(max_bytes / 1024)) AS quota_kb
FROM dba_ts_quotas
WHERE username = 'COIN_ADMIN';

In Oracle 23ai, DBA_USERS gains the AUTHENTICATION_TYPE column identifying schema-only accounts, and the new DBA_SCHEMA_PRIVS view exposes schema-level privilege grants. All data dictionary username references must use uppercase — Oracle stores all usernames in uppercase internally.

Auditing User Management Operations

Oracle Unified Auditing, enabled by default from Oracle 19c onward, captures all user management events. A single audit policy covers the complete lifecycle:

CREATE AUDIT POLICY user_lifecycle_policy
  ACTIONS CREATE USER, ALTER USER, DROP USER, GRANT, REVOKE;

AUDIT POLICY user_lifecycle_policy;

Audit records are queryable from UNIFIED_AUDIT_TRAIL and support compliance requirements under SOX, HIPAA, and PCI-DSS. In Oracle 23ai, improved audit policy filtering and retention controls reduce storage overhead without sacrificing compliance coverage.

Users Data Dictionary - Quiz