Step 1: Create password verification function

CREATE OR REPLACE FUNCTION sys.verify_password(user_name varchar2, new_password varchar2, old_password varchar)
RETURNS boolean IMMUTABLE
LANGUAGE plpgsql
AS $function$
BEGIN
IF (length(new_password) < 8)
THEN
— raise_application_error(-20001, ‘too short’);
RAISE EXCEPTION ‘too short’;
END IF;
IF new_password= old_password
THEN
— raise_application_error(-20002, ‘includes old password’);
RAISE exception ‘includes old password’;
END IF;
IF substring(upper(new_password) FROM upper(user_name)) IS NOT NULL
THEN
— raise_application_error(-20003, ‘New passowrd not allowed to include username’);
RAISE exception ‘New password not be allowed to contain username’;
END IF;
IF (SELECT count() from regexp_matches(new_password,'[a-z]’,’g’) )<1 THEN — raise_application_error(-20004, ‘Must be at least ONE lowercase character’); RAISE exception ‘Must be at least 1 lowercase characters’; END IF; IF (SELECT count()
from regexp_matches(new_password,'[A-Z]’,’g’) )<1
THEN
— raise_application_error(-20005, ‘Must be at least ONE uppercase character’);
RAISE exception ‘Must be at least 1 uppercase characters’;
END IF;
IF (SELECT count() from regexp_matches(new_password,'[0-9]’,’g’) )<1 THEN — raise_application_error(-20006, ‘Must be at least ONE digit’); RAISE exception ‘Must be at least 1 digits’; END IF; IF (SELECT count()
from regexp_matches(new_password,'[^A-Za-z0-9]’,’g’) )<1
THEN
— raise_application_error(-20006, ‘Must be at least ONE special character’);
RAISE exception ‘Must be at least 1 special characters’;
END IF;
return 1;
END;
$function$
;

Step 2 – Create the profile

CREATE PROFILE USER_PROFILE LIMIT FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LIFE_TIME 60 PASSWORD_GRACE_TIME 7 PASSWORD_REUSE_TIME 365 PASSWORD_REUSE_MAX 6 PASSWORD_LOCK_TIME 1 PASSWORD_VERIFY_FUNCTION verify_password;

CREATE PROFILE APPUSER LIMIT FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LIFE_TIME 365 PASSWORD_GRACE_TIME 15 PASSWORD_REUSE_TIME 365 PASSWORD_REUSE_MAX 6 PASSWORD_LOCK_TIME 1 PASSWORD_VERIFY_FUNCTION verify_password;

Step 3 – Assign profile to users

ALTER USER hayat1 PROFILE USER_PROFILE;

CREATE user hayat1 LOGIN PASSWORD ‘Welcome212@12’;

CREATE USER my_user WITH ROLE my_role;