ORA-28000 the account is locked錯誤類比

ORA-28000 the account is locked錯誤類比

錯誤資訊如下：
OCI-Call Error sql code 28000,the account is locked
SQL> !oerr ora 28000
28000, 00000, "the account is locked"
// *Cause:  The user has entered wrong password consequently for maximum
//          number of times specified by the user's profile parameter
//          FAILED_LOGIN_ATTEMPTS, or the DBA has locked the account
// *Action:  Wait for PASSWORD_LOCK_TIME or contact DBA
Note:
FAILED_LOGIN_ATTEMPTS=10 嘗試登陸失敗的次數為10次，10次之後該使用者將被鎖定。
PASSWORD_LOCK_TIME=15  在嘗試登陸指定的次數10後，該使用者將被鎖定15天
目標：將賬戶hjj從OPEN狀態變為LOCKED。
下面進行類比28000錯誤
1.查看當前環境
SQL> select username,account_status,profile from dba_users where username='HJJ';

USERNAME                      ACCOUNT_STATUS                  PROFILE
------------------------------ -------------------------------- ------------------------------
HJJ                            OPEN                            DEFAULT
賬戶hjj是OPEN的
SQL> select * from dba_profiles;

PROFILE                        RESOURCE_NAME                  RESOURCE LIMIT
------------------------------ ------------------------------ -------- ------------------------------
DEFAULT                        FAILED_LOGIN_ATTEMPTS          PASSWORD 10
DEFAULT                        PASSWORD_LOCK_TIME            PASSWORD UNLIMITED
預設是嘗試登陸10次，之後賬戶一直被鎖定。
為了測試，我們將FAILED_LOGIN_ATTEMPTS改為3
SQL> alter profile default limit FAILED_LOGIN_ATTEMPTS 3;

Profile altered.
SQL> select * from dba_profiles where resource_name in('FAILED_LOGIN_ATTEMPTS') and profile='DEFAULT';

PROFILE                        RESOURCE_NAME                  RESOURCE LIMIT
------------------------------ ------------------------------ -------- ------------------------------
DEFAULT                        FAILED_LOGIN_ATTEMPTS          PASSWORD 3
2.登陸測試
故意輸錯密碼3次，看賬戶hjj會不會被鎖定。
[Oracle@ora10g ~]$ sqlplus hjj/hjj

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Nov 3 21:36:00 2014

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
[oracle@ora10g ~]$ sqlplus hjj/hjj

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Nov 3 21:36:04 2014

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
[oracle@ora10g ~]$
[oracle@ora10g ~]$ sqlplus hjj/hjj

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Nov 3 21:36:08 2014

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
再來查看賬戶狀態
SQL> select username,account_status,profile from dba_users where username='HJJ';

USERNAME                      ACCOUNT_STATUS                  PROFILE
------------------------------ -------------------------------- ------------------------------
HJJ                            LOCKED                          DEFAULT
可以看到賬戶被鎖定了。
3.普通賬戶登陸，必須在資料庫處於OPEN狀態才能登陸，而sys使用者在資料庫關閉狀態下也可以，使用OS認證。
測試賬戶hjj在資料庫處於關閉狀態，嘗試登陸3次失敗後會不會被鎖定。先將使用者解鎖。
SQL> alter user hjj account unlock;

User altered.

SQL> select username,account_status,profile from dba_users where username='HJJ';

