Creating and Assigning User Profile in Oracle

Profiles in Oracle


Profiles are used to limit resources a user can use. Then, they can be assigned to users with alter user ... profile command.

Limitable resources

The following limits can be specified:

Kernel limits:


Maximum concurrent sessions for a user: (sessions_per_user)
CPU time limit per session: (cpu_per_session)
CPU time limit per call: (cpu_per_call)
Call being parse, execute and fetch


Maximum connect time: (connect_time)
The session will be dropped by oracle after specified time.
Maximum idle time (idle_time)


The session will be dropped by oracle after specified time of doing nothing. Long running processes are not idle!
Maximum blocks read per session: (logical_reads_per_session)
Maximum blocks read per call: (logical_reads_per_call)
Maximum amount of SGA: (private_sga)


In order to enforce kernel limits, resource_limit must be set to true.


Password limits:




Maximum failed login attempts: (failed_login_attempts)
Maximum time a password is valid: (password_life_time)
Minimum of different passwords before password can be reused: (password_reuse_max)
Minimum of days before a password can be reused: (password_reuse_time)
Number of days an account is locked after failing to login: (password_lock_time)
Verify function for passwords: (password_verify_function)

Resource Parameters

• SESSIONS_PER_USER

Specify the number of concurrent sessions to which you want to limit the user.

• CPU_PER_SESSION

Specify the CPU time limit for a session, expressed in hundredth of seconds.

• CPU_PER_CALL

Specify the CPU time limit for a call (a parse, execute, or fetch), expressed in hundredths of seconds.

• CONNECT_TIME

Specify the total elapsed time limit for a session, expressed in minutes.

• IDLE_TIME

Specify the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit.

• LOGICAL_READS_PER_SESSION

Specify the permitted number of data blocks read in a session, including blocks read from memory and disk.

• LOGICAL_READS_PER_CALL

Specify the permitted the number of data blocks read for a call to process a SQL statement (a parse, execute, or fetch).

• PRIVATE_SGA

Specify the amount of private space a session can allocate in the shared pool of the system global area (SGA), expressed in bytes.

• COMPOSITE_LIMIT

Specify the total resource cost for a session, expressed in service units. Oracle Database calculates the total service units as a weighted sum of CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION, and PRIVATE_SGA.

If a session exceeds one of these limits, Oracle will terminate the session. If there is a logoff trigger, it won't be executed.


Creating and assigning a user profile to ORACLE DB users:

• Check the resource limits of DEFAULT profile.

SQL> SELECT resource_name,resource_type,limit FROM dba_profiles WHERE profile='DEFAULT';

RESOURCE_NAME                    RESOURCE_TYPE LIMIT

-------------------------------- ------------- ---------

COMPOSITE_LIMIT                  KERNEL        UNLIMITED

SESSIONS_PER_USER                KERNEL        UNLIMITED

CPU_PER_SESSION                  KERNEL        UNLIMITED

CPU_PER_CALL                     KERNEL        UNLIMITED

LOGICAL_READS_PER_SESSION        KERNEL        UNLIMITED

LOGICAL_READS_PER_CALL           KERNEL        UNLIMITED

IDLE_TIME                        KERNEL        UNLIMITED

CONNECT_TIME                     KERNEL        UNLIMITED

PRIVATE_SGA                      KERNEL        UNLIMITED

FAILED_LOGIN_ATTEMPTS            PASSWORD      10

PASSWORD_LIFE_TIME               PASSWORD      UNLIMITED

PASSWORD_REUSE_TIME              PASSWORD      UNLIMITED

PASSWORD_REUSE_MAX               PASSWORD      UNLIMITED

PASSWORD_VERIFY_FUNCTION         PASSWORD      NULL

PASSWORD_LOCK_TIME               PASSWORD      UNLIMITED

PASSWORD_GRACE_TIME              PASSWORD      UNLIMITED

All resource limits for DEFAULT profile is set to UNLIMITED, but only for FAILED_LOGIN_ATTEPTS attribute, it’s set to some value (10). Due to this the user account keeps getting locked(timed).When we check in the Oracle Documentations, it’s stated that FAILED_LOGIN_ATTEPTS attribute for DEFAULT profile has been changed from 10.2.0.1 from UNLIMITED to 10.

What we can do is, either we may need to change the resource limit for FAILED_LOGIN_ATTEPTS attribute in DEFAULT profile, or create a new profile for that user with FAILED_LOGIN_ATTEPTS attribute value set to UNLIMITED. But for security reasons, we will not tamper the DEFAULT profile, which is not recommended too. Then let’s go for creating a new profile and assign that profile to the user.

Create a profile.

SQL> CREATE PROFILE TESTUSER_DEFAULT LIMIT
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED;

Profile created.

Assign the newly created profile to the user as default profile.

SQL> ALTER USER TESTUSER PROFILE TESTUSER_default;
User altered.