Home / Oracle DBA / PROFILE MANAGEMENT

PROFILE MANAGEMENT

PROFILE MANAGEMENT

  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.

COMMANDS

# 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:
TABLESPACE ENCRYPTION
UNDO MANAGEMENT
BIGFILE TABLESPACE
DON’T MISS OUT!
Subscribe To Newsletter
Be the first to get latest updates and exclusive content straight to your email inbox.
Stay Updated
Give it a try, you can unsubscribe anytime.

Check Also

Oracle 10g to 11g Enhancements (Difference between Oracle 10g and 11g)

Difference between Oracle 10g and 11g 1- Enhanced Automatic Memory Management System Oracle 9i automated …

Leave a Reply

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

DON’T MISS OUT!
Subscribe To Newsletter
Be the first to get latest updates and exclusive content straight to your email inbox.
Stay Updated
Give it a try, you can unsubscribe anytime.
close-link
GET OUR LATEST CONTENT IN YOUR INBOX

SUBSCRIBE 
Your information will never be shared
close-link
Enquiry
Submit
close-link
Sign-up for exclusive content. Be the first to hear about DBA Centre Blogs.
Subscribe