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


Recent Posts

See All

How to plan database creation

Database creation process involves several operating system files to work together. You create database only once and later on keep on adding more data files. Before you can simply go ahead and start

Oracle Transportable Tablespace

Assume that you have a schema (IQS) on production server and you want to import the schema into test server. The ideal approach would be to perform entire schema export and import using data pump. Rig

Grant Select on all tables in a schema

I encountered this situation where I wanted to grant SELECT on all the tables owned by one user to another user. There are two simple ways to achieve this: Generate SQL for each table In this method,

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis

Feedback