Import Schema and Creation of User Accounts 2

[Michael42]

Hello,

On an Oracle 9i database on Solaris 8 I eported two Schema's. Before importing into my new database I know I need to pre-create the users (about 50).

Can anyone offer a script or process to easily create users on my new system prior to performing an import?

Thanks,

Michael42

 

[LKBrwnDBA]

A simple script if you have still old database:

----------------------------------------------------------------
--  db_migrate_users.sql
--
--  Create DDL to migrate users 
--
------------------------------------------------------------------
col db new_value sid
select name db from v$database;
set pages 0 lin 80 feed off ver off recsep off
set term off
col sq noprint
spo /tmp/&sid._migrate_users.sql
Select username||to_char(100+rownum) sq,
'Create User '||u.username||' identified by values '
||chr(39)||password||chr(39)||chr(10)
||' Default tablespace '||default_tablespace
||' Temporary tablespace '||temporary_tablespace||';'||chr(10)
  from dba_users u 
 where lock_date is null
   and username not in ('SYS','SYSTEM')
   and username not like 'OPS$%'
UNION
Select username||to_char(200+rownum) sq, 'Alter User '||username
||' quota '||decode(max_bytes,-1,'UNLIMITED',max_bytes/1024||'K')
||' ON '||tablespace_name||';'||chr(10)
from dba_ts_quotas q
order by 1
/
spo off
exit

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

...and in addition to LK's, here is my "UserSetup.sql" script that prompts for User Name, Default Tablespace, and Temporary Tablespace. You can "add in" or "take out" whatever setup features you wish:

Section 1 -- Contents of "UserSetup.sql" script:

set verify off
accept un prompt "Enter Username: "
accept ts prompt "Enter Default Tablespace name: "
accept tmp prompt "Enter Temporary Tablespace name: "
create user &un identified by &un
default tablespace &ts
temporary tablespace &tmp
quota unlimited on &ts
/
grant connect, resource, select any table to &un
/
grant plustrace to &un
/
revoke unlimited tablespace from &un

Section 2 -- Sample invocation of "UserSetup.sql":

SQL> @UserSetup
Enter Username: Michael
Enter Default Tablespace name: data1
Enter Temporary Tablespace name: temp

User created.

Grant succeeded.

Grant succeeded.

Revoke succeeded.

You can even set up all 50 of your user names in a batch file to run with contents that match the following format:

@usersetup
michael
data1
temp
@useretup
user2
data2
temp
...et cetera

Let us know if any of this is useful to you.


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Michael42]

Guys,

Thanks for posting. Those scripts are really useful!

To complete this picture, can you please share a script or process to easily re-create Roles?

Thanks,

Michael42

 

[LKBrwnDBA]

Or, another script to migrate (or clone) a user including all grants:

----------------------------------------------------------------
--
--  db_migrate_1_user.sql
--
--  Create DDL to migrate/clone one user with grants
--
------------------------------------------------------------------
cl col
undef to_usr
undef to_user
undef fr_usr
undef from_user
col db new_value sid noprint
accept fr_usr prompt "From user: "
accept to_usr prompt "  To user: "
col fr_usr    new_value fr_usr noprint
col to_usr    new_value to_usr noprint
col cr_type   new_value cr_typ noprint
select UPPER(NVL('&&fr_usr','SCOTT'))    fr_usr 
     , UPPER(NVL('&&to_usr','&&fr_usr')) to_usr 
     , name db from v$database;
select decode('&&to_usr','&&fr_usr'
     ,'Migrate_&&fr_usr','Create_&&to_usr') cr_type from dual;
set pages 0 lin 80 feed off ver off recsep off trims on
set term on
col sq noprint
spo /tmp/&&sid._&&cr_typ..sql
Select username||to_char(100+rownum) sq,
 'Create User '||decode('&&to_usr','&&fr_usr'
,u.username||' identified by values '||chr(39)||password||chr(39)
,'&&to_usr identified by &&to_usr'||to_char(sysdate,'DD'))||chr(10)
||'    Default tablespace '||default_tablespace||chr(10)
||'    Temporary tablespace '||temporary_tablespace||';'||chr(10)
  from dba_users u 
 where username = '&&fr_usr'
UNION
Select username||to_char(200+rownum) sq, 'Alter User &&to_usr'
||' quota '||decode(max_bytes,-1,'UNLIMITED',max_bytes/1024||'K')
||' ON '||tablespace_name||';'||chr(10)
  from dba_ts_quotas q
 where username = '&&fr_usr'
order by 1
/
----------------------------------------------------------------
--  DDL to migrate granted privileges to a user
------------------------------------------------------------------
col sq noprint
select 'Grant '||granted_role||' to &&to_usr'
||decode(admin_option,'YES',' With ADMIN OPTION;',';')||chr(10)
  from dba_role_privs r
 where r.grantee = '&&fr_usr'
   and r.grantee not in ('SYS','SYSTEM')
 order by 1
/
select 'Grant '||privilege||' to &&to_usr'
||decode(admin_option,'YES',' With ADMIN OPTION;',';')||chr(10)
  from dba_sys_privs s
 where s.grantee = '&&fr_usr'
   and s.grantee not in ('SYS','SYSTEM')
 order by 1
/
select x.rn*10000+p.rn sq,
decode(p.rn,1,'conn / '||chr(10)||'@$ORASQL/db_su '||x.schema||chr(10),NULL)
||'Grant '||privilege||' on '||grantor||'.'||table_name
||' to '||grantee||';'||chr(10) sql
from (
select username schema, row_number() over(order by username) rn
 from ( select username from dba_users u
where lock_date is null
  and u.username not in ('SYS','SYSTEM')
UNION select role from dba_roles )
) x
,(
  select grantor, '&&to_usr' grantee, privilege, table_name
       , row_number() over(partition by grantor order by table_name) rn
    from dba_tab_privs 
   where grantee = '&&fr_usr' ) p
where p.grantor=x.schema
order by p.grantor, p.table_name
/ 

spo off
exit

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

Oh, it's easy to re-create the roles:

spo /tmp/migrate_roles.sql
Select 'Create Role '||Role||';' From Dba_Roles;
spo off

Note: If role exist, ignore the "Already Exist" error.

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Michael42]

Perfect!

Thanks again,

Michael