verify_function的10g/11g/12c的版本

botang

10g：

1. CREATE OR REPLACE FUNCTION verify_function
   
2. (username varchar2,
   
3. password varchar2,
   
4. old_password varchar2)
   
5. RETURN boolean IS
   
6.    differ integer;
   
7. BEGIN
   
8.    -- Check if the password is same as the username
   
9.    IF NLS_LOWER(password) = NLS_LOWER(username) THEN
   
10.      raise_application_error(-20001, 'Password same as or similar to user');
    
11.    END IF;
    
12. 
    
13.    -- Check if the password contains at least four characters, including
    
14.    -- one letter, one digit and one punctuation mark.
    
15.    IF NOT ora_complexity_check(password, chars => 4, letter => 1, digit => 1,
    
16.                            special => 1) THEN
    
17.       RETURN(FALSE);
    
18.    END IF;
    
19. 
    
20.    -- Check if the password is too simple. A dictionary of words may be
    
21.    -- maintained and a check may be made so as not to allow the words
    
22.    -- that are too simple for the password.
    
23.    IF NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user',
    
24.                               'password', 'oracle', 'computer', 'abcd') THEN
    
25.       raise_application_error(-20002, 'Password too simple');
    
26.    END IF;
    
27. 
    
28.    -- Check if the password differs from the previous password by at least
    
29.    -- 3 letters
    
30.    IF old_password IS NOT NULL THEN
    
31.      differ := ora_string_distance(old_password, password);
    
32.      IF differ < 3 THEN
    
33.          raise_application_error(-20004, 'Password should differ by at' ||
    
34.                                          'least 3 characters');
    
35.      END IF;
    
36.    END IF;
    
37. 
    
38.    RETURN(TRUE);
    
39. END;
    
40. /
    

复制代码

11g：

1. CREATE OR REPLACE FUNCTION verify_function_11G
   
2. (username varchar2,
   
3. password varchar2,
   
4. old_password varchar2)
   
5. RETURN boolean IS
   
6.    differ integer;
   
7.    db_name varchar2(40);
   
8.    i integer;
   
9.    i_char varchar2(10);
   
10.    simple_password varchar2(10);
    
11.    reverse_user varchar2(32);
    
12. BEGIN
    
13.    IF NOT ora_complexity_check(password, chars => 8, letter => 1, digit => 1) THEN
    
14.       RETURN(FALSE);
    
15.    END IF;
    
16. 
    
17.    -- Check if the password is same as the username or username(1-100)
    
18.    IF NLS_LOWER(password) = NLS_LOWER(username) THEN
    
19.      raise_application_error(-20002, 'Password same as or similar to user');
    
20.    END IF;
    
21.    FOR i IN 1..100 LOOP
    
22.       i_char := to_char(i);
    
23.       if NLS_LOWER(username)|| i_char = NLS_LOWER(password) THEN
    
24.         raise_application_error(-20005, 'Password same as or similar to ' ||
    
25.                                         'username ');
    
26.       END IF;
    
27.    END LOOP;
    
28. 
    
29.    -- Check if the password is same as the username reversed
    
30.    FOR i in REVERSE 1..length(username) LOOP
    
31.      reverse_user := reverse_user || substr(username, i, 1);
    
32.    END LOOP;
    
33.    IF NLS_LOWER(password) = NLS_LOWER(reverse_user) THEN
    
34.      raise_application_error(-20003, 'Password same as username reversed');
    
35.    END IF;
    
36. 
    
37.    -- Check if the password is the same as server name and or servername(1-100)
    
38.    select name into db_name from sys.v$database;
    
39.    if NLS_LOWER(db_name) = NLS_LOWER(password) THEN
    
40.       raise_application_error(-20004, 'Password same as or similar ' ||
    
41.                                       'to server name');
    
42.    END IF;
    
43.    FOR i IN 1..100 LOOP
    
44.       i_char := to_char(i);
    
45.       if NLS_LOWER(db_name)|| i_char = NLS_LOWER(password) THEN
    
46.         raise_application_error(-20005, 'Password same as or similar ' ||
    
47.                                         'to server name ');
    
48.       END IF;
    
49.    END LOOP;
    
50. 
    
