DBMS_METADATA.GET_DDL Does Not Return Hashed Passwords Anymore

Introduction:

If you have been using DBMS_METADATA.GET_DDL to extract user DDLs along with their hashed passwords, you should be aware that starting with Oracle DataPump Bundle Patch 19.17 (which includes the fix for bug 33517865), a significant change has been introduced. The function no longer includes password hashes in the output, and this change is intentional, driven by security enhancements.

What It Used to Be Like :

In older Oracle releases (Earlier than 11g) , simply having the SELECT_CATALOG_ROLE role or SELECT_ANY_DICTIONARY system privilege was enough to extract the full CREATE USER DDL statement , including the hashed password, like this:

SQL> SELECT DBMS_METADATA.GET_DDL('USER','INSANEDBA') as statement from dual;

STATEMENT
__________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________

   CREATE USER "INSANEDBA" IDENTIFIED BY VALUES 'S:A3E508886DC0C95B779873C98C69C5869AA56968567920B573E72A7124EB;T:50356787FB9163B7C8CB24100EA70362653FF257E48D1614732EC2C053B939B072996F45A5B6D741D4AC3B9A6F5E4BBA449EAE1785C3F5116BACAE68DBFBAFA556BB55093AD9E629F3D308C88DC8C10A'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"

This output included the IDENTIFIED BY VALUES clause with the hashed password, which was useful for cloning users, storing user passwords history or migrating accounts across environments.

Then It Got Hardened

Over time(11g and later), Oracle tightened security. It became necessary to grant the user the EXP_FULL_DATABASE role to extract hashed passwords with DBMS_METADATA.GET_DDL. The reason? Oracle demanded more privileges than just SELECT_CATALOG_ROLE. Dbms_metadata.get_ddl(‘USER’,'<username>’) Does Not Return A Password Value (Doc ID 1529478.1) EXP_FULL_DATABASE role already involves SELECT_CATALOG_ROLE and EXECUTE_CATALOG_ROLE.

This approach was strongly discouraged, but it was also possible by setting the O7_DICTIONARY_ACCESSIBILITY parameter to TRUE, which bypasses the limitations and allows access to objects in the SYS schema with SELECT ANY TABLE system privilege. This parameter was deprecated in 18c and completely desupported in 19c.

No More Password Hashes

With the Data Pump Bundle Patch 19.17 and newer, Oracle has included a fix for Bug 33517865. Even if you have both the EXP_FULL_DATABASE and SELECT_CATALOG_ROLE privileges, you will no longer be able to view hashed passwords. This is a deliberate security hardening measure designed to prevent unauthorized access to password hash data.

Still Need the Hashes?

For those who do have a legitimate need to access user password hashes: such as for backing up or controlled migration scenarios. Oracle provided a way with Bug Fix 35018026 ships with Data Pump Bundle Patch 19.19 and later on. This fix restores the behavior, but only if the executing user has explicit READ or SELECT privileges on the underlying dictionary table (SYS.USER$).

Requirements:
You must apply the fix for Bug 35018026 (shipped with DPBP 19.19 and later).
The user running the query must have SELECT or READ privilege on SYS.USER$.

SQL> GRANT SELECT_CATALOG_ROLE ON TO USER_XXX;
SQL> GRANT SELECT ON  SYS.USER$ TO USER_XXX;

Once these conditions are met, DBMS_METADATA.GET_DDL can again return hashed passwords, but only for users who are explicitly authorized to see them.

It’s important to understand why you might or might not be seeing this behavior, depending on how you patch your environment.

The change described above is included only in the Data Pump Bundle Patches, not in standard Release Updates (RUs). That’s because of the Data Pump patches are currently non-RAC rolling and thus are delivered separately. Mike Dietrich explains this well in his blog post:
Apply the Data Pump Bundle Patch – non-rolling but online. Oracle is working on enhancing the Data Pump patching process, potentially integrating it with RUs in the future. For now, though, you’ll need to apply the Data Pump Bundle Patch manually if you want these changes.

Another way : Using PL/SQL function

Instead of granting direct SELECT access on SYS.USER$ to USER_XXX, a PL/SQL function can be created under the SYS schema with definer rights. This function would be solely responsible for returning hashed passwords only for allowed users, making it possible to implement row-level security on SYS.USER$

Here is a sample PL/SQL function that returns the hashed passwords of all users except SYS.

-- Create function returning users hashed passwords
CREATE OR REPLACE FUNCTION SYS.FN_GET_USER_PASSWORD (p_username VARCHAR2) 
RETURN VARCHAR2 
IS 
    v_hashed_password VARCHAR2(1000 BYTE); -- Variable to store hashed password
BEGIN
    -- Check if the user is not 'SYS'
    IF UPPER(p_username) <> 'SYS' THEN 
        -- Retrieve the hashed password for the specified user
        SELECT SPARE4 
        INTO v_hashed_password 
        FROM SYS.USER$ 
        WHERE NAME = UPPER(p_username) 
          AND NAME <> 'SYS';
    ELSE 
        -- Return a message if the user is 'SYS'
        RETURN 'SYS password is protected!';
    END IF;
    
    -- Return the hashed password if found
    RETURN v_hashed_password;

EXCEPTION 
    WHEN NO_DATA_FOUND THEN
        -- Handle case where no user was found
        RAISE_APPLICATION_ERROR(-20001, 'User not found: ' || p_username);
    WHEN OTHERS THEN 
        -- General exception handler for any other errors
        RAISE_APPLICATION_ERROR(-20000, 'An error occurred while reading the user password for ' || p_username);
END;
/

Grant the EXECUTE privilege to the user who needs access to hashed passwords.

-- Grant execute permission to USER_XXX user
SQL> GRANT EXECUTE ON SYS.FN_GET_USER_PASSWORD TO USER_XXX;

Here’s a quick sample to test it out. First, we grant the EXP_FULL_DATABASE role to USER_XXX, and then use the sample SQL query below to retrieve usernames and their hashed passwords

SQL>  GRANT EXP_FULL_DATABASE TO USER_XXX;

SQL> SELECT REPLACE(
           DBMS_METADATA.GET_DDL('USER', USERNAME),
           'CREATE USER "' || USERNAME || '"',
           'CREATE USER "' || USERNAME || '" IDENTIFIED BY VALUES ''' || SYS.FN_GET_USER_PASSWORD(USERNAME) || ''''
       ) AS create_user_with_password
FROM   DBA_USERS 
WHERE  USERNAME = 'INSANEDBA';

Conclusion :

With recent Oracle Data Pump patches (involving bug fix 33517865), access to hashed passwords via DBMS_METADATA.GET_DDL is no longer available by default even for users with granted roles like EXP_FULL_DATABASE. This change, introduced as part of security-focused fixes, enforces stricter controls over sensitive data exposure. However, for legitimate administrative needs, Oracle provides controlled methods (with bug fix 35018026), such as granting SELECT access on SYS.USER$ directly, or using a definer-rights PL/SQL function created under the SYS schema.

Hope it helps.