Monday, November 2, 2009

Database User Profile resource_type = 'PASSWORD'

Database User Profile resource_type = 'PASSWORD'
================================================
syntax:-

CREATE PROFILE profile
LIMIT
{ { FAILED_LOGIN_ATTEMPTS
| PASSWORD_LIFE_TIME
| PASSWORD_REUSE_TIME
| PASSWORD_REUSE_MAX
| PASSWORD_LOCK_TIME
| PASSWORD_GRACE_TIME } { expr | UNLIMITED | DEFAULT }
| PASSWORD_VERIFY_FUNCTION { function | NULL | DEFAULT }
};

FAILED_LOGIN_ATTEMPTS
-------------------
if a user attempts to login more than the specified number of times the account
will be locked. Default is 10 days.

PASSWORD_LIFE_TIME
------------------
number of days the same password can be used unless a grace period is specified.
Default is 108 days.

PASSWORD_REUSE_TIME
------------------
number of days that must pass before the same password can be used again.
Default is unlimited.

PASSWORD_REUSE_MAX
-----------------
number of times a password must be changed before a previous password can be used again.
Default is unlimited.

PASSWORD_LOCK_TIME
-----------------
number of days an account will remain locked after failed login attempts has been triggered.
Default is 1 day.

PASSWORD_GRACE_TIME
-----------------
number of grace days for user to change password.
Default is 7 days.

PASSWORD_VERIFY_FUNCTION
-----------------
allows you to define PL/SQL that can be used for password verification.


===========================Example==============================

SQL> SELECT * FROM dba_profiles
WHERE profile = 'DEFAULT'
AND resource_type = 'PASSWORD';


SQL> CREATE USER auser IDENTIFIED BY apassword
DEFAULT TABLESPACE dfts
TEMPORARY TABLESPACE tpts
PROFILE aprofile;


SQL> CREATE PROFILE restrictive
LIMIT FAILED_LOGIN_ATTEMPTS 1
PASSWORD_LIFE_TIME 30
PASSWORD_LOCK_TIME 7
PASSWORD_GRACE_TIME 0;


SQL> ALTER USER scott PROFILE restrictive;

SQL> ALTER USER scott PROFILE default;


SQL> ALTER PROFILE default
LIMIT PASSWORD_REUSE_TIME 3
PASSWORD_REUSE_MAX unlimited;

No comments: