Oracle Database Profile Configuration Guide

Introduction
Goals and Assumptions
Profile Basics
1. Check the Current State (Existing Users and Profiles)
2. Whether Resource Limits Apply (resource_limit)
3. Create a New Profile (Example: PF_APP)
4. Create a User and Assign the Profile (Minimum Setup)
5. Assign the Profile to Existing Users and Verify
Commonly Used Profile Items (Excerpt)
Typical Issues and Quick Hints
Operational Approach (Start Small and Scale Safely)

Introduction

The basics of account administration are to assign an appropriate profile to each oracle user and standardize password policies and resource limits. This article keeps your existing setup intact and organizes the minimum steps and verification SQL.

Goals and Assumptions

Enable you to perform profile creation → assignment → verification as quickly as possible according to your existing database standards
Limit actions to read/verify and minimal DDL, avoiding unnecessary settings
Environment: Oracle Linux / Oracle Database 19c (non-CDB)

【Elements】
  ┌───────────┐    ┌────────────┐
  │  PROFILE   │←───│  DEFAULT   │  … default profile
  └─────┬──────┘    └────────────┘
        │           (create a new one per corporate standard)
        ▼
  ┌───────────┐
  │  PF_APP    │  … new (example)
  └─────┬──────┘
        │ ALTER USER PROFILE PF_APP
        ▼
  ┌───────────┐
  │  USERS     │  … application user
  └───────────┘

Profile Basics

A profile is a bundle of policies for a user. It broadly consists of two categories:

Password-related: FAILED_LOGIN_ATTEMPTS / PASSWORD_LOCK_TIME / PASSWORD_LIFE_TIME / PASSWORD_GRACE_TIME / PASSWORD_REUSE_TIME / PASSWORD_REUSE_MAX, etc.
Resource-related: SESSIONS_PER_USER / CONNECT_TIME / IDLE_TIME, etc.
- Note: To enable resource-related limits, the initialization parameter resource_limit must be TRUE (see later).

【Conceptual flow】
 LOGIN
  ├─ Password checks (always active)
  └─ Resource checks (apply when resource_limit=TRUE)

1. Check the Current State (Existing Users and Profiles)

First, see what is currently assigned.

-- Connect as SYS (example: SQL*Plus)
-- $ sqlplus / as sysdba

-- Check the profile assigned to the target users
SELECT username, profile
FROM   dba_users
WHERE  username IN ('APP_USER', 'SCOTT');  -- adjust as needed

Notes (how to read the SQL)

FROM dba_users reads from the list of users in the database.
WHERE username IN (...) narrows down to the target users.

2. Whether Resource Limits Apply (resource_limit)

Only if you use resource limits, check the current value and enable it if necessary.

-- Current value
SHOW PARAMETER resource_limit;

-- Enable only if necessary (no restart required)
ALTER SYSTEM SET resource_limit = TRUE SCOPE=BOTH;

Notes

SHOW PARAMETER checks initialization parameters.
ALTER SYSTEM changes a database-wide setting. Review your standard operating procedures before changing it.

3. Create a New Profile (Example: PF_APP)

Create one application standard and put in only the minimal required limits. Values are examples—adjust to your corporate standards.

-- Create a new profile (if it does not already exist)
CREATE PROFILE PF_APP LIMIT
  -- Password-related (example)
  FAILED_LOGIN_ATTEMPTS  10
  PASSWORD_LOCK_TIME     1       -- 1 day (specify N days)
  PASSWORD_LIFE_TIME     90      -- expires in 90 days
  PASSWORD_GRACE_TIME    7       -- 7-day grace period
  PASSWORD_REUSE_TIME    365     -- forbid reuse within 365 days (use this OR REUSE_MAX)
  -- Resource-related (example: set only if needed)
  SESSIONS_PER_USER      3
  CONNECT_TIME           480     -- minutes (8 hours)
  IDLE_TIME              30;     -- minutes (disconnect when idle)

Notes

