DBA_USERS In the view ACCOUNT_STATUS The current state of the recorded user , In general, normal users are in OPEN state .
So let’s look at sec Current status of the user , At this point, the user is in OPEN state , It 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
Except for the common OPEN Status and which user status ？
This information can be obtained through USER_ASTATUS_MAP get .
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.
User status will not exceed the above nine .
2. The classification of nine states
The above nine can be divided into two categories ：1. The basic state ;2. Combined state .
The first five are basic states ：
0 OPEN
1 EXPIRED
2 EXPIRED(GRACE)
4 LOCKED(TIMED)
8 LOCKED
The last four are combinatorial states ：
5 EXPIRED & LOCKED(TIMED)
6 EXPIRED(GRACE) & LOCKED(TIMED)
9 EXPIRED & LOCKED
10 EXPIRED(GRACE) & LOCKED
This is the law ： The last four combined states can be determined by the state number STATUS# Get the combination of which two states it is , for example 10=2+8（10 EXPIRED(GRACE) & LOCKED = 2 EXPIRED(GRACE) + 8 LOCKED）. Therefore, as long as you understand the meaning of the basic state, you can learn from others without a teacher .
3. The interpretation of five basic states
These five basic states can be divided into three categories ：1. The normal state ;2. locked ;3. Password expiration status .
1）OPEN There’s no need to explain the state , Indicates that the user is in a normal state .
2） The user is locked ,LOCKED and LOCKED(TIMED) Both states are locked
There are generally two types of users being locked ： One is DBA Explicitly pass SQL Statement to lock the user ; The other is passive locking , For example, by default, if the password is entered incorrectly for more than 10 Time （ This limitation is caused by PROFILE Medium FAILED_LOGIN_ATTEMPTS The control of the , This information can be obtained through DBA_PROFILES View query ）, Users will be locked , For a description of passive locking, please refer to the article 《【 fault 】“ORACLE User locked ” Troubleshooting and analysis 》（http://space.itpub.net/519536/viewspace-608769）.
（1） Explicit locking sec user LOCKED State Demonstration
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） Input 10 Passive lock after wrong password LOCKED(TIMED) State Demonstration
Try typing 10 Query the user status again after the wrong password .
sys@ora10g> select username, account_status from dba_users where username = ‘SEC’;
USERNAME ACCOUNT_STATUS
—————————— ——————————–
SEC LOCKED(TIMED)
3） User password expiration status ,EXPIRED and EXPIRED(GRACE) Both States belong to the password expiration state
Whether the password is expired or not is determined by modifying PROFILE Medium PASSWORD_LIFE_TIME Realized , The number of days the password can be used after expiration is through PROFILE Medium PASSWORD_GRACE_TIME The control of the . of PROFILE Please refer to the article 《【PROFILE】 Use Oracle Of PROFILE Research and Exploration on user resource restriction and password restriction 》（http://space.itpub.net/519536/viewspace-616287）.
About password expiration, we can also use SQL Do it explicitly , Just to show you .
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. An explanation of the four combined states
Because the two states of locking （LOCKED and LOCKED(TIMED)） And password expiration （EXPIRED and EXPIRED(GRACE)） There is no relationship between . So they can be any combination ,2×2=4, So there are four combinations ：
5 EXPIRED & LOCKED(TIMED)
6 EXPIRED(GRACE) & LOCKED(TIMED)
9 EXPIRED & LOCKED
10 EXPIRED(GRACE) & LOCKED
Explain the four combination states respectively
1）EXPIRED & LOCKED(TIMED) Status means that after the user has expired , Wrong password attempt more than PROFILE Medium FAILED_LOGIN_ATTEMPTS The limitation of ;
2）EXPIRED(GRACE) & LOCKED(TIMED) Status indicates that the user is in the valid period after the password has expired , The number of unsuccessful logins exceeds PROFILE Medium FAILED_LOGIN_ATTEMPTS The limitation of ;
3）EXPIRED & LOCKED Status means that the user is in a locked state when they are out of date , 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 has been DBA Manual locking .
5. Summary
Oracle Although users have nine states , It seems very complicated , But as long as we can categorize it clearly , It’s easy to understand .
From normal 、 Understand user status from three perspectives: lock and password expiration , Everything will come naturally .
Good luck.
secooler
10.08.31
— The End —
come from “ ITPUB Blog ” , link ：http://blog.itpub.net/26736162/viewspace-2129454/, If you want to reprint , Please indicate the source , Otherwise, the legal liability will be investigated .