Profile Definitions (dprofile.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script displays all database profiles and their associated resource limits and password policies. Profiles are used to manage database security and resource consumption by defining limits on CPU time, memory usage, password policies, and other database resources.

Script

rem  dprofile.sql
rem
ttitle 'Profile Definitions'
rem
col profile format a15 heading 'PROFILE'
col resource_name format a32 heading 'RESOURCE NAME'
col resource_type format a8 heading 'TYPE'
col limit format a22 heading 'LIMIT'
rem
break on profile
rem
select profile,
       resource_name,
       resource_type,
       limit
  from sys.dba_profiles
 order by profile, resource_name;

Usage

-- Run the script in SQL*Plus or SQLcl
@dprofile.sql

-- No parameters required
-- Shows all profiles and their resource limits

Required Privileges

• SELECT on SYS.DBA_PROFILES
• Typically requires DBA role or SELECT_CATALOG_ROLE

Sample Output

Profile Definitions

PROFILE         RESOURCE NAME                    TYPE     LIMIT
--------------- -------------------------------- -------- ----------------------
DEFAULT         COMPOSITE_LIMIT                  KERNEL   UNLIMITED
                CONNECT_TIME                     KERNEL   UNLIMITED
                CPU_PER_CALL                     KERNEL   UNLIMITED
                CPU_PER_SESSION                  KERNEL   UNLIMITED
                FAILED_LOGIN_ATTEMPTS            PASSWORD 10
                IDLE_TIME                        KERNEL   UNLIMITED
                LOGICAL_READS_PER_CALL           KERNEL   UNLIMITED
                LOGICAL_READS_PER_SESSION        KERNEL   UNLIMITED
                PASSWORD_GRACE_TIME              PASSWORD 7
                PASSWORD_LIFE_TIME               PASSWORD 180
                PASSWORD_LOCK_TIME               PASSWORD 1
                PASSWORD_REUSE_MAX               PASSWORD UNLIMITED
                PASSWORD_REUSE_TIME              PASSWORD UNLIMITED
                PASSWORD_VERIFY_FUNCTION         PASSWORD NULL
                PRIVATE_SGA                      KERNEL   UNLIMITED
                SESSIONS_PER_USER                KERNEL   UNLIMITED

APP_USER        COMPOSITE_LIMIT                  KERNEL   UNLIMITED
                CONNECT_TIME                     KERNEL   480
                CPU_PER_CALL                     KERNEL   60000
                CPU_PER_SESSION                  KERNEL   300000
                FAILED_LOGIN_ATTEMPTS            PASSWORD 3
                IDLE_TIME                        KERNEL   60
                LOGICAL_READS_PER_CALL           KERNEL   10000
                LOGICAL_READS_PER_SESSION        KERNEL   UNLIMITED
                PASSWORD_GRACE_TIME              PASSWORD 3
                PASSWORD_LIFE_TIME               PASSWORD 90
                PASSWORD_LOCK_TIME               PASSWORD 1
                PASSWORD_REUSE_MAX               PASSWORD 5
                PASSWORD_REUSE_TIME              PASSWORD 30
                PASSWORD_VERIFY_FUNCTION         PASSWORD VERIFY_FUNCTION
                PRIVATE_SGA                      KERNEL   UNLIMITED
                SESSIONS_PER_USER                KERNEL   5

Key Output Columns

• PROFILE: Profile name
• RESOURCE NAME: Specific resource or password policy setting
• TYPE: Resource type (KERNEL for system resources, PASSWORD for password policies)
• LIMIT: Configured limit value

Understanding Resource Types

Kernel Resources (System Resources)

• CPU_PER_SESSION: Total CPU time per session (centiseconds)
• CPU_PER_CALL: CPU time per SQL call (centiseconds)
• CONNECT_TIME: Maximum session duration (minutes)
• IDLE_TIME: Maximum idle time before disconnect (minutes)
• LOGICAL_READS_PER_SESSION: Total logical reads per session
• LOGICAL_READS_PER_CALL: Logical reads per SQL call
• SESSIONS_PER_USER: Maximum concurrent sessions per user
• PRIVATE_SGA: Maximum private SGA memory (bytes)
• COMPOSITE_LIMIT: Weighted resource cost limit

Password Resources (Security Policies)

• PASSWORD_LIFE_TIME: Password expiration period (days)
• PASSWORD_GRACE_TIME: Grace period after expiration (days)
• PASSWORD_REUSE_TIME: Days before password can be reused
• PASSWORD_REUSE_MAX: Number of password changes before reuse
• FAILED_LOGIN_ATTEMPTS: Login attempts before account lock
• PASSWORD_LOCK_TIME: Account lock duration (days)
• PASSWORD_VERIFY_FUNCTION: Custom password verification function

Security Analysis

Password Policy Assessment

• Strong policies: Short life time, limited reuse, complexity verification
• Weak policies: UNLIMITED values, no verification function
• Account lockout: Appropriate failed attempt limits with reasonable lock time

Resource Control Review

• Resource limits: Prevent runaway processes and resource abuse
• Session management: Control concurrent access and connection time
• Memory limits: Prevent excessive memory consumption

Common Use Cases

Security Audit

-- Review all password policies
@dprofile.sql
-- Focus on PASSWORD type resources
-- Verify compliance with security standards

Resource Management

-- Analyze resource limits
@dprofile.sql
-- Review KERNEL type resources
-- Ensure appropriate controls for different user types

Capacity Planning

-- Understand resource allocation
@dprofile.sql
-- Plan for user growth and resource requirements

Profile Management

Creating Custom Profiles

-- Create profile for application users
CREATE PROFILE app_users LIMIT
  SESSIONS_PER_USER 5
  CPU_PER_SESSION 300000
  CONNECT_TIME 480
  IDLE_TIME 60
  FAILED_LOGIN_ATTEMPTS 3
  PASSWORD_LIFE_TIME 90
  PASSWORD_LOCK_TIME 1;

Assigning Profiles to Users

-- Assign profile to user
ALTER USER username PROFILE app_users;

-- Check user profile assignments
SELECT username, profile, account_status
FROM dba_users
WHERE profile != 'DEFAULT'
ORDER BY profile, username;

Modifying Existing Profiles

-- Modify profile limits
ALTER PROFILE app_users LIMIT
  FAILED_LOGIN_ATTEMPTS 5
  PASSWORD_LIFE_TIME 120;

Troubleshooting Profile Issues

Resource Limit Violations

1. Identify violations:

   -- Check for resource limit hits
   SELECT username, resource_name, limit_value, current_utilization
   FROM v$resource_limit
   WHERE current_utilization > 0;

2. Session resource usage:

   -- Monitor session resource consumption
   SELECT s.username, s.sid, r.resource_name, r.current_utilization
   FROM v$session s, v$session_resource r
   WHERE s.sid = r.sid
   AND s.username IS NOT NULL;

Password Policy Issues

1. Account lockouts:

   -- Find locked accounts
   SELECT username, account_status, lock_date, expiry_date
   FROM dba_users
   WHERE account_status LIKE '%LOCKED%';

2. Password expiration:

   -- Check upcoming password expirations
   SELECT username, expiry_date, profile
   FROM dba_users
   WHERE expiry_date BETWEEN SYSDATE AND SYSDATE + 30
   ORDER BY expiry_date;

Best Practices

Password Security

1. Implement strong policies:

   • PASSWORD_LIFE_TIME: 60-90 days
   • FAILED_LOGIN_ATTEMPTS: 3-5 attempts
   • PASSWORD_VERIFY_FUNCTION: Use complexity verification
   • PASSWORD_REUSE_MAX: Minimum 5 passwords

2. Example secure profile:

   CREATE PROFILE secure_users LIMIT
     PASSWORD_LIFE_TIME 60
     PASSWORD_GRACE_TIME 3
     FAILED_LOGIN_ATTEMPTS 3
     PASSWORD_LOCK_TIME 1
     PASSWORD_REUSE_MAX 10
     PASSWORD_REUSE_TIME 365
     PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION_11G;

Resource Management

1. Application users: Limit sessions, CPU, and connect time
2. Batch users: Higher CPU limits, longer connect time
3. Administrative users: More liberal limits with strong password policies
4. Default profile: Should have reasonable security settings

Advanced Analysis

Profile Usage Analysis

-- Count users by profile
SELECT p.profile, COUNT(u.username) user_count,
       LISTAGG(u.username, ', ') WITHIN GROUP (ORDER BY u.username) users
FROM dba_profiles p, dba_users u
WHERE p.profile = u.profile(+)
GROUP BY p.profile
ORDER BY user_count DESC;

Resource Limit Effectiveness

-- Check if limits are being hit
SELECT resource_name,
       COUNT(*) profiles_using,
       MIN(limit) min_limit,
       MAX(limit) max_limit
FROM dba_profiles
WHERE limit != 'UNLIMITED'
AND resource_type = 'KERNEL'
GROUP BY resource_name
ORDER BY profiles_using DESC;

Password Policy Comparison

-- Compare password policies across profiles
SELECT profile,
       MAX(CASE WHEN resource_name = 'PASSWORD_LIFE_TIME' THEN limit END) life_time,
       MAX(CASE WHEN resource_name = 'FAILED_LOGIN_ATTEMPTS' THEN limit END) failed_attempts,
       MAX(CASE WHEN resource_name = 'PASSWORD_LOCK_TIME' THEN limit END) lock_time
FROM dba_profiles
WHERE resource_type = 'PASSWORD'
GROUP BY profile
ORDER BY profile;

Related Scripts

• User Account Analysis (duser.sql) - User account details with profiles
• User Privileges (dprivall.sql) - User privilege analysis
• Session Analysis (vuser.sql) - Active session monitoring
• Role Analysis (drole.sql) - Database role assignments