Oracle默认允许用户输入10次错误密码，超过10次后账户就会被锁定
|
1
2
3
4
5
|
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次
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
|
[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来防止这种情况发生。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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无论使用多少次错误密码都不会导致账户锁定。
官方文档：http://docs.oracle.com/cd/E11882_01/network.112/e36292/authentication.htm#CHDEGBEG
参考文档：http://www.2cto.com/database/201203/124001.html
本文转自hbxztc 51CTO博客，原文链接：http://blog.51cto.com/hbxztc/1892362，如需转载请自行联系原作者
![【数据库】解决 oracle: SQL 错误 [900] [42000]: ORA-00900: 无效 SQL 语句](https://ucc.alicdn.com/pic/developer-ecology/f790f8e9ea28458da1cf5cbb69a7d12a.jpg?x-oss-process=image/resize,h_160,m_lfit)
