Oracle for Beginner: Password Resource Management

Everytime a user account was created, Oracle will assign a profile for that user. The default profile is 'DEFAULT' if for that user was not specified the profile when was created. There are two kind of resouces that can be managed in a profile: Kernel Resouce and Password resource. Lets we try to do a simple exercise to manage password resource:

oracle$ sqlplus /nolog
SQL> conn / as sysdba
SQL> select profile from dba_profiles;
(you will see all of profile objects that have been created. There are two default profile objects, i.e. DEFAULT and MONITORING_PROFILE).
SQL> select * from dba_profiles where profile='DEFAULT' and resource_type='PASSWORD';

PROFILE              RESOURCE_NAME             RESOURCE LIMIT
-------------------- ------------------------- -------- --------------------
DEFAULT              FAILED_LOGIN_ATTEMPTS     PASSWORD 10
DEFAULT              PASSWORD_LIFE_TIME        PASSWORD UNLIMITED
DEFAULT              PASSWORD_REUSE_TIME       PASSWORD UNLIMITED
DEFAULT              PASSWORD_REUSE_MAX        PASSWORD UNLIMITED
DEFAULT              PASSWORD_VERIFY_FUNCTION  PASSWORD NULL
DEFAULT              PASSWORD_LOCK_TIME        PASSWORD UNLIMITED
DEFAULT              PASSWORD_GRACE_TIME       PASSWORD UNLIMITED

(with this query, we can obtain all of password resource parameter for profile DEFAULT)
SQL> @$ORACLE_HOME/rdbms/admin/utlpwdmg.sql
(this is a build-in script that will change password resource parameters in DEFAULT profile to make some tight restriction for password management. utlpwdmg.sql script will create a new stored function which is called verify_function, and then alter the DEFAULT profile)
SQL> select * from dba_profiles where profile='DEFAULT' and resource_type='PASSWORD';

PROFILE              RESOURCE_NAME             RESOURCE LIMIT
-------------------- ------------------------- -------- --------------------
DEFAULT              FAILED_LOGIN_ATTEMPTS     PASSWORD 3
DEFAULT              PASSWORD_LIFE_TIME        PASSWORD 60
DEFAULT              PASSWORD_REUSE_TIME       PASSWORD 1800
DEFAULT              PASSWORD_REUSE_MAX        PASSWORD UNLIMITED
DEFAULT              PASSWORD_VERIFY_FUNCTION  PASSWORD VERIFY_FUNCTION
DEFAULT              PASSWORD_LOCK_TIME        PASSWORD .0006
DEFAULT              PASSWORD_GRACE_TIME       PASSWORD 10

Now, the DEFAULT profile was changed. Try some practice below:
1. try to create a new user account.

SQL> conn / as sysdba
SQL> create user myuser identified by oracle;
(because the password_verify_function has been changed with verify_function, evertime new password want to save to oracle should consider some rules that already have defined by verify_function.)
SQL> create user myuser identified by oracle_2007;
(done!)
SQL> grant connect to myuser;
SQL>

2. try to login with invalid password for 3 times, and then login with valid password for last time:

SQL> conn myuser/gggg
SQL> conn myuser/rrrr
SQL> conn myuser/ttttt
SQL> conn myuser/oracle_2007

ERROR:
ORA-28000: the account is locked

(what happen? yes, your account is locked, because we have failed to login for 3 times (FAILED_LOGIN_ATTEMPTS). Now, we should wait for about 1 minute (1/1440) which is 1440 is number of minutes in a day, see PASSWORD_LOCK_TIME).
(after 1 minute, try to login again with correct password)
SQL> conn myuser/oracle_2007

Connected.

3. The parameter PASSWORD_LIFE_TIME is to determine the age of your password (in days). After the (PASSWORD_LIFE_TIME + 1)th day, you have PASSWORD_GRACE_TIME days to retain your old password. Now, we will try to change both parameters for short time, so you can try it immediately.
SQL> conn / as sysdba
SQL> alter profile default limit
PASSWORD_LIFE_TIME 5/1440
PASSWORD_GRACE_TIME 5/1440;
SQL> conn myuser/oracle_2007
(please wait for about 5 minutes)
SQL> conn myuser/oracle_2007

ERROR:
ORA-28002: the password will expire within 0 days

(please wait for about 5 minutes again)
SQL> conn myuser/oracle_2007

ERROR:
ORA-28001: the password has expired

Changing password for testing
New password: 

(please give your new password with minimum 3 new character that different from the old one)
SQL> conn / as sysdba
SQL> alter profile default limit
PASSWORD_LIFE_TIME 60
PASSWORD_GRACE_TIME 10;


4. Between parameter PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX should be choosen only one. It's mean, if we mention a value for one parameter, the second parameter should have UNLIMITED value. Both parameters are used to determine how many times old passwords can be reuse again.