Automatic Role creator script

-- This is a dynamic role creator script
-- Based on the schema name you provide and the role type (SELECT or INSERT or DELETE or UPDATE)
-- This code will create a procedure
-- When you execute this procedure it will create a role and grant the type of privilege mentioned for all the tables to that role.
set lines 200 PAGES 50000 LONG 2000000
set serveroutput on
set define off
grant select on sys.dba_tables to user;

create or replace procedure user.create_role (schema_name varchar2, role_type varchar2)
is
role_name varchar2(50) ;
table_name varchar2(30);
sql_stmt varchar2(100);
CURSOR TABLE_LIST IS SELECT TABLE_NAME FROM sys.DBA_TABLES WHERE OWNER = schema_name;
begin
if upper(role_type)='SELECT' then
role_name := upper(schema_name)||'_SROLE';
elsif upper(role_type)='INSERT' then
role_name := upper(schema_name)||'_IROLE';
elsif upper(role_type)='UPDATE' then
role_name := upper(schema_name)||'_UROLE';
elsif upper(role_type)='DELETE' then
role_name := upper(schema_name)||'_DROLE';
ELSE
RAISE_APPLICATION_ERROR
(-20001, 'Incorrect Role Type Entered');
end if;
dbms_output.put_line('Role Name is:'||role_name);
sql_stmt:= 'create role '||role_name;
EXECUTE IMMEDIATE sql_stmt;
dbms_output.put_line('Role Created');
open TABLE_LIST;
loop
fetch TABLE_LIST into table_name;
dbms_output.put_line('GRANTING '||role_type||' ON '||schema_name||'.'||table_name||' TO '||table_name||' ...');
sql_stmt:= 'GRANT '||role_type||' ON '||schema_name||'.'||table_name||' to '||role_name;
EXECUTE IMMEDIATE sql_stmt;
exit when TABLE_LIST%notfound;
end loop;
close TABLE_LIST;
dbms_output.put_line('All grants completed successfully');
end;
/

-- This is how you execute the procedure.
-- exec user.create_role('user','SELECT');