Profile Management in Oracle
A profile is a way to control system resource that can be used by a database user. Profile management is of two types:
The password management allows a DBA to have more control over user passwords. Some of the parameters you might be familiar in general like failed login attempts, password lock time etc.
FAILED_LOGIN_ATTEMPTS: How many times a user can fail to login
PASSWORD_LOCK_TIME: Users who exceed failed login attempts, their password will be locked for specific time
PASSWORD_LIFE_TIME: Till when password is valid in days
PASSWORD_GRACE_TIME: Grace period for user to change password, else account will be locked
PASSWORD_REUSE_TIME: After how many days user can re-use same password
PASSWORD_REUSE_MAX: Specify how many times old password can be used
PASSWORD_VERIFY_FUNCTION: Defines rules for setting a new password
Resource management helps in limiting the database abuse a user can cause. For example, if a user connects to database and never runs a query then this ideal connection will take system resources like CPU.
To restrict such kind of issues, we have resource management parameters:
SESSIONS_PER_USER: How many concurrent sessions user can open
IDLE_TIME: Specifies how much time user can stay inside database without doing any activity
CONNECT_TIME: Specifies total time user can stay inside database whether idle of active.
Note: resource management parameters will take in effect only if RESOURCE_LIMIT parameter is set to TRUE.
Check RESOURCE_LIMIT parameter
Use below command to check the RESOURCE_LIMIT parameter:
SQL> show parameter resource_limit;
By default the parameter is set to FALSE. You can change it to true using below command:
SQL> alter system set resource_limit=TRUE scope=both;
Create new profile
Use below command to create new profile inside Oracle database:
SQL> create profile my_profile limit failed_login_attempts 3 password_lock_time 1 sessions_per_user 1 idle_time 5;
Note: password lock time by default is for 1 day. You can specify it in minutes (n/1440) or even in seconds (n/86400).
Assign profile to user
Once you create a profile inside the database, use below command to assign the profile to a user:
SQL> alter user scott profile my_profile;
Check user profiles
Sometimes you need to check profiles that are assigned to a user. Use below command to check user profile:
SQL> SELECT USERNAME, PROFILE FROM DBA_USERS WHERE USERNAME='SCOTT';
Check profile values
You must be able to check all the values inside a profile by querying DBA_PROFILES:
SQL> SELECT * FROM DBA_PROFILES WHERE PROFILE='&PROFILE_NAME';