Renee, January 27, 2018 - 2:33 am UTC
Yes, that will work. Thank you.
January 28, 2018 - 5:16 am UTC
glad we could help
Nice! One minor change...
Brandon, March 06, 2019 - 7:51 pm UTC
Thank you for the tip, this is great, especially with the password hash changes in Oracle 12c. Since this returns output as a CLOB, one thing I had to change to get the correct output was to either increase the 'long' SQL*Plus parameter (I set it to 300), or put a TO_CHAR in front of the 'dbms_metadata.get_ddl' function:
Before:
SQL> with t as
2 ( select (dbms_metadata.get_ddl('USER','SCOTT')) ddl from dual )
3 select replace(substr(ddl,1,instr(ddl,'DEFAULT')-1),'CREATE','ALTER')||';'
4 from t;
REPLACE(SUBSTR(DDL,1,INSTR(DDL,'DEFAULT')-1),'CREATE','ALTER')||';'
--------------------------------------------------------------------------------
ALTER USER "SCOTT" IDENTIFIED BY VALUES 'S:1D071B834C431288450B55679ACAA90
SQL>After:
SQL> with t as
2 ( select <b>TO_CHAR</b>(dbms_metadata.get_ddl('USER','SCOTT')) ddl from dual )
3 select replace(substr(ddl,1,instr(ddl,'DEFAULT')-1),'CREATE','ALTER')||';'
4 from t;
REPLACE(SUBSTR(DDL,1,INSTR(DDL,'DEFAULT')-1),'CREATE','ALTER')||';'
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ALTER USER "SCOTT" IDENTIFIED BY VALUES 'S:1D071B834C431288450B55679ACAA907260552378021881A007EC1A0DD31;T:A4301D4A26AC3AED147801B408B6D5187B310AC34F0B63CB268E53918912C99B9F6FEFB7B4E926D09BCD9CEBB
6FAE230D11AE860DAFCAFA7AF7E86B3B34539384823B1B004A4DDDD79096505AFF5BA56'
;
SQL>Just a suggestion that helped get the correct output length. Thanks again for the tip!
March 12, 2019 - 5:00 am UTC
Nice input.
Good info
A reader, June 05, 2020 - 10:51 am UTC
I found this recently while searching for a solution to my problem and it's helpful.
My requirement, however, is to get the password for all the users in the DB and not one specific user.
It would be very helpful if you could share the query for that please.
June 10, 2020 - 1:10 am UTC
select dbms_metadata.get_ddl('USER','SCOTT') ddl from dual
becomes
select dbms_metadata.get_ddl('USER',username) ddl from dba_users
script works only for passwords created in current release
A reader, November 25, 2020 - 7:28 pm UTC
We recently upgraded our oracle database from 12.1.0.2 to 19.7.1 I tested the script and was able to restore new password created after the upgrade. But passwords that were created in 10g were saved similar to this: 'S:0000000000000000000000000000000000000000000'.
I am wondering if there is a different script that can save passwords that were created in earlier oracle versions.
Thanks.
November 27, 2020 - 7:14 am UTC
In very early versions of Oracle, the password was stored in SYS.USER$ so you could get (the hashed value) directly from there.
How about versions 19c and higher?
Rey, February 17, 2021 - 1:21 pm UTC
I tried this in version 19c, but no luck. All I got is -
IDENTIFIED BY VALUES 'S:00000000000000000000000000000000'
Any insight and workaround would be highly appreciated.
Thanks & regards
February 23, 2021 - 6:03 am UTC
Works fine on 19c too
SQL> with t as
2 ( select TO_CHAR(dbms_metadata.get_ddl('USER','SCOTT')) ddl from dual )
3 select replace(substr(ddl,1,instr(ddl,'DEFAULT')-1),'CREATE','ALTER')||';'
4 from t;
REPLACE(SUBSTR(DDL,1,INSTR(DDL,'DEFAULT')-1),'CREATE','ALTER')||';'
----------------------------------------------------------------------------------------------------------------------------------
ALTER USER "SCOTT" IDENTIFIED BY VALUES 'S:012F297B7D1C281EFDBDFD2FB158DCE026D0E128D6B74800DFE490719F2D;T:69FED274A9818540B07E1
0F2BF42967A13D8F151C448629EF25ACF2BDE9C941AFBDB108D46078761B0371205A53DE1A144DB3AE5450E485475E0E8D3DAB454AA2352A2D370384C00BDEBC8B
883DEC19F' ;
Result from DBMS_METADATA.get_ddl is all 0's
Gary, June 16, 2021 - 6:42 pm UTC
Greetings
When I ran the script against a 19c database most of the account password values looked 'normal'. There were several however that were all 0's like this:
VALUES 'S:000000000000000000000000000000000000000000000000000000000000'
What does that mean?
The accounts all have valid passwords.
Does it have to do with 'when' i.e. what version of the database was used to create the password?
This particular account has a 'Password Version' of 10g 11g.
It seems this will NOT re-create the existing password in the new database.
Thoughts?
Thanks very much!!!
-Gary
June 22, 2021 - 1:57 am UTC
Check SYS.USER$ for those older accounts. I think the password hash will be in there.
Looks like get_ddl doesn't work for sys user in 19c
Bernie Jimenez, March 15, 2022 - 10:28 am UTC
Hello!
Before migrating to 19c we saved sys password with "select TO_CHAR(dbms_metadata.get_ddl('USER','SYS')) ddl from dual;", and executed the exit of that query to reset sys password as it was before.
But after migrating to 19c looks like this procedure is not working any more.
Any idea of how can we save sys password in 19c to be able to reset it as it was before?
March 16, 2022 - 3:30 am UTC
Why doesn't it work? Works for me.
SQL> select dbms_metadata.get_ddl('USER','SCOTT') from dual;
DBMS_METADATA.GET_DDL('USER','SCOTT')
--------------------------------------------------------------------------------
CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:BA540C05F947D9D5F66623EEC02158B4E
4856D6D4D7964A972499F9A94AD;T:42CBAA8A8CF67427C904E701CF7AC687AD24A8333A40224A10
07393C18FD2E96402DBCCF2B1BEA9F348FB5168F69A821758227A1556DA3826723A72472147028E7
97EB77E1CBE019AF55BFA8344372F2' DEFAULT COLLATION "USING_NLS_COMP"
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
SQL> CREATE USER "SCOTT2"
2 IDENTIFIED BY VALUES 'S:BA540C05F947D9D5F66623EEC02158B4E485......... ;
User created.
SQL> grant create session to scott2;
Grant succeeded.
SQL> conn scott2/tiger@db19_pdb1
Connected.
Looks like get_ddl doesn't work for sys user in 19c
Bernie Jimenez, March 16, 2022 - 9:39 am UTC
Yes! With no SYS users works fine to me too. But not with SYS user.
SQL> conn a/"a"@DWH212BE
Connected.
SQL> conn / as sysdba
Connected.
SQL> select dbms_metadata.get_ddl('USER','A') from dual;
DBMS_METADATA.GET_DDL('USER','A')
--------------------------------------------------------------------------------
CREATE USER "A" IDENTIFIED BY VALUES 'S:8FE41257342969B977A8F965E443C14F51E49
084AFDE22A569BC1B7FED7F;T:AB7F99EB2BDBEB57CE2A5B621B878B912DDBF63F8DCF7970C8454D
0DE5078AC2B36B43A88BD6FE00B4BAB9065A44857C2910F41B482EECDBB59DF9B4D4F55E6043130D
836C9F954F2CC9A37F7B2803EE;AFCC9478DFBF9029'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
SQL> alter user a IDENTIFIED BY VALUES
2 'S:8FE41257342969B977A8F965E443C14F51E49084AFDE22A569BC1B7FED7F;T:AB7F99EB2BDBEB57CE2A5B621B878B912DDBF63F8DCF7970C8454D0DE5078AC2B36B43A88BD6FE00B4BAB9065A44857C2910F41B482EECDBB59DF9B4D4F55E6043130D836C9F954F2CC9A37F7B2803EE;AFCC9478DFBF9029';
User altered.
SQL> conn a/"a"@DWH212BE
Connected.
SQL>
SQL> conn sys/"a"@DWH212BE as sysdba
Connected.
SQL> select dbms_metadata.get_ddl('USER','SYS') from dual;
DBMS_METADATA.GET_DDL('USER','SYS')
--------------------------------------------------------------------------------
ALTER USER "SYS" IDENTIFIED BY VALUES 'S:EC38145C4788BBABF59BAD2DBBB28F542E39
316EE371555EF1FBA80C3474;T:3608D5E622FFB518DDFA6AB634F99B58074736F73CD7C8B2EECCB
A16CC93236FFD06EB0BEA280779936729D9E9946EB10AA0C9E07C48ACD6AED4122CDEF2F8D0B7FC8
AD8D3B5808E3902CBFB53897EE1'
TEMPORARY TABLESPACE "TEMP"
SQL> ALTER USER "SYS" IDENTIFIED BY VALUES
2 'S:EC38145C4788BBABF59BAD2DBBB28F542E39316EE371555EF1FBA80C3474;T:3608D5E622FFB518DDFA6AB634F99B58074736F73CD7C8B2EECCBA16CC93236FFD06EB0BEA280779936729D9E9946EB10AA0C9E07C48ACD6AED4122CDEF2F8D0B7FC8AD8D3B5808E3902CBFB53897EE1';
User altered.
SQL> conn sys/"a"@DWH212BE as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL>
March 18, 2022 - 4:50 am UTC
For SYS, it uses the password file. If you are tinkering with the VALUES command, there is a good chance you've now got the dictionary and password file out of sync, hence the problem
Looks like get_ddl doesn't work for sys user in 19c
Bernie Jimenez, March 18, 2022 - 7:25 am UTC
Hello!
I tried same procedure but backing up password file too, and after recovering sys password with "alter user sys identified by values ..." and restoring passworf file from original, I can connect with original password.
Thanks a lot!
tm, July 15, 2022 - 5:08 pm UTC
To generate the full CREATE USER statement with the hashed password values, the user executing DBMS_DDL will need the role EXP_FULL_DATABASE. The reason is that the hashed password values need to be protected and the SELECT_CATALOG role does not provide the needed access to the values stored in the USER$ table.
Ability to extract user create with encrypted password not working after 19.18 patching
Devin Conner, February 23, 2023 - 10:00 pm UTC
I've been using the following to extract out user setups for when we do database refreshes from Prod to lower environments and I want to maintain the schema passwords from before the refresh:
select to_char(dbms_metadata.get_ddl('USER','PWDTEST')) ddl from dual;
This has worked up through 19.17, but when I just patched to 19.18 (January 2023 DB-RU) this no longer works to get the password. Here are the output examples for user "PWDTEST":
19.17: DDL
--------------------------------------------------------------------------------
CREATE USER "PWDTEST" IDENTIFIED BY VALUES 'S:538CB85B6F58CFB45EE453AE672E9B9
EFE9C97B4386A2D96B55EF79ACDFC;T:EB1901AF4F1815DE0EE8F8DFF6130ECBA6BC1799A65B0C06
471AEE334CA3905609ABD6296FEDFAE97A0461D7BFF21E187D231E565C3FBFB1AB0F2877E0F72D48
8BC502BD01EF02811A6F1024A7AC3000'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
19.18: CREATE USER "PWDTEST"
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
Any help would be greatly appreciated.
February 27, 2023 - 1:42 am UTC
If you have access to sys.user$, you can get the hash value from there
SQL> select * from sys.user$
2 where name = 'SCOTT'
3 @pr
==============================
USER# : 110
NAME : SCOTT
TYPE# : 1
PASSWORD :
DATATS# : 5
TEMPTS# : 3
CTIME : 02-NOV-21
PTIME : 03-JAN-23
EXPTIME : 02-JUL-23
LTIME :
RESOURCE$ : 0
AUDIT$ :
DEFROLE : 1
DEFGRP# :
DEFGRP_SEQ# :
ASTATUS : 16
LCOUNT : 0
DEFSCHCLASS : DEFAULT_CONSUMER_GROUP
EXT_USERNAME :
SPARE1 : 0
SPARE2 :
SPARE3 : 16382
SPARE4 :
S:8EE9249C1EBA1525F1C76AB91D239B22D2168D2996444D18E625D33202E0;T:D1CDA9DB5366C2A8694F71F27C1B51C3C9496EF009D699DDFDBFE6992EACDC475
B7A686F48A014122FB7B5CD2BF22298990221C0DEAF1590CBF6AF25C9A06466D5912D145F529F296E08B87747060799
SPARE5 :
SPARE6 : 19-FEB-23
SPARE7 :
SPARE8 :
SPARE9 : 3
SPARE10 : 0
SPARE11 :
Correction to my last post regarding Oracle 19.18 and changes to dbms_metadata.get_ddl
Devin Conner, February 23, 2023 - 11:38 pm UTC
Sorry for the incorrect information in my previous post today - it isn't the Oracle 19.18 DB-RU (patch id 34765931) that is causing the change in behavior to retrieving a users encrypted password.
It is the currently Datapump Bundle Patch (patch id 34972375) that is causing the issue.
Sorry about that.
February 27, 2023 - 1:43 am UTC
thanks for getting back to us
GJ, March 01, 2023 - 3:31 pm UTC
I have the same problem with dbms_metadata.get_ddl and ended up rewriting my script to pick sys.user$ password field.
March 07, 2023 - 1:39 am UTC
Passwords are meant to be hard to find :-)
How to get the password for all the users in the DB with DEFAULT ROLE
Harrison, June 14, 2023 - 6:59 pm UTC
My requirement is How to get the password for all the users in the DB with DEFAULT ROLE before the it been overwritten by restore DB from Prod.
The sample result is like following:
CREATE USER "BCOST" IDENTIFIED BY VALUES 'S:52C12CD934D7F9680ADED20D96;T:F2B6BD4ED579D8F7523CEADCE4AAB97CCBD7F8895A0B1D783454E719D03BA7940F1BC1712AFB159AED4B480C5E3'
DEFAULT COLLATION "USING_NLS_COMP"
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
ACCOUNT UNLOCK ;
-- QUOTAS
-- ROLES
ALTER USER "BCOST" DEFAULT ROLE "AWSPRCBIL_RO_ROLE","AWSPRCPOL_RO_ROLE","AWSPRCATH_RO_ROLE";
-- SYSTEM PRIVILEGES
June 19, 2023 - 4:33 am UTC
See the posts above about how to get it from the SYS tables
How to capture user and encrypted password
Igor, July 23, 2023 - 12:05 am UTC
None of the following codes will help you retrieve the password if the PASSWORD_VERSION is 10G. The code works only for PASSWORD_VERSION 11G and 12C.
SQL> with t as
( select TO_CHAR(dbms_metadata.get_ddl('USER','SCOTT')) ddl from dual )
select replace(substr(ddl,1,instr(ddl,'DEFAULT')-1),'CREATE','ALTER')||';'
from t;
select * from sys.user$ where name = 'SCOTT';
August 04, 2023 - 5:36 am UTC
10g? C'mon man :-)
get_ddl for user not working after changing password with ALLOWED_LOGON_VERSION*=12
JJ, November 28, 2023 - 2:12 pm UTC
We are currently working through our annual id certification with users, where they are required to update their database userid passwords. This is our first certification since we updated sqlnet.ora to include ALLOWED_LOGON_VERSION*=12.
GET_DDL is spitting out an incorrect password values string that includes specifications we have not seen previously(in bold below):
select dbms_metadata.get_ddl('USER', username) || '/' usercreate from dba_users; CREATE USER "xxx" IDENTIFIED BY VALUES 'S:xxx;T:xxx;
t:xxx;V:xxx;s:xxx;U:xxx'
Usernames and values string have been replaced with xxx for security, but the unknown identifiers in question remain - "t::" "V:" "s:" "U:"
Any assistance would be appreciated. The only other updates have been quarterly CPUs. We are currently at 19.20
January 15, 2024 - 5:33 am UTC
Can you elucidate a little more on exactly what you're trying to achieve?
If people are updating their passwords as part of annual process, why do you need to generate "CREATE USER" commands?
oraclenthusiast
Piyush, July 04, 2024 - 10:52 am UTC
I used both the method of getting the values from 10.2.0.4.0 dev environment for user SAPSR3. The values obtained are same.
I altered the user in another environment QA which is 11.2.0.4.0.
We have initially setup these lower environment servers to have common password string for the ease of connecting.
but in my case I am receiving-
SQL> conn SAPSR3
Enter password:
ERROR:
ORA-01017: invalid username/password; logon denied
What am I missing?
July 09, 2024 - 12:25 pm UTC
I'm unclear exactly what you did, so it's hard to say