set linesize 80;
set pagesize 10000;
set long 50;
set echo on;

/* ************************************************ */
/*                                                  */
/* Script to create roles.                          */
/* Script must be run by a user with the DBA role.  */
/*                                                  */
/*         @c:\oracle\admin\cre8_rol.sql            */ 
/*                                                  */
/* NOTE: This script will not capture the create or */
/*       grant on the Oracle predefined roles,      */
/*       CONNECT, RESOURCE, DBA, EXP_/IMP_FULL_...  */
/*                                                  */
/* ************************************************ */

set verify off
set feedback off;
set termout off;
set echo off;
set pagesize 0;

set termout on
select 'CREATING ROLE BUILD SCRIPT...' from dual;
set termout off;

spool cre8_rol.rslt

/* ********************************* */
/* 1. Generate drop table commands   */
/* 2. Generate create table commands */
/* ********************************* */

select 'CREATE ROLE '||lower(role)||' NOT IDENTIFIED;'
   from sys.dba_roles
   where role not in ('CONNECT', 'RESOURCE', 'DBA',
                      'EXP_FULL_DATABASE', 'EXP_FULL_DATABASE') and
         password_required = 'NO';

select 'CREATE ROLE '||lower(role)||' IDENTIFIED BY VALUES '||'''' || paswword || '''' ||';'
   from sys.dba_roles,
        sys.user$
   where role not in ('CONNECT', 'RESOURCE', 'DBA',
                      'EXP_FULL_DATABASE', 'EXP_FULL_DATABASE') and
         password_required = 'YES'				and
 	 dba_roles.role = user$.name				and
	 user$.type = 0;

select 'GRANT '||lower(granted_role)||' TO '||lower(grantee) || ' WITH ADMIN OPTION;'
   from sys.dba_role_privs
   where admin_option = 'YES'					and
	 granted_role not in 
		     ('CONNECT', 'RESOURCE', 'DBA',
                      'EXP_FULL_DATABASE', 'EXP_FULL_DATABASE')
   order by grantee;
                 
spool off;

commit;










