Grant all access to all objects in a schema to a user or role, for Oracle database.

grant_everything_from_schema_to_user_or_role.sql

/*
Purpose: Grant everything in a schema to a specific user or role
How to use: Replace &USER_OR_ROLE and &OWNER with the correct values and run the block.
*/
begin
	for grants in
	(
		--SQL statements.
		--CHANGE THE BELOW LINE:
		select replace(v_sql, '#USER#', trim(upper('&USER_OR_ROLE'))) v_sql
		from
		(
			--Grants on each object type.
			select 'grant all on  "'||owner||'"."'||object_name||'" to #USER#' v_sql
			from dba_objects
			--CHANGE THE BELOW LINE:
			where owner = '&OWNER'
				--These objects do not have privileges to grant.
				and object_type not in ('SYNONYM', 'INDEX', 'DATABASE LINK', 'LOB', 'TABLE PARTITION')
			order by object_type, object_name
		)
	) loop
		begin
			execute immediate grants.v_sql;
		exception when others then
			--Ignore ORA-04063: view "X.Y" has errors
			if sqlcode = -4063 then
				null;
			--Raise exception along with the statement that failed.
			else
				raise_application_error(-20000, 'Problem with this statement: '||grants.v_sql||chr(10)||sqlerrm);
			end if;
		end;
	end loop;
end;
/