Create a profile and add users

When attempting to change the password for the TEST user, I encountered the following error.

alter user TEST identified by Test#123;

Error report -
ORA-28003: password verification for the specified password failed
ORA-20000: password length less than 9 bytes
28003. 00000 — “password verification for the specified password failed”
*Cause: The new password did not meet the necessary complexity
specifications and the password_verify_function failed
*Action: Enter a different password. Contact the DBA to know the rules for
choosing the new password

I had to create a new profile and add a test user to it.

Here's how it's going.

Executing the first command, you can get details about each profile and by executing the second, you can get details about the DEFAULT profile details.

select PROFILE,RESOURCE_NAME,LIMIT from dba_profiles order by PROFILE;

select PROFILE,RESOURCE_NAME,LIMIT from dba_profiles
where PROFILE=’DEFAULT’ order by PROFILE;

Here is the interesting part.

Please use the following command to create a PROFILE with customizable options and modify them according to your needs.

CREATE PROFILE <PROFILE_NAME> LIMIT
PASSWORD_LIFE_TIME 90
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 7
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 1;

EX:
SQL> alter profile <PROFILE_NAME> limit PASSWORD_VERIFY_FUNCTION ORA12C_STRONG_VERIFY_FUNCTION;

SQL>alter profile <PROFILE_NAME> limit PASSWORD_REUSE_TIME 3;

SQL>alter profile <PROFILE_NAME> limit PASSWORD_LIFE_TIME UNLIMITED;

Here is how to Add the TEST user to the PROFILE.

alter user TEST profile APP;

Changing the TEST user password is now possible without encountering any errors, since the PASSWORD_VERIFY_FUNCTION has been set to NULL.

alter user TEST identified by Test#123;
User TEST altered.