RE: Transfer users
- From: Feighery Raymond <Raymond.Feighery@xxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 14 May 2004 11:11:46 +0100
I have a script which can do this. It was written for 8.1.7, but I think it
will work on 8.0.6 and 9i.
It creates three spool files. One to create the users and grant system
privileges, one to grant object privileges and one to reset the passwords.
It's pretty simple: it creates a temporary table with the users you wish to
recreate (see the insert into temp_appl_users). Test before using.
Email me direct if you need advice.
Ray
------------------------------------------------------
/*
############################################################################
####
# File: gen_cr_users.sql
# Function: Generate script to recreate users, passwords and grants
# for users in temp_appl_users
# Created: 10/05/2001
# Created by: R Feighery
# Parameters: 1 LOG_DIR
# 2 GEN_SQL_DIR
#
# History
# Modified by When Reason
# ----------- -------
----------------------------------------------------
############################################################################
####
*/
define LOG_DIR = .
define GEN_SQL_DIR = .
prompt LOG_DIR=&LOG_DIR
prompt GEN_SQL_DIR=&GEN_SQL_DIR
spool &GEN_SQL_DIR/run_cr_users.sql
prompt spool &LOG_DIR/run_cr_users.lst
set feedback off termout off pages 0 verify off
REM Build temporary table to contain usernames
REM Easier to restrict selection once here
create table temp_appl_users
(username varchar2(30))
storage ( initial 16K
next 16K)
/
insert into temp_appl_users
(select username
from dba_users
where username not in
('SYS','PUBLIC','CTXSYS','DBSNMP','MDSYS','ORDPLUGINS','ORDSYS','OUTLN','SYS
TEM'))
/
Rem
Rem Re-create Users
Rem
select
'create USER ' || username ||
' identified by ' || username || ' ' || chr(10) ||
' default tablespace ' || default_tablespace ||
' temporary tablespace '|| temporary_tablespace || chr(10) ||
'/'
from
sys.dba_users t1
where
exists
(select 'X'
from temp_appl_users t2
where t2.username = t1.username)
order by
t1.username
/
Rem
Rem Create Tablespace Quotas
Rem
select
'alter USER ' || username || ' quota ' ||
decode(max_bytes,-1,'unlimited',to_char(max_bytes/1024) ||' K') ||
' on '|| tablespace_name ||';'
from
sys.dba_ts_quotas t1
where
exists
(select 'X'
from temp_appl_users t2
where t2.username = t1.username)
/
Rem
Rem Grant System Privileges
Rem
select
'grant ' || S.name || ' to ' || U.username || ';'
from
system_privilege_map S,
sys.sysauth$ P,
sys.dba_users U
where
exists
(select 'X'
from temp_appl_users t2
where t2.username = U.username)
and
U.user_id = P.grantee#
and
P.privilege# = S.privilege
and
P.privilege# < 0
/
Rem
Rem Grant Roles
Rem
set echo off
set serveroutput on
variable uname varchar2(30);
declare
cursor c1 is
select
grantee,
granted_role,
decode(admin_option,'YES','with admin option;','/') adm
from
dba_role_privs
where grantee = :uname;
cursor c2 is
select
username
from
temp_appl_users;
begin
dbms_output.enable(100000);
for q2 in c2 loop
:uname:=q2.username;
for q1 in c1 loop
dbms_output.put_line('grant ' || q1.granted_role);
dbms_output.put_line('to '|| q1.grantee);
dbms_output.put_line(q1.adm);
end loop;
end loop;
end;
/
prompt spool off
prompt exit
spool off
spool &GEN_SQL_DIR/cr_obj_grants.sql
prompt spool &LOG_DIR/cr_obj_grants.lst
Rem
Rem Grant Object Privileges
Rem
set echo off
set serveroutput on
variable uname varchar2(30);
declare
cursor c1 is
select
grantee,
owner,
table_name,
grantor,
privilege,
decode(grantable,'YES','with grant option;','/') gr_opt
from
dba_tab_privs dtp
where grantor = :uname
and exists
(select 'x'
from temp_appl_users tau
where dtp.grantee = tau.username)
order by
owner;
cursor c2 is
select
distinct grantor
from
dba_tab_privs dtp
where exists
(select 'x'
from temp_appl_users tau
where dtp.grantee = tau.username);
begin
dbms_output.enable(1000000);
for q2 in c2 loop
:uname:=q2.grantor;
dbms_output.put_line('connect '||q2.grantor||'/'||q2.grantor);
for q1 in c1 loop
dbms_output.put_line('grant ' || q1.privilege);
dbms_output.put_line('on '|| q1.owner ||'.'||q1.table_name);
dbms_output.put_line('to '|| q1.grantee);
dbms_output.put_line(q1.gr_opt);
end loop;
end loop;
end;
/
prompt spool off
prompt exit
set feedback on termout on pages 40 verify on
spool off
set termout off heading off pages 0 feedback off
spool &GEN_SQL_DIR/reset_passwords.sql
prompt spool &LOG_DIR/reset_passwords.lst
select
'alter user '||username||
decode (password,'EXTERNAL',' identified externally;',' identified
by values ''' ||PASSWORD|| ''';')
from
sys.dba_users du
where exists
(select 'x'
from temp_appl_users tau
where tau.username = du.username)
order by
username
/
prompt spool off
spool off
drop table temp_appl_users;
set feedback on termout on pages 40 verify on
exit
--------------------------------------------------------
-----Original Message-----
From: Eddor [mailto:Edouard.Dormidontov@xxxxxxxxx]
Sent: Friday, May 14, 2004 10:42 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Transfer users
Hi
How to transfer users (login, password) from one Database - Oracle 8.0.6 to
Oracle9i.
Thanks
ED
___________________________________________________________________________
This email and any attached to it are confidential and intended only for the
individual or entity to which it is addressed. If you are not the intended
recipient, please let us know by telephoning or emailing the sender. You
should also delete the email and any attachment from your systems and should
not copy the email or any attachment or disclose their content to any other
person or entity. The views expressed here are not necessarily those of
Churchill Insurance Group plc or its affiliates or subsidiaries. Thank you.
Churchill Insurance Group plc. Company Registration Number - 2280426.
England.
Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1
1DP.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
