Bo's Oracle Station

查看: 2691|回复: 0

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

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2018-7-21 10:38:57 | 显示全部楼层 |阅读模式
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.    -- Check if the password contains at least four characters, including
  13.    -- one letter, one digit and one punctuation mark.
  14.    IF NOT ora_complexity_check(password, chars => 4, letter => 1, digit => 1,
  15.                            special => 1) THEN
  16.       RETURN(FALSE);
  17.    END IF;

  18.    -- Check if the password is too simple. A dictionary of words may be
  19.    -- maintained and a check may be made so as not to allow the words
  20.    -- that are too simple for the password.
  21.    IF NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user',
  22.                               'password', 'oracle', 'computer', 'abcd') THEN
  23.       raise_application_error(-20002, 'Password too simple');
  24.    END IF;

  25.    -- Check if the password differs from the previous password by at least
  26.    -- 3 letters
  27.    IF old_password IS NOT NULL THEN
  28.      differ := ora_string_distance(old_password, password);
  29.      IF differ < 3 THEN
  30.          raise_application_error(-20004, 'Password should differ by at' ||
  31.                                          'least 3 characters');
  32.      END IF;
  33.    END IF;

  34.    RETURN(TRUE);
  35. END;
  36. /
复制代码
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.    -- Check if the password is same as the username or username(1-100)
  17.    IF NLS_LOWER(password) = NLS_LOWER(username) THEN
  18.      raise_application_error(-20002, 'Password same as or similar to user');
  19.    END IF;
  20.    FOR i IN 1..100 LOOP
  21.       i_char := to_char(i);
  22.       if NLS_LOWER(username)|| i_char = NLS_LOWER(password) THEN
  23.         raise_application_error(-20005, 'Password same as or similar to ' ||
  24.                                         'username ');
  25.       END IF;
  26.    END LOOP;

  27.    -- Check if the password is same as the username reversed
  28.    FOR i in REVERSE 1..length(username) LOOP
  29.      reverse_user := reverse_user || substr(username, i, 1);
  30.    END LOOP;
  31.    IF NLS_LOWER(password) = NLS_LOWER(reverse_user) THEN
  32.      raise_application_error(-20003, 'Password same as username reversed');
  33.    END IF;

  34.    -- Check if the password is the same as server name and or servername(1-100)
  35.    select name into db_name from sys.v$database;
  36.    if NLS_LOWER(db_name) = NLS_LOWER(password) THEN
  37.       raise_application_error(-20004, 'Password same as or similar ' ||
  38.                                       'to server name');
  39.    END IF;
  40.    FOR i IN 1..100 LOOP
  41.       i_char := to_char(i);
  42.       if NLS_LOWER(db_name)|| i_char = NLS_LOWER(password) THEN
  43.         raise_application_error(-20005, 'Password same as or similar ' ||
  44.                                         'to server name ');
  45.       END IF;
  46.    END LOOP;

  47.    -- Check if the password is too simple. A dictionary of words may be
  48.    -- maintained and a check may be made so as not to allow the words
  49.    -- that are too simple for the password.
  50.    IF NLS_LOWER(password) IN ('welcome1', 'database1', 'account1', 'user1234',
  51.                               'password1', 'oracle123', 'computer1',
  52.                               'abcdefg1', 'change_on_install') THEN
  53.       raise_application_error(-20006, 'Password too simple');
  54.    END IF;

  55.    -- Check if the password is the same as oracle (1-100)
  56.     simple_password := 'oracle';
  57.     FOR i IN 1..100 LOOP
  58.       i_char := to_char(i);
  59.       if simple_password || i_char = NLS_LOWER(password) THEN
  60.         raise_application_error(-20006, 'Password too simple ');
  61.       END IF;
  62.     END LOOP;

  63.    -- Check if the password differs from the previous password by at least
  64.    -- 3 letters
  65.    IF old_password IS NOT NULL THEN
  66.      differ := ora_string_distance(old_password, password);
  67.      IF differ < 3 THEN
  68.          raise_application_error(-20011, 'Password should differ from the ' ||  
  69.                                  'old password by at least 3 characters');
  70.      END IF;
  71.    END IF;

  72.    RETURN(TRUE);
  73. END;
  74. /
