The Ora DBAPractical Oracle Database Administration Knowledge & Solutions

Saturday, August 22, 2026

Oracle Database User Profile Management - Complete DBA Guide

Oracle Database User Management Profiles Password Policy Security Resource Limits DBA

Oracle Database profiles provide a centralized way to define password security policies and resource limits for database users. A profile is not a user itself; instead, it is a collection of limits that can be assigned to one or more users. This makes profiles useful for applying consistent security and resource-management rules across an Oracle environment.

In this guide: Profile definition, requirements, basic syntax, DEFAULT profile, password parameters, resource limits, password policies, profile creation and assignment, password verification functions, account locking, password expiration, profile modification, dropping profiles and common DBA troubleshooting scenarios.

What is an Oracle User Profile?

Every Oracle database user is associated with a profile. When a profile is not explicitly specified during user creation, Oracle assigns the DEFAULT profile. Profiles allow DBAs to separate security requirements for different types of accounts, such as application users, reporting users, service accounts and administrative users.
A profile can control two major areas: Password-related settings and Resource-related settings. Password settings govern account security and password lifecycle, while resource settings can restrict how much database resource a session can consume.

Profile Area Purpose Examples
Password Security Controls password lifecycle and account security. FAILED_LOGIN_ATTEMPTS,
PASSWORD_LIFE_TIME,
PASSWORD_LOCK_TIME
Password Verification Applies password complexity rules. PASSWORD_VERIFY_FUNCTION
Resource Limits Controls session and resource consumption. SESSIONS_PER_USER,
IDLE_TIME,
CONNECT_TIME

Requirements and Privileges

The following privileges may be required when managing Oracle profiles:

  • CREATE PROFILE privilege to create a profile.
  • ALTER PROFILE privilege to modify a profile.
  • DROP PROFILE privilege to remove a profile.
  • ALTER USER privilege to assign a profile to a user.
  • For a custom password verification function, the appropriate privileges are required to create and execute the function.
Note: Profile changes can affect multiple users simultaneously. Always test security-policy changes with a controlled account before applying them broadly in production.

Basic Profile Syntax

Create Profile

CREATE PROFILE profile_name LIMIT
    parameter value
    [parameter value ...];

Alter Profile

ALTER PROFILE profile_name LIMIT
    parameter value
    [parameter value ...];

Drop Profile

DROP PROFILE profile_name;

Rejected by the database if active users still use the profile.
ORA-02382 : profile string has users assigned, cannot drop without CASCADE 

DROP PROFILE profile_name CASCADE;

The CASCADE option removes the profile assignment from users affected by the dropped profile. The users themselves are not dropped.

Profile Parameters

Password Parameters

Parameter Definition
FAILED_LOGIN_ATTEMPTS Maximum consecutive failed login attempts before the account is locked.
PASSWORD_LIFE_TIME Number of days the password remains valid.
PASSWORD_REUSE_TIME Minimum number of days before an old password can be reused.
PASSWORD_REUSE_MAX Number of password changes required before an old password can be reused.
PASSWORD_LOCK_TIME Duration for which an account remains locked after the failed-login threshold is exceeded.
PASSWORD_GRACE_TIME Grace period associated with an expired password.
PASSWORD_VERIFY_FUNCTION Password verification function used to enforce additional password rules.
PASSWORD_ROLLOVER_TIME Password rollover period supported by applicable Oracle releases.

Resource Parameters

Parameter Definition
SESSIONS_PER_USER Maximum number of concurrent sessions for a user.
CPU_PER_SESSION Maximum CPU time allowed for a session, expressed in hundredths of seconds.
CPU_PER_CALL Maximum CPU time allowed for a single call.
CONNECT_TIME Maximum connection duration in minutes.
IDLE_TIME Maximum continuous inactive time for a session in minutes.
LOGICAL_READS_PER_SESSION Maximum logical data block reads for a session.
LOGICAL_READS_PER_CALL Maximum logical data block reads for a call.
PRIVATE_SGA Private SGA limit relevant primarily to shared-server environments.

DEFAULT Profile

Oracle provides a built-in profile named DEFAULT. When a user is created without explicitly specifying a profile, Oracle assigns the DEFAULT profile. 

CREATE USER APPUSER IDENTIFIED BY "Password123";
SELECT USERNAME,
       PROFILE
FROM DBA_USERS
WHERE USERNAME = 'APPUSER';
USERNAME PROFILE --------- ------- APPUSER DEFAULT

The DEFAULT profile already exists and does not need to be created.

Can A Profile Be Renamed?

No. Oracle does not provide a RENAME PROFILE statement, and profile cannot be renamed. 

ALTER PROFILE DEFAULT RENAME TO APP_DEFAULT;
Important: The statement above is not valid Oracle syntax. A Profile can be modified, but it cannot be renamed.

If a custom profile is required, create a separate profile and explicitly assign it to users. 

CREATE PROFILE APP_DEFAULT LIMIT
    FAILED_LOGIN_ATTEMPTS 5
    PASSWORD_LIFE_TIME 90
    PASSWORD_LOCK_TIME 1
    PASSWORD_GRACE_TIME 7;
