how to fix user in grace period if you dont know the password .is it possible?

sunny thakur - October 13, 2026

Quick Answer: Yes, you can change an Oracle database user password when you don't know the current one by using administrative privileges (SYSDBA). If you need to preserve, backup, or clone an existing password hash—especially when an account is locked or stuck in a password grace period—you can extract its exact DDL using DBMS_METADATA.GET_DDL, query the internal password verifier directly from the data dictionary, modify the user, and reapply credentials safely.

Picture this scenario: An application service account or critical developer login is sitting in an OPEN(GRACE) password expiry state, or locked out entirely, and nobody on the team remembers the current plaintext password. Production is humming along, and you need to intervene immediately without breaking existing application connection pools or triggering unexpected authentication failures.

As a DBA, you might be tempted to blindly run a standard ALTER USER app_user IDENTIFIED BY ... command. But in enterprise environments, doing that blindly can cause silent connection drops, violate strict corporate compliance profiles, or trigger frustrating database errors. A professional, bulletproof DBA approach involves inspecting the account's exact state, capturing its existing DDL definition or internal password verifier hash as a fallback, navigating profile restrictions, and applying the fix cleanly.

Let's walk through two distinct methods to handle this like a seasoned production DBA, complete with exact SQL commands and edge-case handling.

Method 1: The DBMS_METADATA DDL Extraction Approach

When an account is in a password grace period or nearing expiration, your first line of defense is understanding how the database object is structured. If you want to view how the user was created—or capture their existing encrypted password verifier hash so you have a foolproof backup before making structural changes—you can use the built-in DBMS_METADATA package.

Step 1: Check Account Status and Grace Period

Before touching anything, run this query to find out if the account is active, expired, or currently operating inside a password grace period:

SQL> SELECT username, account_status, expiry_date, profile 
     FROM dba_users 
     WHERE username = 'APP_USER';

Output Example:

USERNAME              ACCOUNT_STATUS       EXPIRY_DATE         PROFILE
--------------------- -------------------- ------------------- ----------------
APP_USER              OPEN(GRACE)          14-OCT-26           DEFAULT

Seeing OPEN(GRACE) means the password has technically crossed its lifetime threshold, but the user is currently allowed to log in within the grace window defined by the database profile. If you change the password now without precautions, you reset the clock—but you must ensure your new password satisfies all profile rules.

Step 2: Extract the User DDL and Password Verifier Hash

To inspect the full creation script—including the encrypted password verifier—run the following SQL statements:

SQL> SET LONG 20000;
SQL> SELECT DBMS_METADATA.GET_DDL('USER', 'APP_USER') FROM DUAL;

Output Example:

  CREATE USER "APP_USER" IDENTIFIED BY VALUES 'S:5F38B9...;T:9C2A...'
  DEFAULT TABLESPACE "USERS"
  TEMPORARY TABLESPACE "TEMP";

Notice the IDENTIFIED BY VALUES clause. This contains the hashed password verifier string. Saving this DDL ensures that if an emergency rollback is required, you can re-apply the exact previous authentication hash immediately without ever needing to know the original plaintext password.

Method 2: Extracting and Reusing Password Hashes via the USER$ Table (Advanced DBA Trick)

What if you need to copy a working password hash from one environment (like a test instance) to a target user, or extract the raw verifier directly when metadata packages aren't enough? Oracle stores user metadata, including encrypted password verifiers, in the internal dictionary table USER$ (accessible via SYS).

Step 1: Query the Password Verifier Directly from SYS.USER$

Logged in as SYSDBA, you can query the internal password hash directly:

SQL> SELECT name, password, spare4 
     FROM sys.user$ 
     WHERE name = 'APP_USER';

Note: In modern Oracle versions (Oracle 12c, 19c, and 21c/23ai), SHA-2 and case-sensitive password verifiers are typically stored in the spare4 column.

Step 2: Apply the Hash Directly Using IDENTIFIED BY VALUES

Once you have extracted the valid password verifier string (e.g., the hash string starting with S: or T:), you can assign it directly to another user or reset it on the same user without knowing the plaintext password:

SQL> ALTER USER app_user IDENTIFIED BY VALUES 'S:7C89F2...;T:4E1A...';

This method is exceptionally powerful during database migrations, cross-environment refreshes, or disaster recovery scenarios where application connection strings cannot afford downtime or credential mismatches.

Handling the Profile Limit Trap (ORA-28007)

Whether you are setting a brand-new plaintext password or applying a hash, Oracle's security profiles can block your changes. If your organization enforces strict password history compliance, you may encounter this error:

ERROR at line 1:
ORA-28007: the password cannot be reused

To bypass this roadblock temporarily without violating your long-term security architecture, execute these steps:

  1. Temporarily lift the reuse restriction: 
    SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_MAX UNLIMITED;
  2. Apply your password change or verifier hash: 
    SQL> ALTER USER app_user IDENTIFIED BY NewSecurePassword99$;
  3. Immediately restore the security compliance limit: 
    SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_REUSE_MAX 5;

Verification Output:

User altered.

Summary & Next Steps

Managing database user accounts in grace periods or recovering forgotten credentials requires more than just running a blind ALTER USER command. By leveraging DBMS_METADATA.GET_DDL, querying internal dictionary tables like SYS.USER$ for password verifier hashes, and carefully handling profile restrictions, you maintain total administrative control while keeping production applications running smoothly.

If you are building up your foundational Oracle DBA skills from scratch or looking at how enterprise database architectures are shifting away from legacy setups, check out these related deep-dives on our blog:

Post a Comment

0 Comments

Really Thanks