3.3 How do I…Summarize basic user account information?

Problem

I need to see information on each user in the database, including the date the account was created, the date and time that the password will expire, the date that the account was locked, the default profile, and the default and temporary tablespaces. How do I summarize basic user account information?

Technique

The DBA_USERS view contains information on all accounts. By querying the DBA_USERS view, you can obtain various attributes of each user account.

Steps

1. Run SQL*Plus and connect as the WAITE user account. Run the CHP3_4.sql script, shown in Figure 3.3. By executing the script, you will see basic user account information for each user account of the Oracle database. Figure 3.4 shows a sample output from the CHP3_4.sql script.

How It Works

The CHP3_4.sql script queries the DBA_USERS view, reporting on user account information.

For the DBA_USERS view, the USERNAME column contains the username of the account.

The ACCOUNT_STATUS column shows whether an account is OPEN, LOCKED, or EXPIRED. An OPEN account enables the user to log on to the database. A LOCKED account prevents the user from logging on to the database until the lock is removed by a database administrator. An account becomes locked either manually by the database administrator or when a user enters an incorrect password more times than the defined limit allows. An EXPIRED account will not enable a user to log on to the database until the password is changed. An account is set to EXPIRED when the password for the user account goes for a defined amount of time without changing.

The LOCK_DATE column contains the date and time that the user has been locked out of the database. A null value indicates that the user has not been locked out.

The EXPIRY_DATE column contains the date and time that the password for the user account will expire. The user must change the password before the EXPIRY_DATE, or he or she will not be able to connect to the database. This column is derived from adding the date and time that the password was last changed to the number of days required before a password change must occur.

The DEFAULT_TABLESPACE column contains the tablespace that all objects created by the user account will be placed in, unless otherwise specified.

The TEMPORARY_TABLESPACE column contains the tablespace that will be used to create temporary sorting tables. These temporary tables are created when a user issues a statement with a GROUP BY, ORDER BY, or another group function and the sort area for the user is not large enough to process the statement.

The CREATED column contains the date and time that the user account was created.

The PROFILE column contains the profile to which the user account is assigned. The DEFAULT profile is used by all users unless defined otherwise.

Comments

The PASSWORD column, not included in the CHP3-4.sql script, contains the encoded password for the user account. It is impossible to decode the password, but it can be set in another user account. This is done by using the VALUES clause of the ALTER USER username IDENTIFIED BY command.

In the CHP3_4.sql script, only the date portion of the date fields are being returned. You can also retrieve the time portion using TO_CHAR(EXPIRY_DATE, ‘DD-MON-YY HH:MI:SS’) or TO_CHAR(CREATE_DATE, ‘DD-MON-YY HH:MI:SS’). For the majority of cases, the time is not needed.

The DEFAULT_TABLESPACE should not be SYSTEM, except for the SYS user. If the DEFAULT_TABLESPACE is set to SYSTEM for a user account, any object created by that user without specifying a tablespace will be created in SYSTEM. Only vital data dictionary objects should be placed in the SYSTEM tablespace.