Inicio » Scripts sql curiosos » Recrear un usuario con las mismas caracteristicas que tenía originalemente

Recrear un usuario con las mismas caracteristicas que tenía originalemente

set heading off
set verify off
set feedback off
set echo off
set linesize 200
SET TERMOUT OFF
SPOOL &1/CreateUser&2..sql
SELECT ‘@@&5/parameters.sql’ FROM DUAL;
SELECT ‘spool &4/CreateUser.log’ FROM DUAL;
SELECT  ‘SET TERMOUT ON’ FROM DUAL;
SELECT  ‘Prompt >>Dropping User &2’ FROM DUAL;
SELECT  ‘SET TERMOUT OFF’ FROM DUAL;
SELECT ‘DROP USER &2 CASCADE;’  FROM DUAL;
SELECT  ‘SET TERMOUT ON’ FROM DUAL;
SELECT  ‘Prompt >>Creating User &2’ FROM DUAL;
SELECT  ‘SET TERMOUT OFF’ FROM DUAL;
SELECT    ‘CREATE USER ‘|| USERNAME||’ IDENTIFIED BY ‘
       || ‘”‘
       || ‘&3’
       || ‘”‘
       || ‘ DEFAULT TABLESPACE ‘
       || default_tablespace
       || ‘ TEMPORARY TABLESPACE ‘
       || temporary_tablespace
       || ‘ PROFILE ‘
       || PROFILE
       || ‘;’
  FROM dba_users
 WHERE username = upper(‘&2’);
SELECT  ‘SET TERMOUT ON’ FROM DUAL;
SELECT  ‘Prompt >>Granting QUOTA’ from dual;
SELECT  ‘SET TERMOUT OFF’ FROM DUAL;
SELECT    ‘ALTER USER ‘
       || username
       || ‘ QUOTA UNLIMITED ON ‘
       || tablespace_name
       || ‘;’
  FROM dba_ts_quotas
 WHERE username = upper( ‘&2’);
SELECT  ‘SET TERMOUT ON’ FROM DUAL;
SELECT  ‘Prompt >>Granting &2’ FROM DUAL;
SELECT  ‘SET TERMOUT OFF’ FROM DUAL;
SELECT ‘GRANT ‘ || granted_role || ‘ TO &2;’
  FROM dba_role_privs
 WHERE grantee = upper(‘&2’);
SELECT ‘GRANT ‘ || PRIVILEGE || ‘ TO  &2;’
  FROM dba_sys_privs
 WHERE grantee = upper(‘&2’);
SELECT ‘GRANT ‘ || PRIVILEGE || ‘ ON ‘ || table_name || ‘ TO &2;’
  FROM dba_tab_privs
 WHERE grantee = upper(‘&2’);
SELECT  ‘SET TERMOUT ON’ FROM DUAL;
SELECT  ‘Prompt >>Setting default role’ from dual;
SELECT  ‘SET TERMOUT OFF’ FROM DUAL;
SELECT ‘DECLARE ‘ FROM DUAL;
SELECT ‘  CURSOR c1 ‘ FROM DUAL;
SELECT ‘  IS ‘ FROM DUAL;
SELECT ‘  SELECT granted_role ‘ FROM DUAL;
SELECT ‘    FROM dba_role_privs ‘ FROM DUAL;
SELECT ‘    WHERE grantee = upper(‘||CHR (39)||’&2’||CHR (39)||’); ‘ FROM DUAL;
SELECT ‘    all_roles   VARCHAR2 (500); ‘ FROM DUAL;
SELECT ‘BEGIN ‘ FROM DUAL;
SELECT ‘  FOR c IN c1 ‘ FROM DUAL;
SELECT ‘  LOOP ‘ FROM DUAL;
SELECT ‘    all_roles := all_roles||c.granted_role ||’ ||CHR (39)||’,’||CHR (39)||’; ‘ FROM DUAL;
SELECT ‘  END LOOP ;’ FROM DUAL;
SELECT ‘    all_roles := SUBSTR (all_roles, 1, LENGTH (all_roles) – 1); ‘ FROM DUAL;
SELECT ‘  DECLARE ‘ FROM DUAL;
SELECT ‘    str   VARCHAR2 (128); ‘ FROM DUAL;
SELECT ‘  BEGIN ‘ FROM DUAL;
SELECT ‘     str := ‘||CHR (39)||’ALTER USER &2 DEFAULT ROLE ‘||CHR (39)|| ‘|| all_roles; ‘ FROM DUAL;
SELECT ‘     EXECUTE IMMEDIATE str; ‘ FROM DUAL;
SELECT ‘  END; ‘ FROM DUAL;
SELECT ‘  END; ‘ FROM DUAL;
SELECT ‘/ ‘ FROM DUAL;
SELECT ‘spool off’ FROM DUAL;
spool off

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s