In the world of Oracle Database administration, managing how users connect and how their passwords behave is a critical security task. By default, Oracle imposes restrictions to protect the database, but there are scenarios—such as configuring service accounts or development environments—where you need to grant unlimited access and unlimited login attempts.

In this guide, we will walk through the essential commands to check, modify, and create Oracle profiles to suit your needs.

1. What are Oracle Profiles?

An Oracle Profile is a set of attributes that limit database resources and manage password requirements for a user. Every user is assigned a profile (usually the DEFAULT profile).

Key Parameters We’ll Cover:

• FAILED_LOGIN_ATTEMPTS: How many times a user can enter the wrong password before the account is locked.
• PASSWORD_LIFE_TIME: How many days a password remains valid before it must be changed.
• PASSWORD_LOCK_TIME: How many days an account remains locked after reaching the failed login limit.

How to Check Current User Profile Settings

SELECT username, profile 
FROM dba_users 
WHERE username = 'SYSTEM'; -- Replace with your target username

How to check the limits of profile attached to User

SELECT resource_name, limit 
FROM dba_profiles 
WHERE profile = 'DEFAULT';

Configuring Unlimited Access (No Password Expiry)

ALTER PROFILE DEFAULT 
LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Configuring Unlimited Login Attempts (No Account Locking)

ALTER PROFILE DEFAULT 
LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;
ALTER PROFILE DEFAULT 
LIMIT PASSWORD_LOCK_TIME UNLIMITED;

Creating a Custom “Service” Profile

-- 1. Create the profile with unlimited settings
CREATE PROFILE SM_UNLIMITED_PROFILE LIMIT 
  FAILED_LOGIN_ATTEMPTS UNLIMITED 
  PASSWORD_LIFE_TIME UNLIMITED 
  PASSWORD_LOCK_TIME UNLIMITED;
-- 2. Assign it to your specific user
ALTER USER app_user PROFILE SM_UNLIMITED_PROFILE;

Summary Table for Quick Reference