1. 生成建立用戶的腳本

set pagesize 0 set escape on select 'create user '		U.username		' identified '		DECODE(password, NULL, 'EXTERNALLY', ' by values '		''''		password		'''' )		chr(10)		'default tablespace '		default_tablespace		chr(10)		'temporary tablespace '		temporary_Tablespace		chr(10)		' profile '		profile		chr(10)		'quota '		decode ( Q.max_bytes, -1, 'UNLIMITED', NULL, 'UNLIMITED', Q.max_bytes)		' on '		default_tablespace		decode (account_status,'LOCKED', ' account lock', 'EXPIRED', ' password expire', 'EXPIRED & LOCKED', ' account lock password expire', null)

';'
from dba_users U, dba_ts_quotas Q
-- Comment this clause out to include system & default users
where U.username not in ('SYS','SYSTEM',
'SCOTT','DBSNMP','OUTLN','WKPROXY','WMSYS','ORDSYS','ORDPLUGINS','MDSYS',
'CTXSYS','XDB','ANONYMOUS','OWNER','WKSYS','ODM_MTR','ODM','OLAPSYS',
'HR','OE','PM','SH','QS_ADM','QS','QS_WS','QS_ES','QS_OS','QS_CBADM',
'QS_CB','QS_CS','PERFSTAT')
and U.username=Q.username(+) and U.default_tablespace=Q.tablespace_name(+)
;
set pagesize 100
set escape off

建網(wǎng)站原本是網(wǎng)站策劃師、網(wǎng)絡(luò)程序員、網(wǎng)頁(yè)設(shè)計(jì)師等，應(yīng)用各種網(wǎng)絡(luò)程序開(kāi)發(fā)技術(shù)和網(wǎng)頁(yè)設(shè)計(jì)技術(shù)配合操作的協(xié)同工作。創(chuàng)新互聯(lián)建站專業(yè)提供做網(wǎng)站、網(wǎng)站建設(shè),網(wǎng)頁(yè)設(shè)計(jì),網(wǎng)站制作(企業(yè)站、成都響應(yīng)式網(wǎng)站建設(shè)公司、電商門戶網(wǎng)站)等服務(wù),從網(wǎng)站深度策劃、搜索引擎友好度優(yōu)化到用戶體驗(yàn)的提升,我們力求做到極致!

2. 生成對(duì)象授權(quán)語(yǔ)句的腳本

set verify off
set feedback off
set termout off
set pagesize 500
set heading off
set recsep off

set termout on
select 'Creating object grant script by user...' from dual;
set termout off

create table g_temp (seq NUMBER, grantor_owner varchar2(20),
text VARCHAR2(800));

DECLARE
cursor grant_cursor is
SELECT ur$.name, uo$.name, o$.name, ue$.name,
m$.name, t$.sequence#,
decode(NVL(t$.option$,0), 1, ' WITH GRANT OPTION;',';')
FROM sys.objauth$ t$, sys.obj$ o$, sys.user$ ur$,
sys.table_privilege_map m$, sys.user$ ue$, sys.user$ uo$
WHERE o$.obj# = t$.obj# AND t$.privilege# = m$.privilege AND
t$.col# IS NULL AND t$.grantor# = ur$.user# AND
t$.grantee# = ue$.user# and
o$.owner#=uo$.user# and
t$.grantor# != 0
order by sequence#;
lv_grantor sys.user$.name%TYPE;
lv_owner sys.user$.name%TYPE;
lv_table_name sys.obj$.name%TYPE;
lv_grantee sys.user$.name%TYPE;
lv_privilege sys.table_privilege_map.name%TYPE;
lv_sequence sys.objauth$.sequence#%TYPE;
lv_option VARCHAR2(30);
lv_string VARCHAR2(800);
lv_first BOOLEAN;

procedure write_out(p_seq INTEGER, p_owner VARCHAR2, p_string VARCHAR2) is
begin
    insert into g_temp (seq, grantor_owner,text)
        values (lv_sequence, lv_grantor, lv_string);
end;

BEGIN
OPEN grant_cursor;
LOOP
FETCH grant_cursor INTO lv_grantor,lv_owner,lv_table_name,lv_grantee,
lv_privilege,lv_sequence,lv_option;
EXIT WHEN grant_cursor%NOTFOUND;
lv_string := 'GRANT ' || lv_privilege || ' ON ' || lower(lv_owner) ||
'.' ||
lower(lv_table_name) || ' TO ' || lower(lv_grantee) ||
lv_option;
write_out(lv_sequence, lv_grantor,lv_string);
END LOOP;
CLOSE grant_cursor;
END;
/

spool tfscsopv.lst
break on guser skip 1
col text format a60 word_wrap

select 'connect ' || grantor_owner || '/' guser, text
from g_temp
order by seq, grantor_owner
/

spool off

drop table g_temp;

3. 生成系統(tǒng)授權(quán)語(yǔ)句的腳本

set verify off
set feedback off
set termout off
set pagesize 0

set termout on
select 'Creating system privilege grant script...' from dual;
set termout off

spool tfscsspv.sql

select 'GRANT ' || rpad(lower(privilege),30) || ' TO ' || lower(grantee) || decode(admin_option,'YES',' WITH ADMIN OPTION;',';')
from sys.dba_sys_privs
where grantee not in ('CONNECT','RESOURCE','DBA','EXP_FULL_DATABASE','IMP_FULL_DATABASE')
order by grantee
/

spool off

4. 生成授予角色的腳本

set verify off
set feedback off
set termout off
set pagesize 0

set termout on
select 'Creating role build script...' from dual;
set termout off

spool tfscsrol.sql

select 'CREATE ROLE ' || lower(role) || ' NOT IDENTIFIED;'
from sys.dba_roles
where role not in ('CONNECT','RESOURCE','DBA', 'EXP_FULL_DATABASE','IMP_FULL_DATABASE')
and password_required='NO'
/
select 'CREATE ROLE ' || lower(role) || ' IDENTIFIED BY VALUES ' ||
'''' || password || '''' || ';'
from sys.dba_roles, sys.user$
where role not in ('CONNECT','RESOURCE','DBA', 'EXP_FULL_DATABASE','IMP_FULL_DATABASE')
and password_required='YES' and dba_roles.role=user$.name
and user$.type=0
/

select 'GRANT ' || lower(granted_role) || ' TO ' || lower(grantee) ||
' WITH ADMIN OPTION;'
from sys.dba_role_privs
where admin_option='YES'
and granted_role not in ('CONNECT','RESOURCE','DBA', 'EXP_FULL_DATABASE','IMP_FULL_DATABASE')
order by grantee
/
spool off

本文標(biāo)題：Oracle生成用戶及權(quán)限復(fù)制
鏈接URL：http://biofuelwatch.net/article/gpopsi.html