Re: Mistakenly changes db users passwords
- From: Robert Freeman <robertgfreeman@xxxxxxxxx>
- To: Thomas Roach <troach@xxxxxxxxx>, WLJohnson@xxxxxxxxxxxxxxxxxxx
- Date: Tue, 8 Dec 2009 17:59:31 -0800 (PST)
A good point... If the undo is there..... also, depending on the version,
dba_users may not work. Hashes were removed in DBA_USERS (10g or 11g can't
recall) so you have to use user$.
RF
Robert G. Freeman
Oracle ACE
Ask me about on-site Oracle Training! RMAN, DBA, Tuning, you name it!
Author:
Oracle Database 11g RMAN Backup and Recovery (Oracle Press) - ON ITS WAY SOON!
OCP: Oracle Database 11g Administrator Certified Professional Study Guide
(Sybex)
Oracle Database 11g New Features (Oracle Press)
Oracle Database 10g New Features (Oracle Press)
Other various titles
Blog: http://robertgfreeman.blogspot.com
Check out my new blog series on installing Oracle Database 11gR2 on Windows
using VMWare!
________________________________
From: Thomas Roach <troach@xxxxxxxxx>
To: WLJohnson@xxxxxxxxxxxxxxxxxxx
Cc: "robertgfreeman@xxxxxxxxx" <robertgfreeman@xxxxxxxxx>; "stant_98@xxxxxxxxx"
<stant_98@xxxxxxxxx>; "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
Sent: Tue, December 8, 2009 6:41:41 PM
Subject: Re: Mistakenly changes db users passwords
I agree with this approach if the undo is there to perform the flashback query.
Do it fast.
If you can do that, you can recover. All you need is the usernames and the
password hash and then you can do "alter user username identified by values
'HASH_VALUE'"
Good luck!
On Tue, Dec 8, 2009 at 8:24 PM, Johnson, William L (TEIS)
<WLJohnson@xxxxxxxxxxxxxxxxxxx> wrote:
Why wouldn’t you just simply run a flashback query against dba_users to gather
their password values at a point-in-time prior to your oops? You could create
a temp table with the data and then do the updates from there…
>create table my_dba_users tablespace mytablespace as select * from
>>dba_users as of timestamp to_timestamp('2007-07-30 06:30:00', 'YYYY-MM-DD
>>HH24:MI:SS'); ß fill in your own time here…
>
>
>
________________________________
>From:oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
>Behalf Of Robert Freeman
>Sent: Tuesday, December 08, 2009 8:17 PM
>To: stant_98@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
>Subject: Re: Mistakenly changes db users passwords
>
>You did this in production? Holy cow...
>
>How many users did you manage to change? If it's just a few, I'd just call
>them and tell them that they have new passwords. If you are talking about
>large scale then you have big problems... sure you can do a point-in-time
>recovery but what are the impacts of that? You COULD recover the database to a
>point-in-time prior to the change, get all the password hashes and then use
>the alter user command with the using values command. You can get the hashes
>from user$. So you would do something like this:
>
>After the restore to ANOTHER database (you would only need the SYSTEM, SYSAUX,
>and UNDO tablespaces restored and then create a temporary tablespace.
>
>On the temporary restored database log in as sysdba.
>
>>Issue this query:
>
>select name, password from user$ where name='SCOTT';
>
>name password
>-------------- ----------------
>SCOTT F09492C0593049B99
>
>then in the messed up database simply change the password using the hash:
>
>
>alter user scott identified by values ' F09492C0593049B99';
>
>This should reset the password to the correct password. You could easily write
>a script to to massive changes if need be.
>
>Then, I'd be updating my resume, depending on how forgiving your boss is. If
>it were me, you'd be cleaning bathrooms for at least a week. With a
>toothbrush..... :) But, I'm just a nice guy. :-)
>
>RF
>
>Robert G. Freeman
>Oracle ACE
>Ask me about on-site Oracle Training! RMAN, DBA, Tuning, you name it!
>Author:
>Oracle Database 11g RMAN Backup and Recovery (Oracle Press) - ON ITS WAY SOON!
>>OCP: Oracle Database 11g Administrator Certified Professional Study Guide
>>(Sybex)
>Oracle Database 11g New Features (Oracle Press)
>Oracle Database 10g New Features (Oracle Press)
>Other various titles
>Blog: http://robertgfreeman.blogspot.com/
>>Check out my new blog series on installing Oracle Database 11gR2 on Windows
>>using VMWare!
>
>
>
________________________________
>From:Viktor <stant_98@xxxxxxxxx>
>To: oracle-l@xxxxxxxxxxxxx
>Sent: Tue, December 8, 2009 5:56:29 PM
>Subject: Mistakenly changes db users passwords
>Hi gurus,
>
>10.2.0.4 Linux Redhat:
>
>I mistakely ran a script and changed db passwords for all users. Is the only
>option here RMAN restore/recovery? Would point-in-time work, won't it?
>
>The database has not been set as a flashback db.
>
>Thanks much in advance!
>
--
Thomas Roach
813-404-6066
troach@xxxxxxxxx
Other related posts:
