Oracle dba_users: List All User Account Information

calendar Dec 31, 2023 / · 8 min read · users Oracle dba_users all_users  ·
Share on: linkedin copy

Oracle dba_users: List All User Account Information

Purpose

DBA_USERS is the data dictionary view that describes all users of the database. Each row is one account. The row shows the account status, the creation date, the default tablespace and the temporary tablespace. Other columns show the profile, the password expiry date and, in newer releases, the last login time.

The query below reads that view and prints one line per account, sorted by name. A DBA runs it to see who has an account, which accounts are locked or expired, and where each account stores its data. It is a good first query on a database you do not know yet.

The view lists accounts, not connections. It tells you that an account exists and what state it is in. It does not tell you who is logged in at this moment. Use the session views for that question.

Code

 1set pages 999 lines 100
 2col username format a20
 3col status format a8
 4col tablespace format a20
 5col temp_ts format a20
 6select username
 7, account_status status
 8, created
 9, default_tablespace tablespace
10, temporary_tablespace temp_ts
11from dba_users
12order by username
13/

A second query narrows the list to accounts that people created. It hides the accounts that Oracle-supplied scripts created, and it adds the profile and the password expiry date.

 1set pages 999 lines 120
 2col username format a25
 3col account_status format a20
 4col profile format a20
 5select username
 6, account_status
 7, profile
 8, expiry_date
 9, lock_date
10from dba_users
11where oracle_maintained = 'N'
12order by username
13/

A third query uses ALL_USERS. This view is the one a user without access to DBA_USERS can read. It shows fewer columns.

1select username
2, user_id
3, created
4, common
5from all_users
6order by username
7/

Code Breakdown

SQL*Plus formatting lines

  • set pages 999 lines 100 sets two things. pages is the page size: the number of lines on each page of output. lines is the line size: the total number of characters SQL*Plus shows on one line before it starts a new line. A page size of 999 means the column headings repeat less often.
  • col username format a20 sets the display width of the username column to 20 characters. The a stands for alphabetic. The same pattern sets the width of status, tablespace and temp_ts.
  • If a width is shorter than the column heading, SQL*Plus cuts the heading. The first query uses a narrow width of 8 for status. Longer values such as EXPIRED & LOCKED need more room, so the second query uses a wider column.

The select list of the first query

  • username is the name of the user.
  • account_status status returns the account status and gives the column the short alias status.
  • created is the date the user was created.
  • default_tablespace tablespace is the default tablespace for the data of this user.
  • temporary_tablespace temp_ts is the name of the default tablespace for temporary tables. It can also be the name of a tablespace group.
  • order by username sorts the result by name so you can find an account fast.

The second query

  • where oracle_maintained = 'N' keeps only the users that Oracle-supplied scripts did not create. The column is Y for a user that Oracle scripts created and maintain.
  • profile shows the resource profile of the user. The profile holds the password rules, so it explains why an account locks or expires.
  • expiry_date is the date the account expires. lock_date is the date the account was locked, if the status was LOCKED.

The third query

  • all_users lists all users of the database that are visible to the current user. Compare this with dba_users, which describes all users.
  • common shows YES for a common user and NO for a local user. This matters in a multitenant database.

Key Points

  • DBA_USERS has more columns than the first query shows. Useful ones are USER_ID, PROFILE, EXPIRY_DATE, LOCK_DATE, AUTHENTICATION_TYPE, COMMON, ORACLE_MAINTAINED and LAST_LOGIN.
  • ACCOUNT_STATUS holds more than three values. The simple ones are OPEN, EXPIRED, EXPIRED(GRACE), LOCKED and LOCKED(TIMED). Combined values also exist, such as EXPIRED & LOCKED.
  • LAST_LOGIN is not filled when a user connects with an administrative privilege such as SYSDBA, SYSOPER or SYSBACKUP. A blank value does not prove that the account was never used.
  • ORACLE_MAINTAINED marks accounts that Oracle-supplied scripts created. The documentation says such a user must not be changed in any way except by running an Oracle-supplied script.
  • In a multitenant database, a common user should connect to the CDB root when it queries DBA_USERS. This makes sure the result is complete.
  • The PASSWORD column is deprecated. Use AUTHENTICATION_TYPE to see how an account logs in.

