Users in the Oracle Data Dictionary

The Oracle data dictionary is the authoritative source of information about every object, user, privilege, and configuration setting in the database. After creating and configuring user accounts, DBAs use data dictionary views to verify account status, review privilege assignments, audit tablespace quota consumption, and investigate access problems. This lesson covers the primary views for user management and demonstrates the queries used most frequently in day-to-day administration.

The Three Most Tightly Linked User Management Views

Of the several views this lesson covers, three are joined most directly by the USERNAME column and are worth visualizing together:

Querying DBA_USERS

DBA_USERS is the starting point for any user account investigation. The full account profile for a single user:

SELECT username,
 account_status,
 lock_date,
 expiry_date,
 default_tablespace,
 temporary_tablespace,
 profile,
 created,
 authentication_type
FROM dba_users
WHERE username = 'COIN_ADMIN';

The ACCOUNT_STATUS column returns one of several values that describe the current state of the account, and there are more of them than a quick glance might suggest:

-- Common ACCOUNT_STATUS values
-- OPEN               -- account is active and accessible
-- LOCKED             -- DBA-applied lock (ALTER USER ACCOUNT LOCK)
-- LOCKED(TIMED)      -- automatic lock from too many failed login attempts
-- EXPIRED            -- password has expired, reset required
-- EXPIRED & LOCKED   -- both conditions apply
-- EXPIRED(GRACE)     -- within the grace period, password change required soon
-- EXPIRED(GRACE) & LOCKED(TIMED) -- combined states are also possible

To list all accounts currently locked or expired, useful for identifying accounts that need attention:

SELECT username, account_status, lock_date, expiry_date
FROM dba_users
WHERE account_status != 'OPEN'
ORDER BY account_status, username;

To find accounts whose passwords will expire within the next 14 days:

SELECT username, expiry_date,
 ROUND(expiry_date - SYSDATE) AS days_remaining
FROM dba_users
WHERE expiry_date BETWEEN SYSDATE AND SYSDATE + 14
AND account_status = 'OPEN'
ORDER BY expiry_date;

DBA_USERS includes an AUTHENTICATION_TYPE column with four confirmed values: PASSWORD for standard local authentication, EXTERNAL for accounts created with IDENTIFIED EXTERNALLY, GLOBAL for accounts created with IDENTIFIED GLOBALLY, and NONE, meaning the account has not been configured with any authentication method at all, which is exactly the state a schema-only account created with NO AUTHENTICATION ends up in. A related column, PROXY_ONLY_CONNECT, indicates whether an account can connect directly or only be reached through proxy authentication, the mechanism covered in the password management lesson earlier in this module.

Querying DBA_SYS_PRIVS

DBA_SYS_PRIVS returns system privileges granted directly to a user. Note that this view does not show privileges inherited through roles; only direct grants appear here.

List all system privileges granted to a specific user:

SELECT privilege, admin_option
FROM dba_sys_privs
WHERE grantee = 'COIN_ADMIN'
ORDER BY privilege;

Usernames in data dictionary queries must generally be specified in uppercase, since Oracle stores unquoted usernames in uppercase internally. Querying for 'coin_admin' returns no rows; 'COIN_ADMIN' returns the correct results.

The ADMIN_OPTION column is YES if the user can pass the privilege on to others. Identify users with admin option privileges; these warrant close attention in security audits:

SELECT grantee, privilege
FROM dba_sys_privs
WHERE admin_option = 'YES'
ORDER BY grantee, privilege;

To see privileges granted through roles as well as direct grants, use SESSION_PRIVS from within the user's own session, or join DBA_ROLE_PRIVS with DBA_SYS_PRIVS for a complete picture:

-- Privileges from roles assigned to a user
SELECT rp.granted_role, sp.privilege
FROM dba_role_privs rp
JOIN dba_sys_privs sp ON rp.granted_role = sp.grantee
WHERE rp.grantee = 'COIN_ADMIN'
ORDER BY rp.granted_role, sp.privilege;

Querying DBA_TS_QUOTAS

DBA_TS_QUOTAS shows how much space a user has been allocated and how much they have consumed in each tablespace:

SELECT tablespace_name,
 bytes / 1024 AS used_kb,
 max_bytes / 1024 AS quota_kb
FROM dba_ts_quotas
WHERE username = 'COIN_ADMIN'
ORDER BY tablespace_name;

A MAX_BYTES value of -1 indicates unlimited quota on that tablespace. A value of 0 means the quota has been revoked and the user cannot create new objects there. The BYTES column shows current consumption; if this approaches MAX_BYTES, a quota increase may be needed before the user encounters ORA-01536: space quota exceeded.

