使用SQL批量查詢出建立使用者及許可權指令碼

 select 'create user ' || a.username || '  identified by "' ||
       (select password from sys.user$ b where a.username = b.name) || '" ' ||
       chr(10) || ' default tablespace  ' || a.default_tablespace ||
       chr(10) || ' temporary tablespace ' || a.temporary_tablespace ||
       chr(10) || ' profile ' || a.profile || ';'
  from dba_users a  
 union all
 select 'grant unlimited tablespace to  ' || a.username || ';'
  from dba_users a   ; 

 

 

select ' grant ' || t.privilege || ' on ' || owner || '.' || table_name ||
        ' to ' || grantee || case
          when grantable = 'YES' then
           ' with grant option;'
          else
           ';'
        end
   from dba_tab_privs t
  where grantee = 'DMCARR';

 

SELECT 'grant ' || granted_role || ' to ' || grantee || case
             when admin_option = 'YES' THEN
              'with admin option;'
             ELSE
              ';'
           end,
           default_role
      FROM DBA_ROLE_PRIVS
     WHERE GRANTEE = 'DMCARR'
     order by default_role;

  select 'grant ' || privilege || ' to ' || grantee || case
           when admin_option = 'YES' THEN
            'with admin option;'
           ELSE
            ';'
         end
    from dba_sys_privs
   where grantee = 'DMCARR';