I'd suggest using a proxy account instead. For example: SYSTEM@mydb> create user proxy identified by pr0xy; User created. SYSTEM@mydb> create user test identified by t3st; User created. SYSTEM@mydb> grant create session to test; Grant succeeded. SYSTEM@mydb> alter user test grant connect through proxy; User altered. SYSTEM@mydb> connect proxy[test]/pr0xy@mydb Connected. TEST@mydb> show user USER is "TEST" On Wed, Aug 20, 2008 at 11:56 AM, Newman, Christopher < cjnewman@xxxxxxxxxxxxx> wrote: > 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 > > -- > //www.freelists.org/webpage/oracle-l > > > -- "I'm too sexy for my code." - Awk Sed Fred.