To identify users approaching their quota limit across all tablespaces:

SELECT username, tablespace_name,
 ROUND(bytes / 1024 / 1024, 2) AS used_mb,
 ROUND(max_bytes / 1024 / 1024, 2) AS quota_mb,
 ROUND(bytes / max_bytes * 100, 1) AS pct_used
FROM dba_ts_quotas
WHERE max_bytes > 0
AND bytes / max_bytes > 0.8
ORDER BY pct_used DESC;

Querying DBA_ROLE_PRIVS

Role assignments are stored in DBA_ROLE_PRIVS. This view shows which roles have been granted to each user:

SELECT granted_role, admin_option, default_role
FROM dba_role_privs
WHERE grantee = 'COIN_ADMIN'
ORDER BY granted_role;

The DEFAULT_ROLE column indicates whether the role is active by default when the user connects. Roles set to NO must be explicitly enabled with SET ROLE during the session.

Querying DBA_TAB_PRIVS

Object privileges, SELECT, INSERT, UPDATE, DELETE, EXECUTE and others granted on specific objects, are stored in DBA_TAB_PRIVS:

-- Privileges granted TO a user on objects owned by others
SELECT owner, table_name, privilege, grantable
FROM dba_tab_privs
WHERE grantee = 'COIN_ADMIN'
ORDER BY owner, table_name, privilege;

-- Privileges granted BY a user to others on their own objects
SELECT grantee, table_name, privilege, grantable
FROM dba_tab_privs
WHERE grantor = 'COIN_ADMIN'
ORDER BY grantee, table_name;

Querying DBA_SCHEMA_PRIVS

Schema-level privilege grants, covered earlier in this module, are visible through DBA_SCHEMA_PRIVS, which shows privileges granted at the schema level rather than on individual objects. One correction worth making directly: the column identifying which schema a privilege applies to is named SCHEMA, not OWNER; a query filtering on OWNER against this view fails with an invalid identifier error.

SELECT grantee, schema, privilege, admin_option
FROM dba_schema_privs
WHERE schema = 'COIN_ADMIN'
ORDER BY grantee, privilege;

Two further columns are worth knowing about specifically because every 26ai database is a multitenant container database: COMMON indicates whether the grant was made across every container at once, using CONTAINER=ALL, versus locally within one PDB, and INHERITED indicates whether the grant was inherited from another container rather than granted directly here. Neither question has an obvious answer without checking these columns specifically.

A related family of views is worth knowing about for privilege cleanup work: DBA_USED_SCHEMA_PRIVS and DBA_UNUSED_SCHEMA_PRIVS distinguish schema privileges that have actually been exercised from ones that were granted but never used, directly useful when deciding whether a broad schema-level grant is still earning its keep or safe to revoke.

Comprehensive User Account Report

A single query joining DBA_USERS, DBA_SYS_PRIVS, and DBA_TS_QUOTAS provides a complete account summary for audit or handover documentation:

-- Account status and tablespace configuration
SELECT u.username,
 u.account_status,
 u.default_tablespace,
 u.temporary_tablespace,
 u.profile,
 u.created,
 u.expiry_date
FROM dba_users u
WHERE u.username = 'COIN_ADMIN';

-- System privileges
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 quotas
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';

USER_ Views for Non-DBA Queries

The DBA_ prefix views require the SELECT ANY DICTIONARY system privilege or DBA role. For queries that a regular user should be able to run against their own account, Oracle provides equivalent USER_ views that automatically scope results to the current user:

-- Current user's own system privileges
SELECT privilege, admin_option
FROM user_sys_privs;

-- Current user's own tablespace quotas
SELECT tablespace_name, bytes, max_bytes
FROM user_ts_quotas;

-- Current user's own role assignments
SELECT granted_role, default_role
FROM user_role_privs;

-- Current user's own schema privileges
SELECT schema, privilege
FROM user_schema_privs;

These views are useful for application users who need to check their own access configuration without requiring DBA-level dictionary access.

Summary

The Oracle data dictionary provides complete visibility into every aspect of user account configuration. DBA_USERS is the central view for account status, tablespace assignments, and profile information, including whether an account is authenticated at all through AUTHENTICATION_TYPE. DBA_SYS_PRIVS and DBA_ROLE_PRIVS reveal the privilege structure. DBA_TS_QUOTAS tracks storage allocation and consumption. DBA_TAB_PRIVS exposes object-level access grants, and DBA_SCHEMA_PRIVS exposes schema-level ones, with COMMON and INHERITED columns worth checking in any multitenant environment. All views use uppercase username values by default and are linked through the USERNAME or GRANTEE columns.

Data Dictionary Queries - Exercise