DBMS_METADATA.GET_DDL not returning password hashes

Today I've stumbled over a new behaviour Oracle introduced for function DBMS_METADATA.GET_DDL.

Starting with Datapump Bundle Patch 19.17 (which includes fix for bug 33517865), Oracle introduced an important change:

The DBMS_METADATA.GET_DDL no longer includes password hashes in the output.

As it was

In earliert Oracle releases (before 11g), the SELECT_CATALOG_ROLE role or SELECT_ANY_DICTIONARY privilege has been sufficcient to get the user ddl statement, including the hashed password, like:



SELECT DBMS_METADATA.GET_DDL('USER','APP01') as statement from dual;

CREATE USER "APP01" IDENTIFIED IDENTIFIED BY VALUES 'S:C9D439FB9A9161792DB26E9BF2D9EBDFCB6828A258FF9379FF61F1B2125F;D4DF7931AB130E37'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"

next level hardening

With Oracle 11g and later it became necessary to grant EXP_FULL_DATABASE role to extract the password hashes with DBMS_METADATA.GET_DDL.

See the corresponding MyOracleSupport Note

https://support.oracle.com/support/?anchorId=&kmContentId=1529478&page=sptemplate&sptemplate=km-article

no more hashes

Datapump Bunlde Patch 19.17 includes a fix for bug 33517865. With this patch in place you won't see the password hashes even having both roles EXP_FULL_DATABASE and SELECT_CATALOG_ROLE.

As said this is intentional to prevent unauthorized access to password hashes.

and what now?

to bring back the access to the password hashes for a migration or backup scenario etc. Oracle provides bugfix 35018026 which is available with Datapump bundle patch 19.19 and later. This fix restores the previous behaviour though the user needs explicit select privileges on SYS.USER$

the fix

apply fix for bug 35018026 included in DPBP 19.19 and later
user running the query needs select or read privs on SYS.USER$

grant select_catalog_role  to APP01;
grant select on  sys.user$ to APP01;

with the above in place you will get the hashed passwords again via DBMS_METADATA.GET_DDL (only users who are explicitly allowed to see them)

important

you might see this happening depending on how you patch you environment
the behaviour above comes with Data Pump Bundle Patches(DPBP) only, not in standard Release updates (RU)

summary/lessons learned

starting with DPBP 19.17 access to hashed password via DBMS_METADATA.GET_DDL is no longer possible
Oracle provides a fix in bug fix 35018026 available with DPBP 19.19