oracle 查弱口令,oracle弱口令校验规则

[oracle@standalone admin]$ pwd

/oracle/app/oracle/product/11.2.0.4/rdbms/admin

[oracle@standalone admin]$ cat -n utlpwdmg.sql

1  Rem

2  Rem $Header: rdbms/admin/utlpwdmg.sql /st_rdbms_11.2.0/1 2013/01/31 01:34:11 skayoor Exp $

3  Rem

4  Rem utlpwdmg.sql

5  Rem

6  Rem Copyright (c) 2006, 2013, Oracle and/or its affiliates.

7  Rem All rights reserved.

8  Rem

9  Rem    NAME

10  Rem      utlpwdmg.sql - script for Default Password Resource Limits

11  Rem

12  Rem    DESCRIPTION

13  Rem      This is a script for enabling the password management features

14  Rem      by setting the default password resource limits.

15  Rem

16  Rem    NOTES

17  Rem      This file contains a function for minimum checking of password

18  Rem      complexity. This is more of a sample function that the customer

19  Rem      can use to develop the function for actual complexity checks that the

20  Rem      customer wants to make on the new password.

21  Rem

22  Rem    MODIFIED   (MM/DD/YY)

23  Rem    skayoor     01/17/13 - Backport skayoor_bug-14671375 from main

24  Rem    asurpur     05/30/06 - fix - 5246666 beef up password complexity check

25  Rem    nireland    08/31/00 - Improve check for username=password. #1390553

26  Rem    nireland    06/28/00 - Fix null old password test. #1341892

27  Rem    asurpur     04/17/97 - Fix for bug479763

28  Rem    asurpur     12/12/96 - Changing the name of password_verify_function

29  Rem    asurpur     05/30/96 - New script for default password management

30  Rem    asurpur     05/30/96 - Created

31  Rem

32

33

34  -- This script sets the default password resource parameters

35  -- This script needs to be run to enable the password features.

36  -- However the default resource parameters can be changed based

37  -- on the need.

38  -- A default password complexity function is also provided.

39  -- This function makes the minimum complexity checks like

40  -- the minimum length of the password, password not same as the

41  -- username, etc. The user may enhance this function according to

42  -- the need.

43  -- This function must be created in SYS schema.

44  -- connect sys/ as sysdba before running the script

45

46  CREATE OR REPLACE FUNCTION verify_function_11G

47  (username varchar2,

48    password varchar2,

49    old_password varchar2)

50    RETURN boolean IS

51     n boolean;

52     m integer;

53     differ integer;

54     isdigit boolean;

55     ischar  boolean;

56     ispunct boolean;

57     db_name varchar2(40);

58     digitarray varchar2(20);

59     punctarray varchar2(25);

60     chararray varchar2(52);

61     i_char varchar2(10);

62     simple_password varchar2(10);

63     reverse_user varchar2(32);

64

65  BEGIN

66     digitarray:= '0123456789';

67     chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';

68

69     -- Check for the minimum length of the password

70     IF length(password) < 8 THEN

71        raise_application_error(-20001, 'Password length less than 8');                                          --对长度做了显示，最少8位

72     END IF;

73

74

75     -- Check if the password is same as the username or username(1-100)

76     IF NLS_LOWER(password) = NLS_LOWER(username) THEN                                            --用户名和密码不能一致

77       raise_application_error(-20002, 'Password same as or similar to user');

78     END IF;

79     FOR i IN 1..100 LOOP

80        i_char := to_char(i);

81        if NLS_LOWER(username)|| i_char = NLS_LOWER(password) THEN                                --密码不能是用户名后串接1到100的数字

82          raise_application_error(-20005, 'Password same as or similar to user name ');

83        END IF;

84      END LOOP;

85

86     -- Check if the password is same as the username reversed

87

88     FOR i in REVERSE 1..length(username) LOOP

89       reverse_user := reverse_user || substr(username, i, 1);

90     END LOOP;

91     IF NLS_LOWER(password) = NLS_LOWER(reverse_user) THEN                                    --密码不能是用户名的逆置

92       raise_application_error(-20003, 'Password same as username reversed');

93     END IF;

94

95     -- Check if the password is the same as server name and or servername(1-100)

96     select name into db_name from sys.v$database;

97     if NLS_LOWER(db_name) = NLS_LOWER(password) THEN                                         --密码不能是服务名。

98        raise_application_error(-20004, 'Password same as or similar to server name');

99     END IF;

100     FOR i IN 1..100 LOOP

101        i_char := to_char(i);

102        if NLS_LOWER(db_name)|| i_char = NLS_LOWER(password) THEN                               --密码不能是服务名后串1到100的数字的形式。

