Re: Becoming a user - 10g issue
- From: "Greg Norris" <spikey.mcmarbles@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 20 Aug 2008 14:02:43 -0500
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.
Other related posts:
