Create New Password_Verify Function in Oracle database 19c

Create function using the sql script.

## CREATE FUNCTION ##

create sql file

CREATE FUNCTION test_pw_verify
(username     varchar2,
 password     varchar2,
 old_password varchar2)
RETURN boolean IS
   differ   integer;
   db_name  varchar2(40);
   i	    integer;
   reverse_user dbms_id;
   canon_username dbms_id := username;
   lang     varchar2(512);
   message  varchar2(512);
   ret	    number;


BEGIN
   -- Get the cur context lang and use utl_lms for messages- Bug 22730089
   lang := sys_context('userenv','lang');
   lang := substr(lang,1,instr(lang,'_')-1);


   -- Bug 22369990: Dbms_Utility may not be available at this point, so switch
   -- to dynamic SQL to execute canonicalize procedure.
   IF (substr(username,1,1) = '"') THEN


     execute immediate 'begin dbms_utility.canonicalize(:p1,  :p2, 128); end;'
			using IN username, OUT canon_username;
   END IF;
   IF NOT ora_complexity_check(password, chars => 8, letter => 1, digit => 1,
			       special => 1) THEN
      RETURN(FALSE);
   END IF;


   -- Check if the password contains the username
   IF regexp_instr(password, canon_username, 1, 1, 0, 'i') > 0 THEN
      ret := utl_lms.get_message(28207, 'RDBMS', 'ORA', lang, message);
      raise_application_error(-20000, message);
   END IF;


   -- Check if the password contains the username reversed
   FOR i in REVERSE 1..length(canon_username) LOOP
     reverse_user := reverse_user || substr(canon_username, i, 1);
   END LOOP;
   IF regexp_instr(password, reverse_user, 1, 1, 0, 'i') > 0 THEN
      ret := utl_lms.get_message(28208, 'RDBMS', 'ORA', lang, message);
      raise_application_error(-20000, message);
   END IF;


   -- Check if the password contains the server name
   select name into db_name from sys.v$database;
   IF regexp_instr(password, db_name, 1, 1, 0, 'i') > 0 THEN
      ret := utl_lms.get_message(28209, 'RDBMS', 'ORA', lang, message);
      raise_application_error(-20000, message);
   END IF;


   -- Check if the password contains 'oracle'
   IF regexp_instr(password, 'oracle', 1, 1, 0, 'i') > 0 THEN
      ret := utl_lms.get_message(28210, 'RDBMS', 'ORA', lang, message);


      raise_application_error(-20000, message);
   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
	ret := utl_lms.get_message(28211, 'RDBMS', 'ORA', lang, message);
	raise_application_error(-20000, utl_lms.format_message(message, 'three')
);

     END IF;
   END IF ;

   RETURN(TRUE);
END;
/

save it test_pw_verify.sql

Run in sqlplus

SQL> @test_pw_verfy.sql

Verify Function

SQL> select object_name from dba_objects where object_type='FUNCTION' and object_name like '%VERIFY%'

Now Create Profile

Create PROFILE test_profile LIMIT
PASSWORD_LIFE_TIME 180
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX  UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LOCK_TIME 1
INACTIVE_ACCOUNT_TIME UNLIMITED
PASSWORD_VERIFY_FUNCTION test_pw_verify;

Now assign the profile to the user

alter user test profile test_profile;

Verify the user gets new profile

select username, profile from dba_users where username = 'TEST';