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/hjjSQL*Plus: Release 10.2.0.5.0 - Production on Mon Nov 3 21:36:00 2014Copyright （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/hjjSQL*Plus: Release 10.2.0.5.0 - Production on Mon Nov 3 21:36:04 2014Copyright （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/hjjSQL*Plus: Release 10.2.0.5.0 - Production on Mon Nov 3 21:36:08 2014Copyright （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 deniedSP2-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 sysdbaSQL*Plus: Release 10.2.0.5.0 - Production on Mon Nov 3 21:42:43 2014Copyright （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 optionsSQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
[oracle@ora10g ~]$ sqlplus hjj/hjjSQL*Plus: Release 10.2.0.5.0 - Production on Mon Nov 3 21:44:18 2014Copyright （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 deniedSP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
[oracle@ora10g ~]$ sqlplus hjj/hjjSQL*Plus: Release 10.2.0.5.0 - Production on Mon Nov 3 21:44:22 2014Copyright （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 directoryEnter 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/hjjSQL*Plus: Release 10.2.0.5.0 - Production on Mon Nov 3 21:44:24 2014Copyright （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 sysdbaSQL*Plus: Release 10.2.0.5.0 - Production on Mon Nov 3 21:44:33 2014Copyright （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/hjjSQL*Plus: Release 10.2.0.5.0 - Production on Mon Nov 3 22:01:09 2014Copyright （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/oracleSQL*Plus: Release 10.2.0.5.0 - Production on Mon Nov 3 22:03:35 2014Copyright （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 1SQL> / USER# NAME ASTATUS LCOUNT
---------- ------------------------------ ---------- ----------
55 HJJ 0 2SQL> / 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/oracleSQL*Plus: Release 10.2.0.5.0 - Production on Mon Nov 3 22:10:17 2014Copyright （c） 1982, 2010, Oracle. All Rights Reserved.ERROR:
ORA-28000: the account is locked5.总结
普通用户登陆需要访问oracle相关视图，在user$.lcount记录着用户失败登陆的次数，如果lcount>=profile.FAILED_LOGIN_ATTEMPTS时，账户会自动被锁定，锁定的时间由PASSWORD_LOCK_TIME决定。配置profile中的参数主要了为了安全性考虑。更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址