| 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
建网站原本是网站策划师、网络程序员、网页设计师等，应用各种网络程序开发技术和网页设计技术配合操作的协同工作。创新互联建站专业提供做网站、网站建设,网页设计,网站制作(企业站、成都响应式网站建设公司、电商门户网站)等服务,从网站深度策划、搜索引擎友好度优化到用户体验的提升,我们力求做到极致!
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;
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
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
