SET PAGESIZE  0
SET FEEDBACK  OFF
SET VERIFY    OFF
SET RECSEP    OFF

PROMPT

select '-- ===============================================================================' || chr(10) ||
       '-- Generated by "recreate_user.sql" at ' || to_char(sysdate, 'DD-Mon-YYYY HH24:MI') || chr(10) ||
       '-- Source database : ' || v$database.name || chr(10) ||
       '-- Source machine  : ' || v$instance.host_name || chr(10) ||
       '-- ===============================================================================' || chr(10) || chr(10) ||
       'PROMPT Creating user <&1> ...' || chr(10) || chr(10) ||
       'CREATE USER &1 IDENTIFIED ' ||
       decode(password,'EXTERNAL','EXTERNALLY',
                       'BY VALUES ''' || password || '''') || chr(10) ||
       '       DEFAULT TABLESPACE ' || default_tablespace ||
       ' TEMPORARY TABLESPACE ' || temporary_tablespace || chr(10) ||
       '       PROFILE ' || profile ||
       decode(account_status, 'LOCKED',  ' ACCOUNT LOCK',
                              'OPEN',    ' ACCOUNT UNLOCK',
                              'EXPIRED', ' PASSWORD EXPIRE', '') || ';'
  from dba_users, v$database, v$instance
 where username = upper('&1')
union all
select '-- ===============================================================================' || chr(10) ||
       '-- Generated by "recreate_user.sql" at ' || to_char(sysdate, 'DD-Mon-YYYY HH24:MI') || chr(10) ||
       '-- Source database : ' || v$database.name || chr(10) ||
       '-- Source machine  : ' || v$instance.host_name || chr(10) ||
       '-- ===============================================================================' || chr(10) || chr(10) ||
       '-- Parameter <&1> is a role instead of a user.' || chr(10) ||
       '-- No create user statement can be generated.' || chr(10) ||
       '-- proceding with generating grant statement ...'
  from dba_roles, v$database, v$instance
 where role = upper('&1')
;

PROMPT

select 'ALTER USER &1 QUOTA ' ||
       decode(max_bytes,-1,'unlimited', to_char(max_bytes/1024)||' K')
       || ' ON ' || tablespace_name || ';'
  from dba_ts_quotas
 where username = upper('&1');

PROMPT

select 'GRANT ' || privilege || ' TO &1' ||
       decode(admin_option,'NO',';','YES',' WITH ADMIN OPTION;')
  from dba_sys_privs
 where grantee  = upper('&1')
 ORDER BY PRIVILEGE;

select 'GRANT ' || granted_role || ' TO &1' ||
       decode(admin_option,'NO',';','YES',' WITH ADMIN OPTION;')
  from dba_role_privs
 where grantee  = upper('&1');


PROMPT
SET SERVEROUTPUT ON SIZE 1000000 FORMAT WORD_WRAPPED

DECLARE
  first_row      boolean := TRUE;
  defrole_state  number;
  cnt_role       number;
BEGIN
  /* If a role was passed in, then do nothing. */
  SELECT count(*) INTO cnt_role FROM dba_roles WHERE role = upper('&1');
  if cnt_role = 1 then return; end if;

  begin
    SELECT defrole
      INTO defrole_state
      FROM sys.user$
     WHERE name = upper('&1');
  exception
    when no_data_found then dbms_output.put_line( 'User <&1> does not exist.' );
         return;
    when others then raise;
  end;
  if defrole_state = 0 then
    dbms_output.put_line( 'ALTER USER &1 DEFAULT ROLE NONE;' );
  else
    if defrole_state = 1 then
      dbms_output.put_line( 'ALTER USER &1 DEFAULT ROLE ALL;' );
    else
      if defrole_state = 2 then
        for gr in ( SELECT granted_role
                      FROM dba_role_privs
                     WHERE grantee = upper('&1')
                       AND default_role = 'YES' 
                       ORDER BY granted_role) loop
          if first_row then
            dbms_output.put( 'ALTER USER &1 DEFAULT ROLE ' );
            first_row := FALSE;
          else
            dbms_output.put( ', ' );
          end if;
          dbms_output.put( gr.granted_role );
        end loop;
        if not first_row then
          dbms_output.put_line( ';' );
        end if;
        else
          if defrole_state = 3 then
            for gr in ( SELECT granted_role
                          FROM dba_role_privs
                         WHERE grantee = upper('&1')
                           AND default_role = 'NO'
                         ORDER BY granted_role ) loop
              if first_row then
                dbms_output.put( 'ALTER USER &1 DEFAULT ROLE ALL EXCEPT ' );
                first_row := FALSE;
              else
                dbms_output.put( ', ' );
              end if;
              dbms_output.put( gr.granted_role );
            end loop;
            if not first_row then
              dbms_output.put_line( ';' );
            end if;
          end if;
      end if;
    end if;
  end if;
END;
/

PROMPT

DECLARE
  prev_owner        dba_tab_privs.owner%type := '=none=';
  prev_table        dba_tab_privs.table_name%type := '=none=';
  prev_grantable    varchar2(30) := '=none=';
  grant_complete    boolean := FALSE;
  obj_privs         boolean := FALSE;
  grant_option      varchar2(30);
BEGIN
  for r_privs in ( SELECT owner,
                          table_name,
                          privilege,
                          decode(grantable, 'YES', ' WITH GRANT OPTION', '' ) grantable
                     FROM dba_tab_privs
                    WHERE grantee = upper('&1')
                      AND privilege not in ('ENQUEUE','DEQUEUE')
                 ORDER BY owner, table_name, grantable ) loop

    obj_privs := TRUE;
    grant_option := r_privs.grantable;

    if prev_owner = '=none=' then
      dbms_output.put_line( '--' );
      dbms_output.put_line( '-- Granting object privileges to &1 ...' );
    end if;

    if r_privs.owner != prev_owner then
      if grant_complete then
        dbms_output.put_line( ' ON ' || prev_owner || '.' || prev_table || ' TO &1' || grant_option || ';' );
      end if;
      grant_complete := FALSE;
      dbms_output.put_line( '--' );
      dbms_output.put_line( 'PROMPT Granting privileges on objects owned by ' || r_privs.owner || ' ...' );
      dbms_output.put_line( '--' );
      prev_owner     := r_privs.owner;
      prev_table     := '=none=';
      prev_grantable := '=none=';
    end if;

    if r_privs.table_name != prev_table or r_privs.grantable != prev_grantable then
      if grant_complete then
        dbms_output.put_line( ' ON ' || prev_owner || '.' || prev_table || ' TO &1' ||  grant_option || ';' );
      end if;
      grant_complete := TRUE;
      dbms_output.put( 'GRANT ' );
      prev_table     := r_privs.table_name;
      prev_grantable := r_privs.grantable;
    else
      dbms_output.put( ',' );
    end if;
    dbms_output.put( r_privs.privilege );
  end loop;

  if obj_privs then
    dbms_output.put_line( ' ON ' || prev_owner || '.' || prev_table || ' TO &1' ||  grant_option || ';' );
  end if;
END;
/

SELECT '--' ||chr(10) || '-- You have to connect with the proper user to execute the following execute statements.' || chr(10)
  FROM dba_tab_privs
 WHERE grantee = upper('&1')
   AND privilege in ('ENQUEUE','DEQUEUE')
   AND rownum < 2;

SELECT 'execute dbms_aqadm.grant_queue_privilege( ''' || privilege || ''', ''' || table_name || ''', ''' || grantee || ''', '
       || decode(grantable, 'YES', ' TRUE', 'FALSE' ) || ' )    -- connect as <' || owner || '> ;'
  FROM dba_tab_privs
 WHERE grantee = upper('&1')
   AND privilege in ('ENQUEUE','DEQUEUE');

PROMPT

SELECT 'PROMPT Creating private synonyms  ...' || chr(10)
  FROM dba_synonyms
 WHERE owner = upper('&1')
   AND rownum < 2;

SELECT 'create synonym &1..' || synonym_name || ' for ' || table_owner || '.' || table_name
       || decode(db_link,'','','@' || db_link) || ';'
  FROM dba_synonyms
 WHERE owner = upper('&1');

PROMPT

SELECT 'PROMPT Creating public synonyms  ...' || chr(10)
  FROM dba_synonyms
 WHERE owner = upper('&1')
   AND rownum < 2;

SELECT 'create public synonym ' || synonym_name || ' for ' || table_owner || '.' || table_name
       || decode(db_link,'','','@' || db_link) || ';'
  FROM dba_synonyms
 WHERE owner = 'PUBLIC' and table_owner = upper('&1');


PROMPT

SET SERVEROUTPUT OFF
SET PAGESIZE     9999
SET FEEDBACK     ON