oracle11g密码延迟验证测试一

清明节假期最后一天一客户的数据库出现应用系统无法连接的问题，到了客户现场，询问了具体情况，问题大概情况如下：

1、4月2日调整了默认profile中的参数failed_login_attempts为unlimited（默认为10）和参数PASSWORD_LIFE_TIME为unlimited（默认为180），修改这两个参数之前未出现问题，修改后才出现的数据库不能连接的问题

2、4月4日开始数据库开始报ORA-00020错误，用户不能直接连接数据库，重启主机后恢复正常，5日、6日同样出现该问题，也是重启数据库后恢复正常

查看了告警日志，除了4日出现了ORA-00020错误后，5、6日均没出现，查看问题发生前的awr报告，发现library cache lock等待非常高，在1个小时的采样里占到了99%以上的,SQL部分没有异常的SQL（由于客户单位属于保密单位，所以不能公开相关awr报告信息，只能描述下基本情况）

通过查看MOS，初步判断该问题是由于11g新特性密码延迟验证中的BUG（7715339）引起。

Oracle 11g中为了防止暴力破解数据库中用户的密码，提供了一种常见手段：延长失败尝试响应。

这种手段的策略是：在连续使用错误密码反复尝试登录时，从第四次错误尝试开始，每次增加1秒的延迟，最长延迟目前是10秒。

前提是该用户所属的profile中failed_login_attempts 参数为unlimited(默认为10)

如果不是unlimited是具体的次数，那么操作登录次数后该用户就会被lock

针对该特性做了以下测试。

测试一：测试密码延迟验证

SQL> set lines 200
SQL> select * from dba_profiles;
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
DEFAULT IDLE_TIME KERNEL UNLIMITED
DEFAULT CONNECT_TIME KERNEL UNLIMITED
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10
DEFAULT PASSWORD_LIFE_TIME PASSWORD 180
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
DEFAULT PASSWORD_LOCK_TIME PASSWORD 1
DEFAULT PASSWORD_GRACE_TIME PASSWORD 7
SQL>
SQL> create user hcn identified by oracle default tablespace users;
User created.
SQL>
SQL>
SQL>
SQL> select username,account_status,profile from dba_users where username=&＃39;HCN&＃39;;
USERNAME ACCOUNT_STATUS PROFILE
------------------------------ -------------------------------- ------------------------------
HCN OPEN DEFAULT
SQL> alter profile default limit failed_login_attempts unlimited;
Profile altered.
SQL>
SQL> select * from dba_profiles;
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
DEFAULT IDLE_TIME KERNEL UNLIMITED
DEFAULT CONNECT_TIME KERNEL UNLIMITED
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
DEFAULT PASSWORD_LIFE_TIME PASSWORD 180
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
DEFAULT PASSWORD_LOCK_TIME PASSWORD 1
DEFAULT PASSWORD_GRACE_TIME PASSWORD 7
16 rows selected.
SQL>
另外开两个会话：
一个会话执行(以session2代表)：
set lines 200
set pages 200
SQL> select event,count(*) from V$session group by event order by 2;
EVENT COUNT(*)
---------------------------------------------------------------- ----------
VKTM Logical Idle Wait 1
wait for unread message on broadcast channel 1
Streams AQ: waiting for time management or cleanup tasks 1
asynch descriptor resize 1
Space Manager: slave idle wait 1
Streams AQ: qmn coordinator idle wait 1
ASM background timer 1
smon timer 1
Streams AQ: qmn slave idle wait 1
pmon timer 1
DIAG idle wait 2
jobq slave wait 2
SQL*Net message from client 3
rdbms ipc message 17
另外个会话执行(以session3代表)
SQL> select name,lcount from user$ where name=&＃39;HCN&＃39;;
NAME LCOUNT
------------------------------ ----------
HCN 0
在第一个窗口中执行操作(以session1代表)
for i in {1..10}
do
echo &＃39;No.&＃39; $i
time echo &＃39;select sysdate from dual;&＃39; | sqlplus -s hcn/hcn_123
echo &＃39; &＃39;
done
执行结束后重新执行另外两个会话中的SQL
session2：
EVENT COUNT(*)
---------------------------------------------------------------- ----------
ASM background timer 1
wait for unread message on broadcast channel 1
Streams AQ: qmn slave idle wait 1
Streams AQ: waiting for time management or cleanup tasks 1
Streams AQ: qmn coordinator idle wait 1
pmon timer 1
smon timer 1
Space Manager: slave idle wait 1
SQL*Net message to client 1
VKTM Logical Idle Wait 1
DIAG idle wait 2
SQL*Net message from client 4
rdbms ipc message 17
13 rows selected.
SQL>
SQL>
session3：
NAME LCOUNT
------------------------------ ----------
HCN 10
SQL>
SQL>
session3中记录了失败的次数
session2无明显变化
session1中的记录：
[oracle@hcn ~]$
[oracle@hcn ~]$ for i in {1..10}
> do
> echo &＃39;No. $i&＃39;
> time echo &＃39;select sysdate from dual;&＃39; | sqlplus -s hcn/hcn_123
> done
1
ERROR:
ORA-01017: invalid username/password; logon denied
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
where ::= [/

][@]
::= [][/

][@]
real 0m0.074s
user 0m0.012s
sys 0m0.011s
2
ERROR:
ORA-01017: invalid username/password; logon denied
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
where ::= [/

][@]
::= [][/

][@]
real 0m0.040s
user 0m0.007s
sys 0m0.006s
3
ERROR:
ORA-01017: invalid username/password; logon denied
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
where ::= [/

][@]
::= [][/

][@]
real 0m0.040s
user 0m0.003s
sys 0m0.009s
4 -----从第四次开始每次一秒的累加延迟，一直到累加到10s延迟以后不再累加
ERROR:
ORA-01017: invalid username/password; logon denied
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
where ::= [/

][@]
::= [][/

][@]
real 0m1.048s
user 0m0.008s
sys 0m0.008s
5
ERROR:
ORA-01017: invalid username/password; logon denied
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
where ::= [/

][@]
::= [][/

][@]
real 0m2.061s
user 0m0.011s
sys 0m0.013s
6
ERROR:
ORA-01017: invalid username/password; logon denied
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
where ::= [/

][@]
::= [][/

][@]
real 0m3.055s
user 0m0.012s
sys 0m0.006s
7
ERROR:
ORA-01017: invalid username/password; logon denied
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
where ::= [/

][@]
::= [][/

][@]
real 0m4.066s
user 0m0.011s
sys 0m0.015s
8
ERROR:
ORA-01017: invalid username/password; logon denied
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
where ::= [/

][@]
::= [][/

][@]
real 0m5.065s
user 0m0.010s
sys 0m0.015s
9
ERROR:
ORA-01017: invalid username/password; logon denied
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
where ::= [/

][@]
::= [][/

][@]
real 0m6.053s
user 0m0.012s
sys 0m0.011s
10
ERROR:
ORA-01017: invalid username/password; logon denied
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
where ::= [/

][@]
::= [][/

][@]
real 0m7.043s
user 0m0.007s
sys 0m0.006s
[oracle@hcn ~]$

通过以上实验，可以看到密码延迟特性的效果