Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Mauro.

Asked: March 05, 2026 - 3:08 pm UTC

Last updated: March 10, 2026 - 3:54 am UTC

Version: 19

Viewed 100+ times

You Asked

I am normally using
"
SELECT DBMS_METADATA.GET_DDL('USER', 'User_Name') FROM DUAL;
"
to get the hashed password ( which I use to re-create the user as it was ), with SYSTEM user.
This unfortunately does not work for users which have password_versions = 10G ( I am using Oracle 19 but I think this happens with 12 as well )
The hashed password in that case is stored in a column of USER$ sys table that SYSTEM user is not allowed to see
However I observed that I can use export datapump to dump the user definition and then import datapump to get a sql file that has the hashed password, even with SYSTEM user ( but it is a long way ).
So how can SYSTEM user get the hashed password?
I would like a simpler way to get a script to re-create a user

Regards

Mauro

and Connor said...

Can you elaborate on "This unfortunately does not work for users which have password_versions = 10G"

For example (from my old 11g instance)

SQL> create user demo123 identified by abcd1234;

User created.

SQL> grant create session to demo123;

Grant succeeded.

SQL> select * from dba_users
  2  where username = 'DEMO123'
  3  @pr
==============================
USERNAME                      : DEMO123
USER_ID                       : 107
PASSWORD                      :
ACCOUNT_STATUS                : OPEN
LOCK_DATE                     :
EXPIRY_DATE                   : 01-SEP-26
DEFAULT_TABLESPACE            : USERS
TEMPORARY_TABLESPACE          : TEMP
CREATED                       : 05-MAR-26
PROFILE                       : DEFAULT
INITIAL_RSRC_CONSUMER_GROUP   : DEFAULT_CONSUMER_GROUP
EXTERNAL_NAME                 :
PASSWORD_VERSIONS             : 10G 11G
EDITIONS_ENABLED              : N
AUTHENTICATION_TYPE           : PASSWORD

SQL> select dbms_metadata.get_ddl('USER','DEMO123') from dual;

DBMS_METADATA.GET_DDL('USER','DEMO123')
--------------------------------------------------------------------------------

   CREATE USER "DEMO123" IDENTIFIED BY VALUES 'S:E38E13CC98AA4D7417D41B198E5C6E5
B26AD4A1617B23450F04BD3257D5D;170E7CE1A5B29B4F'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"


SQL> drop user demo123;

User dropped.

SQL> CREATE USER "DEMO123" IDENTIFIED BY VALUES 'S:E38E13CC98AA4D7417D41B198E5C6E5B26AD4A1617B23450F04BD3257D5D;170E7CE1A5B29B4F';

User created.

SQL> conn demo123/abcd1234@//192.168.1.180/db11
Connected.

Rating

  (2 ratings)

Comments

Mauro Papandrea, March 09, 2026 - 9:50 am UTC

Sorry If I wan not clear enough.
In your example you have password_versions = '10G 11G', the '11g' part is what makes it works.
If you have just '10G' you get a bunch of zeroes instead of the hashed value:

username, password_versions

ALFA  10G
BETA  10G 11G

DBMS_METADATA.GET_DDL('USER','BETA')
--------------------------------------------------------------------------------

   CREATE USER "BETA" IDENTIFIED BY VALUES 'S:939085939AE421D88ABAD8353DFA
87B5ADBAA228A6A2CEE08E04E4D9CB09;CB075B6D3EA3D797'


but:

DBMS_METADATA.GET_DDL('USER','ALFA')
--------------------------------------------------------------------------------

   CREATE USER "ALFA" IDENTIFIED BY VALUES 'S:0000000000000000000000000000000000
00000000000000000000000000'



Best regards

Mauro

Connor McDonald
March 10, 2026 - 3:54 am UTC

I think for those old versions you should be able to spot the password in SYS.USER$ in one of the "spare" columns

Mauro Papandrea, March 10, 2026 - 7:31 am UTC

Yes, of course, but that requires the use of SYS user.
However, datapump is able to retrieve the password even when using SYSTEM user, so I am wondering how it can manage, I think there must be a way but I cannot figure it out

Regards
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.