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
