this script has a limitation when running from sqlplus command line. But no issues when running using sql developer.
--- Limitation on substitution variable value length with 240 characters. so proc list should be limited.
--- JUST EDIT THE TOP 3 LINES AND MAKE SURE THEY ARE CORRECT FOR THAT ENVIRONMENT:
--variable M2 varchar2(30);
--DEFINE M2 = &&1;
-- DEFINE SCHEMA_NAME = &&2;
DEFINE SCHEMA_NAME = SCHEMA_NAME;
DEFINE USERS_LIST = 'BIMUSER,AIO98J,JMUSER'
DEFINE PROC_LIST = 'UPDATE_COL_PROC,UPDATE_TAB_PROC'
SET TRIMSPOOL ON TRIMOUT ON DEFINE "&" CONCAT "." SERVEROUTPUT ON;
COL SPOOL_NAME NEW_VALUE SPOOL_NAME;
SET FEEDBACK OFF
SET HEADING OFF
VARIABLE SPOOL_NAME VARCHAR2(100);
SELECT ''||(SELECT PDB_NAME FROM DBA_PDBS)||'-&&SCHEMA_NAME-ASSIGN_GRANTS_TO_APP-SPECIALISTS.LOG' SPOOL_NAME FROM DUAL;
SPOOL &SPOOL_NAME
ALTER SESSION SET CURRENT_SCHEMA=&&SCHEMA_NAME;
SET LINESIZE 200
SET SERVEROUTPUT ON SIZE 1000000
SET FEEDBACK ON ECHO OFF VERIFY OFF
DECLARE
check_user varchar2(10);
CURSOR C_F IS (
SELECT REGEXP_SUBSTR ('&USERS_LIST', '[^,]+', 1, LEVEL) AS USER_L
FROM DUAL CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE('&USERS_LIST','[^,]*'))+1);
CURSOR C_G IS (
SELECT REGEXP_SUBSTR ('&PROC_LIST', '[^,]+', 1, LEVEL) AS PROC_L
FROM DUAL CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE('&PROC_LIST','[^,]*'))+1);
BEGIN
FOR X IN C_F
LOOP
select count(*) into check_user from dba_users where username in (''||X.USER_L||'');
if (check_user = 1) then
DBMS_OUTPUT.PUT_LINE ('USER:-> '||X.USER_L||' - Exists in the DB');
BEGIN
FOR Y IN C_G
LOOP
BEGIN
EXECUTE IMMEDIATE 'GRANT EXECUTE ON "&&SCHEMA_NAME".'||Y.PROC_L||' TO '||X.USER_L||'';
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('ROW NOT FOUND FROM THE LIST');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('ERROR OCCURRED WHEN RUNNING FOR USER: '||X.USER_L||' for '||Y.PROC_L||'');
ROLLBACK;
CONTINUE;
END;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
CONTINUE;
END;
else
DBMS_OUTPUT.PUT_LINE (' USER:-> '||X.USER_L||' - Does not exists in the DB. ');
end if;
END LOOP;
END;
/
SPOOL OFF;
UNDEFINE;