USERNAME                      ACCOUNT_STATUS                  PROFILE
------------------------------ -------------------------------- ------------------------------
HJJ                            OPEN                            DEFAULT
[oracle@ora10g ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Nov 3 21:42:43 2014

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
[oracle@ora10g ~]$ sqlplus hjj/hjj

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Nov 3 21:44:18 2014

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory

Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
[oracle@ora10g ~]$ sqlplus hjj/hjj

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Nov 3 21:44:22 2014

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory

Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
[oracle@ora10g ~]$ sqlplus hjj/hjj

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Nov 3 21:44:24 2014

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory

Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
[oracle@ora10g ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Nov 3 21:44:33 2014

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  503316480 bytes
Fixed Size                  1274548 bytes
Variable Size            327159116 bytes
Database Buffers          171966464 bytes
Redo Buffers                2916352 bytes
Database mounted.
Database opened.
SQL> select username,account_status,profile from dba_users where username='HJJ';
USERNAME                      ACCOUNT_STATUS                  PROFILE
------------------------------ -------------------------------- ------------------------------
HJJ                            OPEN                            DEFAULT
這也就證明了資料庫沒有處於OPEN狀態，普通使用者無法登陸，登陸的時候要去dba_users尋找使用者是否存在，如果存在，判斷使用者名稱和密碼是否正確；如果找不到該使用者，就會提示使用者不存在。
4.oracle如何記錄使用者的登陸次數
在dba_users的基表user$記錄著使用者的登陸次數，我們看看user$表的建立文法
[oracle@ora10g db_1]$ cd rdbms/admin/
[oracle@ora10g admin]$ ls -ltr sql.bsq
-rw-r--r-- 1 oracle oinstall 445473 Apr  2  2010 sql.bsq
create table user$                                           
( user#        number not null,                 
  name          varchar2("M_IDEN") not null,               
  type#        number not null,                     
  password      varchar2("M_IDEN"),                 
  datats#      number not null,
  tempts#      number not null,
  ctime        date not null,               
  ptime        date,                             
  exptime      date,                   
  ltime        date,                       
  resource$    number not null,                     
  audit$        varchar2("S_OPFL"),                 
  defrole      number not null,               
             
  defgrp#      number,                             
  defgrp_seq#  number,             
  astatus      number default 0 not null,       
             
  lcount        number default 0 not null,  ---失敗登陸嘗試次數。
  defschclass  varchar2("M_IDEN"),             
  ext_username  varchar2("M_VCSZ"),                   
  spare1        number,
  spare2        number,
  spare3        number,
  spare4        varchar2(1000),
  spare5        varchar2(1000),
  spare6        date
)
SQL> select user#,name,ASTATUS,LCOUNT from user$ where name='HJJ';

    USER# NAME                              ASTATUS    LCOUNT
---------- ------------------------------ ---------- ----------
        55 HJJ                                    0          0
LCOUNT就是記錄使用者登陸次數(失敗和成功)
[oracle@ora10g ~]$ sqlplus hjj/hjj

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Nov 3 22:01:09 2014

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
SQL> /

    USER# NAME                              ASTATUS    LCOUNT
---------- ------------------------------ ---------- ----------
        55 HJJ                                    0          1  --登陸一次
[oracle@ora10g ~]$ sqlplus hjj/oracle

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Nov 3 22:03:35 2014

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> /

    USER# NAME                              ASTATUS    LCOUNT
---------- ------------------------------ ---------- ----------
        55 HJJ                                    0          0
只要在FAILED_LOGIN_ATTEMPTS指定的範圍之內登陸成功一次，LCOUNT會重設為0。
如果失敗登陸三次，觀察LCOUNT的值和狀態。
SQL> /

    USER# NAME                              ASTATUS    LCOUNT
---------- ------------------------------ ---------- ----------
        55 HJJ                                    0          1

SQL> /

    USER# NAME                              ASTATUS    LCOUNT
---------- ------------------------------ ---------- ----------
        55 HJJ                                    0          2

SQL> /

    USER# NAME                              ASTATUS    LCOUNT
---------- ------------------------------ ---------- ----------
        55 HJJ                                    8          3
SQL> select username,account_status,profile from dba_users where username='HJJ';
USERNAME                      ACCOUNT_STATUS                  PROFILE
------------------------------ -------------------------------- ------------------------------
HJJ                            LOCKED                          DEFAULT
再次用正確的密碼登陸
[oracle@ora10g admin]$ sqlplus hjj/oracle

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Nov 3 22:10:17 2014

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

ERROR:
ORA-28000: the account is locked

5.總結
    普通使用者登陸需要訪問oracle相關視圖，在user$.lcount記錄著使用者失敗登陸的次數，如果lcount>=profile.FAILED_LOGIN_ATTEMPTS時，賬戶會自動被鎖定，鎖定的時間由PASSWORD_LOCK_TIME決定。配置profile中的參數主要了為了安全性考慮。