Insights and Best Practices

Read the account status correctly

The status tells you why a user cannot log in. Use this table when you read the output.

  • OPEN means the account is open.
  • EXPIRED means the password is expired. Either the PASSWORD_LIFE_TIME limit was reached, or someone ran ALTER USER ... PASSWORD EXPIRE.
  • EXPIRED(GRACE) means the PASSWORD_LIFE_TIME limit was reached, but the PASSWORD_GRACE_TIME period has not ended yet.
  • LOCKED means the account is locked. Either someone ran ALTER USER ... ACCOUNT LOCK, or failed logins went over FAILED_LOGIN_ATTEMPTS while PASSWORD_LOCK_TIME is UNLIMITED.
  • LOCKED(TIMED) means the account is locked because of failed logins, and the PASSWORD_LOCK_TIME period has not ended yet.
  • OPEN & IN ROLLOVER means the account is in the password rollover period. The user can log in with the earlier password or the new password.

Do not treat all of these the same way. LOCKED(TIMED) is tied to the PASSWORD_LOCK_TIME period. LOCKED has no lock period that runs out: either a DBA locked the account, or PASSWORD_LOCK_TIME is UNLIMITED.

Separate your accounts from Oracle accounts

Oracle-supplied scripts create some accounts for you. Filter with oracle_maintained = 'N' when you review accounts for an audit. Use oracle_maintained = 'Y' when you want to confirm that Oracle accounts stay locked and unchanged. Do not alter these accounts by hand.

Check how each account authenticates

The AUTHENTICATION_TYPE column shows the method. PASSWORD means the user was created with IDENTIFIED BY. EXTERNAL means IDENTIFIED EXTERNALLY. GLOBAL means IDENTIFIED GLOBALLY. NONE means no method is set for the user. Add this column when you want to find accounts that use a password and accounts that do not.

Also look at PASSWORD_VERSIONS. It lists the password hash versions stored for the account, such as 10G, 11G and 12C. An account that keeps only an old verifier may need a password change.

Use the multitenant columns

In a multitenant database, COMMON tells you if a user is common or local. INHERITED tells you if the user definition came from another container. When you work in a pluggable database, check which container your session is in before you trust the result. A common user should query from the CDB root to get complete information.

Keep the report simple

Spool the result to a file with a date in the name and keep it. Two reports from different weeks show you new accounts, removed accounts and status changes. A simple text comparison is enough.

When to Use This Check

  • At the start of work on a database that is new to you
  • Before an audit, to list every account and its profile
  • When a user reports that a login fails, to see if the account is locked or expired
  • Before you drop a tablespace, to find the users that name it as default or temporary
  • After an application release, to find new accounts
  • When you plan a password policy change and need the list of profiles in use

Troubleshooting Common Issues

If the query returns an error that the table or view does not exist, your account cannot read DBA_USERS. Run the third query against ALL_USERS instead. It lists the users that are visible to you. You can also ask the DBA team for access.

If the output looks cut or wrapped, change the lines value or the col ... format widths. A status such as EXPIRED(GRACE) & LOCKED is longer than eight characters, so the first query hides part of it. Use a wider column when you need the full value.

If LAST_LOGIN is empty for an account you know is used, check how the user connects. The column is not filled for logins with an administrative privilege. If you see fewer users than you expect in a multitenant database, check the container you are connected to. Connect to the CDB root as a common user for the complete list.

If a user is locked and you want to open it, the status tells you the cause. A user locked after failed logins needs the cause fixed first. Check the profile limits FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME before you unlock.

References

Posts in this series