• Arun Kumar

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


322 views

Recent Posts

See All

Oracle 12c to 19c Database Upgrade

In this article we will be looking at two most commonly used methods of upgrading Oracle database from 12c to 19c Pre-upgrade tasks Upgrade using DBUA Upgrade using Manual Method You can perform a dir