Movendo usuários entre bases II

Há tempos atrás eu postei este script tosco para extrair usuários com suas respectivas senhas no Oracle. Eu sei, no PostgreSQL, basta um ‘pg_dumpall -r’ e tudo se resolve, mas no Oracle, você tem um pouco mais de trabalho, ainda mais se não quiser utilizar o ultra-super-mega-boga-hyper-duper-master-blaster-heavy-metal Database Control, também conhecido como Enterprise Manager e mais uma dúzia de apelidos.

Bom, o script a seguir cria um arquivo com os usuários, grupos (ok, o nome certo é ‘roles’), permissões em tabelas, privilégios de sistema, roles, quotas de tablespace e mais alguma coisa. Se você precisa atualizar bases entre versões diferentes (como do 9i para o 10g ou para o 11g), ou entre Sistemas Operacionais diferentes (se você tem o Oracle Enterprise dá para usar o conceito de tablespace transportável).

Enfim, todo DBA sabe utilizar as ferramentas de geração de Dump (exp ou expdp) e importação de Dump (imp, impdp). E quase todos costumam importar os dados por esquema e não tudo de uma vez (full=y).  E como sempre, antes de importar o esquema, o usuário deve existir da base de destino.

Chega de blá, blá, blá. Segue o script:

--
-- 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 profiles, e altera o DEFAULT
BEGIN
  FOR profile IN (SELECT DISTINCT profile FROM dba_profiles) LOOP
    IF profile.profile = 'DEFAULT' THEN
      dbms_output.put_line('ALTER PROFILE ' || profile.profile || ' LIMIT');
    ELSE
      dbms_output.put_line('CREATE PROFILE ' || profile.profile || ' LIMIT');
    END IF;
    FOR prof_limit IN (
      SELECT resource_name, limit
        FROM dba_profiles
        WHERE
          profile = profile.profile AND
          limit != 'DEFAULT') LOOP
      dbms_output.put_line('    ' || prof_limit.resource_name || ' ' || prof_limit.limit);
    END LOOP;
    dbms_output.put_line(';');
    dbms_output.put_line('');
  END LOOP;
END;
/

--Cria usuários
SELECT
    'CREATE USER "' || 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') AND
    LOWER (INITIAL_RSRC_CONSUMER_GROUP) !='default_consumer_group'
  ORDER BY default_tablespace, username
;

-- Cria ROLEs
SELECT
    'CREATE ROLE "' || 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 roles
SELECT
    'GRANT ' || granted_role || ' TO "' || 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 ' || privilege || ' TO "' || 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')
;

-- Gera GRANTs em objetos
SELECT
    'GRANT ' || privilege ||
    ' ON ' || DECODE(privilege,'READ','DIRECTORY ','WRITE','DIRECTORY ') ||
    '"' || owner || '"."' || table_name ||
    '" TO "' || grantee || '"' ||
    DECODE(grantable,'YES',' WITH GRANT OPTION',' ') ||
    DECODE(hierarchy,'YES', ' WITH HIERARCHY OPTION;',';') AS gr
  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 ' || privilege || '("' || column_name || '")' ||
    ' ON "' || owner || '"."' || table_name ||
    '" TO "' || grantee || '"' ||
    DECODE(grantable,'YES',' WITH GRANT OPTION;',';')
  FROM dba_col_privs;

SPOOL OFF