Sunday, July 22, 2007

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.

2 comments:

UML with ICONIX Process said...

It was a wonderful explanation of profiles.

Thanks and keep it up..
Syed.

Pratik said...

thanks. This was a helpful