ALTER USER APPUSER PROFILE APP_DEFAULT;

Check Existing Profiles

SET LINESIZE 200
SET PAGESIZE 1000

COLUMN profile FORMAT A25
COLUMN resource_name FORMAT A30
COLUMN resource_type FORMAT A15
COLUMN limit FORMAT A30

SELECT profile,
       resource_name,
       resource_type,
       limit
FROM dba_profiles
ORDER BY profile,
         resource_name;

Create a User Profile

The following example creates a practical application-user profile with password controls and a concurrent-session limit. 

CREATE PROFILE APP_USER_PROFILE LIMIT
    FAILED_LOGIN_ATTEMPTS 5
    PASSWORD_LIFE_TIME 90
    PASSWORD_REUSE_TIME 365
    PASSWORD_REUSE_MAX 5
    PASSWORD_LOCK_TIME 1
    PASSWORD_GRACE_TIME 7
    SESSIONS_PER_USER 3;
SELECT PROFILE,
       RESOURCE_NAME,
       LIMIT
FROM DBA_PROFILES
WHERE PROFILE = 'APP_USER_PROFILE'
ORDER BY RESOURCE_NAME;
Important: When creating a new profile, any parameter that is not explicitly specified, is initialized using the current value of that parameter in the DEFAULT profile.

Assign a Profile to a User

ALTER USER APPUSER PROFILE APP_USER_PROFILE;
SELECT USERNAME,
       ACCOUNT_STATUS,
       PROFILE
FROM DBA_USERS
WHERE USERNAME = 'APPUSER';

Change a User Back to DEFAULT

ALTER USER APPUSER PROFILE DEFAULT;
SELECT USERNAME,
       PROFILE
FROM DBA_USERS
WHERE USERNAME = 'APPUSER';

Modify an Existing Profile

A profile can be changed without recreating it. The new settings apply to users assigned to that profile. 

ALTER PROFILE APP_USER_PROFILE LIMIT
    FAILED_LOGIN_ATTEMPTS 3
    PASSWORD_LIFE_TIME 60
    PASSWORD_GRACE_TIME 7;
SELECT PROFILE,
       RESOURCE_NAME,
       LIMIT
FROM DBA_PROFILES
WHERE PROFILE = 'APP_USER_PROFILE'
ORDER BY RESOURCE_NAME;

Password Policy Example

A typical password-security profile may combine failed-login protection, password lifetime, password reuse restrictions and a grace period. 

CREATE PROFILE SECURE_USER_PROFILE LIMIT
    FAILED_LOGIN_ATTEMPTS 5
    PASSWORD_LIFE_TIME 90
    PASSWORD_REUSE_TIME 365
    PASSWORD_REUSE_MAX 5
    PASSWORD_LOCK_TIME 1
    PASSWORD_GRACE_TIME 7;
Note: Exact values should be aligned with organizational security requirements and application behavior. Service accounts may require a separately documented policy.

Password Verification Function

PASSWORD_VERIFY_FUNCTION allows Oracle to call a PL/SQL function when a password is created or changed. The function can enforce rules such as minimum length, character requirements and restrictions on passwords matching the username.

The following is a simple example. It requires at least 10 characters, prevents the password from matching the username and requires at least one numeric character. 

CREATE OR REPLACE FUNCTION verify_app_password
(
    username VARCHAR2,
    password VARCHAR2
) RETURN BOOLEAN
IS
    l_digit_count NUMBER;
BEGIN
    IF password IS NULL THEN
        RAISE_APPLICATION_ERROR(-20001,
            'Password cannot be NULL');
    END IF;

    IF LENGTH(password) < 10 THEN
        RAISE_APPLICATION_ERROR(-20002,
            'Password must contain at least 10 characters');
    END IF;

    IF LOWER(password) = LOWER(username) THEN
        RAISE_APPLICATION_ERROR(-20003,
            'Password cannot be the same as the username');
    END IF;

    l_digit_count := REGEXP_COUNT(password, '[0-9]');

    IF l_digit_count = 0 THEN
        RAISE_APPLICATION_ERROR(-20004,
            'Password must contain at least one digit');
    END IF;

    RETURN TRUE;
END;
/
Important: Password verification functions should be tested carefully before production use. Use Oracle-supported password verification functions when they meet the organization's requirements.

Assign Password Verification Function to a Profile

ALTER PROFILE APP_USER_PROFILE LIMIT
    PASSWORD_VERIFY_FUNCTION VERIFY_APP_PASSWORD;
SELECT PROFILE,
       RESOURCE_NAME,
       LIMIT
FROM DBA_PROFILES
WHERE PROFILE = 'APP_USER_PROFILE'
  AND RESOURCE_NAME = 'PASSWORD_VERIFY_FUNCTION';

Test the Password Verification Function

Use a test account to validate both rejected and accepted passwords. 

ALTER USER APPUSER IDENTIFIED BY "Short1";

The password should be rejected because it does not satisfy the minimum length and digit requirements defined by the function. Test additional cases such as a password equal to the username, a password without a digit and a compliant password. 

