DBA_PROFILES - Manage Oracle Password & Resource Limits

DBA_PROFILES

Overview

DBA_PROFILES lists every resource and password parameter defined across all profiles in the database. Profiles are named policy containers assigned to user accounts; they enforce password complexity, password lifetime and reuse rules, failed login lockout thresholds, and — when the Resource Limit parameter is enabled — CPU and session resource consumption limits. Oracle ships a single DEFAULT profile, but production databases should define custom profiles with stricter password policies for application and privileged accounts. Each parameter in a profile appears as a separate row in DBA_PROFILES.

View Type: Data Dictionary View Available Since: Oracle 7 Required Privileges: SELECT on DBA_PROFILES or SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY

Key Columns

Column	Datatype	Description
PROFILE	VARCHAR2(128)	Name of the profile
RESOURCE_NAME	VARCHAR2(32)	Name of the resource or password parameter (e.g., PASSWORD_LIFE_TIME, FAILED_LOGIN_ATTEMPTS)
RESOURCE_TYPE	VARCHAR2(8)	PASSWORD or KERNEL (resource limit)
LIMIT	VARCHAR2(40)	Effective limit value; DEFAULT means the value is inherited from the DEFAULT profile; UNLIMITED means no limit is enforced
COMMON	VARCHAR2(3)	YES if the profile was created with CONTAINER=ALL in a multitenant environment
INHERITED	VARCHAR2(3)	YES if the profile definition is inherited from the root CDB container
IMPLICIT	VARCHAR2(3)	YES if the profile was implicitly created by Oracle

Essential Queries

Basic Usage

Show every password-related setting for all non-DEFAULT profiles, pivoted for readability:

SELECT
    profile,
    resource_name,
    resource_type,
    limit
FROM
    dba_profiles
WHERE
    resource_type = 'PASSWORD'
ORDER BY
    profile,
    resource_name;

Monitoring Query

Find profiles where password controls are weaker than recommended minimums — a standard security audit check:

SELECT
    p.profile,
    MAX(CASE WHEN p.resource_name = 'FAILED_LOGIN_ATTEMPTS'
             THEN p.limit END)                  AS failed_login_attempts,
    MAX(CASE WHEN p.resource_name = 'PASSWORD_LIFE_TIME'
             THEN p.limit END)                  AS password_life_time,
    MAX(CASE WHEN p.resource_name = 'PASSWORD_REUSE_TIME'
             THEN p.limit END)                  AS password_reuse_time,
    MAX(CASE WHEN p.resource_name = 'PASSWORD_REUSE_MAX'
             THEN p.limit END)                  AS password_reuse_max,
    MAX(CASE WHEN p.resource_name = 'PASSWORD_LOCK_TIME'
             THEN p.limit END)                  AS password_lock_time,
    MAX(CASE WHEN p.resource_name = 'PASSWORD_VERIFY_FUNCTION'
             THEN p.limit END)                  AS verify_function
FROM
    dba_profiles p
GROUP BY
    p.profile
ORDER BY
    p.profile;

Combined with Other Views

Show which users are assigned each profile along with the profile’s password lifetime setting:

SELECT
    u.username,
    u.account_status,
    u.profile,
    p.limit                                     AS password_life_days,
    u.expiry_date,
    ROUND(u.expiry_date - SYSDATE, 0)           AS days_until_expiry
FROM
    dba_users   u
    JOIN dba_profiles p
        ON  p.profile        = u.profile
        AND p.resource_name  = 'PASSWORD_LIFE_TIME'
        AND p.resource_type  = 'PASSWORD'
WHERE
    u.oracle_maintained = 'N'
    AND u.account_status = 'OPEN'
ORDER BY
    days_until_expiry NULLS FIRST,
    u.username;

Resource Limit Settings

Show kernel (session resource) limits across all profiles to identify overly permissive settings:

SELECT
    profile,
    resource_name,
    limit
FROM
    dba_profiles
WHERE
    resource_type = 'KERNEL'
    AND limit NOT IN ('DEFAULT', 'UNLIMITED')
ORDER BY
    profile,
    resource_name;

Profile Assignment Summary

Count how many users are assigned each profile to identify orphaned profiles (zero users) or over-used DEFAULT profiles:

SELECT
    p.profile,
    COUNT(DISTINCT u.username)                  AS user_count,
    SUM(CASE WHEN u.account_status = 'OPEN'
             THEN 1 ELSE 0 END)                 AS open_accounts,
    SUM(CASE WHEN u.account_status LIKE '%LOCKED%'
             THEN 1 ELSE 0 END)                 AS locked_accounts
FROM
    (SELECT DISTINCT profile FROM dba_profiles) p
    LEFT JOIN dba_users u
        ON  u.profile        = p.profile
        AND u.oracle_maintained = 'N'
GROUP BY
    p.profile
ORDER BY
    user_count DESC,
    p.profile;

Common Use Cases

• Security baseline verification — Confirm that no production application profile uses FAILED_LOGIN_ATTEMPTS = UNLIMITED, PASSWORD_LIFE_TIME = UNLIMITED, or PASSWORD_VERIFY_FUNCTION = NULL, all of which are common security findings
• CIS Benchmark compliance — The CIS Oracle Database Benchmark mandates specific minimum values for password parameters; DBA_PROFILES provides the evidence needed to confirm or remediate compliance
• Profile rationalisation — Organisations often accumulate dozens of profiles over years of DBA turnover; pivot the view to compare profiles and consolidate redundant ones
• Password expiry co-ordination — Combine DBA_PROFILES with DBA_USERS.EXPIRY_DATE to forecast which accounts will lock in the next 30 days so application teams can plan maintenance windows
• Resource limit investigation — When a batch job is being killed with ORA-02391 (exceeded resource limit), check DBA_PROFILES for the SESSIONS_PER_USER or CPU_PER_SESSION limit on the batch account’s profile
• DEFAULT profile risk — In production, every non-Oracle user account should be on a custom profile; filter DBA_USERS WHERE PROFILE = ‘DEFAULT’ to find accounts that need remediation

Related Views

• DBA_USERS — Joins to DBA_PROFILES via PROFILE column; shows which accounts are assigned each profile
• DBA_SYS_PRIVS — System privileges that complement profile-based access controls
• V$PARAMETER — Shows whether the RESOURCE_LIMIT parameter is set to TRUE; resource (KERNEL) limits in DBA_PROFILES are only enforced when this is TRUE
• DBA_AUDIT_TRAIL — When standard auditing is enabled, failed logins subject to FAILED_LOGIN_ATTEMPTS are recorded here
• V$SESSION — Active sessions; join with profile information to correlate resource consumption against profile limits

Version Notes

• Oracle 7: Core profile functionality including both resource (KERNEL) and password parameters
• Oracle 10g: PASSWORD_VERIFY_FUNCTION default changed; Oracle supplied ora10g_strong_pwd_verify as a sample complexity function
• Oracle 11g: Stronger default DEFAULT profile introduced with FAILED_LOGIN_ATTEMPTS = 10, PASSWORD_LIFE_TIME = 180 — older installations may have weaker settings
• Oracle 12c: COMMON and INHERITED columns added; PASSWORD_VERIFY_FUNCTION updated to ora12c_strong_verify_function; CONTAINER=ALL profiles introduced
• Oracle 12.2: ORA_STIG_PROFILE introduced as a preconfigured profile meeting DISA STIG requirements; available as a hardening starting point
• Oracle 19c: No structural changes; Oracle recommends using ORA_STIG_PROFILE or a derivative for privileged accounts
• Oracle 23ai: New parameters related to multi-factor authentication added in some editions