Home » RDBMS Server » Server Administration » synchronizing user password from another database
synchronizing user password from another database [message #213170] Tue, 09 January 2007 13:04 Go to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
One of my rare questions.

I have a dev/test database that is copy of production. It is outdated as far as passwords for the 100 or so schemas. Can I synch up the passwords in the dev/test database with those of the production database, not knowing what they are in production?

Both database are version 10gr2.
Re: synchronizing user password from another database [message #213175 is a reply to message #213170] Tue, 09 January 2007 13:29 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't know is it possible or not, but this Mahesh's trick gave me the idea: connected as a privileged user (SYS), do something like this:
UPDATE TEST.dba_users u SET
  u.PASSWORD = (SELECT p.PASSWORD
                FROM production.dba_users p
                WHERE p.username = u.username
               )
  WHERE EXISTS (SELECT NULL FROM production.dba_users p
                WHERE p.username = u.username
               );

Do note that I didn't test it (and I don't intend to ./fa/1599/0/); perhaps it would be a good idea to wait for someone who really knows whether it is possible or not.
Re: synchronizing user password from another database [message #213177 is a reply to message #213175] Tue, 09 January 2007 13:46 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
Thanks Littlefoot. I'll cautiously try it out in a day or two. I guess I am not a good searcher as that result did not come up, or it was down 20 pages.
Re: synchronizing user password from another database [message #213180 is a reply to message #213177] Tue, 09 January 2007 14:01 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
No problem; you know I like to review old messages, so I did that through ~8000 Mahesh's posts and found this one.

Just kidding! It was a couple of weeks ago; I remember I thought that something like this was absolutely impossible, but, oh well, Mahesh showed me/us that it wasn't.
Re: synchronizing user password from another database [message #213200 is a reply to message #213180] Tue, 09 January 2007 15:35 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
It should work.But, I would not update the dba_users directly.
Standard procedure is to generate the statements
scott@9i > select 'alter user '||username||' identified by values '''||password||''';' from dba_users;

'ALTERUSER'||USERNAME||'IDENTIFIEDBYVALUES'''||PASSWORD||''';'
------------------------------------------------------------------------------------------------
alter user SYS identified by values 'D4C5016086B2DC6A';
alter user SYSTEM identified by values '1C4DEB81D4E4B2B4';
alter user OUTLN identified by values '4A3BA55E08595C81';
alter user DBSNMP identified by values 'E066D214D5421CCC';
alter user SCOTT identified by values 'F894844C34402B67';


and apply against the target (add your excludes like SYS/SYSTEM etc).
Re: synchronizing user password from another database [message #213202 is a reply to message #213200] Tue, 09 January 2007 16:04 Go to previous message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
Yes, thanks to both of you, I gave it a shot for just a single schema and it worked. Now, when I get around to it, I'll run it for all the users minus the important ones. I just do it for anything in DBA_USERS with a CREATED_ON greater than the creation date of the database (or some other date after that in which "regular" schemas were created).
Previous Topic: Env variables
Next Topic: Gathering Satistics - External
Goto Forum:
  


Current Time: Thu Dec 08 00:41:49 CST 2016

Total time taken to generate the page: 0.15766 seconds