changing passwords and back again(2)

[Arno Bouwman]

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. ar...@the-bouwmans.demon.nl

[Thomas Kyte]

In article <u0kvaga...@corp.supernews.com>, "Arno says...

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 (tk...@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

[Mark D Powell]

"Arno Bouwman" <ar...@the-bouwmans.demon.nl> wrote in message news:<u0kvaga...@corp.supernews.com>...

Does this ring a bell?
identified by values 'xxx'

-- Mark D Powell --