PASSWORD_VERIFY_FUNCTION
To enforce password complexity and strength to users, we can choose one of the native password verify functions for the profile.
GRANT EXECUTE ON Verify Function TO PUBLIC
A customized verify function should be granted to PUBLIC to execute before applying it on any profile.
SQL> grant execute on verify_function_custom to public;
Grant succeeded.
However, all native password verify functions should have been already granted to PUBLIC to use, so this step can be ignored.
ALTER PROFILE LIMIT PASSWORD_VERIFY_FUNCTION
The step is to set a limit PASSWORD_VERIFY_FUNCTION to it.
SQL> alter profile default limit password_verify_function ora12c_verify_function;
Profile altered.
Then we make sure the result.
SQL> column limit format a30;
SQL> select limit from dba_profiles where profile = 'DEFAULT' and resource_name = 'PASSWORD_VERIFY_FUNCTION';
LIMIT
------------------------------
ORA12C_VERIFY_FUNCTION
The result is expected.
ALTER USER PASSWORD EXPIRE
Additionally, we can expire users' passwords to make the new policy take effective immediately.
SQL> alter user hr password expire;
User altered.
SQL> alter user sh password expire;
User altered.
...
They'll see the change very soon. Just like we make new users change password at their first time login.
If the enforcement resulted unexpected problems, we have a way to disable the password verify function.