ALTER USER APPUSER IDENTIFIED BY "SecurePass123";

Account Locking

When failed login attempts reach the profile's FAILED_LOGIN_ATTEMPTS limit, Oracle can lock the account according to PASSWORD_LOCK_TIME. 

SELECT USERNAME,
       ACCOUNT_STATUS,
       LOCK_DATE,
       PROFILE
FROM DBA_USERS
WHERE USERNAME = 'APPUSER';

Unlock an Account

ALTER USER APPUSER ACCOUNT UNLOCK;
Important: Before unlocking an account in production, determine why the account was locked. Application connection pools or stored credentials can repeatedly cause failed logins.

Password Expiration

SELECT USERNAME,
       ACCOUNT_STATUS,
       EXPIRY_DATE,
       PROFILE
FROM DBA_USERS
ORDER BY EXPIRY_DATE;

PASSWORD_LIFE_TIME controls the password lifetime. PASSWORD_GRACE_TIME provides the configured grace period associated with password expiration.

Check User Profile Assignments

SET LINESIZE 200
SET PAGESIZE 1000

COLUMN username FORMAT A30
COLUMN account_status FORMAT A25
COLUMN profile FORMAT A25

SELECT username,
       account_status,
       profile
FROM dba_users
ORDER BY profile,
         username;

Profile Usage by Users

SELECT profile,
       COUNT(*) user_count
FROM dba_users
GROUP BY profile
ORDER BY profile;

Resource Limits for a User

USER_RESOURCE_LIMITS can be used to inspect resource limits associated with the current user. DBAs can also use DBA_PROFILES to inspect the policy definition. 

SELECT RESOURCE_NAME,
       LIMIT,
       INITIAL_ALLOCATION,
       CURRENT_UTILIZATION,
       MAX_UTILIZATION
FROM USER_RESOURCE_LIMITS;

Dropping a Profile

A profile should be dropped only after confirming that it is no longer required and understanding which users are assigned to it.

Check Users Assigned to the Profile

SELECT USERNAME,
       PROFILE
FROM DBA_USERS
WHERE PROFILE = 'APP_USER_PROFILE'
ORDER BY USERNAME;

Drop Profile

DROP PROFILE APP_USER_PROFILE;

If the profile is assigned to users, use CASCADE when appropriate: 

DROP PROFILE APP_USER_PROFILE CASCADE;
Important: Do not drop a profile simply to change its settings. ALTER PROFILE is normally the appropriate operation when the profile is still required.

Profile Security Best Practices

  1. Use separate profiles when different categories of users require different policies.
  2. Avoid unnecessary changes to the DEFAULT profile because many users may depend on it.
  3. Do not use UNLIMITED indiscriminately for password-security parameters.
  4. Use strong password verification rules appropriate for the environment.
  5. Monitor locked and expired accounts.
  6. Test profile changes using non-production accounts before broad deployment.
  7. Review profile assignments periodically.
  8. Audit changes to security-sensitive profiles in production.

Common DBA Scenarios

Scenario Relevant Profile Area Typical Action
Account repeatedly locked FAILED_LOGIN_ATTEMPTS Investigate failed logins and application credentials.
Password expires unexpectedly PASSWORD_LIFE_TIME / PASSWORD_GRACE_TIME Review profile and account expiry information.
Old password can be reused PASSWORD_REUSE_TIME / PASSWORD_REUSE_MAX Review password reuse settings.
Password complexity is insufficient PASSWORD_VERIFY_FUNCTION Configure and test an appropriate verification function.
Too many concurrent sessions SESSIONS_PER_USER Review sessions and profile limit.
Idle sessions remain connected IDLE_TIME Review idle-time policy and application behavior.
Different user groups need different policies Custom profiles Create and assign separate profiles.

Common Profile-Related Errors and Checks

Oracle Error What to Check Typical DBA Response
ORA-28000
The account is locked.		 DBA_USERS.ACCOUNT_STATUS
FAILED_LOGIN_ATTEMPTS 		Determine the cause of the failed login attempts. Typical solution: ALTER USER APPUSER ACCOUNT UNLOCK;
ORA-28001
The password has expired.		 DBA_USERS.EXPIRY_DATE
PASSWORD_LIFE_TIME 		Review the account expiration information and password policy. Typical approach: Reset the password or review the configured password lifetime.
Password rejected during password change PASSWORD_VERIFY_FUNCTION
Password policy 		Review the verification function requirements and test the password against the configured policy.
ORA-02391
Exceeded simultaneous SESSIONS_PER_USER limit.		 SESSIONS_PER_USER
Current sessions 		Review current sessions for the user and determine whether the configured session limit is appropriate. Typical approach: Terminate unnecessary sessions or adjust the profile when justified.

Conclusion

Oracle User Profile management is an important DBA responsibility because profiles provide a centralized mechanism for password security and resource controls. A well-designed profile strategy makes account management consistent while reducing the need for individual configuration changes.

The most important areas are the DEFAULT profile, password parameters, resource limits, password verification functions, account locking, password expiration, profile assignment and regular review of profile usage.

Profile changes should always be tested and documented before being applied to production.

at
Labels: , ,