Shulou(Shulou.com)06/01 Report--

How to interpret the nine states of Oracle user ACCOUNT_STATUS, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

The current state of the user recorded by ACCOUNT_STATUS in the DBA_USERS view, and the normal user in use is generally in the OPEN state.

For example, we look at the current status of the sec user, which is in the OPEN state and can be used normally.

Sys@ora10g > select username, account_status from dba_users where username = 'SEC'

USERNAME ACCOUNT_STATUS

SEC OPEN

1. Summary of all user status

What other user states are there besides the commonly used OPEN status?

This information is available through USER_ASTATUS_MAP.

Sys@ora10g > select * From USER_ASTATUS_MAP

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

9 rows selected.

The user status will not exceed the above nine.

two。 Classification of nine states

The above nine can be divided into two categories: 1. Basic state; 2. Combination status.

The first five are basic states:

0 OPEN

1 EXPIRED

2 EXPIRED (GRACE)

4 LOCKED (TIMED)

8 LOCKED

The last four are combined states:

5 EXPIRED & LOCKED (TIMED)

6 EXPIRED (GRACE) & LOCKED (TIMED)

9 EXPIRED & LOCKED

10 EXPIRED (GRACE) & LOCKED

The law goes like this: the combination of the last four states can be obtained by the state number STATUS#, for example, 10 EXPIRED 8 (10 EXPIRED (GRACE) & LOCKED = 2 EXPIRED (GRACE) + 8 LOCKED). Therefore, as long as you understand the meaning of the basic state, others can be self-taught.

3. The interpretation of the five basic states

These five basic states can be divided into three categories: 1. Normal state; 2. Locked state; 3. Password expiration status.

1) the OPEN status does not need to be explained, which means that the user is in a normal state.

2) the user is locked. Both LOCKED and LOCKED (TIMED) belong to the locked state.

Users are generally locked into two types: one is that DBA explicitly locks users through SQL statements The other is passive locking, for example, if the password is entered more than 10 times by default (this limit is controlled by FAILED_LOGIN_ATTEMPTS in PROFILE and this information can be queried through the DBA_PROFILES view), the user will be locked. For a description of passive locking, please refer to the article "[failure]" ORACLE user locked "Fault handling and Analysis" (http://space.itpub.net/519536/viewspace-608769).

(1) demonstration of explicitly locking sec user LOCKED status

Sys@ora10g > alter user sec account lock

User altered.

Sys@ora10g > select username, account_status from dba_users where username = 'SEC'

USERNAME ACCOUNT_STATUS

SEC LOCKED

(2) demonstration of passively locking LOCKED (TIMED) status after entering 10 incorrect passwords

Query the user status again after attempting to enter the wrong password 10 times.

Sys@ora10g > select username, account_status from dba_users where username = 'SEC'

USERNAME ACCOUNT_STATUS

SEC LOCKED (TIMED)

3) user password expiration status. Both EXPIRED and EXPIRED (GRACE) states belong to password expiration state.

Whether the password expires or not is achieved by modifying the PASSWORD_LIFE_TIME in the PROFILE, and the number of days after the password expires is controlled by the PASSWORD_GRACE_TIME in the PROFILE. For a description of PROFILE, please refer to the article [PROFILE] Research and Exploration of user Resource restrictions and password restrictions using Oracle's PROFILE (http://space.itpub.net/519536/viewspace-616287).

About password expiration we can also use SQL to do it explicitly, a brief demonstration.

Sys@ora10g > alter user sec password expire

User altered.

Sys@ora10g > select username, account_status from dba_users where username = 'SEC'

USERNAME ACCOUNT_STATUS

SEC EXPIRED

Sys@ora10g > conn sec/sec

ERROR:

ORA-28001: the password has expired

Changing password for sec

New password:

Retype new password:

Password changed

Connected.

Sec@ora10g >

Sec@ora10g > select username, account_status from dba_users where username = 'SEC'

USERNAME ACCOUNT_STATUS

SEC OPEN

4. The explanation of four kinds of combination state

Because there is no relationship between the two states of lock (LOCKED and LOCKED (TIMED)) and the two states of password expiration (EXPIRED and EXPIRED (GRACE)). So they can be combined arbitrarily, 2 × 2 by 4, so there are four combination states:

5 EXPIRED & LOCKED (TIMED)

6 EXPIRED (GRACE) & LOCKED (TIMED)

9 EXPIRED & LOCKED

10 EXPIRED (GRACE) & LOCKED

Explain the four combined states respectively.

1) EXPIRED & LOCKED (TIMED) status means that after the user expires, the number of incorrect password attempts exceeds the limit of FAILED_LOGIN_ATTEMPTS in PROFILE

2) EXPIRED (GRACE) & LOCKED (TIMED) status indicates that the number of unsuccessful logins of a user exceeds the limit of FAILED_LOGIN_ATTEMPTS in PROFILE during the period of validity after the password expires.

3) EXPIRED & LOCKED status means that the user is locked at the same time when it expires. Do an experiment.

Sec@ora10g > alter user sec account lock password expire

User altered.

Sec@ora10g > select username, account_status from dba_users where username = 'SEC'

USERNAME ACCOUNT_STATUS

SEC EXPIRED & LOCKED

Sec@ora10g > conn sec/sec

ERROR:

ORA-28000: the account is locked

Warning: You are no longer connected to ORACLE.

4) EXPIRED (GRACE) & LOCKED status indicates that the user is manually locked by DBA during the expiration of the password.

5. Summary

Although Oracle users have as many as nine states, which may seem complex, as long as we can clearly classify them, it will be easy to understand.

Understand the user's status from the perspectives of normal, locked and expired passwords, and everything will be natural.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.