Re: changing passwords and back again(2)

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 2 Dec 2001 13:52:43 -0800
Message-ID: <9ue7rb02jfu@drn.newsguy.com>

In article <u0kvagacrjpjc7_at_corp.supernews.com>, "Arno says...
>
>Hello,
>
>I would like to change certain user's passwords to prevent them from
>connecting during an euro-conversion. And change them back to their original
>passwords afterwards.
>
>I've done this in the past something like this:
>
>spool reset.sql
>select 'alter user '||username||' identified by '||password||';' from
>dba_users where user in ('USER1','USER2');
>spool off
>than change the password so they can not connect any more
>do the job
>start reset.sql
>
>I can't get it to work any more ..... anybody knows how to???????????
>
>Arno. arno_at_the-bouwmans.demon.nl
>
>
>
>

Well, the best solution is to startup the database in restricted mode and allow only the special users that have the restricted session priv the ability to connect.

Short of that you can:

create table temp as select * from dba_users;

begin
  for x in ( select * from dba_users )
   loop

      execute immediate 'alter user ' || username || ' identified by hello';    end loop;
end;

now everones password is HELLO

then later issue:

begin
  for x in ( select * from temp )
   loop

      execute immediate 'alter user ' || username || 
           ' identified by values ''' || x.password || '''';

   end loop;
end;

If you have 8.0 or before, you'll have to write your own execute_immediate procedure using DBMS_SQL.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Sun Dec 02 2001 - 15:52:43 CST