Becoming a user - 10g issue

We thought we were pretty good with 'su''ing to another account within the DB using the following script:

spool temp.sql
select 'alter user &1 identified by values
'||chr(39)||password||chr(39)||';'

from dba_users
where username = UPPER('&1')
/
spool off
alter user &1 identified by temp;
connect &1/temp
@temp.sql

This changes the current password to 'temp', allows us to login, then changes it back to what it was originally. I recently ran into an issue however, with strong password verification set to 'on', the script modified the password, but didn't set it back. Does this make sense?

SQL > @su TESTUSER
alter user TESTUSER identified by values 'SOMEHASH'; <-- actual PW here is 'temp'
User altered.
Connected.
alter user TESTUSER identified by values 'SOMEHASH' <-- here is the original PW hash for the user

ERROR at line 1:
ORA-28221: REPLACE not specified

What I don't understand, is how it was allowed to change the password to 'temp', but *not* change it back?

We're really looking for a better solution, and ALTER SESSION SET CURRENT_SCHEMA=<SCHEMA_NAME>; doesn't seem ideal, as it appears to be really just for eliminating the need to fully qualify object names.

The initial script runs as 'sys', after reading some docs, I'm guessing that 'TESTUSER' does not have alter user privs. Solution?

select 'alter user &1 identified by values
'||chr(39)||password||chr(39)||' replace temp;'
from dba_users
where username = UPPER('&1')
/

Metalink note: 194726.1 Cause: User is changing password but password verification function is turned on and the original password is not specified and the user does not have the alter user system privilege. Action: Supply the original password. We don't want to give 'alter user', so supplying the 'original' password sounds like it should work. Is the problem with:

select 'alter user &1 identified by values
'||chr(39)||password||chr(39)||' replace temp;' ?

Chris Newman
Database Specialist
AITS, University of Illinois
217-333-5429

--
http://www.freelists.org/webpage/oracle-l