verify_function的10g/11g/12c的版本
10g：CREATE OR REPLACE FUNCTION verify_function
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
differ integer;
BEGIN
-- Check if the password is same as the username
IF NLS_LOWER(password) = NLS_LOWER(username) THEN
raise_application_error(-20001, 'Password same as or similar to user');
END IF;
-- Check if the password contains at least four characters, including
-- one letter, one digit and one punctuation mark.
IF NOT ora_complexity_check(password, chars => 4, letter => 1, digit => 1,
special => 1) THEN
RETURN(FALSE);
END IF;
-- Check if the password is too simple. A dictionary of words may be
-- maintained and a check may be made so as not to allow the words
-- that are too simple for the password.
IF NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user',
'password', 'oracle', 'computer', 'abcd') THEN
raise_application_error(-20002, 'Password too simple');
END IF;
-- Check if the password differs from the previous password by at least
-- 3 letters
IF old_password IS NOT NULL THEN
differ := ora_string_distance(old_password, password);
IF differ < 3 THEN
raise_application_error(-20004, 'Password should differ by at' ||
'least 3 characters');
END IF;
END IF;
RETURN(TRUE);
END;
/
11g：
CREATE OR REPLACE FUNCTION verify_function_11G
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
differ integer;
db_name varchar2(40);
i integer;
i_char varchar2(10);
simple_password varchar2(10);
reverse_user varchar2(32);
BEGIN
IF NOT ora_complexity_check(password, chars => 8, letter => 1, digit => 1) THEN
RETURN(FALSE);
END IF;
-- Check if the password is same as the username or username(1-100)
IF NLS_LOWER(password) = NLS_LOWER(username) THEN
raise_application_error(-20002, 'Password same as or similar to user');
END IF;
FOR i IN 1..100 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;
-- Check if the password is same as the username reversed
FOR i in REVERSE 1..length(username) LOOP
reverse_user := reverse_user || substr(username, i, 1);
END LOOP;
IF NLS_LOWER(password) = NLS_LOWER(reverse_user) THEN
raise_application_error(-20003, 'Password same as username reversed');
END IF;
-- Check if the password is the same as server name and or servername(1-100)
select name into db_name from sys.v$database;
if NLS_LOWER(db_name) = NLS_LOWER(password) THEN
raise_application_error(-20004, 'Password same as or similar ' ||
'to server name');
END IF;
FOR i IN 1..100 LOOP
i_char := to_char(i);
if NLS_LOWER(db_name)|| i_char = NLS_LOWER(password) THEN
raise_application_error(-20005, 'Password same as or similar ' ||
'to server name ');
END IF;
END LOOP;
-- Check if the password is too simple. A dictionary of words may be
-- maintained and a check may be made so as not to allow the words
-- that are too simple for the password.
IF NLS_LOWER(password) IN ('welcome1', 'database1', 'account1', 'user1234',
'password1', 'oracle123', 'computer1',
'abcdefg1', 'change_on_install') THEN
raise_application_error(-20006, 'Password too simple');
END IF;
-- Check if the password is the same as oracle (1-100)
simple_password := 'oracle';
FOR i IN 1..100 LOOP
i_char := to_char(i);
if simple_password || i_char = NLS_LOWER(password) THEN
raise_application_error(-20006, 'Password too simple ');
END IF;
END LOOP;
-- Check if the password differs from the previous password by at least
-- 3 letters
IF old_password IS NOT NULL THEN
differ := ora_string_distance(old_password, password);
IF differ < 3 THEN
raise_application_error(-20011, 'Password should differ from the ' ||
'old password by at least 3 characters');
END IF;
END IF;
RETURN(TRUE);
END;
/$ 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
----------------------------
修改一下这个函数的例子：
没修改之前：
SQL> alter user user2 identified by user2000;
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;
SQL> alter user user2 identified by user22000;
alter user user2 identified by user22000
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20005: Password same as or similar to username
SQL> alter user user2 identified by user22001;
User altered.
SQL>
12c：
CREATE OR REPLACE FUNCTION ora12c_verify_function
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
differ integer;
pw_lower varchar2(256);
db_name varchar2(40);
i integer;
simple_password varchar2(10);
reverse_user varchar2(32);
BEGIN
IF NOT ora_complexity_check(password, chars => 8, letter => 1, digit => 1) THEN
RETURN(FALSE);
END IF;
-- Check if the password contains the username
pw_lower := NLS_LOWER(password);
IF instr(pw_lower, NLS_LOWER(username)) > 0 THEN
raise_application_error(-20002, 'Password contains the username');
END IF;
-- Check if the password contains the username reversed
reverse_user := '';
FOR i in REVERSE 1..length(username) LOOP
reverse_user := reverse_user || substr(username, i, 1);
END LOOP;
IF instr(pw_lower, NLS_LOWER(reverse_user)) > 0 THEN
raise_application_error(-20003, 'Password contains the username ' ||
'reversed');
END IF;
-- Check if the password contains the server name
select name into db_name from sys.v$database;
IF instr(pw_lower, NLS_LOWER(db_name)) > 0 THEN
raise_application_error(-20004, 'Password contains the server name');
END IF;
-- Check if the password contains 'oracle'
IF instr(pw_lower, 'oracle') > 0 THEN
raise_application_error(-20006, 'Password too simple');
END IF;
-- Check if the password is too simple. A dictionary of words may be
-- maintained and a check may be made so as not to allow the words
-- that are too simple for the password.
IF pw_lower IN ('welcome1', 'database1', 'account1', 'user1234',
'password1', 'oracle123', 'computer1',
'abcdefg1', 'change_on_install') THEN
raise_application_error(-20006, 'Password too simple');
END IF;
-- Check if the password differs from the previous password by at least
-- 3 characters
IF old_password IS NOT NULL THEN
differ := ora_string_distance(old_password, password);
IF differ < 3 THEN
raise_application_error(-20010, 'Password should differ from the '
|| 'old password by at least 3 characters');
END IF;
END IF ;
RETURN(TRUE);
END;
/
O7_DICTIONARY_ACCESSIBILITY改为true会出现各种怪异：
1. 不需要select_catalog_role，就能查字典。
2. sys不需要sysdba权限能够以"internal"登录。
REMOTE_OS_AUTHENT改为true会对ops$oracle这样的外部验证用户，打开巨大的安全性漏洞：
SQL> show parameter authen
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix string ops$
remote_os_authent boolean FALSE
SQL> conn /@orcl
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn /
Connected.
SQL> show user
USER is "OPS$ORACLE"
SQL> conn /@orcl
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> conn /@orcl as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> show parameter authe
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix string ops$
remote_os_authent boolean FALSE
SQL> alter system set remote_os_authent=true scope=spfile;
System altered.
SQL> startup force
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 2924928 bytes
Variable Size 889196160 bytes
Database Buffers 704643072 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.
SQL> conn /@orcl as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn /@orcl
Connected.
SQL> show user
USER is "OPS$ORACLE"
SQL>
页:
[1]