Custom password security

See Privileges and grants
See Profiles
delete password history
delete from sys.USER_HISTORY$ where user# = (select user_id from dba_users where username = 'SPOLVERIGIANI')

alter system flush shared_pool;

 

Enable password complexity
Oracle supplied routine in ?/rdbms/admin/utlpwdmg.sql
Run as SYS:

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) < 4 THEN
      raise_application_error(-20002, 'Password length less than 4');
   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', 'manager') 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
      raise_application_error(-20003, 'Password should contain at least one digit, one character and one punctuation');
   END IF;
   -- 2. Check for the character
   <>
   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
   <>
   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;
   <>
   -- Check if the password differs from the previous password by at least
   -- 3 letters
   IF old_password IS NOT NULL THEN
     differ := length(old_password) - length(password);
     IF abs(differ) < 3 THEN
       IF length(password) < length(old_password) THEN
         m := length(password);
       ELSE
         m := length(old_password);
       END IF;
       differ := abs(differ);
       FOR i IN 1..m LOOP
         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;
   END IF;
   -- Everything is fine; return TRUE ;   
   RETURN(TRUE);
END;
/

 

CREATE PROFILE "APP_SUPP" 
    LIMIT CPU_PER_SESSION DEFAULT CPU_PER_CALL DEFAULT 
    CONNECT_TIME DEFAULT IDLE_TIME DEFAULT SESSIONS_PER_USER 
    DEFAULT LOGICAL_READS_PER_SESSION DEFAULT 
    LOGICAL_READS_PER_CALL DEFAULT PRIVATE_SGA DEFAULT 
    COMPOSITE_LIMIT DEFAULT FAILED_LOGIN_ATTEMPTS UNLIMITED 
    PASSWORD_LOCK_TIME UNLIMITED PASSWORD_GRACE_TIME UNLIMITED 
    PASSWORD_LIFE_TIME UNLIMITED PASSWORD_REUSE_MAX UNLIMITED 
    PASSWORD_REUSE_TIME UNLIMITED 
	PASSWORD_VERIFY_FUNCTION verify_function

 

db
ORA-28221: REPLACE not specified

----

You have probably enabled profiles with custom function.
Supply the old password:
alter user tst identified by "pioone" replace "pioo"

 

db
ORA-00988: missing or invalid password(s)

-------------
Use double quotes:
alter user tst identified by "43fg!" replace "12ab!"

 

create user
CREATE USER &1 IDENTIFIED BY "1234abcd"
 DEFAULT TABLESPACE USERS
 TEMPORARY TABLESPACE TEMP
 QUOTA UNLIMITED ON USERS
 PASSWORD EXPIRE
 PROFILE FW_USER_NOM;

grant connect to &&1;