Oracle用户及权限脚本生成语句

本来用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'