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:

  1. Password management
  2. Resource management

Password management

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

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';

Was this article helpful?

Related Articles

Leave a Comment