[Translated from mos] explanation of the account_status column in The dba_users view, dbausers

[Translated from mos] explanation of the account_status column in The dba_users view, dbausers

Description of the account_status column in The dba_users View

Reference Original:
How to Interpret the ACCOUNT_STATUS Column in DBA_USERS (Doc ID 260111.1)

Applicable:
Oracle Server-Enterprise Edition-Version 9.2.0.1 to 11.2.0.3 [Release 9.2 to 11.2]
Information in this document applies to any platform.
Checked for relevance on 06-Jan-2013

Purpose:
This article explains the meaning of the account_status column in The dba_users view.

Details:

The ACCOUNT_STATUS column in The DBA_USERS view has the following values:
Select * from user_astatus_map; --> pay attention to this view. This view is more valuable.

STATUS # STATUS
------------------------------------------
0 OPEN
1 EXPIRED
2 EXPIRED (GRACE)
4 LOCKED (TIMED)
8 LOCKED
5 EXPIRED & LOCKED (TIMED)
6 EXPIRED (GRACE) & LOCKED (TIMED)
9 EXPIRED & LOCKED
10 EXPIRED (GRACE) & LOCKED

The above values are directly related to the two features 'account locking' and 'password Aging and expiration', which are explained as follows:

1. Account Locking-LOCKED/LOCKED (TIMED)

An account can be locked by dba or automatically locked after the number of failed attempts is exceeded.
When PASSWORD_LOCK_TIME is defined, the account will be automatically unlocked after this time: this situation is marked as LOCKED (TIMED ).
LOCKED (TIMED) indicates that the account is LOCKED because of the number of wrong logins> FAILED_LOGIN_ATTEMPTS
 
If this account is explicitly LOCKED during create or alter, you will only see LOCKED

You may want to set PASSWORD_LOCK_TIME to unlimited. when the account is automatically LOCKED, the account status will be displayed as LOCKED.
However, the expected status is incorrect: The account status will still display LOCKED (TIMED)
However, this account will never automatically unlock because the unlimited PASSWORD_LOCK_TIME --- that is, the lock time is unlimited (I .e., Permanent lock)

To add to the confusion, at some point it was decided that even if the account is locked automatically,
We shoshould not set it to LOCKED (TIMED) if the PASSWORD_LOCK_TIME is unlimited, since in that case the account wowould never automatically unlock,
This wowould give up on a crucial piece of information, namely if the account was locked manually or automatically, this change was introduced in 11.2.0.1.
However this change caused a regression in Bug 9693615 causing the lock_date to be NULL in dba_users in case the account was locked automatically, the fix to this bug backed out the change again and now we have the LOCKED (TIMED) for automatically locked accounts back with this fix.

Therefore, we know that when the ACCOUNT_STATUS value is LOCKED (TIMED), lock is the result of a failed login attempt, even if lock will not expire
In 11.2.0.1 (without the fix to Bug 9693615), you can verify that if the account is automatically locked, check that the lock_date column in the dba_users view is null.

2. Password Expiration-EXPIRED/EXPIRED (GRACE)

Whether grace period exists or not, a password can be set to expire. When a password expired and no grace is defined, this password is set to EXPIRED. The meaning of EXPIRED is that the user will be prompted to enter a new password upon next login.

When grace is defined, a warning is issued during grace period and ACCOUNT_STATUS is set to EXPIRED (GRACE ).

Conclusion:
Password Expiration and Account Locking are two independent features:
An account will not be locked because it exceeds expire or grace time.

An account that neither expired nor is locked is displayed as open (status # is 0), because account locking (based on the number of wrong logins) and expiration (based on the password not modified) it is basically irrelevant.

In addition to the EXPIRED (1, 2) and LOCKED (4, 8) bits being set, you can
Have combinations of both with STATUS #5, 6, 9 and 10: internally
STATUS # are added for the combinations of expired and locked.

For example:

'Expired' + 'locked' = 1 + 8 = 9 = 'expired'

 

 

 

Data in dba_tablespaces, dba_users, and user_tables in oracle

Dba_tablespaces indicates all tablespace information.

Dba_users indicates all database users.

User_tables indicates information about all tables of the current user.

There are three tables in the oracle database that involve user permissions. What are the differences between dba_users, all_users, and user_users?

Dba _ starts with all databases. all _ starts with the current user. user _ starts with the current user.