Managing Users in Oracle AI Database 26ai

Oracle Database provides three core SQL commands for managing user accounts throughout their lifecycle. These commands cover every stage from initial account creation through ongoing administration to permanent removal. Understanding when and how to use each command is a foundational skill for any Oracle DBA.

CREATE USER

The CREATE USER statement establishes a new database account. You must hold the CREATE USER system privilege to execute it. The command defines the username, the authentication method, and optionally the default tablespace, temporary tablespace, storage quotas, and profile. Lesson 1 covers CREATE USER's full syntax in depth, including the current authentication options and the CDB-scoping CONTAINER clause; this lesson focuses on where it fits alongside ALTER USER and DROP USER in the account lifecycle.

CREATE USER app_user IDENTIFIED BY SecurePass#2024
 DEFAULT TABLESPACE users
 TEMPORARY TABLESPACE temp
 QUOTA UNLIMITED ON users
 QUOTA 200M ON app_data;

GRANT CREATE SESSION TO app_user;

A newly created user has an empty privilege domain and cannot connect until CREATE SESSION is granted, exactly as shown above.

ALTER USER

The ALTER USER statement modifies any attribute of an existing user account. You must hold the ALTER USER system privilege, with one exception: users may change their own password without requiring the privilege.

Changing a Password

ALTER USER app_user IDENTIFIED BY NewSecurePass#2025;

To force the user to set their own password at next login without specifying it yourself, expire the current password:

ALTER USER app_user PASSWORD EXPIRE;

When a password is expired, Oracle prompts the user to choose a new one upon connection. The user cannot access the database until the reset is completed. This approach is commonly used during account provisioning so that users establish their own credentials before first use.

Locking and Unlocking an Account

ALTER USER app_user ACCOUNT LOCK;

The account lock is immediate. All privileges, schema objects, and password settings remain intact. Existing active sessions are not terminated by the lock; they remain open until they disconnect or are killed explicitly. To restore access:

ALTER USER app_user ACCOUNT UNLOCK;

Account locking is the recommended approach for handling employee leaves, security investigations, and maintenance windows. It avoids the overhead of dropping and recreating an account along with all of its privilege assignments.

Modifying Tablespace and Profile Assignments

ALTER USER app_user DEFAULT TABLESPACE new_tablespace;
ALTER USER app_user QUOTA 500M ON users;
ALTER USER app_user PROFILE developer_profile;

A correction worth making plainly, since it circulated in earlier material: schema-level privilege grants, letting a DBA grant a broad set of privileges across every object in a schema in one statement, are real and current, but they are granted through GRANT, not through any special clause of ALTER USER:

GRANT SELECT ANY TABLE ON SCHEMA app_data TO app_user;

DROP USER

The DROP USER statement permanently removes a user account from the database. You must hold the DROP USER system privilege. This operation is a DDL statement and takes effect immediately; it cannot be rolled back.

If the user owns no schema objects, a simple drop is sufficient:

DROP USER app_user;

If the user owns any tables, views, indexes, sequences, or other objects, Oracle returns an error unless the CASCADE option is included:

DROP USER app_user CASCADE;

An IF EXISTS clause is also available, the DROP-side counterpart to CREATE USER's IF NOT EXISTS, letting a cleanup script skip the statement quietly rather than error out if the user has already been removed:

DROP USER IF EXISTS app_user CASCADE;

One restriction worth knowing before you attempt this: you cannot drop a user whose schema contains a table using a flashback data archive for historical tracking. You must disable that table's flashback data archive use first.

What CASCADE Actually Does, and Does Not Do

CASCADE's effects on objects in other schemas are more specific, and in one important respect more limited, than "everything related gets cleaned up." It is worth knowing exactly what happens, since assuming too much here is a real way to get surprised later:

• Foreign keys elsewhere are dropped, not just disabled. If a table in another schema has a foreign key referencing a primary or unique key on one of the dropped user's tables, that constraint is automatically dropped along with the table it referenced.
• Views, synonyms, and PL/SQL in other schemas are invalidated, not dropped. A view, synonym, stored procedure, function, or package in a different schema that references one of the dropped user's objects is not removed. It becomes invalid, and stays that way until someone drops it manually or recreates whatever it depended on. This is the detail most likely to catch a DBA off guard: CASCADE cleans up the dropped user's own schema thoroughly, but leaves broken references sitting in other schemas rather than removing them.
• Materialized views elsewhere survive, but cannot refresh again. The base tables are gone, so any materialized view built on them in another schema becomes permanently stale, without being dropped itself.
• Roles created by the dropped user are not removed. Only the user's own schema objects and their privilege grants are affected.
• Within the dropped user's own schema, CASCADE drops all triggers, drops all domains with FORCE, and drops all types owned by the user with FORCE as well.

