We take the following steps to make a new user change his password at the first time logon to the database.
Create User
We set an initial password for the new user when creating it.
SQL> create user erpapp identified by erpapp;
User created.
You may choose a more complex one for the initial password.
Grant Create Session
We provide the new user the ability to connect to the database.
SQL> grant create session to erpapp;
Grant succeeded.
You may grant some other privileges he needs.
Password Expire
We expire the initial password so as to make the new user to change his password for the first time logon.
SQL> alter user erpapp password expire;
User altered.
Even though we expired the initial password, it's still connectable for the user.
PASSWORD_REUSE_TIME
To make the new user choose a different password other than the initial one, you have to make sure that PASSWORD_REUSE_TIME is not unlimited.
SQL> select limit from dba_profiles where profile = 'DEFAULT' and resource_name = 'PASSWORD_REUSE_TIME';
LIMIT
--------------------------------------------------------------------------------
180
If the returned value is UNLIMITED, then you may change the value by this:
SQL> alter profile default limit password_reuse_time 180;
Profile altered.
Test Connection
Let's see what will happen at his first time logon.
SQL> conn erpapp@orclpdb
Enter password:
ERROR:
ORA-28001: the password has expired
Changing password for erpapp
New password:
Retype new password:
Password changed
Connected.
As you can see, the user have to change the password for the first time logon.
If the user insists to use the same password to be the new one, he will receive error ORA-28007 to remind the user to choose a different password.
With the same trick, we can make users change their passwords whenever the situation requires.