--
-- mov_user.sql
--
-- Exporta usuários roles e privilegios.
--
--
-- Criado por Fábio Telles Rodriguez
--
-- Última atualização: 05/05/2010
--
--
 
-- Ajusta parâmetros de visualização do SQL*Plus
SET DEFINE OFF
SET PAGESIZE 0
SET WRAP      ON
SET TRIMSPOOL ON
SET SERVEROUTPUT ON
SET VERIFY OFF
SET FEED OFF
SET HEADING OFF
SET LINESIZE  120
 
-- Gera arquivo em mov_user.sql
SPOOL mov_user_.sql
 
-- Verifica se está conectado com o usuário SYS
WHENEVER SQLERROR EXIT;
 
DECLARE
  v_user varchar(100);
BEGIN
  SELECT user INTO v_user FROM dual WHERE user = 'SYS';
EXCEPTION
  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20000, 
        'ERRO! Você deve estar conectado como SYS para rodar este script!!!');
END;
/
 
 
--Cria usuários
SELECT
    'CREATE USER ' || LOWER(username) || 
    DECODE (password, 
            'EXTERNAL',' IDENTIFIED EXTERNALLY',
            'GLOBAL',' IDENTIFIED GLOBALLY AS ''' || external_name,
            ' IDENTIFIED BY VALUES ''' || password || '''') || CHR(10) ||
    '    DEFAULT TABLESPACE ' || default_tablespace || CHR(10) ||
    '    TEMPORARY TABLESPACE ' || temporary_tablespace || CHR(10) ||
    '    PROFILE ' || profile || CHR(10) ||
    DECODE (account_status,
        'OPEN',                           '    ACCOUNT UNLOCK;',
        'LOCKED',                         '    ACCOUNT LOCK;',
        'LOCKED(TIMED)',                  '    ACCOUNT LOCK;',
        'EXPIRED & LOCKED',               '    ACCOUNT LOCK PASSWORD EXPIRE;',
        'EXPIRED & LOCKED(TIMED)',        '    ACCOUNT LOCK PASSWORD EXPIRE;',
        'EXPIRED(GRACE) & LOCKED',        '    ACCOUNT LOCK PASSWORD EXPIRE;',
        'EXPIRED(GRACE) & LOCKED(TIMED)', '    ACCOUNT LOCK PASSWORD EXPIRE;',
        'EXPIRED',                        '    ACCOUNT UNLOCK PASSWORD EXPIRE;',
        'EXPIRED(GRACE)',                 '    ACCOUNT UNLOCK PASSWORD EXPIRE;') mov_user
  FROM dba_users
  WHERE
    default_tablespace NOT IN ('EXAMPLE','DRSYS','CWMLITE','ODM','XDB','SYSTEM','SYSAUX')
  ORDER BY default_tablespace, username
;
 
-- Concede quotas para usuários
SELECT 
    'ALTER USER ' || LOWER(username) || 
    ' QUOTA UNLIMITED ON ' || LOWER(tablespace_name) || ';' 
  FROM dba_ts_quotas;
 
-- Especifica planos de recursos para usuários
SELECT 
    'EXEC DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING' || CHR(10) ||
    '(DBMS_RESOURCE_MANAGER.ORACLE_USER, ''' || LOWER(username) || 
    ''', ''' || LOWER (INITIAL_RSRC_CONSUMER_GROUP) || ''');'
  FROM dba_users
  WHERE
    default_tablespace NOT IN ('EXAMPLE','DRSYS','CWMLITE','ODM','XDB','SYSTEM','SYSAUX')
  ORDER BY default_tablespace, username
;
 
-- Cria ROLEs
SELECT 
    'CREATE ROLE ' || LOWER(name) || ' ' ||
    DECODE (password, 
        NULL,'NOT IDENTIFIED;',
        'EXTERNAL',' IDENTIFIED EXTERNALLY;',
        'GLOBAL',' IDENTIFIED GLOBALLY',
        ' IDENTIFIED BY VALUES ''' || password || ''';')
  FROM sys.user$
  WHERE 
    type# = 0 AND 
    name NOT IN ('PUBLIC', '_NEXT_USER', 'AQ_ADMINISTRATOR_ROLE', 'AQ_USER_ROLE',
      'AUTHENTICATEDUSER', 'CONNECT', 'CTXAPP', 'DBA', 'DELETE_CATALOG_ROLE', 'EJBCLIENT',
      'EXECUTE_CATALOG_ROLE', 'EXP_FULL_DATABASE', 'GATHER_SYSTEM_STATISTICS',
      'GLOBAL_AQ_USER_ROLE', 'HS_ADMIN_ROLE', 'IMP_FULL_DATABASE', 'JAVADEBUGPRIV',
      'JAVAIDPRIV', 'JAVASYSPRIV', 'JAVAUSERPRIV', 'JAVA_ADMIN', 'JAVA_DEPLOY',
      'LOGSTDBY_ADMINISTRATOR', 'MGMT_USER', 'OEM_ADVISOR', 'OEM_MONITOR',
      'PLUSTRACE', 'RECOVERY_CATALOG_OWNER', 'RESOURCE',  'SCHEDULER_ADMIN',
      'SELECT_CATALOG_ROLE', 'SNMPAGENT','WM_ADMIN_ROLE', 'XDBADMIN', 'XDBWEBSERVICES')
;
 
-- Gera GRANTs em objetos
SELECT 
    'GRANT ' || LOWER(privilege) ||
    ' ON ' || LOWER(owner) || '.' || LOWER(table_name) ||
    ' TO ' || LOWER(grantee) || 
    DECODE(grantable,'YES','WITH GRANT OPTION',' ') ||
    DECODE(hierarchy,'YES', 'WITH HIERARCHY OPTION;',';')
  FROM dba_tab_privs p, dba_users u
  WHERE
    u.username = p.grantee AND
    u.default_tablespace NOT IN ('EXAMPLE','DRSYS','CWMLITE','ODM','XDB','SYSTEM','SYSAUX')
;
 
-- Gera GRANTs em colunas
SELECT 
    'GRANT ' || LOWER(privilege) || '(' || LOWER(column_name) || ')' ||
    ' ON ' || LOWER(owner) || '.' || LOWER(table_name) || 
    ' TO ' || LOWER(grantee) || 
    DECODE(grantable,'YES','WITH GRANT OPTION;',';')
  FROM dba_col_privs;
 
-- Gera GRANTs em roles
SELECT 
    'GRANT ' || LOWER(granted_role) || ' TO ' || LOWER(grantee) || 
        DECODE(admin_option,'YES','WITH ADMIN OPTION;',';')
  FROM dba_role_privs p, dba_users u
  WHERE
    u.username = p.grantee AND
    u.default_tablespace NOT IN ('EXAMPLE','DRSYS','CWMLITE','ODM','XDB','SYSTEM','SYSAUX')
;
 
-- Altera roles que não são padrão
DECLARE
  v_non_default   NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_non_default 
        FROM dba_role_privs WHERE default_role = 'NO';
  IF v_non_default > 0 THEN
    FOR u IN (SELECT DISTINCT grantee 
                FROM dba_role_privs 
        WHERE default_role = 'NO' 
        ORDER BY grantee) 
    LOOP
      DBMS_OUTPUT.PUT('ALTER USER ' || LOWER(u.grantee) || ' DEFAULT ROLE ALL EXCEPT ');
      SELECT count(*) INTO v_non_default 
        FROM dba_role_privs 
        WHERE grantee = u.grantee AND default_role = 'NO';
      FOR r IN (SELECT rownum, granted_role 
                  FROM dba_role_privs 
                  WHERE grantee = u.grantee AND default_role = 'NO') 
      LOOP
        DBMS_OUTPUT.PUT(LOWER(r.granted_role));
        IF r.rownum != v_non_default THEN 
          DBMS_OUTPUT.PUT(',');
        END IF;
      END LOOP;
      DBMS_OUTPUT.PUT_LINE(';');
    END LOOP;
  END IF;
END;
/
 
-- Gera GRANTs em privilégios de sistema
SELECT 
    'GRANT ' || LOWER(privilege) || ' TO ' || LOWER(grantee) || 
        DECODE(admin_option,'YES','WITH ADMIN OPTION;',';')
  FROM dba_sys_privs p, dba_users u
  WHERE
    u.username = p.grantee AND
    u.default_tablespace NOT IN ('EXAMPLE','DRSYS','CWMLITE','ODM','XDB','SYSTEM','SYSAUX')
;
 
SPOOL OFF