DBA_USERS - Query Oracle User Accounts & Status

DBA_USERS

Overview

DBA_USERS is the authoritative source for every database account defined in an Oracle instance or CDB. It exposes account status, default and temporary tablespace assignments, the password profile applied to the account, the authentication method, and the date the account was created or locked. DBAs consult it daily for user provisioning, security audits, expired or locked account investigations, and compliance reporting. In a multitenant environment it covers all local users within the current PDB as well as common users whose accounts exist in the root container.

View Type: Data Dictionary View Available Since: Oracle 7 Required Privileges: SELECT on DBA_USERS or SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY

Key Columns

Column	Datatype	Description
USERNAME	VARCHAR2(128)	Database account name
USER_ID	NUMBER	Internal numeric identifier for the account
ACCOUNT_STATUS	VARCHAR2(32)	OPEN, EXPIRED, EXPIRED(GRACE), LOCKED, EXPIRED & LOCKED, or variants thereof
LOCK_DATE	DATE	Date the account was locked; NULL if not locked
EXPIRY_DATE	DATE	Date the password expires or expired
DEFAULT_TABLESPACE	VARCHAR2(30)	Tablespace used for permanent objects created without an explicit tablespace clause
TEMPORARY_TABLESPACE	VARCHAR2(30)	Tablespace used for sort and temporary segments
CREATED	DATE	Date and time the account was created
PROFILE	VARCHAR2(128)	Name of the password and resource profile assigned to the account
INITIAL_RSRC_CONSUMER_GROUP	VARCHAR2(128)	Default Resource Manager consumer group
EXTERNAL_NAME	VARCHAR2(4000)	External identifier for externally authenticated accounts (e.g., LDAP DN)
PASSWORD_VERSIONS	VARCHAR2(17)	Hash algorithm versions stored: 10G, 11G, 12C
EDITIONS_ENABLED	VARCHAR2(1)	Y if editions are enabled for this user
AUTHENTICATION_TYPE	VARCHAR2(8)	PASSWORD, EXTERNAL, GLOBAL, or NONE
PROXY_ONLY_CONNECT	VARCHAR2(1)	Y if the account can only connect through a proxy
COMMON	VARCHAR2(3)	YES if common user (CDB), NO if local user
LAST_LOGIN	TIMESTAMP WITH TIME ZONE	Timestamp of the most recent successful login (12.2+)
ORACLE_MAINTAINED	VARCHAR2(1)	Y if the account was created and maintained by Oracle-supplied scripts

Essential Queries

Basic Usage

List all non-Oracle-maintained user accounts with their status, profile, and tablespace assignments:

SELECT
    username,
    account_status,
    profile,
    default_tablespace,
    temporary_tablespace,
    created,
    lock_date,
    expiry_date,
    authentication_type
FROM
    dba_users
WHERE
    oracle_maintained = 'N'
ORDER BY
    username;

Monitoring Query

Identify accounts that are locked, expired, or approaching password expiry — essential for pre-maintenance security checks:

SELECT
    username,
    account_status,
    profile,
    created,
    lock_date,
    expiry_date,
    CASE
        WHEN account_status = 'OPEN'
             AND expiry_date IS NOT NULL
             AND expiry_date - SYSDATE <= 14
        THEN 'EXPIRING SOON'
        WHEN account_status LIKE '%EXPIRED%' THEN 'EXPIRED'
        WHEN account_status LIKE '%LOCKED%'  THEN 'LOCKED'
        ELSE account_status
    END                                         AS effective_status
FROM
    dba_users
WHERE
    oracle_maintained = 'N'
    AND account_status != 'OPEN'
       OR (account_status = 'OPEN'
           AND expiry_date IS NOT NULL
           AND expiry_date - SYSDATE <= 14)
ORDER BY
    expiry_date NULLS LAST,
    lock_date   NULLS LAST;

Combined with Other Views

Join DBA_USERS with DBA_PROFILES to show the effective password lifetime for every open account:

SELECT
    u.username,
    u.account_status,
    u.profile,
    u.created,
    u.expiry_date,
    p.limit                                     AS password_life_days,
    ROUND(u.expiry_date - SYSDATE, 0)           AS days_until_expiry
