/*
 MODIFIED from utlpwdmg.sql
 Original version at $ORACLE_HOME/rdbms/admin/utlpwdmg.sql
 Changes are noted with --Yong:...
*/
CREATE OR REPLACE FUNCTION verify_function
( username varchar2,
  password varchar2,
  old_password varchar2)
  RETURN boolean IS 
   n boolean;
   m integer;
   differ integer;
   isdigit boolean;
   ischar  boolean;
   ispunct boolean;
   digitarray varchar2(20);
   punctarray varchar2(25);
   chararray varchar2(52);

BEGIN 
   digitarray:= '0123456789';
   chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
   punctarray:='!"#$%&()``*+,-/:;<=>?_';

   -- 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 for the minimum length of the password
   IF length(password) < 7 THEN
      raise_application_error(-20002, 'Password length less than 7');
   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 contains at least one letter, one digit and one
   -- punctuation mark.
   -- 1. Check for the digit
   isdigit:=FALSE;
   m := length(password);
   FOR i IN 1..10 LOOP 
      FOR j IN 1..m LOOP 
         IF substr(password,j,1) = substr(digitarray,i,1) THEN
            isdigit:=TRUE;
             GOTO findchar;
         END IF;
      END LOOP;
   END LOOP;
 IF isdigit = FALSE THEN
--Yong: The line below (and some similar lines later) should be one line,
--without using \ followed by a line break and some blank spaces. Developer of
--the original utlpwdmg.sql was thinking of UNIX shell script I guess.
      raise_application_error(-20003, 'Password should contain at least one digit, one character and one punctuation');
   END IF;
   -- 2. Check for the character
   <<findchar>>
   ischar:=FALSE;
   FOR i IN 1..length(chararray) LOOP 
      FOR j IN 1..m LOOP 
         IF substr(password,j,1) = substr(chararray,i,1) THEN
            ischar:=TRUE;
             GOTO findpunct;
         END IF;
      END LOOP;
   END LOOP;
   IF ischar = FALSE THEN
      raise_application_error(-20003, 'Password should contain at least one digit, one character and one punctuation');
   END IF;
   -- 3. Check for the punctuation
   <<findpunct>>
   ispunct:=FALSE;
   FOR i IN 1..length(punctarray) LOOP 
      FOR j IN 1..m LOOP 
         IF substr(password,j,1) = substr(punctarray,i,1) THEN
            ispunct:=TRUE;
             GOTO endsearch;
         END IF;
      END LOOP;
   END LOOP;
   IF ispunct = FALSE THEN
      raise_application_error(-20003, 'Password should contain at least one digit, one character and one punctuation');
   END IF;

   <<endsearch>>
   -- Check if the password differs from the previous password by at least
   -- 3 letters
--Yong: I added the following check.
   IF old_password is null THEN
      raise_application_error(-20004, 'Old password is null');
   END IF;

--Yong: I moved ABS function up here so it may run a little faster.
   differ := abs(length(old_password) - length(password));

   IF differ < 3 THEN
      IF length(password) < length(old_password) THEN
         m := length(password);
      ELSE
         m := length(old_password);
      END IF;
      FOR i IN 1..m LOOP
--Yong: I think we need to compare each char case-insensitively because Oracle
--passwords are not case-sensitive (until 11g with a patch!); otherwise passwords
--"abc1," will be considered different from "ABC1," (without quotes) and pass this
--verify_function test. So the line below may need to be
-- IF lower(substr(password,i,1)) != lower(substr(old_password,i,1)) THEN
--I need to check this again.
--However, if you try to change password from "abc1," to "ABC1,", although you
--won't be caught by this verify_function, you'll be caught by Oracle which
--throws ORA-28007 (password cannot be reused).
          IF substr(password,i,1) != substr(old_password,i,1) THEN
             differ := differ + 1;
          END IF;
      END LOOP;
      IF differ < 3 THEN
          raise_application_error(-20004, 'Password should differ by at least 3 characters');
      END IF;
   END IF;
   -- Everything is fine; return TRUE ;   
   RETURN(TRUE);
END;
/

--------------------------------------------------------------------------------

ALTER PROFILE user_profile LIMIT
PASSWORD_LIFE_TIME 60
PASSWORD_GRACE_TIME 10
PASSWORD_REUSE_TIME 1800
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1/1440
PASSWORD_VERIFY_FUNCTION verify_function;

--------------------------------------------------------------------------------

Here're the rules enforced by the function:

1. The password should not be the same as the username, compared case-INSENSITIVEly. Or you will (or should, due to 8.1.7.2 bug) get "Password same as or similar to user". I don't know why the message contains the words "or similar to".

2. The password should not have a length of less than 4 characters. Or you'll get "Password length less than 4". This can be changed easily.

3. The password should not be one of these words: 'welcome', 'database', 'account', 'user', 'password', 'oracle', 'computer', 'abcd', compared case-INSENSITIVEly. Or you'll get "Password too simple". We can easily expand this word list.

