Oracle裡面的user被lock了
今天遇到一個很常見的問題“ORA-28000: the account is locked”，如是也沒多想，直接操作
SQL>conn /as sysdba
SQL>alter user test account unlock;
SQL>conn test/test
還是報同樣的錯誤，這就奇怪了。看看dba_users中該使用者的狀態等資訊
SQL>select account_status,lock_date,profile from dba_users where username='TEST';
發現就是今天才鎖定的，帳號狀態為“LOCKED(TIMED)”，不明白這個TIMED什麼意思，搜了一把，原來是密碼錯誤次數超過了系統設定的允許最大次數。
檢視現在設定的最大次數
SELECT resource_name,resource_type,limit FROM dba_profiles WHERE profile='DEFAULT';
找到
FAILED_LOGIN_ATTEMPTS PASSWORD 10
也就是說，原來允許的最大密碼錯誤次數為10次，超過10次自動鎖定帳號。難怪我怎麼alter unlock都沒用，看來還得先把這限制放寬才行。
也就是說，原來允許的最大密碼錯誤次數為10次，超過10次自動鎖定帳號。難怪我怎麼alter unlock都沒用，看來還得先把這限制放寬才行。
把限制引數 FAILED_LOGIN_ATTEMPTS 修改成：unlimited
sql> alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;
或 修改成比較大次數：100000
sql>alter profile default limit FAILED_LOGIN_ATTEMPTS 100000；
再執行如下
SQL>alter user test account unlock;
SQL>conn test/test
就可以正常登入了。
下面，就看看dba_users中的account_status執行的各種狀態以及對應的說明。
DBA_USERS中的ACCOUNT_STATUS的值有
OPEN
EXPIRED
EXPIRED(GRACE)
LOCKED(TIMED)
LOCKED
EXPIRED & LOCKED(TIMED)
EXPIRED(GRACE) & LOCKED(TIMED)
EXPIRED & LOCKED
EXPIRED(GRACE) & LOCKED
下面分別解釋一下open.locked,expired,EXPIRED(GRACE) ,LOCKED(TIMED) ,其餘幾個概念就是這幾個狀態的組合。
OPEN
EXPIRED
EXPIRED(GRACE)
LOCKED(TIMED)
LOCKED
EXPIRED & LOCKED(TIMED)
EXPIRED(GRACE) & LOCKED(TIMED)
EXPIRED & LOCKED
EXPIRED(GRACE) & LOCKED
下面分別解釋一下open.locked,expired,EXPIRED(GRACE) ,LOCKED(TIMED) ,其餘幾個概念就是這幾個狀態的組合。
1. open 當前使用者是開放的可以使用的.
SQL> select username,account_status from dba_users where username = 'TONGJW';
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
TONGJW OPEN
SQL> conn tongjw
Enter password: *******
Connected.
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
TONGJW OPEN
SQL> conn tongjw
Enter password: *******
Connected.
2. expired 表示使用者已經過期, 登入的時候, 如果密碼輸入正確,系統會提示重新輸入密碼的.
SQL> conn jfdata/jf
Connected.
SQL> alter user tongjw account unlock;
User altered.
SQL> alter user tongjw password expire;
User altered.
SQL> conn tongjw
Enter password: *******
ERROR:
ORA-28001: the password has expired
Changing password for tongjw
New password: *******
Retype new password: *******
Password changed
Connected.
Connected.
SQL> alter user tongjw account unlock;
User altered.
SQL> alter user tongjw password expire;
User altered.
SQL> conn tongjw
Enter password: *******
ERROR:
ORA-28001: the password has expired
Changing password for tongjw
New password: *******
Retype new password: *******
Password changed
Connected.
3. locked 表明當前帳戶被鎖了, 不可以登入的.
SQL> conn jfdata/jf
Connected.
SQL> alter user tongjw account lock;
User altered.
SQL> conn tongjw
Enter password: *******
ERROR:
ORA-28000: the account is locked
Warning: You are no longer connected to ORACLE.
Connected.
SQL> alter user tongjw account lock;
User altered.
SQL> conn tongjw
Enter password: *******
ERROR:
ORA-28000: the account is locked
Warning: You are no longer connected to ORACLE.
expired (grace) 與 locked (timed) 是有系統的profile來進行控制的.
4. expired (grace) 指定一個期限, 到達這個期限的時候,系統會給出一個警告並且允許登入(grace), 如果過了這段期限, 對應的密碼就會自動過期, 相當於我們的密碼過期提醒.
PASSWORD_GRACE_TIME
PASSWORD_LIFE_TIME
Specify the number of days after the grace period begins during which a warning is issued and login is allowed. If the password is not changed during the grace period, the password expires.
SQL> select username,account_status,profile from dba_users where username = 'TONGJW';
USERNAME ACCOUNT_STATUS PROFILE
------------------------------ -------------------------------- ------------------------------
TONGJW OPEN DEFAULT
SQL> create profile test limit
2 password_life_time 5 password_grace_time 5;
Profile created.
SQL> alter user tongjw profile test;
User altered.
SQL> select username,account_status,profile from dba_users where username = 'TONGJW';
USERNAME ACCOUNT_STATUS PROFILE
------------------------------ -------------------------------- ------------------------------
TONGJW OPEN TEST
SQL> conn tongjw/playboy
Connected.
SQL> /
PASSWORD_LIFE_TIME
Specify the number of days after the grace period begins during which a warning is issued and login is allowed. If the password is not changed during the grace period, the password expires.
SQL> select username,account_status,profile from dba_users where username = 'TONGJW';
USERNAME ACCOUNT_STATUS PROFILE
------------------------------ -------------------------------- ------------------------------
TONGJW OPEN DEFAULT
SQL> create profile test limit
2 password_life_time 5 password_grace_time 5;
Profile created.
SQL> alter user tongjw profile test;
User altered.
SQL> select username,account_status,profile from dba_users where username = 'TONGJW';
USERNAME ACCOUNT_STATUS PROFILE
------------------------------ -------------------------------- ------------------------------
TONGJW OPEN TEST
SQL> conn tongjw/playboy
Connected.
SQL> /
這時我們修改系統時間, 將日期往後修改幾天
[oracle@TzDbTzcenter2 ]$ su
Password:
[root@TzDbTzcenter2 ]# date
Tue Mar 22 18:42:26 CST 2005
[root@TzDbTzcenter2 ]# date '03281842'
Mon Mar 28 18:42:00 CST 2005
[root@TzDbTzcenter2 ]# exit
[oracle@TzDbTzcenter2 ]$ sqlplus tongjw/tongjw
SQL*Plus: Release 9.2.0.4.0 - Production on Mon Mar 28 18:42:01 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
ERROR:
ORA-28002: the password will expire within 5 days
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> conn jfdata/jf
Connected.
SQL> select username,account_status,profile from dba_users where username = 'TONGJW';
USERNAME ACCOUNT_STATUS PROFILE
------------------------------ -------------------------------- ------------------------------
TONGJW EXPIRED(GRACE) TEST
SQL>
Password:
[root@TzDbTzcenter2 ]# date
Tue Mar 22 18:42:26 CST 2005
[root@TzDbTzcenter2 ]# date '03281842'
Mon Mar 28 18:42:00 CST 2005
[root@TzDbTzcenter2 ]# exit
[oracle@TzDbTzcenter2 ]$ sqlplus tongjw/tongjw
SQL*Plus: Release 9.2.0.4.0 - Production on Mon Mar 28 18:42:01 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
ERROR:
ORA-28002: the password will expire within 5 days
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> conn jfdata/jf
Connected.
SQL> select username,account_status,profile from dba_users where username = 'TONGJW';
USERNAME ACCOUNT_STATUS PROFILE
------------------------------ -------------------------------- ------------------------------
TONGJW EXPIRED(GRACE) TEST
SQL>
再將日期向後修改幾天, 我們可以看到此時使用者的狀態已經改為Expired
[oracle@TzDbTzcenter2 ]$ sqlplus tongjw/tongjw
SQL*Plus: Release 9.2.0.4.0 - Production on Tue Apr 5 18:44:02 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
ERROR:
ORA-28001: the password has expired
Changing password for tongjw
New password:
Retype new password:
ERROR:
ORA-00988: missing or invalid password(s)
Password unchanged
Enter user-name: jfdata/jf
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> select username,account_status,profile from dba_users where username = 'TONGJW';
USERNAME ACCOUNT_STATUS PROFILE
------------------------------ -------------------------------- ------------------------------
TONGJW EXPIRED TEST
SQL*Plus: Release 9.2.0.4.0 - Production on Tue Apr 5 18:44:02 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
ERROR:
ORA-28001: the password has expired
Changing password for tongjw
New password:
Retype new password:
ERROR:
ORA-00988: missing or invalid password(s)
Password unchanged
Enter user-name: jfdata/jf
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> select username,account_status,profile from dba_users where username = 'TONGJW';
USERNAME ACCOUNT_STATUS PROFILE
------------------------------ -------------------------------- ------------------------------
TONGJW EXPIRED TEST
5. locked(timed)
PASSWORD_LOCK_TIME
Specify the number of days an account will be locked after the specified number of consecutive failed login attempts.
FAILED_LOGIN_ATTEMPTS
Specify the number of failed attempts to log in to the user account before the account is locked.
設定相關的profile屬性^_^.
SQL> alter user tongjw profile default;
User altered.
SQL> drop profile test;
Profile dropped.
SQL> create profile test limit
2 password_lock_time 5 failed_login_attempts 3;
Profile created.
SQL> alter user tongjw profile test;
User altered.
User altered.
SQL> drop profile test;
Profile dropped.
SQL> create profile test limit
2 password_lock_time 5 failed_login_attempts 3;
Profile created.
SQL> alter user tongjw profile test;
User altered.
修改系統的時間資訊.
[root@TzDbTzcenter2 ]# date
Tue Mar 22 18:55:42 CST 2005
[root@TzDbTzcenter2 ]# date '03281855'
Mon Mar 28 18:55:00 CST 2005
[root@TzDbTzcenter2 ]# exit
[oracle@TzDbTzcenter2 ]$ sqlplus jfdata/jf
SQL*Plus: Release 9.2.0.4.0 - Production on Mon Mar 28 18:55:02 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> conn tongjw/tongjw
Connected.
SQL> conn tongjw/tongj1
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn tongjw/tongj2
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn tongjw/tongj3
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn tongjw/tongj4
ERROR:
ORA-28000: the account is locked
SQL> conn tongjw/tongjw
ERROR:
ORA-28000: the account is locked
SQL> conn jfdata/jf
Connected.
SQL> select username,account_status,profile from dba_users where username = 'TONGJW';
USERNAME ACCOUNT_STATUS PROFILE
------------------------------ -------------------------------- ------------------------------
TONGJW LOCKED(TIMED) TEST
SQL>
Tue Mar 22 18:55:42 CST 2005
[root@TzDbTzcenter2 ]# date '03281855'
Mon Mar 28 18:55:00 CST 2005
[root@TzDbTzcenter2 ]# exit
[oracle@TzDbTzcenter2 ]$ sqlplus jfdata/jf
SQL*Plus: Release 9.2.0.4.0 - Production on Mon Mar 28 18:55:02 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> conn tongjw/tongjw
Connected.
SQL> conn tongjw/tongj1
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn tongjw/tongj2
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn tongjw/tongj3
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn tongjw/tongj4
ERROR:
ORA-28000: the account is locked
SQL> conn tongjw/tongjw
ERROR:
ORA-28000: the account is locked
SQL> conn jfdata/jf
Connected.
SQL> select username,account_status,profile from dba_users where username = 'TONGJW';
USERNAME ACCOUNT_STATUS PROFILE
------------------------------ -------------------------------- ------------------------------
TONGJW LOCKED(TIMED) TEST
SQL>
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9399028/viewspace-720003/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle使用者被lockOracle
- withoutEvents函式裡面的事件被觸發函式事件
- Oracle中SQL裡面的Help命令OracleSQL
- 考考你,: ) 靜態方法裡面的變數被存放在哪裡?變數
- Oracle-批量修改欄位裡面的值Oracle
- oracle裡面的幾個環境變數表Oracle變數
- 當刪除oracle資料庫user時發生row cache lock 等待事件Oracle資料庫事件
- Go 裡面的 ^ 和 &^Go
- Oracle EXECUTE IMMEDIATE語句裡面的引號處理Oracle
- oracle user$Oracle
- oracle 使用者user鎖定lock如何知道是什麼原因導致的Oracle
- oracle lock鎖_v$lock_轉Oracle
- [Oracle Script] LockOracle
- About Oracle LockOracle
- oracle enqueue lockOracleENQ
- Oracle Latch & LockOracle
- 【轉】Oracle:檢查被鎖資料在V$LOCK中的情況Oracle
- Linux記憶體被吃掉了，它去哪裡了？Linux記憶體
- 問問banq，jivejdon中的messageInitFactory被用在哪裡了？
- angular8裡面的*ngSwitchAngular
- 聊聊Go裡面的閉包Go
- 獲取cookie裡面的值Cookie
- $(function{})裡面的onclick報錯Function
- 遊戲裡面的容斥原理遊戲
- ORACLE LOCK,LATCH,PINOracle
- ORACLE LOCK MODE 1.2.3.4.5.6Oracle
- Oracle Lock Information QueriesOracleORM
- Dead lock - oracleOracle
- ORACLE lock 轉貼Oracle
- ORACLE查LOCK表Oracle
- oracle lock系列一Oracle
- 父頁面如何輸出iframe裡面的變數，或者呼叫iframe裡面的方法？變數
- Oracle OCP(28)：USEROracle
- [Oracle Script] check userOracle
- oracle user privsOracle
- 【oracle】user_tablesOracle
- 如何檢視Oracle11g控制檔案裡面的內容Oracle
- 如何獲取 alter 裡面的字串？字串