set echo off
set termout on

--
--  File     : cr_ddl_user.sql
--  Version  : 1.0 / 31.08.2000
--  Created  : Hans Wesnitzer / MuniQSoft GmbH
--
--  Purpose  : Extract DDL statements to restore all defined users except 
--             SYS and SYSTEM.
--
--  Requires : Oracle RDBMS 7.3.4 onwards
--
--  To Start : Must be called as user SYSTEM from sqlplus.
--
--  Changes  :
--

set feedback off
set serveroutput on
set termout on
set trimspool on

declare

  cursor c_users is
    select *
    from dba_users
    where username not in ('SYS', 'SYSTEM');

  cursor c_user_rprivs(name in varchar2) is
    select *
    from dba_role_privs
    where grantee = name;

  cursor c_user_tprivs(name in varchar2) is
    select *
    from dba_tab_privs
    where grantee = name
    order by owner,table_name,privilege;

  cursor c_user_sprivs(name in varchar2) is
    select *
    from dba_sys_privs
    where grantee = name;

  cursor c_user_tsq(name in varchar2) is
    select *
    from dba_ts_quotas
    where username = name;

  first boolean default true;
  cnt integer default 0;

  text varchar2(255) default '';  -- 255 bytes is limit of dbms_output.put_line

  procedure pl (line in varchar2) is
  begin
    dbms_output.put_line(line);
  end;

begin

  dbms_output.enable(1000000);

  for rec_user in c_users loop

   first := true;
   cnt := 0;

   pl(' ');
   pl('--');
   pl('-- Generate DDL Statements for USER ' || rec_user.username);
   pl('-- Datum: ' || to_char(sysdate, 'DD.MM.RRRR HH24:MI:SS'));
   pl('-- ');
   pl(' ');
   if rec_user.password = 'EXTERNAL' then
     pl('CREATE USER ' || rec_user.username || ' IDENTIFIED EXTERNALLY');
   else
     pl('CREATE USER ' || rec_user.username || ' IDENTIFIED BY VALUES ''' || rec_user.password || '''');
   end if;
   pl('  DEFAULT TABLESPACE ' || rec_user.default_tablespace);
   pl('  TEMPORARY TABLESPACE ' || rec_user.temporary_tablespace);
   pl('  PROFILE ' || rec_user.profile);
   pl('/');
   pl(' ');

   for rec_tsq in c_user_tsq(rec_user.username) loop
     if rec_tsq.max_bytes = -1 then
       pl('ALTER USER ' || rec_user.username || ' QUOTA UNLIMITED ON ' || rec_tsq.tablespace_name || ';');
     else
       pl('ALTER USER ' || rec_user.username || ' QUOTA ' || rec_tsq.max_bytes || ' ON ' || rec_tsq.tablespace_name || ';');
     end if;
   end loop;
   pl(' ');
   pl('-- ');
   pl('-- User Roles');

   for rec_rp in c_user_rprivs(rec_user.username) loop
     if rec_rp.admin_option = 'NO' then
       pl('GRANT ' || rec_rp.granted_role || ' TO ' || rec_user.username || ';');
     else
       pl('GRANT ' || rec_rp.granted_role || ' TO ' || rec_user.username || ' WITH ADMIN OPTION;');
     end if;
   end loop;
   pl(' ');
   pl('-- ');
   pl('-- User system privileges');

   for rec_sp in c_user_sprivs(rec_user.username) loop
     if rec_sp.admin_option = 'NO' then
       pl('GRANT ' || rec_sp.privilege || ' TO ' || rec_user.username || ';');
     else
       pl('GRANT ' || rec_sp.privilege || ' TO ' || rec_user.username || ' WITH ADMIN OPTION;');
     end if;
   end loop;
   pl(' ');
   pl('-- ');
   pl('-- User object privileges');

   first := true;
   for rec_tp in c_user_tprivs(rec_user.username) loop
     if first = true then
       first := false;
       pl('--');
       pl('-- ATTENTION: Table privileges must be granted directly as table owner.');
       pl('--');
     end if;
--    order by owner,table_name,privilege;
     if rec_tp.grantable = 'NO' then
       pl('-- GRANT ' || rec_tp.privilege || ' ON ' || rec_tp.owner || '.' || rec_tp.table_name || ' TO ' || rec_user.username || ';');
     else
       pl('-- GRANT ' || rec_tp.privilege || ' ON ' || rec_tp.owner || '.' || rec_tp.table_name || ' TO ' || rec_user.username || ' WITH ADMIN OPTION;');
     end if;
   end loop;

 end loop;

end;

/