same users across multiple databases

[bookouri]

Ive got a database with about 400 users. We just put a new database on line and the users are going to be the same 400 as on the old database. Is there any way to have a single logon to get to both databases, or if that's not possible, can I select the existing users and import them into the new database with their username and passwords from the old database?

any suggestions that would keep me from having to create 400 users manually would be appreciated...

 

[Wonboodoo]

SQLPlus script to generate a SQL script to create your users:

set echo off
set heading off
set pagesize 0
set feedback off
spool create_users.sql
select 'create user ' || username || ' identified by values '''
|| password || ''';'
from dba_users
where username not in ('SYS', 'SYSTEM');
spool off

 

[bookouri]

thanks, I noticed in my database there are a lot of other odd users as well as sys and system, are there any other users I need to worry about not including...

 

[Wonboodoo]

Not that I'm aware of. SYS & SYSTEM are the only ones that are installed with the database (unless Oracle have added others for 9i). User SCOTT/TIGER can sometimes be there too (but shouldn't be in a production database).

You'll also need to bring the grants across which you can do with running similar SQL against the DBA_TAB_PRIVS, DBA_SYS_PRIVS, & DBA_ROLE_PRIVS.

 

[Swany]

Context            => CTXSYS
Enterprise Man     => DBSNMP
JAVA               => ORDSYS, ORDPLUGINS
Spatial            => MDSYS
UltraSearch        => WKSYS
Stored Outlines    => OUTLN
StatsPack          => PERFSTAT

You may have the following users associated with web services
AURORA$JIS$UTILITY$
OSE$HTTP$ADMIN
AURORA$ORB$UNAUTHENTICATED

.
.. Eat, think and be merry .
... ....................... .

 

[carp]

If you create a database link from your new database to your old database, you can automatically screen out duplicate users:

select 'create user ' || username || ' identified by values '''|| password || ''';'
from dba_users@old_database
where username not in (SELECT username FROM dba_users);

You may also need to create scripts to grant roles and system/object privileges to your users.

Finally, the script to create users may not work the way you want it to since the passwords in dba_users are actually encrypted character strings; consequently, you may be creating very unpopular (32 character!) but very secure passwords. Instead, you might think about using a generic password or the user name for the password. However, either of THESE approaches creates a pretty bad security risk!