Oracle 用户登录错误次数修改

Oracle默认允许用户输入10次错误密码，超过10次后账户就会被锁定

sys@ORCL>select*fromdba_profileswhereprofile='DEFAULT'andresource_name='FAILED_LOGIN_ATTEMPTS';

PROFILE	RESOURCE_NAME		RESOURCE_TYPE		LIMIT
----------------------------------------------------------------------------------------
DEFAULT	FAILED_LOGIN_ATTEMPTS	PASSWORD		10

测试zx用户，使用错误的密码连接11次

[oracle@rhel6~]$catlogin.sh
sqlpluszx/123<<EOF
EOF
sqlpluszx/123<<EOF
EOF
sqlpluszx/123<<EOF
EOF
sqlpluszx/123<<EOF
EOF
sqlpluszx/123<<EOF
EOF
sqlpluszx/123<<EOF
EOF
sqlpluszx/123<<EOF
EOF
sqlpluszx/123<<EOF
EOF
sqlpluszx/123<<EOF
EOF
sqlpluszx/123<<EOF
EOF
sqlpluszx/123<<EOF
EOF
[oracle@rhel6~]$shlogin.sh

SQL*Plus:Release11.2.0.4.0ProductiononMonJan1617:09:162017

Copyright(c)1982,2013,Oracle.Allrightsreserved.

ERROR:
ORA-01017:invalidusername/password;logondenied


Enteruser-name:
SQL*Plus:Release11.2.0.4.0ProductiononMonJan1617:09:162017

Copyright(c)1982,Oracle.Allrightsreserved.

ERROR:
ORA-01017:invalidusername/password;logondenied


Enteruser-name:
SQL*Plus:Release11.2.0.4.0ProductiononMonJan1617:09:172017

Copyright(c)1982,Oracle.Allrightsreserved.

ERROR:
ORA-01017:invalidusername/password;logondenied


Enteruser-name:
SQL*Plus:Release11.2.0.4.0ProductiononMonJan1617:09:192017

Copyright(c)1982,Oracle.Allrightsreserved.

ERROR:
ORA-01017:invalidusername/password;logondenied


Enteruser-name:
SQL*Plus:Release11.2.0.4.0ProductiononMonJan1617:09:222017

Copyright(c)1982,Oracle.Allrightsreserved.

ERROR:
ORA-01017:invalidusername/password;logondenied


Enteruser-name:
SQL*Plus:Release11.2.0.4.0ProductiononMonJan1617:09:262017

Copyright(c)1982,Oracle.Allrightsreserved.

ERROR:
ORA-01017:invalidusername/password;logondenied


Enteruser-name:
SQL*Plus:Release11.2.0.4.0ProductiononMonJan1617:09:312017

Copyright(c)1982,Oracle.Allrightsreserved.

ERROR:
ORA-01017:invalidusername/password;logondenied


Enteruser-name:
SQL*Plus:Release11.2.0.4.0ProductiononMonJan1617:09:372017

Copyright(c)1982,Oracle.Allrightsreserved.

ERROR:
ORA-01017:invalidusername/password;logondenied


Enteruser-name:
SQL*Plus:Release11.2.0.4.0ProductiononMonJan1617:09:452017

Copyright(c)1982,Oracle.Allrightsreserved.

ERROR:
ORA-28000:theaccountislocked
#提示用户被锁定
#查询视图zx用户被锁定
sys@ORCL>selectusername,account_status,profilefromdba_userswhereusername='ZX';

USERNAMEACCOUNT_STATUS	PROFILE
---------------------------------------------
ZX	OPEN		DEFAULT

基于这个特性，如果在生产上修改了数据库用户密码，但是应用部分没有修改完全，则应用启动后有问题的应用会一直尝试连接数据库导致数据库用户被锁定，影响业务正常运行。可以修改用户尝试登录次数为ulimited来防止这种情况发生。

sys@ORCL>alteruserzxaccountunlock;

Useraltered.

sys@ORCL>alterprofiledefaultlimitfailed_login_attemptsunlimited;

Profilealtered.

sys@ORCL>select*fromdba_profileswhereprofile='DEFAULT'andresource_name='FAILED_LOGIN_ATTEMPTS';

PROFILE	RESOURCE_NAME		RESOURCE_TYPE		LIMIT
----------------------------------------------------------------------------------------
DEFAULT	FAILED_LOGIN_ATTEMPTS	PASSWORD		UNLIMITED

至此，用户zx无论使用多少次错误密码都不会导致账户锁定。

官方文档：http://docs.oracle.com/cd/E11882_01/network.112/e36292/authentication.htm#CHDEGBEG

参考文档：http://www.2cto.com/database/201203/124001.html

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!