103          raise_application_error(-20005, 'Password same as or similar to server name ');

104        END IF;

105      END LOOP;

106

107     -- Check if the password is too simple. A dictionary of words may be

108     -- maintained and a check may be made so as not to allow the words

109     -- that are too simple for the password.

110     IF NLS_LOWER(password) IN ('welcome1', 'database1', 'account1', 'user1234', 'password1', 'oracle123', 'computer1', 'abcdefg1', 'change_on_install') THEN         --密码不能是指定的这几个简单的组合。

111        raise_application_error(-20006, 'Password too simple');

112     END IF;

113

114     -- Check if the password is the same as oracle (1-100)

115      simple_password := 'oracle';

116      FOR i IN 1..100 LOOP

117        i_char := to_char(i);

118        if simple_password || i_char = NLS_LOWER(password) THEN            --密码不能是"oracle"后串接1到100数字的形式。

119          raise_application_error(-20007, 'Password too simple ');

120        END IF;

121      END LOOP;

122

123     -- Check if the password contains at least one letter, one digit            --判断密码中至少包含一个字母，一个数字

124     -- 1. Check for the digit

125     isdigit:=FALSE;

126     m := length(password);

127     FOR i IN 1..10 LOOP

128        FOR j IN 1..m LOOP

129           IF substr(password,j,1) = substr(digitarray,i,1) THEN

130              isdigit:=TRUE;

131               GOTO findchar;

132           END IF;

133        END LOOP;

134     END LOOP;

135

136     IF isdigit = FALSE THEN

137        raise_application_error(-20008, 'Password must contain at least one digit, one character');

138     END IF;

139     -- 2. Check for the character

140     <>

141     ischar:=FALSE;

142     FOR i IN 1..length(chararray) LOOP

143        FOR j IN 1..m LOOP

144           IF substr(password,j,1) = substr(chararray,i,1) THEN

145              ischar:=TRUE;

146               GOTO endsearch;

147           END IF;

148        END LOOP;

149     END LOOP;

150     IF ischar = FALSE THEN