Use CREATE PROFILE ... LIMIT to list items and their limits. For items you don’t need, omit them or use UNLIMITED.
Typically, use only one of PASSWORD_REUSE_TIME or PASSWORD_REUSE_MAX.

4. Create a User and Assign the Profile (Minimum Setup)

Minimum example when you create a new application schema.
Per request, the password does not use @.

-- Assume USERS/TEMP tablespaces exist (typical in 19c default setups)
CREATE USER app_user IDENTIFIED BY StrongP4ssw0rd1
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  PROFILE PF_APP
  QUOTA UNLIMITED ON users;

-- Minimal privileges (tighten or expand per business requirements)
GRANT CREATE SESSION, CREATE TABLE TO app_user;

-- Test table for a simple sanity check (example included for this article)
CONN app_user/StrongP4ssw0rd1
CREATE TABLE t_sample (
  id   NUMBER PRIMARY KEY,
  note VARCHAR2(100)
);

INSERT INTO t_sample VALUES (1, 'profile check');
COMMIT;

Notes

CREATE USER creates the schema; PROFILE PF_APP assigns the profile.
QUOTA permits space usage in a tablespace. While UNLIMITED is convenient, set an upper limit in real operations.
The password is an example. In production, follow your password policy (if using special characters, enclose with double quotes).

5. Assign the Profile to Existing Users and Verify

Steps to switch existing users to the new profile.

-- Run as SYS or another privileged account
ALTER USER app_user PROFILE PF_APP;

-- Check the defined limits in the profile (DBA view)
SELECT resource_name, limit
FROM   dba_profiles
WHERE  profile = 'PF_APP'
ORDER  BY resource_name;

-- After logging in, values effective for the current session
CONN app_user/StrongP4ssw0rd1
SELECT resource_name, current_utilization AS current_value, limit
FROM   user_resource_limits
ORDER  BY resource_name;

Notes

DBA_PROFILES shows defined values; USER_RESOURCE_LIMITS shows the values effective for the current session.
If resource limits don’t apply, re-check the resource_limit setting (Step 2).

Commonly Used Profile Items (Excerpt)

Category	Item	Role (Key Point)
Password	`FAILED_LOGIN_ATTEMPTS`	Threshold for failed attempts; exceeding it locks the account
Password	`PASSWORD_LOCK_TIME`	Lock duration (days)
Password	`PASSWORD_LIFE_TIME`	Expiration (days); 0 or `UNLIMITED` is effectively no expiration
Password	`PASSWORD_GRACE_TIME`	Grace period before expiration (days)
Password	`PASSWORD_REUSE_TIME` / `REUSE_MAX`	Prevents reuse of past passwords
Resource	`SESSIONS_PER_USER`	Maximum concurrent sessions
Resource	`CONNECT_TIME`	Maximum session duration (minutes)
Resource	`IDLE_TIME`	Time to disconnect an idle session (minutes)

Typical Issues and Quick Hints

Account locked (ORA-28000)
Use ALTER USER <user> ACCOUNT UNLOCK;. The threshold is FAILED_LOGIN_ATTEMPTS in the profile.
Password grace warning (ORA-28002)
Have the user change the password, or review PASSWORD_LIFE_TIME / GRACE_TIME per your standard procedures.
Cannot reuse an old password (ORA-28007)
Controlled by PASSWORD_REUSE_TIME / REUSE_MAX. Changes must follow your profile policy.

Security tip: Enforce complexity (length/character classes, etc.) with a password verify function (PASSWORD_VERIFY_FUNCTION). A common practice is to implement a corporate-standard function and attach it to the profile.

Operational Approach (Start Small and Scale Safely)

Create a new profile (do not modify DEFAULT)
Validate impact with pilot users (lock/expiration/disconnect behavior)
Prepare FAQ for monitoring/help desk (unlock and extension procedures)
After validation, assign to groups in phases

【Staged Rollout Diagram】
PF_APP (new) ──> Pilot ──> Limited Production ──> Full Rollout

This article targets Oracle Database 19c (screens and defaults may differ in other versions).

[reference]
CREATE PROFILE