Role Refresher script

-- This is a dynamic role refresher script
-- Based on the role name you provide (user_SROLE,user_IROLE,user_DROLE,user_UROLE)
-- This code will create a procedure
-- When you execute this procedure it will refresh the role with the type of privilege selected, like SROLE is for SELECT 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.refresh_role (role_name varchar2)
is
schema_name varchar2(30);
schema_check varchar2(30);
table_name varchar2(30);
sql_stmt varchar2(100);
role_flag char(1);
privilege_name varchar2(6);
CURSOR TABLE_LIST IS SELECT TABLE_NAME FROM sys.DBA_TABLES WHERE OWNER = schema_name;
begin
SELECT substr(upper(role_name),1,(instr(upper(role_name),'_',1,1))-1) INTO schema_name from dual;
select DISTINCT owner into schema_check from dba_tables where owner=schema_name;
if sql%notfound then
RAISE_APPLICATION_ERROR
(-20002, 'Schema not found');
end if;
SELECT substr(upper(role_name),(instr(upper(role_name),'_',1,1))+1,1) INTO role_flag from dual;
if upper(role_flag)='S' then
privilege_name := 'SELECT';
elsif upper(role_flag)='I' then
privilege_name := 'INSERT';
elsif upper(role_flag)='U' then
privilege_name := 'UPDATE';
elsif upper(role_flag)='D' then
privilege_name := 'DELETE';
ELSE
RAISE_APPLICATION_ERROR
(-20001, 'Incorrect Role Entered or Role doesnot match script format');
end if;
dbms_output.put_line('Role Name is:'||role_name);
dbms_output.put_line('Schema Name is:'||schema_name);
dbms_output.put_line('Privilege is:'||privilege_name);
open TABLE_LIST;
loop
fetch TABLE_LIST into table_name;
sql_stmt:= 'GRANT '||privilege_name||' 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('Role refresh completed successfully');
end;
/

-- This is how you execute the procedure.
-- exec user.refresh_role('user_SROLE');