-- grants.sql
--
-- Skript zur Erzeugung der Read-Only-Rechte auf den Tabellen der e_prtr Datenbank
-- für den sicheren Betrieb der e_prtr Datenbank bei der BLE
--
-- Erstellt durch die ENDA GmbH & Co. KG, 2010
--
-- Rev. 6, 2013-10-24, tba: Entfernung von x_-Tabellen
-- Rev. 5, 2013-05-16, rmi: Ergänzug l_wrrl_stoffe
-- Rev. 4, 2011-04-15, mlt: Ergänzung von k_-Tabellen und den 2009er Jahrestabellen
-- Rev. 3, 2010-05-27, mlt: Ergänzung von x_- und k_-Tabellen
-- Rev. 2, 2010-04-08, mlt: Ergänzung der Rechte für MicroStrategy Views und restliche Tabellen
-- Rev. 1, 2010-04-08, mlt: Ergänzung fehlender Fachtabellen, 2008er Suchtabellen, Sortierung der Listentabellen
-- Rev. 0, 2010-04-08, mlt: Erstellung aus SQL Strukturdump der BLE Produktivdatenbank

GRANT SELECT ON TABLE p_betrieb TO e_prtr_ro;
GRANT SELECT ON TABLE p_betrieb_geheimk TO e_prtr_ro;
GRANT SELECT ON TABLE p_betrieb_vorgaenger TO e_prtr_ro;
GRANT SELECT ON TABLE p_taet TO e_prtr_ro;
GRANT SELECT ON TABLE p_taet_geheimk TO e_prtr_ro;
GRANT SELECT ON TABLE p_freis TO e_prtr_ro;
GRANT SELECT ON TABLE p_freis_geheimk TO e_prtr_ro;
GRANT SELECT ON TABLE p_vabw TO e_prtr_ro;
GRANT SELECT ON TABLE p_vabw_geheimk TO e_prtr_ro;
GRANT SELECT ON TABLE p_vabf TO e_prtr_ro;
GRANT SELECT ON TABLE p_vabf_geheimk TO e_prtr_ro;

GRANT SELECT ON TABLE l_abfalltypen TO e_prtr_ro;
GRANT SELECT ON TABLE l_bearbeitungsstatus TO e_prtr_ro;
GRANT SELECT ON TABLE l_behoerden TO e_prtr_ro;
GRANT SELECT ON TABLE l_bestimmungsverfahren TO e_prtr_ro;
GRANT SELECT ON TABLE l_bestimmungsverfahren_abfall TO e_prtr_ro;
GRANT SELECT ON TABLE l_bestimmungsmethoden_ermittlungsarten TO e_prtr_ro;
GRANT SELECT ON TABLE l_bundeslaender TO e_prtr_ro;
GRANT SELECT ON TABLE l_disposition_abfall TO e_prtr_ro;
GRANT SELECT ON TABLE l_flusseinzugsgebiete TO e_prtr_ro;
GRANT SELECT ON TABLE l_geheimhaltungsvorschriften TO e_prtr_ro;
GRANT SELECT ON TABLE l_gemeinden TO e_prtr_ro;
GRANT SELECT ON TABLE l_gewaesser TO e_prtr_ro;
GRANT SELECT ON TABLE l_geodaetische_systeme TO e_prtr_ro;
GRANT SELECT ON TABLE l_glossar TO e_prtr_ro;
GRANT SELECT ON TABLE l_ia TO e_prtr_ro;
GRANT SELECT ON TABLE l_kompartimente TO e_prtr_ro;
GRANT SELECT ON TABLE l_nace_abschnitt TO e_prtr_ro;
GRANT SELECT ON TABLE l_nace_wirtschaftszweige TO e_prtr_ro;
GRANT SELECT ON TABLE l_prtr_branchengruppen TO e_prtr_ro;
GRANT SELECT ON TABLE l_pruefstati TO e_prtr_ro;
GRANT SELECT ON TABLE l_pruefstati_geheimhaltung TO e_prtr_ro;
GRANT SELECT ON TABLE l_schadstoffgruppen TO e_prtr_ro;
GRANT SELECT ON TABLE l_schutzgruende TO e_prtr_ro;
GRANT SELECT ON TABLE l_schwellenwertueberschreitungsarten TO e_prtr_ro;
GRANT SELECT ON TABLE l_staaten TO e_prtr_ro;
GRANT SELECT ON TABLE l_stoffe TO e_prtr_ro;
GRANT SELECT ON TABLE l_taetigkeiten_anhang1_eprtr TO e_prtr_ro;
GRANT SELECT ON TABLE l_taetigkeiten_anhang1_ivu TO e_prtr_ro;
GRANT SELECT ON TABLE l_wrrl_stoffe TO e_prtr_ro;
GRANT SELECT ON TABLE s_l_stoffe TO e_prtr_ro;

GRANT SELECT ON TABLE releases TO e_prtr_ro;
GRANT SELECT ON TABLE translationlist TO e_prtr_ro;
GRANT SELECT ON TABLE geometry_columns TO e_prtr_ro;
GRANT SELECT ON TABLE spatial_ref_sys TO e_prtr_ro;

GRANT SELECT ON TABLE demo TO e_prtr_ro;
GRANT SELECT ON TABLE schema_migrations TO e_prtr_ro;

GRANT SELECT ON TABLE k_abfall_empfaengerstaat TO e_prtr_ro;
GRANT SELECT ON TABLE k_suppressoren TO e_prtr_ro;
GRANT SELECT ON TABLE k_bvcode_id TO e_prtr_ro;
GRANT SELECT ON TABLE k_laender_bleiben_so TO e_prtr_ro;
GRANT SELECT ON TABLE k_laender_fuer_aktualisierung TO e_prtr_ro;