4. The password should contain at least one ASCII character (English letter, either case is fine), one digit, and one punctuation mark (i.e., one of !"#$%&()``*+,-/:;<=>?_). Or you'll get "Password should contain at least one digit, one character and one punctuation".

5. The password "should differ by at least 3 characters" from the previous password. The check is done this way. Get the old and new password length difference in absolute value. If it's less than 3, add to it the number of different characters compared case-SENSITIVEly in the same position in the two passwords. Therefore, "abc1," will be considered different by more than 3 characters from "bc1,a" or "ABC1,". , However, this only means you can change abc1, to ABC1, without being caught by verify_function; it doesn't mean you won't be caught by Oracle. In fact, you'll get ORA-28007 (password cannot be reused) if you do this.

Two comments:

<One> There's no way to enforce case sensitivity in passwords. Oracle internally doesn't care about the case in password input:

SQL> alter user yong identified by "AbCd1,";

User altered.

SQL> conn yong/abcd1,@mgdc
Connected.


Make the simple fix to the code to compare case-insensitively, plus change the "\" at some line ends to -.

<Two> My previous message implies that as long as we grant alter user privilege to the users confined by this password profile, the bug (not launching verify_function) will be gone. I just found that that's not completely true; Rule 5 is still not enforced. For instance, I can use alter user to change password from "abc1," to "abc1*" even though they differ by only 1 character. SQL*Plus password command fully supports these rules. I highly recommend everyone use SQL*Plus, not only for reasons of password change, but also for performance reason (Toad or other GUI tools always run commands like "select * from all_XXX" possibly without a where clause when you go to any screen; but SQL*Plus runs a SQL only when you submit a command).

Yong

--------------------------------------------------------------------------------

From: Huang, Yong 
Sent: Wednesday, April 30, 2003 8:53 AM

The password verification function comes from the Oracle default verify_function as defined in $ORACLE_HOME/rdbms/admin/utlpwdmg.sql. Among others, it requires a punctuation. Unfortunately, in this version of Oracle (8.1.7.2), the "alter user identified by" command has problems (Bug:1231172) if the user doesn't have alter user privilege. The workaround is to use the SQL*Plus password command (unless the database is upgraded to 8.1.7.4). In fact the password command may be the preferred way since it doesn't echo the password when you type it whereas alter user command shows it on screen.

SQL> password
Changing password for Q
Old password:
New password:
Retype new password:
ERROR:
ORA-28003: password verification for the specified password failed
ORA-20004: Password should differ by at \
least 3 characters


Password unchanged
SQL> password
Changing password for Q
Old password:
New password:
Retype new password:
Password changed

Note that the backslash in my first attemp simply serves as a line continuation mark, an unsightly artifact in $ORACLE_HOME/rdbms/admin/utlpwdmg.sql.

If you want to change other people's passwords (if you have the privilege), simply type password thatuser.

Since it's a SQL*Plus command, Toad won't work.

Yong

--------------------------------------------------------------------------------

How interesting? I can't set password_lock_time to 1 second or below:

SQL> create profile user_profile limit
  2  failed_login_attempts 5
  3  password_life_time 180
  4  password_reuse_max unlimited
  5  password_verify_function verify_function
  6  password_lock_time 1/86400
  7  idle_time 600
  8  password_grace_time 15
  9  ;
create profile user_profile limit
*
ERROR at line 1:
ORA-02377: invalid resource limit


SQL> create profile user_profile limit
  2  failed_login_attempts 5
  3  password_life_time 180
  4  password_reuse_max unlimited
  5  password_verify_function verify_function
  6  password_lock_time 1/86399
  7  idle_time 600
  8  password_grace_time 15
  9  ;

Profile created.

alter profile user_profile limit PASSWORD_LIFE_TIME 90 PASSWORD_REUSE_TIME unlimited PASSWORD_REUSE_MAX 1;

Interesting to know is that, users must add a punctuation mark to the password, therefore SQL command has to have double quotes around the password:

SQL> create user yong identified by yong default tablespace users temporary tablespace temp profile user_profile;
create user yong identified by yong default tablespace users temporary tablespace temp profile user_profile
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20001: Password same as or similar to user

SQL> create user yong identified by yongpassword default tablespace users temporary tablespace temp profile user_profile;
create user yong identified by yongpassword default tablespace users temporary tablespace temp profile user_profile
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20003: Password should contain at least one digit, one character and one punctuation

SQL> create user yong identified by qwerty123456, default tablespace users temporary tablespace temp profile user_profile;
create user yong identified by qwerty123456, default tablespace users temporary tablespace temp profile user_profile
                                           *
ERROR at line 1:
ORA-00922: missing or invalid option


SQL> create user yong identified by "qwerty123456," default tablespace users temporary tablespace temp profile user_profile;

User created.

SQL> grant create session to yong;

Grant succeeded.


SQL*Plus connection doesn't need quotes:

SQL> conn yong/qwerty123456,@mgdc
Connected.


Interestingly, once the profile is created, password_lock_time is shown as 0:

SQL> select resource_name, limit from dba_profiles where profile = 'USER_PROFILE';

RESOURCE_NAME                    LIMIT
-------------------------------- -----------------
COMPOSITE_LIMIT                  DEFAULT
SESSIONS_PER_USER                DEFAULT
CPU_PER_SESSION                  DEFAULT
CPU_PER_CALL                     DEFAULT
LOGICAL_READS_PER_SESSION        DEFAULT
LOGICAL_READS_PER_CALL           DEFAULT
IDLE_TIME                        600
CONNECT_TIME                     DEFAULT
PRIVATE_SGA                      DEFAULT
FAILED_LOGIN_ATTEMPTS            5
PASSWORD_LIFE_TIME               180
PASSWORD_REUSE_TIME              DEFAULT
PASSWORD_REUSE_MAX               UNLIMITED
PASSWORD_VERIFY_FUNCTION         VERIFY_FUNCTION
PASSWORD_LOCK_TIME               0
PASSWORD_GRACE_TIME              15

16 rows selected.