FROM
    dba_users   u
    JOIN dba_profiles p
        ON  p.profile   = u.profile
        AND p.resource_name = 'PASSWORD_LIFE_TIME'
        AND p.resource_type = 'PASSWORD'
WHERE
    u.oracle_maintained = 'N'
    AND u.account_status = 'OPEN'
ORDER BY
    days_until_expiry NULLS LAST;

Security Audit Query

Find accounts with excessive or missing controls: no profile other than DEFAULT, PASSWORD authentication storing legacy 10G hashes, or accounts never logged in since creation:

SELECT
    username,
    account_status,
    profile,
    authentication_type,
    password_versions,
    created,
    last_login,
    CASE
        WHEN profile = 'DEFAULT'                 THEN 'Using DEFAULT profile'
        WHEN password_versions LIKE '%10G%'      THEN 'Legacy 10G hash present'
        WHEN last_login IS NULL
             AND created < SYSDATE - 90          THEN 'Never logged in (90+ days)'
        ELSE 'OK'
    END                                         AS finding
FROM
    dba_users
WHERE
    oracle_maintained = 'N'
    AND account_status = 'OPEN'
    AND (
        profile = 'DEFAULT'
        OR password_versions LIKE '%10G%'
        OR (last_login IS NULL AND created < SYSDATE - 90)
    )
ORDER BY
    finding,
    username;

Account Activity Summary

Count accounts by status and profile to produce a high-level inventory for compliance reporting:

SELECT
    profile,
    account_status,
    authentication_type,
    COUNT(*)                                    AS account_count,
    MIN(created)                                AS earliest_created,
    MAX(created)                                AS latest_created,
    SUM(CASE WHEN last_login IS NULL THEN 1 ELSE 0 END) AS never_logged_in
FROM
    dba_users
WHERE
    oracle_maintained = 'N'
GROUP BY
    profile,
    account_status,
    authentication_type
ORDER BY
    account_count DESC;

Common Use Cases

• Pre-upgrade account audit — Enumerate all non-Oracle accounts, their statuses, and profiles before a major upgrade to ensure no accounts will block post-upgrade connectivity
• Compliance reporting — Generate evidence for security audits by listing all OPEN accounts, their authentication type, and profile, confirming no accounts use the DEFAULT profile in production
• Password expiry management — Proactively find accounts whose passwords expire within 14 days to coordinate password resets with application teams before outage
• Dormant account cleanup — Identify accounts that have never logged in or have not logged in for 90+ days as candidates for locking or dropping
• Tablespace assignment review — Verify that no application accounts have SYSTEM or SYSAUX as their default tablespace, which can cause objects to land in the wrong location
• Multitenant user scoping — In a CDB, distinguish between common users (COMMON = YES) and local PDB users to understand where privilege changes must be applied

Related Views

• DBA_PROFILES — Password and resource limits for the profiles assigned to accounts in DBA_USERS
• DBA_ROLE_PRIVS — Roles granted to each user account
• DBA_SYS_PRIVS — System privileges granted directly to each user account
• DBA_TAB_PRIVS — Object-level grants made to each user account
• DBA_TS_QUOTAS — Tablespace quotas assigned to each user; complement to the tablespace columns in DBA_USERS
• V$SESSION — Shows currently connected sessions; join on USERNAME to correlate live connections with account metadata

Version Notes

• Oracle 10g: EDITIONS_ENABLED and EXTERNAL_NAME columns added
• Oracle 11g: PASSWORD_VERSIONS column introduced, exposing which password hash algorithms are stored (10G, 11G)
• Oracle 12c: COMMON, CON_ID, ORACLE_MAINTAINED, IMPLICIT, and ALL_SHARD columns added for multitenant and sharding support; PASSWORD_VERSIONS extended to include 12C (SHA-2 based)
• Oracle 12.2: LAST_LOGIN (TIMESTAMP WITH TIME ZONE) added, enabling dormant account detection without querying audit trails
• Oracle 19c: No structural changes; MOS Note 2592561.1 describes behaviour of ORACLE_MAINTAINED accounts after patching
• Oracle 23ai: Additional columns for schema-only accounts (no password required for schema-only schemas created with CREATE USER … NO AUTHENTICATION)