Re: DBA? - copying users to another instance - DBA?

David Ng wrote:
>
> komix Hui wrote:
> >
> > Robert Cordingley wrote:
> >
> > >
> > > You can do CREATE USER username IDENTIFIED BY VALUES '<encoded password
> > > string here>';
> > >
> >
> > I cannot find such syntax of 'create user' statement as above in 'SQL
> > Reference' from Oracle.
> > Could you give some indication which hardware/software platform you are
> > using?
> >
> > Thank you.
> I believe he means 'GRANT CONNECT to USER UserName identfied by
> Password'.
>
> David Ng

No, he does mean CREATE USER. You should be using CREATE USER in V7, not grant connect. CREATE USER allows you to specify the user's default and temporary tablespaces. If you use GRANT CONNECT, they default to the SYSTEM tablespace.

The IDENTIFIED BY VALUES clause is an undocumented feature. It allows you to set a user's password without the value being encrypted. So, if you get the user's current encrypted password from DBA_USERS, you can then set the user's account in another database to that same password, even though you can't tell what the password is. You can do the same thing from one user to another.

This example shows how you could set one user's password to another user's password.

column password new_value password noprint select password from sys.dba_users where username = 'USERA'; alter user userb identified by values '&password';

You could also use this to become a user, without knowing or changing their password. You need to alter user privilege to do this.

We call this script goto.sql

set termout off
set echo off

col password new_value oldpswd noprint

select password from sys.dba_users
 where username = upper('&&1');

alter user &&1 identified by temppw;

connect &&1/temppw

alter user &&1 identified by values '&&oldpswd';

column password clear
set termout on
show user

The

-- 
Bob Swisshelm
Eli Lilly and Company
swisshelm_at_lilly.com