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

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Renee.

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

Last updated: February 23, 2021 - 6:03 am UTC

Version: 11.2.0.4

Viewed 10K+ 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 we 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

  (5 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' ;


More to Explore

Backup/Recovery

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