set echo off
set termout on

--
--  File     : cr_ddl_role.sql
--  Version  : 1.0 / 31.08.2000
--  Created  : Hans Wesnitzer / MuniQSoft GmbH
--
--  Purpose  : Extract DDL statements to restore all defined roles except 
--             roles DBA,CONNECT,RESOURCE,EXP_FULL_DATABASE,IMP_FULL_DATABASE
--             and SNMPAGENT.
--
--  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_roles is
    select *
    from dba_roles
    where role not in ('DBA', 'CONNECT', 'RESOURCE', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE', 'SNMPAGENT');

  cursor c_role_rprivs(role in varchar2) is
    select *
    from dba_role_privs
    where grantee = role;

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

  cursor c_role_sprivs(role in varchar2) is
    select *
    from dba_sys_privs
    where grantee = role;

  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_role in c_roles loop

   first := true;
   cnt := 0;

   pl(' ');
   pl('--');
   pl('-- Generate DDL Statements for ROLE ' || rec_role.role);
   pl('-- Datum: ' || to_char(sysdate, 'DD.MM.RRRR HH24:MI:SS'));
   pl('-- ');
   pl('');
   pl('-- ');
   pl('-- DROP ROLE ' || rec_role.role || ';');
   pl('-- ');
   pl(' ');
   if rec_role.password_required = 'NO' then
     pl('CREATE ROLE ' || rec_role.role || ';');
   else
     if rec_role.password_required = 'EXTERNAL' then
       pl('CREATE ROLE ' || rec_role.role || ' IDENTIFIED EXTERNALLY;');
     else
       pl('CREATE ROLE ' || rec_role.role || ';');
--       pl('CREATE ROLE ' || rec_role.username || ' IDENTIFIED BY VALUES ''' || rec_role. || '''');
     end if;
   end if;
   pl('');
   pl('-- ');
   pl('-- Extract roles');

   for rec_rp in c_role_rprivs(rec_role.role) loop
     if rec_rp.admin_option = 'NO' then
       pl('GRANT ' || rec_rp.granted_role || ' TO ' || rec_role.role || ';');
     else
       pl('GRANT ' || rec_rp.granted_role || ' TO ' || rec_role.role || ' WITH ADMIN OPTION;');
     end if;
   end loop;
   pl(' ');
   pl('-- ');
   pl('-- Extract system privileges');

   for rec_sp in c_role_sprivs(rec_role.role) loop
     if rec_sp.admin_option = 'NO' then
       pl('GRANT ' || rec_sp.privilege || ' TO ' || rec_role.role || ';');
     else
       pl('GRANT ' || rec_sp.privilege || ' TO ' || rec_role.role || ' WITH ADMIN OPTION;');
     end if;
   end loop;
   pl(' ');
   pl('-- ');
   pl('-- Extract table privileges');

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

 end loop;

end;

/