Oracle 用户登录密码错误次数修改

共计 3439 个字符，预计需要花费 9 分钟才能阅读完成。

Oracle 默认允许用户输入 10 次错误密码，超过 10 次后账户就会被锁定
sys@ORCL>select * from dba_profiles where profile=’DEFAULT’ and resource_name=’FAILED_LOGIN_ATTEMPTS’;
 
PROFILE    RESOURCE_NAME              RESOURCE_TYPE    LIMIT
————— —————————— ———————— ——————-
DEFAULT    FAILED_LOGIN_ATTEMPTS          PASSWORD        10

测试 zx 用户，使用错误的密码连接 11 次
[oracle@rhel6 ~]$ cat login.sh 
sqlplus zx/123 <<EOF
EOF
sqlplus zx/123 <<EOF
EOF
sqlplus zx/123 <<EOF
EOF
sqlplus zx/123 <<EOF
EOF
sqlplus zx/123 <<EOF
EOF
sqlplus zx/123 <<EOF
EOF
sqlplus zx/123 <<EOF
EOF
sqlplus zx/123 <<EOF
EOF
sqlplus zx/123 <<EOF
EOF
sqlplus zx/123 <<EOF
EOF
sqlplus zx/123 <<EOF
EOF
[oracle@rhel6 ~]$ sh login.sh
 
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 16 17:09:16 2017
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
ERROR:
ORA-01017: invalid username/password; logon denied
 
 
Enter user-name: 
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 16 17:09:16 2017
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
ERROR:
ORA-01017: invalid username/password; logon denied
 
 
Enter user-name: 
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 16 17:09:16 2017
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
ERROR:
ORA-01017: invalid username/password; logon denied
 
 
Enter user-name: 
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 16 17:09:16 2017
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
ERROR:
ORA-01017: invalid username/password; logon denied
 
 
Enter user-name: 
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 16 17:09:17 2017
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
ERROR:
ORA-01017: invalid username/password; logon denied
 
 
Enter user-name: 
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 16 17:09:19 2017
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
ERROR:
ORA-01017: invalid username/password; logon denied
 
 
Enter user-name: 
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 16 17:09:22 2017
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
ERROR:
ORA-01017: invalid username/password; logon denied
 
 
Enter user-name: 
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 16 17:09:26 2017
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
ERROR:
ORA-01017: invalid username/password; logon denied
 
 
Enter user-name: 
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 16 17:09:31 2017
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
ERROR:
ORA-01017: invalid username/password; logon denied
 
 
Enter user-name: 
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 16 17:09:37 2017
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
ERROR:
ORA-01017: invalid username/password; logon denied
 
 
Enter user-name: 
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 16 17:09:45 2017
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
ERROR:
ORA-28000: the account is locked
# 提示用户被锁定
# 查询视图 zx 用户被锁定
sys@ORCL>select username,account_status,profile from dba_users where username=’ZX’;
 
USERNAME  ACCOUNT_STATUS    PROFILE
———- ——————– —————
ZX    OPEN        DEFAULT

基于这个特性，如果在生产上修改了数据库用户密码，但是应用部分没有修改完全，则应用启动后有问题的应用会一直尝试连接数据库导致数据库用户被锁定，影响业务正常运行。可以修改用户尝试登录次数为 ulimited 来防止这种情况发生。
12345678910111213 sys@ORCL>alter user zx account unlock;
 
User altered.
 
sys@ORCL>alter profile default limit failed_login_attempts unlimited;
 
Profile altered.
 
sys@ORCL>select * from dba_profiles where profile=’DEFAULT’ and resource_name=’FAILED_LOGIN_ATTEMPTS’;
 
PROFILE    RESOURCE_NAME              RESOURCE_TYPE    LIMIT
————— —————————— ———————— ——————-
DEFAULT    FAILED_LOGIN_ATTEMPTS          PASSWORD        UNLIMITED

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

更多 Oracle 相关信息见 Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2017-01/139781.htm