GRANT SELECT ON TABLE ble_releases TO e_prtr_ro;
GRANT SELECT ON TABLE abfallmengen TO e_prtr_ro;

-- MicroStrategy Leserechte vergeben:

GRANT SELECT ON TABLE v_ms_betrieb TO microstrategy_ro;
GRANT SELECT ON TABLE v_ms_p_freis TO microstrategy_ro;
GRANT SELECT ON TABLE v_ms_p_taet TO microstrategy_ro;
GRANT SELECT ON TABLE v_ms_p_vabw TO microstrategy_ro;
GRANT SELECT ON TABLE v_ms_year TO microstrategy_ro;

GRANT SELECT ON TABLE p_betrieb TO microstrategy_ro;
GRANT SELECT ON TABLE p_betrieb_geheimk TO microstrategy_ro;
GRANT SELECT ON TABLE p_betrieb_vorgaenger TO microstrategy_ro;
GRANT SELECT ON TABLE p_taet TO microstrategy_ro;
GRANT SELECT ON TABLE p_taet_geheimk TO microstrategy_ro;
GRANT SELECT ON TABLE p_freis TO microstrategy_ro;
GRANT SELECT ON TABLE p_freis_geheimk TO microstrategy_ro;
GRANT SELECT ON TABLE p_vabw TO microstrategy_ro;
GRANT SELECT ON TABLE p_vabw_geheimk TO microstrategy_ro;
GRANT SELECT ON TABLE p_vabf TO microstrategy_ro;
GRANT SELECT ON TABLE p_vabf_geheimk TO microstrategy_ro;

GRANT SELECT ON TABLE l_abfalltypen TO microstrategy_ro;
GRANT SELECT ON TABLE l_bearbeitungsstatus TO microstrategy_ro;
GRANT SELECT ON TABLE l_behoerden TO microstrategy_ro;
GRANT SELECT ON TABLE l_bestimmungsverfahren TO microstrategy_ro;
GRANT SELECT ON TABLE l_bestimmungsverfahren_abfall TO microstrategy_ro;
GRANT SELECT ON TABLE l_bestimmungsmethoden_ermittlungsarten TO microstrategy_ro;
GRANT SELECT ON TABLE l_bundeslaender TO microstrategy_ro;
GRANT SELECT ON TABLE l_disposition_abfall TO microstrategy_ro;
GRANT SELECT ON TABLE l_flusseinzugsgebiete TO microstrategy_ro;
GRANT SELECT ON TABLE l_geheimhaltungsvorschriften TO microstrategy_ro;
GRANT SELECT ON TABLE l_gemeinden TO microstrategy_ro;
GRANT SELECT ON TABLE l_gewaesser TO microstrategy_ro;
GRANT SELECT ON TABLE l_geodaetische_systeme TO microstrategy_ro;
GRANT SELECT ON TABLE l_glossar TO microstrategy_ro;
GRANT SELECT ON TABLE l_ia TO microstrategy_ro;
GRANT SELECT ON TABLE l_kompartimente TO microstrategy_ro;
GRANT SELECT ON TABLE l_nace_abschnitt TO microstrategy_ro;
GRANT SELECT ON TABLE l_nace_wirtschaftszweige TO microstrategy_ro;
GRANT SELECT ON TABLE l_prtr_branchengruppen TO microstrategy_ro;
GRANT SELECT ON TABLE l_pruefstati TO microstrategy_ro;
GRANT SELECT ON TABLE l_pruefstati_geheimhaltung TO microstrategy_ro;
GRANT SELECT ON TABLE l_schadstoffgruppen TO microstrategy_ro;
GRANT SELECT ON TABLE l_schutzgruende TO microstrategy_ro;
GRANT SELECT ON TABLE l_schwellenwertueberschreitungsarten TO microstrategy_ro;
GRANT SELECT ON TABLE l_staaten TO microstrategy_ro;
GRANT SELECT ON TABLE l_stoffe TO microstrategy_ro;
GRANT SELECT ON TABLE l_taetigkeiten_anhang1_eprtr TO microstrategy_ro;
GRANT SELECT ON TABLE l_taetigkeiten_anhang1_ivu TO microstrategy_ro;
GRANT SELECT ON TABLE l_wrrl_stoffe TO microstrategy_ro;
GRANT SELECT ON TABLE s_l_stoffe TO microstrategy_ro;

GRANT SELECT ON TABLE releases TO microstrategy_ro;
GRANT SELECT ON TABLE translationlist TO microstrategy_ro;
GRANT SELECT ON TABLE geometry_columns TO microstrategy_ro;
GRANT SELECT ON TABLE spatial_ref_sys TO microstrategy_ro;

GRANT SELECT ON TABLE demo TO microstrategy_ro;
GRANT SELECT ON TABLE schema_migrations TO microstrategy_ro;

GRANT SELECT ON TABLE k_abfall_empfaengerstaat TO microstrategy_ro;
GRANT SELECT ON TABLE k_suppressoren TO microstrategy_ro;
GRANT SELECT ON TABLE k_bvcode_id TO microstrategy_ro;
GRANT SELECT ON TABLE k_laender_bleiben_so TO microstrategy_ro;
GRANT SELECT ON TABLE k_laender_fuer_aktualisierung TO microstrategy_ro;

GRANT SELECT ON TABLE ble_releases TO microstrategy_ro;
GRANT SELECT ON TABLE abfallmengen TO microstrategy_ro;