Considerations Before Dropping a User

Because DROP USER CASCADE is irreversible, and because of the invalidation behavior above, several precautions are worth taking before executing it in a production environment:

1. Back up the schema first. Use Oracle Data Pump to export the user's schema objects before dropping, providing a recovery path if the removal was unintended:

   expdp system/password SCHEMAS=app_user DIRECTORY=backup_dir DUMPFILE=app_user.dmp;

2. Check for objects that will be invalidated, not dropped. Query DBA_DEPENDENCIES to identify views, synonyms, and PL/SQL objects in other schemas that reference objects owned by the user being dropped; these are exactly the objects CASCADE leaves behind in a broken state rather than removing:

   SELECT owner, name, type
   FROM dba_dependencies
   WHERE referenced_owner = 'APP_USER';

   This query does not, however, surface foreign-key relationships. Those are a separate concern covered next.
3. Check for foreign keys that will be silently dropped. DBA_DEPENDENCIES does not track table-to-table foreign key relationships at all; those live in the constraint views instead. To see which constraints in other schemas will be dropped along with the user's tables:

   SELECT c.owner, c.constraint_name, c.table_name
   FROM dba_constraints c
   WHERE c.constraint_type = 'R'
   AND c.r_constraint_name IN (
    SELECT constraint_name
    FROM dba_constraints
    WHERE owner = 'APP_USER'
    AND constraint_type IN ('P', 'U')
   );

   This is the query that actually answers "what foreign keys elsewhere depend on this user's tables," which the dependency-view check above cannot answer on its own.
4. Verify active sessions. Confirm the user has no open sessions before dropping. Dropping a user with active sessions succeeds, but can cause in-flight transactions to fail abruptly:

   SELECT sid, serial#, status
   FROM v$session
   WHERE username = 'APP_USER';

5. Large object consideration. If the user owns tables with LOB columns or large partitioned objects, the cascade drop may take considerable time as Oracle deallocates storage segment by segment.

Querying User Information from the Data Dictionary

Oracle maintains a comprehensive set of data dictionary views for monitoring and auditing user accounts. The primary view is DBA_USERS:

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

To review system privileges granted directly to a user:

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

To review role assignments:

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

To review object privileges granted to a user:

SELECT owner, table_name, privilege, grantable
FROM dba_tab_privs
WHERE grantee = 'APP_USER';

Auditing User Management Operations

Unified auditing captures user management events, and it is worth being precise about its status rather than describing it as simply the preferred default: starting with Oracle AI Database 26ai, traditional auditing is fully desupported. You cannot create new traditional audit settings or modify existing ones at all; unified auditing is the only mechanism available for any new auditing work. To audit all CREATE USER, ALTER USER, and DROP USER operations:

CREATE AUDIT POLICY user_mgmt_policy
 ACTIONS CREATE USER, ALTER USER, DROP USER;

AUDIT POLICY user_mgmt_policy;

An audit policy can also be scoped explicitly to a container with a CONTAINER clause, specifying either CURRENT for the container you are connected to or ALL to apply the policy across every container, the same CDB-scope question that comes up throughout this course whenever you configure something at the database level rather than the session level.

Audit records are written to the unified audit trail and queried from UNIFIED_AUDIT_TRAIL. One correction worth making directly: the username column on this view is DBUSERNAME, with no underscore between DB and USERNAME, not DB_USERNAME as sometimes shown; a query using the underscored form fails with an invalid identifier error.

SELECT event_timestamp, dbusername, action_name, return_code
FROM unified_audit_trail
WHERE action_name IN ('CREATE USER', 'ALTER USER', 'DROP USER')
ORDER BY event_timestamp DESC;

Summary

The three commands that govern the Oracle user account lifecycle are CREATE USER, ALTER USER, and DROP USER. CREATE USER establishes the account with its authentication method, tablespace assignments, and profile. ALTER USER handles ongoing modifications including password changes and account locking, while schema-level privilege grants specifically go through GRANT. DROP USER CASCADE permanently removes the account and its own schema objects, but leaves dependent views, synonyms, and PL/SQL in other schemas invalidated rather than dropped, which is worth checking for before you run it rather than after. Each operation should be accompanied by unified auditing, now the only auditing mechanism available in Oracle AI Database 26ai, to maintain a complete record of user management activity for security and compliance purposes.