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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 |
-- -- 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 |
