kamailio/utils/kamctl/oracle/inc/_grantfunc.tmpl

BEGIN
  FOR cur IN (SELECT OBJECT_NAME FROM All_Procedures
	WHERE owner=UPPER('%DBROOTUSER%') and object_name <> 'MAP2USERS') LOOP
    EXECUTE IMMEDIATE('GRANT EXECUTE ON %DBROOTUSER%.'||cur.OBJECT_NAME
	||' to %DBRWUSER%');
    EXECUTE IMMEDIATE('GRANT EXECUTE ON %DBROOTUSER%.'||cur.OBJECT_NAME
	||' to %DBROUSER%');
    EXECUTE IMMEDIATE('CREATE OR REPLACE SYNONYM %DBRWUSER%.'||cur.OBJECT_NAME
	||' for %DBROOTUSER%.'||cur.OBJECT_NAME);
    EXECUTE IMMEDIATE('CREATE OR REPLACE SYNONYM %DBROUSER%.'||cur.OBJECT_NAME
	||' for %DBROOTUSER%.'||cur.OBJECT_NAME);
  END LOOP;
END;
/

CREATE OR REPLACE PROCEDURE map2users(tbl varchar2) is
BEGIN
  EXECUTE IMMEDIATE('GRANT SELECT ON '||tbl||' to %DBROUSER%');
  EXECUTE IMMEDIATE('GRANT SELECT,INSERT,UPDATE,DELETE ON '||tbl
    ||' to %DBRWUSER%');
  EXECUTE IMMEDIATE('CREATE OR REPLACE SYNONYM %DBROUSER%.'||tbl
    ||' for %DBROOTUSER%.'||tbl);
  EXECUTE IMMEDIATE('CREATE OR REPLACE SYNONYM %DBRWUSER%.'||tbl
    ||' for %DBROOTUSER%.'||tbl);
END map2users;
/