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
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.