-- =======================================================================
-- codigo_usuario
-- Esse script irá solicitar o nome do owner e gerar o script de criação já recuperando o hash da senha, default tablespace e temporary_tablespace, além de salvar o profile padrão do usuário, quotas em tablespaces, roles e privilégios de sistema. E também ele gera as permissões do usuário também.
-- =======================================================================
accept var_user prompt 'Usuario: '
set arraysize 10
set verify off
set heading off
set feedback off
--set termout off echo off feedback off pagesize 0 heading off verify off
REM
prompt
prompt
prompt
select 'create user '||a.username||' identified by values '''||b.password||''''||CHR(10)||
' default tablespace '||a.DEFAULT_TABLESPACE||' temporary tablespace '||a.temporary_tablespace || ';'
from dba_users a, sys.user$ b
where a.username = b.name
and a.username like upper('&var_user');
SELECT 'alter user '||username||DECODE(profile, ' default', ';', ' profile '||profile||';')
from dba_users
where username like upper('&var_user');
SELECT 'alter user ' || username || ' quota unlimited on ' || tablespace_name || ';'
from dba_ts_quotas
where username like upper('&var_user')
and max_bytes = -1;
select 'grant '||granted_role||' to '||grantee||';'
from dba_role_privs
where grantee like upper('&var_user');
select 'grant '||privilege||' to '||grantee||';'
from dba_sys_privs
where grantee like upper('&var_user');
-- permissões
select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||';'
from dba_tab_privs
where grantee like upper('%&var_user%')
/
-- objetos
select object_name, object_type
from dba_objects
where owner = upper('&var_user')
order by object_type
/
set termout on feedback 15 verify on pagesize 20 linesize 80 space 1 heading on
set feedback on
set verify on
set heading on
prompt
prompt
prompt
-- OUTPUT --
create user JOE identified by values '85E62084BF1AAD5F' default tablespace USERS temporary tablespace TEMP;
alter user JOE profile USUARIOS;
grant RL_SAC_USUARIO to JOE;
grant RL_SAC_GERENTE to JOE;
grant RL_SAC_CONSULTA to JOE;
grant RL_ADMSAC to JOE;
grant RL_SRH_CONSULTA to JOE;
grant RL_SRH_SEGURANCA to JOE;
grant RL_SRH_GERENTE to JOE;
grant RL_ENTRADA to JOE;
grant RL_ACESSO_CONSULTA to JOE;
grant CREATE SESSION to JOE;
-- =======================================================================
-- codigo tablespace
-- Esse script irá solicitar parte do nome da(s) tablespace(s) e exibir na tela o script de criação da(s) tablespace(s), inclusive mantendo a mesma quantidade de bytes
-- =======================================================================
set serverout on size 1000000
set head off echo off feedback off verify off
set pagesize 0
set trims on
set serveroutput off
set serveroutput on size 1000000
set linesize 5000
accept tablespace char prompt "Informe parte do nome da tablespace: "
prompt
set pause off
declare
nome dba_data_files.tablespace_name%type;
arq dba_data_files.file_name%type;
tam dba_data_files.bytes%type;
status dba_data_files.status%type;
kbytes smallint;
ult_nome dba_data_files.tablespace_name%type := ' ';
--
cursor c_tbs is
select distinct(d.tablespace_name),d.file_name,d.bytes,d.status
from dba_data_files d,dba_segments s
where d.tablespace_name != 'SYSTEM'
and (d.tablespace_name LIKE upper('&tablespace%'))
order by tablespace_name;
begin
-- dbms_output.enable(50000);
kbytes := 0;
open c_tbs;
loop
fetch c_tbs into nome, arq, tam, status;
exit when c_tbs%notfound;
kbytes := tam/1024;
if ult_nome <> nome then
dbms_output.put_line('CREATE TABLESPACE '||nome||' DATAFILE');
dbms_output.put_line(''''||arq||''''||' SIZE '||kbytes||' K ONLINE; ');
else
dbms_output.put_line('ALTER TABLESPACE '||nome||' ADD DATAFILE');
dbms_output.put_line(''''||arq||''''||' SIZE '||kbytes||' K; ');
end if;
ult_nome := nome;
end loop;
close c_tbs;
end;
/
prompt
set verify on
-- OUTPUT --
CREATE TABLESPACE ADMBASE_128K_I DATAFILE
'/u01/oradata/cdb/alfa/admbase/admbase_128k_i01.dbf' SIZE 513024 K ONLINE;
CREATE TABLESPACE ADMBASE_128K_LOB DATAFILE
'/u01/oradata/cdb/alfa/admbase/admbase_128k_lob01.dbf' SIZE 5120 K ONLINE;
CREATE TABLESPACE ADMBASE_128K_T DATAFILE
'/u01/oradata/cdb/alfa/admbase/admbase_128k_t01.dbf' SIZE 5120 K ONLINE;
-- =======================================================================
-- codigo job
-- Logado com o owner dos jobs que você quer gerar a reversa, esse script irá exibir na tela o DDL de criação de todos os jobs do usuário
-- =======================================================================
set serveroutput on
set verify off
prompt
prompt
declare
job_body VARCHAR2(500);
inststr VARCHAR2(50);
type tipoJob is table of number index by binary_integer;
arrJob tipoJob;
indice number := 1;
begin
for v_registro in (select job from user_jobs)
loop
arrJob(indice) := v_registro.job;
indice := indice + 1;
end loop;
indice := arrJob.first;
loop
exit when indice is null;
dbms_output.put_line(indice || ':' || arrJob(indice));
dbms_job.user_export(arrJob(indice), job_body);
dbms_output.put_line(job_body);
dbms_output.put_line(inststr);
indice := arrJob.next(indice);
end loop;
end;
/
prompt
-- =======================================================================
-- codigo Role
-- Esse script irá solicitar o nome da role e exibir na tela o script de criação da role a atribuição de todas as permissões
-- =======================================================================
set verify off
set echo off
set long 10000000
set linesize 32767
set pages 0
set feedback off
accept role prompt "Role: "
prompt
prompt
select 'create role &role;' from dual
/
prompt
prompt
select 'grant ' || privilege || ' on ' || owner || '.' || table_name || ' to ' || grantee || ';'
from dba_tab_privs
where grantee = upper('&role')
and grantable = 'NO'
order by owner, table_name
/
prompt
prompt
select 'grant ' || privilege || ' on ' || owner || '.' || table_name || ' to ' || grantee || ' with grant option;'
from dba_tab_privs
where grantee = upper('&role')
and grantable != 'NO'
order by owner, table_name
/
prompt
prompt
select 'grant ' || privilege || ' to ' || grantee || ';'
from dba_sys_privs
where grantee = upper('&role')
and admin_option = 'NO'
order by privilege
/
prompt
prompt
select 'grant ' || privilege || ' to ' || grantee || ' with admin option;'
from dba_sys_privs
where grantee = upper('&role')
and admin_option != 'NO'
order by privilege
/
prompt
prompt
select 'grant ' || granted_role || ' to ' || grantee || ';'
from dba_role_privs
where grantee = upper('&role')
order by grantee
/
prompt
prompt
select 'grant ' || granted_role || ' to ' || grantee || ';'
from dba_role_privs
where granted_role = upper('&role')
order by grantee
/
undef role
set verify on
set feedback on
-- =======================================================================
-- codigo Profile
-- Esse script irá solicitar o nome do profile gerar o DDL de criação dele.
-- =======================================================================
set verify off
set heading off
set feedback off
accept var_profile prompt 'Profile: '
select
dbms_metadata.get_ddl('PROFILE', profile) || '/'
from
dba_profiles
where
profile like upper('&var_profile')
group by profile;
undef var_profile
set heading on
set feedback on
set verify on
font: https://dirceuresende.com/en/blog/gerando-engenharia-reversa-ddl-de-criacao-de-usuarios-tablespaces-roles-jobs-e-profiles-no-oracle-database/#