复制代码
[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. 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. SQL> alter user user2 identified by user22001;

  8. User altered.

  9. 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.    -- Check if the password contains the username
  17.    pw_lower := NLS_LOWER(password);
  18.    IF instr(pw_lower, NLS_LOWER(username)) > 0 THEN
  19.      raise_application_error(-20002, 'Password contains the username');
  20.    END IF;

  21.    -- Check if the password contains the username reversed
  22.    reverse_user := '';
  23.    FOR i in REVERSE 1..length(username) LOOP
  24.      reverse_user := reverse_user || substr(username, i, 1);
  25.    END LOOP;
  26.    IF instr(pw_lower, NLS_LOWER(reverse_user)) > 0 THEN
  27.      raise_application_error(-20003, 'Password contains the username ' ||
  28.                                      'reversed');
  29.    END IF;

  30.    -- Check if the password contains the server name
  31.    select name into db_name from sys.v$database;
  32.    IF instr(pw_lower, NLS_LOWER(db_name)) > 0 THEN
  33.       raise_application_error(-20004, 'Password contains the server name');
  34.    END IF;

  35.    -- Check if the password contains 'oracle'
  36.    IF instr(pw_lower, 'oracle') > 0 THEN
  37.         raise_application_error(-20006, 'Password too simple');
  38.    END IF;

  39.    -- Check if the password is too simple. A dictionary of words may be
  40.    -- maintained and a check may be made so as not to allow the words
  41.    -- that are too simple for the password.
  42.    IF pw_lower IN ('welcome1', 'database1', 'account1', 'user1234',
  43.                               'password1', 'oracle123', 'computer1',
  44.                               'abcdefg1', 'change_on_install') THEN
  45.       raise_application_error(-20006, 'Password too simple');
  46.    END IF;

  47.    -- Check if the password differs from the previous password by at least
  48.    -- 3 characters
  49.    IF old_password IS NOT NULL THEN
  50.      differ := ora_string_distance(old_password, password);
  51.      IF differ < 3 THEN
  52.         raise_application_error(-20010, 'Password should differ from the '
  53.                                 || 'old password by at least 3 characters');
  54.      END IF;
  55.    END IF ;

  56.    RETURN(TRUE);
  57. END;
  58. /
复制代码


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

REMOTE_OS_AUTHENT改为true会对ops$oracle这样的外部验证用户,打开巨大的安全性漏洞:
  1. SQL> show parameter authen

  2. NAME                                     TYPE         VALUE
  3. ------------------------------------ ----------- ------------------------------
  4. os_authent_prefix                     string         ops$
  5. remote_os_authent                     boolean         FALSE
  6. SQL> conn /@orcl
  7. ERROR:
  8. ORA-01017: invalid username/password; logon denied


  9. Warning: You are no longer connected to ORACLE.
  10. SQL> conn /
  11. Connected.
  12. SQL> show user
  13. USER is "OPS$ORACLE"
  14. SQL> conn /@orcl
  15. ERROR:
  16. ORA-01017: invalid username/password; logon denied


  17. Warning: You are no longer connected to ORACLE.
  18. SQL> conn / as sysdba
  19. Connected.
  20. SQL> conn /@orcl as sysdba
  21. ERROR:
  22. ORA-01017: invalid username/password; logon denied


  23. Warning: You are no longer connected to ORACLE.
  24. SQL> conn / as sysdba
  25. Connected.
  26. SQL> show parameter authe

  27. NAME                                     TYPE         VALUE
  28. ------------------------------------ ----------- ------------------------------
  29. os_authent_prefix                     string         ops$
  30. remote_os_authent                     boolean         FALSE
  31. SQL> alter system set remote_os_authent=true scope=spfile;

  32. System altered.

  33. SQL> startup force
  34. ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
  35. ORACLE instance started.

  36. Total System Global Area 1610612736 bytes
  37. Fixed Size                    2924928 bytes
  38. Variable Size                  889196160 bytes
  39. Database Buffers          704643072 bytes
  40. Redo Buffers                   13848576 bytes
  41. Database mounted.
  42. Database opened.
  43. SQL> conn /@orcl as sysdba
  44. ERROR:
  45. ORA-01017: invalid username/password; logon denied


  46. Warning: You are no longer connected to ORACLE.
  47. SQL> conn /@orcl
  48. Connected.
  49. SQL> show user
  50. USER is "OPS$ORACLE"
  51. SQL>

复制代码








回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-29 16:53 , Processed in 0.035999 second(s), 24 queries .

快速回复 返回顶部 返回列表