0

I have to provide multiple permissions to different users. Currently i run script manually(one by one) in oracle like - 

 GRANT SELECT ON xyz.a_BCKUP TO abc;
 GRANT SELECT ON xyz.b_BCKUP TO abc;
 GRANT SELECT ON xyz.c_BCKUP TO abc;

 GRANT SELECT,INSERT,DELETE,UPDATE ON abc.map1 TO xyz;
 GRANT SELECT,INSERT,DELETE,UPDATE ON abc.map2 TO xyz;
 GRANT SELECT,INSERT,DELETE,UPDATE ON abc.map3 TO xyz;

My objective is to run it at one go. Can i just put in a SP and execute. Will it work in ORACLE ? I am not very sure about this. Any ideas to do it without SPs!!

CREATE or replace
PROCEDURE GRANTS AS
BEGIN
*grant permission script*
END;
qwerty
  • 11
  • 3

1 Answers1

0

You can use Dynamic SQL for this

CREATE OR REPLACE PROCEDURE GRANT_PROC AS
v_sql VARCHAR2(10000);
BEGIN
  v_sql := 'GRANT SELECT ON xyz.a_BCKUP TO abc';
  EXECUTE IMMEDIATE v_sql;
END;
Nitish
  • 1,686
  • 8
  • 23
  • 42