SET TERM !! ;

-- Conceder permissões em todas as tabelas
EXECUTE BLOCK AS
DECLARE VARIABLE tablename VARCHAR(31);
BEGIN
  FOR SELECT RDB$RELATION_NAME 
      FROM RDB$RELATIONS 
      WHERE RDB$SYSTEM_FLAG = 0 AND RDB$VIEW_BLR IS NULL
      and (RDB$RELATION_NAME like 'TB%' or RDB$RELATION_NAME like 'TMP%' or RDB$RELATION_NAME like 'VW%')
      INTO :tablename 
  DO BEGIN
    EXECUTE STATEMENT 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' || :tablename || ' TO GC;';
  END
END !!

-- Conceder permissões em todas as visões
EXECUTE BLOCK AS
DECLARE VARIABLE viewname VARCHAR(31);
BEGIN
  FOR SELECT RDB$RELATION_NAME 
      FROM RDB$RELATIONS 
      WHERE RDB$SYSTEM_FLAG = 0 AND RDB$VIEW_BLR IS NOT NULL
      INTO :viewname 
  DO BEGIN
    EXECUTE STATEMENT 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' || :viewname || ' TO GC;';
  END
END !!

-- Conceder permissões em todos os procedimentos armazenados
EXECUTE BLOCK AS
DECLARE VARIABLE procname VARCHAR(31);
BEGIN
  FOR SELECT RDB$PROCEDURE_NAME 
      FROM RDB$PROCEDURES 
      WHERE RDB$SYSTEM_FLAG = 0
      and RDB$PROCEDURE_NAME not in ( 'TRANSITIONS')
      INTO :procname
  DO BEGIN
    EXECUTE STATEMENT 'GRANT EXECUTE ON PROCEDURE ' || :procname || ' TO GC;';
  END
END !!

-- Conceder permissões em todas as funções
EXECUTE BLOCK AS
DECLARE VARIABLE funcname VARCHAR(31);
BEGIN
  FOR SELECT RDB$FUNCTION_NAME 
      FROM RDB$FUNCTIONS
      WHERE RDB$SYSTEM_FLAG = 0
      and RDB$FUNCTION_NAME not in( 'DATABASE_VERSION' )
      INTO :funcname 
  DO BEGIN
    EXECUTE STATEMENT 'GRANT EXECUTE ON FUNCTION ' || :funcname || ' TO GC;';
  END
END !!

SET TERM ; !!

COMMIT;
