Becoming a user - 10g issue
- From: "Newman, Christopher" <cjnewman@xxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 20 Aug 2008 11:56:27 -0500
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
Other related posts:
