grant_select.sql

DECLARE 

first_owner varchar2(40) := 'SOURCE_USER_NAME'; --SOURCE SCHEMA

grant_to varchar2(40) := 'TARGET_USER_NAME'; --USER WHO WANTS GRANT

BEGIN 

	FOR r IN (

	SELECT 'GRANT SELECT ON '|| OWNER || '.' || TABLE_NAME || ' TO ' || grant_to g  FROM ALL_tables WHERE owner LIKE first_owner

		)

	LOOP 

	BEGIN 

	--	dbms_output.put_line(r.g);

		EXECUTE IMMEDIATE r.g;
	EXCEPTION WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE(r.g);

		END;

	END LOOP;

FOR r IN (

	SELECT 'GRANT SELECT ON '|| OWNER || '.' || MVIEW_NAME || ' TO ' || grant_to g   FROM ALL_MVIEWS WHERE owner LIKE first_owner

		)

	LOOP 

	BEGIN 

	--	dbms_output.put_line(r.g);

	
		EXECUTE IMMEDIATE r.g;
	EXCEPTION WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE(r.g);

		END;

	END LOOP;
FOR r IN (

	SELECT 'GRANT SELECT ON '|| OWNER || '.' || SYNONYM_NAME || ' TO ' || grant_to g   FROM ALL_SYNONYMS  WHERE owner LIKE first_owner

		)

	LOOP 

	BEGIN 

	--	dbms_output.put_line(r.g);

	
		EXECUTE IMMEDIATE r.g;
	EXCEPTION WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE(r.g);

		END;

	END LOOP;
FOR r IN (

	SELECT 'GRANT SELECT ON '|| SEQUENCE_OWNER || '.' || SEQUENCE_NAME || ' TO ' || grant_to g   FROM ALL_SEQUENCES  WHERE SEQUENCE_OWNER LIKE first_owner
	)

	LOOP 

	BEGIN 

	--	dbms_output.put_line(r.g);

	
		EXECUTE IMMEDIATE r.g;
	EXCEPTION WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE(r.g);

		END;

	END LOOP;

FOR r IN (

	SELECT
	'GRANT SELECT ON ' || OWNER || '.' || VIEW_NAME || ' TO ' || grant_to g
FROM
	ALL_VIEWS
WHERE
	owner LIKE first_owner

		)

	LOOP 

	BEGIN
	--	dbms_output.put_line(r.g);

		EXECUTE IMMEDIATE r.g;
	EXCEPTION WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE(r.g);
END;

	END LOOP;
END;