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 Core User Management Views

Three data dictionary views form the foundation of user account reporting in Oracle. They are linked by the USERNAME column, which allows a DBA to join them for comprehensive account analysis.

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:

-- Common ACCOUNT_STATUS values
-- OPEN             — account is active and accessible
-- LOCKED           — DBA-applied lock (ALTER USER ACCOUNT LOCK)
-- EXPIRED          — password has expired, reset required
-- EXPIRED & LOCKED — both conditions apply
-- EXPIRED(GRACE)   — within the grace period, password change required soon

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;

In Oracle 23ai, DBA_USERS includes the AUTHENTICATION_TYPE column which identifies whether the account uses password authentication, external authentication, global authentication, or the new NONE value for schema-only accounts created with NO AUTHENTICATION.

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 be specified in uppercase — Oracle stores all 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;

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;

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

Oracle 23ai Data Dictionary Enhancements

Oracle 23ai adds AUTHENTICATION_TYPE to DBA_USERS with the new value NONE for schema-only accounts. DBAs can identify all schema-only accounts — accounts that own objects but cannot log in — with:

SELECT username, account_status, created
FROM dba_users
WHERE authentication_type = 'NONE'
ORDER BY username;

Schema-level privilege grants introduced in Oracle 23ai are visible through a new view DBA_SCHEMA_PRIVS, which shows privileges granted at the schema level rather than on individual objects:

SELECT grantee, owner, privilege, grantable
FROM dba_schema_privs
WHERE owner = 'COIN_ADMIN'
ORDER BY grantee, privilege;

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. 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. All views use uppercase username values and are linked through the USERNAME or GRANTEE columns. In Oracle 23ai, AUTHENTICATION_TYPE in DBA_USERS and the new DBA_SCHEMA_PRIVS view extend dictionary coverage to schema-only accounts and schema-level privilege grants.

Data Dictionary Queries - Exercise