151        raise_application_error(-20009, 'Password must contain at least one \

152                digit, and one character');

153     END IF;

154

155

156     <>

157     -- Check if the password differs from the previous password by at least         --检查新老密码是否最多只能三个位置的字符相同。

158     -- 3 letters

159     IF old_password IS NOT NULL THEN

160       differ := length(old_password) - length(password);

161

162       differ := abs(differ);

163       IF differ < 3 THEN

164         IF length(password) < length(old_password) THEN

165           m := length(password);

166         ELSE

167           m := length(old_password);

168         END IF;

169

170         FOR i IN 1..m LOOP

171           IF substr(password,i,1) != substr(old_password,i,1) THEN

172             differ := differ + 1;

173           END IF;

174         END LOOP;

175

176         IF differ < 3 THEN

177           raise_application_error(-20011, 'Password should differ from the \

178              old password by at least 3 characters');

179         END IF;

180       END IF;

181     END IF;

182     -- Everything is fine; return TRUE ;

183     RETURN(TRUE);

184  END;

185  /

186

187  GRANT EXECUTE ON verify_function_11G TO PUBLIC;

188

189  -- This script alters the default parameters for Password Management

190  -- This means that all the users on the system have Password Management

191  -- enabled and set to the following values unless another profile is

192  -- created with parameter values set to different value or UNLIMITED

193  -- is created and assigned to the user.

194

195  ALTER PROFILE DEFAULT LIMIT

196  PASSWORD_LIFE_TIME 180

197  PASSWORD_GRACE_TIME 7

198  PASSWORD_REUSE_TIME UNLIMITED

199  PASSWORD_REUSE_MAX UNLIMITED

200  FAILED_LOGIN_ATTEMPTS 10

201  PASSWORD_LOCK_TIME 1

202  PASSWORD_VERIFY_FUNCTION verify_function_11G;

203

204

205

206  -- Below is the older version of the script                             --此后是老版的密码规则

207

208  -- This script sets the default password resource parameters

209  -- This script needs to be run to enable the password features.

210  -- However the default resource parameters can be changed based

211  -- on the need.

212  -- A default password complexity function is also provided.

213  -- This function makes the minimum complexity checks like

214  -- the minimum length of the password, password not same as the

215  -- username, etc. The user may enhance this function according to

216  -- the need.

217  -- This function must be created in SYS schema.

218  -- connect sys/ as sysdba before running the script

219

220  CREATE OR REPLACE FUNCTION verify_function

221  (username varchar2,

222    password varchar2,

223    old_password varchar2)

224    RETURN boolean IS

225     n boolean;

226     m integer;

227     differ integer;

228     isdigit boolean;

229     ischar  boolean;

230     ispunct boolean;

231     digitarray varchar2(20);

232     punctarray varchar2(25);

233     chararray varchar2(52);

234

235  BEGIN

236     digitarray:= '0123456789';

237     chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';

238     punctarray:='!"#$%&()``*+,-/:;<=>?_';

239

240     -- Check if the password is same as the username

241     IF NLS_LOWER(password) = NLS_LOWER(username) THEN

242       raise_application_error(-20001, 'Password same as or similar to user');

243     END IF;

244

245     -- Check for the minimum length of the password

246     IF length(password) < 4 THEN

247        raise_application_error(-20002, 'Password length less than 4');

248     END IF;

249

250     -- Check if the password is too simple. A dictionary of words may be

251     -- maintained and a check may be made so as not to allow the words

252     -- that are too simple for the password.

253     IF NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user', 'password', 'oracle', 'computer', 'abcd') THEN

254        raise_application_error(-20002, 'Password too simple');

255     END IF;

256

257     -- Check if the password contains at least one letter, one digit and one

258     -- punctuation mark.

259     -- 1. Check for the digit

260     isdigit:=FALSE;

261     m := length(password);

262     FOR i IN 1..10 LOOP

263        FOR j IN 1..m LOOP

264           IF substr(password,j,1) = substr(digitarray,i,1) THEN

265              isdigit:=TRUE;

266               GOTO findchar;

267           END IF;

268        END LOOP;

269     END LOOP;

270     IF isdigit = FALSE THEN

271        raise_application_error(-20003, 'Password should contain at least one digit, one character and one punctuation');

272     END IF;

273     -- 2. Check for the character

274     <>

275     ischar:=FALSE;

276     FOR i IN 1..length(chararray) LOOP

277        FOR j IN 1..m LOOP

278           IF substr(password,j,1) = substr(chararray,i,1) THEN

279              ischar:=TRUE;

280               GOTO findpunct;

281           END IF;

282        END LOOP;

283     END LOOP;

284     IF ischar = FALSE THEN

285        raise_application_error(-20003, 'Password should contain at least one \

286                digit, one character and one punctuation');

287     END IF;

288     -- 3. Check for the punctuation

289     <>

290     ispunct:=FALSE;

291     FOR i IN 1..length(punctarray) LOOP

292        FOR j IN 1..m LOOP

293           IF substr(password,j,1) = substr(punctarray,i,1) THEN

294              ispunct:=TRUE;

295               GOTO endsearch;

296           END IF;

297        END LOOP;

298     END LOOP;

299     IF ispunct = FALSE THEN

300        raise_application_error(-20003, 'Password should contain at least one \

301                digit, one character and one punctuation');

302     END IF;

303

304     <>

305     -- Check if the password differs from the previous password by at least

306     -- 3 letters

307     IF old_password IS NOT NULL THEN

308       differ := length(old_password) - length(password);

309

310       IF abs(differ) < 3 THEN

311         IF length(password) < length(old_password) THEN

312           m := length(password);

313         ELSE

314           m := length(old_password);

315         END IF;

316

317         differ := abs(differ);

318         FOR i IN 1..m LOOP

319           IF substr(password,i,1) != substr(old_password,i,1) THEN

320             differ := differ + 1;

321           END IF;

322         END LOOP;

323

324         IF differ < 3 THEN

325           raise_application_error(-20004, 'Password should differ by at \

326           least 3 characters');

327         END IF;

328       END IF;

329     END IF;

330     -- Everything is fine; return TRUE ;

331     RETURN(TRUE);

332  END;

333  /

334

335  GRANT EXECUTE ON verify_function TO PUBLIC;

336

337  Rem *************************************************************************

338  Rem END Password Verification Functions

339  Rem *************************************************************************

340

341  Rem *************************************************************************

342  Rem BEGIN Password Management Parameters

343  Rem *************************************************************************

344

345  -- This script alters the default parameters for Password Management

346  -- This means that all the users on the system have Password Management

347  -- enabled and set to the following values unless another profile is

348  -- created with parameter values set to different value or UNLIMITED

349  -- is created and assigned to the user.

350

351  -- Enable this if you want older version of the Password Profile parameters

352  -- ALTER PROFILE DEFAULT LIMIT

353  -- PASSWORD_LIFE_TIME 60

354  -- PASSWORD_GRACE_TIME 10

355  -- PASSWORD_REUSE_TIME 1800

356  -- PASSWORD_REUSE_MAX UNLIMITED

357  -- FAILED_LOGIN_ATTEMPTS 3

358  -- PASSWORD_LOCK_TIME 1/1440

359  -- PASSWORD_VERIFY_FUNCTION verify_function;

[oracle@standalone admin]$