Manage User Profiles in Oracle Database

This content outlines the process of managing user profiles in an Oracle Database. It includes steps for creating a profile with specified parameters, assigning it to a user, and verifying the user’s profile. Additionally, it explains the meanings of various profile parameters related to resource limits and password management.

1. Create a new profile
Parameter set according to your need.

CREATE PROFILE app_user LIMIT
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL 3000
CONNECT_TIME 45
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL 1000
PRIVATE_SGA 15K
COMPOSITE_LIMIT 5000000
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LIFE_TIME 60
PASSWORD_REUSE_TIME 60
PASSWORD_REUSE_MAX 5
PASSWORD_LOCK_TIME 1/24
PASSWORD_GRACE_TIME 10;

2. Assigned the profile to user.

SQL> ALTER USER TEST profile app_user;
User altered.

3. Verify the profile of user.

select profile from dba_users where username = 'TEST'

PROFILE
------------------------------
APP_USER

Profile parameter meanings:
COMPOSITE_LIMIT: Specify the total resource cost for a session, expressed in service units. The total service units as a weighted sum of CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION, and PRIVATE_SGA.
SESSIONS_PER_USER: Number of concurrent sessions for the user.
CPU_PER_SESSION: CPU time limit for a session(hundredth of seconds)
CPU_PER_CALL: CPU time limit for a call (a parse, execute, or fetch)(hundredths of seconds)
CONNECT_TIME: Total elapsed time limit for a session(minutes)
IDLE_TIME: Permitted periods of continuous inactive time during a session(minutes).
LOGICAL_READS_PER_SESSION: Permitted number of data blocks read in a session, including blocks read from memory and disk.
LOGICAL_READS_PER_CALL: Permitted number of data blocks read for a call to process a SQL statement (a parse, execute, or fetch).
PRIVATE_SGA: Amount of private space a session can allocate in the shared pool of the system global area (SGA).
FAILED_LOGIN_ATTEMPTS : Attempts made for login exceed this limit will lock the account.
PASSWORD_LIFE_TIME : Number of days password can be used for authentication. It also depend upon PASSWORD_GRACE_TIME is set then password will work until grace period defined after it will not allow to connect. It will give warning only if PASSWORD_GRACE_TIME is unlimited value.
PASSWORD_GRACE_TIME : Warning is issue after number of days value of PASSWORD_LIFE_TIME exceed upto value of PASSWORD_GRACE_TIME defined after that password is expired.
PASSWORD_REUSE_TIME defined in days
PASSWORD_REUSE_MAX defined in days
Both use PASSWORD_REUSE_TIME to 30 and PASSWORD_REUSE_MAX to 15, then the user can reuse the password after 30 days if the password has already been changed 15 times.
PASSWORD_LOCK_TIME : Number of days an account will be locked after the specified number of consecutive failed login attempts.
PASSWORD_VERIFY_FUNCTION : PL/SQL password complexity verification script be passed as an argument to the CREATE PROFILE statement.