错误信息如下：
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中的参数主要了为了安全性考虑。
：