取消 11G延迟密码验证-CSDN博客

新特性在提供方便，安全的同时，也会带来相应的bug.
11G引入了延迟密码验证，在输入错误的密码后，后续如果还是采用错误的密码登陆，将会导致密码延迟验证，
而且会导致失败登陆延长。
我们通过一个小例子来看看11G引入了延迟密码验证新特性。该特性提供个更加安全的同时，也容易产生相应的bug，
在真实的环境中，我们遭遇到了bug.请查看我的上一篇文章《密码延迟验出现大量library cache lock》
我们使用SQLPLUS 的静默链接，把相应的时间

SQL> create user test identified by 123;

User created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@hrtest ~]$ time echo "select sysdate from dual;" | sqlplus -s test/111 1> /dev/null 2>&1

real 0m0.067s
user 0m0.013s
sys 0m0.015s
[oracle@hrtest ~]$ time echo "select sysdate from dual;" | sqlplus -s test/111 1> /dev/null 2>&1

real 0m0.073s
user 0m0.017s
sys 0m0.011s
[oracle@hrtest ~]$ time echo "select sysdate from dual;" | sqlplus -s test/111 1> /dev/null 2>&1

real 0m0.059s
user 0m0.017s
sys 0m0.009s
[oracle@hrtest ~]$ time echo "select sysdate from dual;" | sqlplus -s test/111 1> /dev/null 2>&1

real 0m1.060s
user 0m0.014s
sys 0m0.014s
[oracle@hrtest ~]$ time echo "select sysdate from dual;" | sqlplus -s test/111 1> /dev/null 2>&1

real 0m2.060s
user 0m0.015s
sys 0m0.013s
[oracle@hrtest ~]$ time echo "select sysdate from dual;" | sqlplus -s test/111 1> /dev/null 2>&1

real 0m3.060s
user 0m0.015s
sys 0m0.015s
[oracle@hrtest ~]$ time echo "select sysdate from dual;" | sqlplus -s test/111 1> /dev/null 2>&1

real 0m4.060s
user 0m0.014s
sys 0m0.014s
[oracle@hrtest ~]$ time echo "select sysdate from dual;" | sqlplus -s test/111 1> /dev/null 2>&1

real 0m5.061s
user 0m0.016s
sys 0m0.012s
[oracle@hrtest ~]$ time echo "select sysdate from dual;" | sqlplus -s test/111 1> /dev/null 2>&1

real 0m6.060s
user 0m0.015s
sys 0m0.016s
[oracle@hrtest ~]$

可以看到从第三次开始，后续的每次登陆导致密码延迟1秒左右。

如果有多个会话同时登陆，将会导致会话HANG住，出现一些LATCH的竞争。
我们多开几个回话窗口，一起执行time echo "select sysdate from dual;" | sqlplus -s test/111 1> /dev/null 2>&1
您开几个窗口在下面的查询中就会看到多少个library cache lock，我这里开了4个窗口。
SQL> select sid,username,event,schemaname from v$session order by event;

这时，即便是用正确的用户密码连接，也会一直hang住。
SQL> conn test/123

可以通过如下事件来屏蔽密码的延迟验证。
ALTER SYSTEM SET EVENT = '28401 TRACE NAME CONTEXT FOREVER, LEVEL 1' SCOPE = SPFILE

[oracle@test ~]$ oerr ora 28401
28401, 00000, "Event to disable delay after three failed login attempts"
// *Document: NO
// *Cause: N/A
// *Action: Set this event in your environment to disable the login delay
//          which will otherwise take place after three failed login attempts.
// *Note: THIS IS NOT A USER ERROR NUMBER/MESSAGE. THIS DOES NOT NEED TO BE
//        TRANSLATED OR DOCUMENTED.