Re: Becoming a user - 10g issue

I'd suggest using a proxy account instead. For example:

SYSTEM_at_mydb> create user proxy identified by pr0xy;

User created.

SYSTEM_at_mydb> create user test identified by t3st;

User created.

SYSTEM_at_mydb> grant create session to test;

Grant succeeded.

SYSTEM_at_mydb> alter user test grant connect through proxy;

User altered.

SYSTEM_at_mydb> connect proxy[test]/pr0xy_at_mydb Connected.
TEST_at_mydb> show user
USER is "TEST"

On Wed, Aug 20, 2008 at 11:56 AM, Newman, Christopher < cjnewman_at_uillinois.edu> 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
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
"I'm too sexy for my code." - Awk Sed Fred.

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