本來用plsql developer可以用"檢視SQL"功能直接擷取,但是自己有興趣,是以就查了查,整理的結果如下
1.使用者腳本
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=Q.username(+) and U.default_tablespace=Q.tablespace_name(+)
and U.username='scott';
2.系統權限
select 'GRANT ' || rpad(lower(privilege),30) || ' TO ' || lower(grantee) || decode(admin_option,'YES',' WITH ADMIN OPTION;',';')
from sys.dba_sys_privs
where grantee = 'scott'
3.角色權限
select 'GRANT ' || lower(granted_role) || ' TO ' || lower(grantee) ||
' WITH ADMIN OPTION;'
from sys.dba_role_privs
where grantee='scott'