Monday , September 25 2017



  1. Profile management is divided into
    1. Password management
    2. Resource management
  2. Profiles are assigned to users to control access to resources and also to provide enhanced security while logging into the database.
  3. The following are parameters for password policy management.
    1. FAILED_LOGIN_ATTEMPTS – it specifies how many times a user can fail to login to the database.
    2. PASSWORD_LOCK_TIME – user who exceeds failed_login_attempts will be locked. This parameter specifies till how much time it will be locked and account will get unlocked after that time automatically. DBA can also unlock manually.
    3. PASSWORD_LIFE_TIME – it specifies after how many days a user need to change the password.
    4. PASSWORD_GRACE_TIME – it specified grace period for the user to change the password. If user still fails to change .password even after grace time, account will be locked and only DBA need to manually unlock it.
    5. PASSWORD_REUSE_TIME – this will specify after how many days user can reuse the same password.
    6. PASSWORD_REUSE_MAX – it specifies how many max times previous passwords can be used again.
    7. PASSWORD_VERIFY_FUNCTION – it defines rules for setting a new password like password should be 8 char long, password should contains alphanumeric values etc.
  4. The following parameters used for resource management
    1. SESSIONS_PER_USER – it specifies how many concurrent sessions can be opened.
    2. IDLE_TIME – it specifies how much time a user can reside on the database idle (without doing any work). The session will be killed if it crosses idle_time value, but status in v$SESSION will be marked as SNIPPED. Snipped sessions will still hold resources which is burden to OS.
    3. CONNECT_TIME – it specifies how much time user can stay in the database.


# To create a profile
SQL> create profile my_profile limit
failed_login_attempts 3
password_lock_time 1/24/60
sessions_per_user   1
idle_time 5;
# To assign a profile to user
SQL> alter user scott profile my_profile;
# To alter a profile value
SQL> alter profile my_profile limit sessions_per_user 3;
# To create default password verify function
SQL> @$ORACLE_HOME/rdbms/admin/utlpwdmg.sql
Note: sessions terminated because of idle time are marked as SNIPPED in v$session and DBA need to manually kill the related OS process to clear the session.
# To kill a session
SQL> select sid,serial# from v$session where username=’SCOTT’;
SQL> alter system kill session ‘sid,serial#’ immediate;
Note: Resource management parameters are affective only if RESOURCE_LIMIT is set to TRUE.
# To check and change resource_limit value
SQL> show parameter resource_limit
SQL> alter system set resource_limit=TRUE scope=both;
Note: from 11g onwards passwords for all users are case-sensitive.
Read More:



Check Also

How to switch on primary database to physical standby database

After configuration data guard then data is switching  into primary database  to standby database : …

Leave a Reply

Your email address will not be published. Required fields are marked *