Skip to Main Content
  • Questions
  • How to capture user and encrypted password to be used in alter user statement after duplication of database

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Renee.

Asked: January 25, 2018 - 3:52 pm UTC

Last updated: January 15, 2024 - 5:33 am UTC

Version: 11.2.0.4

Viewed 100K+ times! This question is

You Asked

We perform a duplicate database from prod to test weekly and have a script that captures the current user permissions on dev via the dbms.metadata.get_ddl package that generates DDL to another file to be used after the duplication to re-create any user that has access to test but not dev (we also capture grants, etc.)

After the duplicate we execute the sql generated from the file above. The problem is that users passwords may be different in prod than test. With our current method, the password is not changed. I'd think I just need to generate a query that selects from a view that has the encrypted password and generate an 'alter user <username> identified by values 'xxxxxx' statement. However, how do I extract just the encrypted password from dbms_metadata? How can I code this without using PLSQL?

and Connor said...

Could you do this in your Test environment before overwriting it

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:D8CC9026F20E5E390B7F320616CF167154
7C419BCCCD50C121810CC056C4;T:AEC2CE5711DDFF37812F647B3F1DE7A27297050EDF1BC8E52AC
786586C9F212E90959AB99FCC817AFDD44D440F0C5A93AC964FE55461716ECE83050024DE62724E9
29A6CC8E857833D078ADF0BE5D4D5'
      ;


and then run it in after the duplicate has finished. I'm using a single user there, but you can just as easily run it against DBA_USERS.

Rating

  (16 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Renee, January 27, 2018 - 2:33 am UTC

Yes, that will work. Thank you.
Connor McDonald
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!
Connor McDonald
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.
Connor McDonald
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.
Connor McDonald
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
Connor McDonald
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
Connor McDonald
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?
Connor McDonald
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>
Connor McDonald
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.
Connor McDonald
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.
Connor McDonald
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.
Connor McDonald
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


Connor McDonald
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';
Connor McDonald
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


Connor McDonald
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?


More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.