Oracle DBA Lesson 76 Part 1 — Diagnose Lockout and Expiry

A failed database login can require an account unlock, a password change, or a correction of credentials held by a client. Read the account's state and its effective password policy together before choosing the response.

Oracle DBA Lesson 76 Part 1 — Diagnose Lockout and Expiry

Read the account state

Confirm the current user and container, then read the account:

SHOW USER
SHOW CON_NAME

SELECT username, account_status, profile,
       lock_date, expiry_date
FROM dba_users
WHERE username = 'APP_SERVICE';

SHOW commands belong to SQL*Plus. The query returns database information. For an ordinary unquoted username, use its uppercase dictionary spelling.

Field	What it helps establish
USERNAME	Which account you are diagnosing
ACCOUNT_STATUS	The account's recorded login restriction or password state
PROFILE	Which profile supplies its policy
LOCK_DATE	Lock timing context. Interpret it with the status and the policy.
EXPIRY_DATE	Password expiry context. Its meaning can change as grace begins.

Date display depends on the client settings. Include the time when you investigate a short lock interval.

State	Meaning and response
LOCKED(TIMED)	Consecutive failed logins have triggered a lock whose configured duration has not yet elapsed. Investigate the failing clients and inspect the effective lock duration. Waiting for that interval, or an account unlock, can release this lock.
LOCKED	Can follow an explicit lock, or a failed-login lock with PASSWORD_LOCK_TIME = UNLIMITED. An explicit lock requires an administrator to unlock it. Other account policies, including inactivity rules, can also be relevant. Inspect the account history.
EXPIRED(GRACE)	Login is allowed with a warning during the grace period. Arrange the password change while this opportunity remains.
EXPIRED	A password change is required to complete login. SQL*Plus can prompt for it. Application clients vary in their ability to perform this exchange.
EXPIRED & LOCKED and timed or grace combinations	Both restrictions apply. Resolve the lock and complete the applicable password change.

For a timed lock, DBA_USERS may continue to show the locked state after the duration has elapsed, until a login updates the recorded status. Use the timing and fresh authentication evidence together. Do not generate repeated guesses to test an account.

The account state describes the restriction. It does not identify which application instance, scheduled task, or secret reference submitted a bad password. Correlate the incident with the client and authentication records. See DBA_USERS.

Resolve the effective password policy

Inspect the assigned profile alongside DEFAULT:

SELECT profile, resource_name, limit
FROM dba_profiles
WHERE profile IN (
  'DEFAULT',
  (SELECT profile FROM dba_users
   WHERE username = 'APP_SERVICE'))
AND resource_type = 'PASSWORD'
ORDER BY profile, resource_name;

RESOURCE_TYPE = 'PASSWORD' selects authentication-related settings. LIMIT is a character column. It can contain a number, DEFAULT, UNLIMITED, NULL as a displayed setting, or a verification-function name, depending on the parameter.

For each assigned-profile entry whose limit is DEFAULT, look up the same RESOURCE_NAME in the DEFAULT profile. Omitted parameters in a created profile inherit the default-profile value too. Read the values in the installation rather than assuming its defaults. The query orders the returned rows by profile and parameter name.

APP_SERVICE uses profile APP_ACCESS. These rows show the failed-login limit and the inherited lock duration:

PROFILE	RESOURCE_NAME	LIMIT
APP_ACCESS	FAILED_LOGIN_ATTEMPTS	3
APP_ACCESS	PASSWORD_LOCK_TIME	DEFAULT
DEFAULT	PASSWORD_LOCK_TIME	0.0416667

The account allows three consecutive failed login attempts before locking. Its lock duration is inherited:

1/24 day × 24 hours/day = 1 hour

The displayed decimal is rounded. Dictionary formatting and precision can differ.

Password policy is enforced independently of RESOURCE_LIMIT. Setting that parameter to FALSE disables profile resource-limit enforcement, while password restrictions remain in effect. External and global authentication have different policy applicability. These checks apply to database password authentication.

For common users, password-profile scope is rooted in the common user's root profile. Check that scope before applying this local-user lookup to a common account. See DBA_PROFILES and CREATE PROFILE.

Interpret password aging and replacement rules

PASSWORD_LIFE_TIME measures days since the password was set, or since account creation if it has never changed. Changing the profile lifetime does not restart the password's age.

With a grace period configured, the first correct login after the lifetime has elapsed starts grace and updates the expiry information. Login succeeds with a warning. After grace ends, the password must be changed to complete authentication. An administrator can also force expiry with ALTER USER ... PASSWORD EXPIRE. See password management policy and ALTER USER.

Login activity evaluates this behavior. A background task does not change every account's displayed state at the instant a calendar threshold passes. An already established session does not prove that a fresh login works.

Password replacement must satisfy the effective reuse and complexity rules:

Effective reuse settings	Meaning
PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX both finite	The required elapsed days and the number of password changes must both be satisfied before reuse.
One finite, the other UNLIMITED	Reuse is prevented.
Both UNLIMITED	These two reuse restrictions are ignored.
Either is DEFAULT	Resolve its inherited value first, then apply the appropriate rule above.

See CREATE PROFILE.

PASSWORD_VERIFY_FUNCTION names a function that checks the proposed password's complexity. Oracle requires the function to exist in SYS, and it requires the relevant execution privilege for use in a profile. Inspect the function and its implementation before assignment. NULL means no password verification function is configured for that setting. See password complexity.

Resolve recurring application lockout

When the evidence identifies stale client credentials, use this sequence:

1. Identify the application instances, pools, scheduled jobs, and secret references that use the affected identity. Stop the repeated failing attempts through the application's own procedure.
2. Correct the credentials through the secret-management process. Coordinate a password change if the password is expired or must be replaced.
3. Unlock the account when the lock is the restriction that still blocks login.
4. Verify a fresh authentication from each affected client. Recheck the account state and the failure records.

The unlock statement changes account lock state:

ALTER USER app_service ACCOUNT UNLOCK;

It does not update credentials held by applications. Repeated submissions of an old password can hit the failed-login threshold and lock the account again. An unlock also leaves an expired password, which still requires its change procedure. See ALTER USER.

Quiz

1. An account is LOCKED(TIMED). What should be inspected first?

Its default tablespace only Its status, lock timing, effective failed-login and lock policy, and client failures The database buffer cache size Its object count

2. APP_ACCESS.PASSWORD_LOCK_TIME is DEFAULT. Where is its effective value found?

The matching parameter in the DEFAULT profile The account's tablespace quota The client's idle timeout The value is always zero

3. What does a password lock time of 1/24 mean?

One minute Twenty-four failed logins One hour Twenty-four hours

4. Will unlocking a service account fix clients that continue sending its old password?

Yes, unlocking distributes the password Yes, password checks are suspended Only when RESOURCE_LIMIT is FALSE No. Correct and verify the client credentials

5. Both reuse parameters have finite values. When may a previous password be reused?

After either condition is met After both the required days and password changes are satisfied Immediately after an account unlock Whenever an existing session is working

https://www.apps-dba.com/ https://www.youtube.com/@EnhanceSkillsDaily