| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Transfer users
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
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;
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;
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;',' identifiedby values ''' ||PASSWORD|| ''';')
sys.dba_users du
where exists
(select 'x' from temp_appl_users tau where tau.username = du.username) order by username
drop table temp_appl_users;
set feedback on termout on pages 40 verify on
exit
-----Original Message-----
From: Eddor [mailto:Edouard.Dormidontov_at_zepter.ru]
Sent: Friday, May 14, 2004 10:42 AM
To: oracle-l_at_freelists.org
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.
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Fri May 14 2004 - 05:20:06 CDT
 |
 |