51.    -- Check if the password is too simple. A dictionary of words may be
    
52.    -- maintained and a check may be made so as not to allow the words
    
53.    -- that are too simple for the password.
    
54.    IF NLS_LOWER(password) IN ('welcome1', 'database1', 'account1', 'user1234',
    
55.                               'password1', 'oracle123', 'computer1',
    
56.                               'abcdefg1', 'change_on_install') THEN
    
57.       raise_application_error(-20006, 'Password too simple');
    
58.    END IF;
    
59. 
    
60.    -- Check if the password is the same as oracle (1-100)
    
61.     simple_password := 'oracle';
    
62.     FOR i IN 1..100 LOOP
    
63.       i_char := to_char(i);
    
64.       if simple_password || i_char = NLS_LOWER(password) THEN
    
65.         raise_application_error(-20006, 'Password too simple ');
    
66.       END IF;
    
67.     END LOOP;
    
68. 
    
69.    -- Check if the password differs from the previous password by at least
    
70.    -- 3 letters
    
71.    IF old_password IS NOT NULL THEN
    
72.      differ := ora_string_distance(old_password, password);
    
73.      IF differ < 3 THEN
    
74.          raise_application_error(-20011, 'Password should differ from the ' ||  
    
75.                                  'old password by at least 3 characters');
    
76.      END IF;
    
77.    END IF;
    
78. 
    
79.    RETURN(TRUE);
    
80. END;
    
81. /

复制代码

[oracle@station90 ~]$ sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Sat Jul 21 10:41:05 2018

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected.
SQL> @11g.sql

Function created.

SQL> alter profile profile1 limit PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION_11g;

Profile altered.

SQL> alter user user2 identified by oracle1;
alter user user2 identified by oracle1
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20001: Password length less than 8
----------------------------
修改一下这个函数的例子：
没修改之前：

1. SQL> alter user user2 identified by user2000;
   
2. 
   
3. User altered.
   

复制代码

把verify_function_11g修改成：
   FOR i IN 1..2000 LOOP
      i_char := to_char(i);
      if NLS_LOWER(username)|| i_char = NLS_LOWER(password) THEN
        raise_application_error(-20005, 'Password same as or similar to ' ||
                                        'username ');
      END IF;
   END LOOP;

1. SQL> alter user user2 identified by user22000;
   
2. alter user user2 identified by user22000
   
3. *
   
4. ERROR at line 1:
   
5. ORA-28003: password verification for the specified password failed
   
6. ORA-20005: Password same as or similar to username
   
7. 
   
8. 
   
9. SQL> alter user user2 identified by user22001;
   
10. 
    
11. User altered.
    
12. 
    
13. SQL>
    

复制代码

12c：

1. CREATE OR REPLACE FUNCTION ora12c_verify_function
   
2. (username varchar2,
   
3. password varchar2,
   
4. old_password varchar2)
   
5. RETURN boolean IS
   
6.    differ integer;
   
7.    pw_lower varchar2(256);
   
8.    db_name varchar2(40);
   
9.    i integer;
   
10.    simple_password varchar2(10);
    
11.    reverse_user varchar2(32);
    
12. BEGIN
    
13.    IF NOT ora_complexity_check(password, chars => 8, letter => 1, digit => 1) THEN
    
14.       RETURN(FALSE);
    
15.    END IF;
    
16. 
    
17.    -- Check if the password contains the username
    
18.    pw_lower := NLS_LOWER(password);
    
19.    IF instr(pw_lower, NLS_LOWER(username)) > 0 THEN
    
20.      raise_application_error(-20002, 'Password contains the username');
    
21.    END IF;
    
22. 
    
23.    -- Check if the password contains the username reversed
    
24.    reverse_user := '';
    
25.    FOR i in REVERSE 1..length(username) LOOP
    
26.      reverse_user := reverse_user || substr(username, i, 1);
    
27.    END LOOP;
    
28.    IF instr(pw_lower, NLS_LOWER(reverse_user)) > 0 THEN
    
29.      raise_application_error(-20003, 'Password contains the username ' ||
    
30.                                      'reversed');
    
31.    END IF;
    
32. 
    
33.    -- Check if the password contains the server name
    
