rem $DBA/crroles.sql
rem
rem This script generates another script that will include all the CREATE ROLE
rem statements for those in the database.
rem
rem This script must be run by a user with the DBA role under Oracle7.
rem
rem Assumes that there is an OPS$ account for the user running this script.
rem
rem Created 09/09/97 by Brian Lomasky
rem
set verify off
set feedback off
set echo off
set pagesize 0
set termout on
define q=chr(39)
select 'Creating role build script...' from dual;

create table role_temp (	lineno NUMBER,
				text VARCHAR2(80));

declare
	cursor role_cursor is select
		name,
		password
		from sys.user$
		where type = 0 and name not in ('PUBLIC', '_NEXT_USER')
		order by 1;

	lv_name			sys.user$.name%TYPE;
	lv_password		sys.user$.password%TYPE;
	lv_lineno		number;
	a_lin			varchar2(80);

	function wri(x_lin in varchar2, x_str in varchar2,
		x_force in number) return varchar2 is
	begin
		if length(x_lin) + length(x_str) > 80
		then
			lv_lineno := lv_lineno + 1;
			insert into role_temp values (lv_lineno, x_lin);
			if x_force = 0
			then
				return '    '||x_str;
			else
				lv_lineno := lv_lineno + 1;
				if substr(x_lin,1,2) = '  '
				then
					insert into role_temp values (
						lv_lineno, x_str);
				else
					insert into role_temp values (
						lv_lineno, '    '||x_str);
				end if;
				return '';
			end if;
		else
			if x_force = 0
			then
				return x_lin||x_str;
			else
				lv_lineno := lv_lineno + 1;
				insert into role_temp values (
					lv_lineno, x_lin||x_str);
				return '';
			end if;
		end if;
	end wri;

begin
	a_lin := '';
	open role_cursor;
	loop
		fetch role_cursor into
			lv_name,
			lv_password;
		exit when role_cursor%NOTFOUND;
		a_lin := wri(a_lin, 'CREATE ROLE ', 0);
		a_lin := wri(a_lin, lv_name, 0);
		if nvl(lv_password, 'NO') = 'NO' then
			a_lin := wri(a_lin, ' not identified;', 1);
		elsif lv_password = 'EXTERNAL' then
			a_lin := wri(a_lin, ' identified externally;', 1);
		else
			a_lin := wri(a_lin, ' identified by values ', 0);
			a_lin := wri(a_lin, &q || lv_password || &q || ';', 1);
		end if;
	end loop;
	close role_cursor;
end;
/

set termout off
set heading off
spool cr_role.sql
select 'rem cr_role.sql' from dual;
select 'rem' from dual;
select 'rem ***** All roles for database ' || name from v$database;
select 'rem' from dual;
select 'set feedback off' from dual;
select text from role_temp order by lineno;
spool off
drop table role_temp;
set termout on
select 'Created cr_role.sql...' from dual;
set termout off
exit