The account is locked
SQL> select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
SQL> !cat /etc/issue
Enterprise Linux Enterprise Linux Server release 5.5 (Carthage)
Kernel \r on an \m
从oracle10g開始，对用户默认登陆失败次数有限制，默认用户的failed_login_attempts设置口令的失败次数为10，假设10次登陆失败。则该用户被锁定：
SQL> select * from dba_profiles where resource_name='FAILED_LOGIN_ATTENMPTS';
未选定行
上面没有选定的行是由于上次被我设置限制了,以下我们设置：
SQL> alter profile default limit failed_login_attempts 2;
配置文件已更改
SQL> select * from dba_profiles where resource_name='FAILED_LOGIN_ATTEMPTS';
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 2
MONITORING_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
配置文件已更改
SQL> select * from dba_profiles where resource_name='FAILED_LOGIN_ATTEMPTS';
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
MONITORING_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
以下我们模拟当两次失败登陆提示：
[oracle@localhost ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on 星期三 6月 10 18:52:02 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> conn hr/w
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn hr/w
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn hr/w
ERROR:
ORA-28000: the account is locked
失败登陆次数的限制有利于数据库的安全，生产库能够使用。
可是怎样知道当前失败多次了呢？
用户的状态信息是通过dba_users来展现的：
SQL> select count(*) from dba_users;
运行计划
----------------------------------------------------------
Plan hash value: 3953741735
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 86 | 30 (10)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 86 | | |
|* 2 | HASH JOIN | | 11 | 946 | 30 (10)| 00:00:01 |
|* 3 | HASH JOIN | | 11 | 913 | 23 (14)| 00:00:01 |
|* 4 | HASH JOIN | | 11 | 880 | 20 (10)| 00:00:01 |
|* 5 | HASH JOIN | | 14 | 994 | 18 (12)| 00:00:01 |
|* 6 | HASH JOIN OUTER | | 14 | 952 | 10 (10)| 00:00:01 |
|* 7 | HASH JOIN | | 14 | 546 | 8 (13)| 00:00:01 |
| 8 | MERGE JOIN CARTESIAN| | 1 | 11 | 4 (0)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | PROFILE$ | 1 | 9 | 2 (0)| 00:00:01 |
| 10 | BUFFER SORT | | 1 | 2 | 2 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL | PROFNAME$ | 1 | 2 | 2 (0)| 00:00:01 |
|* 12 | TABLE ACCESS FULL | USER$ | 36 | 1008 | 3 (0)| 00:00:01 |
|* 13 | TABLE ACCESS FULL | RESOURCE_GROUP_MAPPING$ | 1 | 29 | 2 (0)| 00:00:01 |
| 14 | TABLE ACCESS FULL | TS$ | 20 | 60 | 7 (0)| 00:00:01 |
|* 15 | TABLE ACCESS FULL | PROFILE$ | 2 | 18 | 2 (0)| 00:00:01 |
| 16 | TABLE ACCESS FULL | USER_ASTATUS_MAP | 9 | 27 | 2 (0)| 00:00:01 |
| 17 | TABLE ACCESS FULL | TS$ | 20 | 60 | 7 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
看下底层表USER$:
SQL> DESC USER$
名称 是否为空? 类型
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
USER# NOT NULL NUMBER
NAME NOT NULL VARCHAR2(30)
TYPE# NOT NULL NUMBER
PASSWORD VARCHAR2(30)
DATATS# NOT NULL NUMBER
TEMPTS# NOT NULL NUMBER
CTIME NOT NULL DATE
PTIME DATE
EXPTIME DATE
LTIME DATE
RESOURCE$ NOT NULL NUMBER
AUDIT$ VARCHAR2(38)
DEFROLE NOT NULL NUMBER
DEFGRP# NUMBER
DEFGRP_SEQ# NUMBER
ASTATUS NOT NULL NUMBER
LCOUNT NOT NULL NUMBER
DEFSCHCLASS VARCHAR2(30)
.........
.........
以上的探索思路，来自eygle大师《循序渐进，ORACLE 数据库管理，优化与备份恢复》一书。
LCOUNT非常让人敏感：
SQL> select name,lcount from user$ where name='HR';
NAME LCOUNT
------------------------------ ----------
HR 0
==========================================================
[oracle@localhost ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on 星期三 6月 10 19:03:40 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> conn hr/s
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> select name,lcount from user$ where name='HR';
NAME LCOUNT
------------------------------ ----------
HR 1
================================================
SQL> conn hr/s
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> select name,lcount from user$ where name='HR';
NAME LCOUNT
------------------------------ ----------
HR 2
通过实验，我们发现lcount这个字段记录的是失败登陆次数。如登陆成功后，自己主动清空为0
假设再继续研究，则不得不提起之前文章中提起的重要脚本：sql.bsq,读者感兴趣自行研究
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
SQL> !cat /etc/issue
Enterprise Linux Enterprise Linux Server release 5.5 (Carthage)
Kernel \r on an \m
从oracle10g開始，对用户默认登陆失败次数有限制，默认用户的failed_login_attempts设置口令的失败次数为10，假设10次登陆失败。则该用户被锁定：
SQL> select * from dba_profiles where resource_name='FAILED_LOGIN_ATTENMPTS';
未选定行
SQL> alter profile default limit failed_login_attempts 2;
配置文件已更改
SQL> select * from dba_profiles where resource_name='FAILED_LOGIN_ATTEMPTS';
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 2
MONITORING_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
当然也能够无限制：
SQL> alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;配置文件已更改
SQL> select * from dba_profiles where resource_name='FAILED_LOGIN_ATTEMPTS';
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
MONITORING_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
[oracle@localhost ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on 星期三 6月 10 18:52:02 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> conn hr/w
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn hr/w
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> conn hr/w
ERROR:
ORA-28000: the account is locked
失败登陆次数的限制有利于数据库的安全，生产库能够使用。
可是怎样知道当前失败多次了呢？
用户的状态信息是通过dba_users来展现的：
SQL> select count(*) from dba_users;
运行计划
----------------------------------------------------------
Plan hash value: 3953741735
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 86 | 30 (10)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 86 | | |
|* 2 | HASH JOIN | | 11 | 946 | 30 (10)| 00:00:01 |
|* 3 | HASH JOIN | | 11 | 913 | 23 (14)| 00:00:01 |
|* 4 | HASH JOIN | | 11 | 880 | 20 (10)| 00:00:01 |
|* 5 | HASH JOIN | | 14 | 994 | 18 (12)| 00:00:01 |
|* 6 | HASH JOIN OUTER | | 14 | 952 | 10 (10)| 00:00:01 |
|* 7 | HASH JOIN | | 14 | 546 | 8 (13)| 00:00:01 |
| 8 | MERGE JOIN CARTESIAN| | 1 | 11 | 4 (0)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | PROFILE$ | 1 | 9 | 2 (0)| 00:00:01 |
| 10 | BUFFER SORT | | 1 | 2 | 2 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL | PROFNAME$ | 1 | 2 | 2 (0)| 00:00:01 |
|* 12 | TABLE ACCESS FULL | USER$ | 36 | 1008 | 3 (0)| 00:00:01 |
|* 13 | TABLE ACCESS FULL | RESOURCE_GROUP_MAPPING$ | 1 | 29 | 2 (0)| 00:00:01 |
| 14 | TABLE ACCESS FULL | TS$ | 20 | 60 | 7 (0)| 00:00:01 |
|* 15 | TABLE ACCESS FULL | PROFILE$ | 2 | 18 | 2 (0)| 00:00:01 |
| 16 | TABLE ACCESS FULL | USER_ASTATUS_MAP | 9 | 27 | 2 (0)| 00:00:01 |
| 17 | TABLE ACCESS FULL | TS$ | 20 | 60 | 7 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
SQL> DESC USER$
名称 是否为空? 类型
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
USER# NOT NULL NUMBER
NAME NOT NULL VARCHAR2(30)
TYPE# NOT NULL NUMBER
PASSWORD VARCHAR2(30)
DATATS# NOT NULL NUMBER
TEMPTS# NOT NULL NUMBER
CTIME NOT NULL DATE
PTIME DATE
EXPTIME DATE
LTIME DATE
RESOURCE$ NOT NULL NUMBER
AUDIT$ VARCHAR2(38)
DEFROLE NOT NULL NUMBER
DEFGRP# NUMBER
DEFGRP_SEQ# NUMBER
ASTATUS NOT NULL NUMBER
LCOUNT NOT NULL NUMBER
DEFSCHCLASS VARCHAR2(30)
.........
.........
以上的探索思路，来自eygle大师《循序渐进，ORACLE 数据库管理，优化与备份恢复》一书。
LCOUNT非常让人敏感：
SQL> select name,lcount from user$ where name='HR';
NAME LCOUNT
------------------------------ ----------
HR 0
==========================================================
[oracle@localhost ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on 星期三 6月 10 19:03:40 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> conn hr/s
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> select name,lcount from user$ where name='HR';
NAME LCOUNT
------------------------------ ----------
HR 1
================================================
SQL> conn hr/s
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> select name,lcount from user$ where name='HR';
NAME LCOUNT
------------------------------ ----------
HR 2
通过实验，我们发现lcount这个字段记录的是失败登陆次数。如登陆成功后，自己主动清空为0
假设再继续研究，则不得不提起之前文章中提起的重要脚本：sql.bsq,读者感兴趣自行研究
版权声明：本文博主原创文章。博客，未经同意不得转载。