34.    select name into db_name from sys.v$database;
    
35.    IF instr(pw_lower, NLS_LOWER(db_name)) > 0 THEN
    
36.       raise_application_error(-20004, 'Password contains the server name');
    
37.    END IF;
    
38. 
    
39.    -- Check if the password contains 'oracle'
    
40.    IF instr(pw_lower, 'oracle') > 0 THEN
    
41.         raise_application_error(-20006, 'Password too simple');
    
42.    END IF;
    
43. 
    
44.    -- Check if the password is too simple. A dictionary of words may be
    
45.    -- maintained and a check may be made so as not to allow the words
    
46.    -- that are too simple for the password.
    
47.    IF pw_lower IN ('welcome1', 'database1', 'account1', 'user1234',
    
48.                               'password1', 'oracle123', 'computer1',
    
49.                               'abcdefg1', 'change_on_install') THEN
    
50.       raise_application_error(-20006, 'Password too simple');
    
51.    END IF;
    
52. 
    
53.    -- Check if the password differs from the previous password by at least
    
54.    -- 3 characters
    
55.    IF old_password IS NOT NULL THEN
    
56.      differ := ora_string_distance(old_password, password);
    
57.      IF differ < 3 THEN
    
58.         raise_application_error(-20010, 'Password should differ from the '
    
59.                                 || 'old password by at least 3 characters');
    
60.      END IF;
    
61.    END IF ;
    
62. 
    
63.    RETURN(TRUE);
    
64. END;
    
65. /

复制代码

O7_DICTIONARY_ACCESSIBILITY改为true会出现各种怪异：
1. 不需要select_catalog_role，就能查字典。
2. sys不需要sysdba权限能够以"internal"登录。

REMOTE_OS_AUTHENT改为true会对ops$oracle这样的外部验证用户，打开巨大的安全性漏洞：

1. SQL> show parameter authen
   
2. 
   
3. NAME                                     TYPE         VALUE
   
4. ------------------------------------ ----------- ------------------------------
   
5. os_authent_prefix                     string         ops$
   
6. remote_os_authent                     boolean         FALSE
   
7. SQL> conn /@orcl
   
8. ERROR:
   
9. ORA-01017: invalid username/password; logon denied
   
10. 
    
11. 
    
12. Warning: You are no longer connected to ORACLE.
    
13. SQL> conn /
    
14. Connected.
    
15. SQL> show user
    
16. USER is "OPS$ORACLE"
    
17. SQL> conn /@orcl
    
18. ERROR:
    
19. ORA-01017: invalid username/password; logon denied
    
20. 
    
21. 
    
22. Warning: You are no longer connected to ORACLE.
    
23. SQL> conn / as sysdba
    
24. Connected.
    
25. SQL> conn /@orcl as sysdba
    
26. ERROR:
    
27. ORA-01017: invalid username/password; logon denied
    
28. 
    
29. 
    
30. Warning: You are no longer connected to ORACLE.
    
31. SQL> conn / as sysdba
    
32. Connected.
    
33. SQL> show parameter authe
    
34. 
    
35. NAME                                     TYPE         VALUE
    
36. ------------------------------------ ----------- ------------------------------
    
37. os_authent_prefix                     string         ops$
    
38. remote_os_authent                     boolean         FALSE
    
39. SQL> alter system set remote_os_authent=true scope=spfile;
    
40. 
    
41. System altered.
    
42. 
    
43. SQL> startup force
    
44. ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
    
45. ORACLE instance started.
    
46. 
    
47. Total System Global Area 1610612736 bytes
    
48. Fixed Size                    2924928 bytes
    
49. Variable Size                  889196160 bytes
    
50. Database Buffers          704643072 bytes
    
51. Redo Buffers                   13848576 bytes
    
52. Database mounted.
    
53. Database opened.
    
54. SQL> conn /@orcl as sysdba
    
55. ERROR:
    
56. ORA-01017: invalid username/password; logon denied
    
57. 
    
58. 
    
59. Warning: You are no longer connected to ORACLE.
    
60. SQL> conn /@orcl
    
61. Connected.
    
62. SQL> show user
    
63. USER is "OPS$ORACLE"
    
64. SQL>
    
65. 
    

复制代码