construire base de donnée commune à tous les utilisateurs

[cedric]

script sql permettant la création du schéma et le grant de toutes les autorisations

Notes pour mémoire pour Cédric

Remplacer le nom logrami par n'importe quel autre organisme
Sauvegarde et création d'un schéma vide

CD C:\base
C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5433 --schema=iav BD_CONTMIG_NAT > sauv_schema.sql
CREATE SCHEMA logrami GRANT ALL ON SCHEMA logrami;
C:\"Program Files"\PostgreSQL\8.4\bin\psql -d CCM_EDA -p 5433 -U postgres -f sauv_schema.sql

DELETE FROM  tj_caracteristiquelot_car
;
DELETE FROM tj_prelevementlot_prl;

DELETE FROM  tj_conditionenvironnementale_env
;
--DELETE FROM  tr_valeurparametrequalitatif_val
--;
--DELETE FROM  tr_parametrequalitatif_qal
--;
--DELETE FROM  tr_parametrequantitatif_qan
--;
--DELETE FROM  tg_parametre_par
--;
DELETE FROM  tj_actionmarquage_act
;
DELETE FROM  t_marque_mqe
;
DELETE FROM  t_operationmarquage_omq
;
--DELETE FROM  tr_naturemarque_nmq
--;
DELETE FROM  tj_pathologieconstatee_pco
;
--DELETE FROM  tr_pathologie_pat
--;
--DELETE FROM  tr_localisationanatomique_loc
--;
DELETE FROM  t_lot_lot
;
--DELETE FROM  tr_devenirlot_dev
--;
DELETE FROM  tj_coefficientconversion_coe
;
--DELETE FROM  tr_typequantitelot_qte
--;
DELETE FROM  tj_tauxechappement_txe
;
--DELETE FROM  tr_niveauechappement_ech
--;
--DELETE FROM  tr_stadedeveloppement_std
--;
DELETE FROM  tj_dfestdestinea_dtx
;
--DELETE FROM  tr_taxon_tax
--;
--DELETE FROM  tr_niveautaxonomique_ntx
;
DELETE FROM  t_operation_ope
;
DELETE FROM  t_periodefonctdispositif_per
;
--DELETE FROM  tr_typearretdisp_tar
--;
DELETE FROM ts_taillevideo_tav;

DELETE FROM  t_dispositifcomptage_dic
;
--DELETE FROM  tr_typedc_tdc
--;
DELETE FROM  tj_dfesttype_dft
;
--DELETE FROM  tr_typedf_tdf
--;
DELETE FROM  t_dispositiffranchissement_dif
;

DELETE FROM  tg_dispositif_dis
;
DELETE FROM  t_ouvrage_ouv
;
--DELETE FROM  tr_natureouvrage_nov
--;
DELETE FROM  tj_stationmesure_stm 
;
DELETE FROM  t_station_sta 
;
DELETE FROM  ts_description_donnee_dsc;
DELETE FROM  tj_prelevementlot_prl;

DELETE FROM t_bilanmigrationjournalier_bjo;
DELETE FROM t_bilanmigrationmensuel_bme;

ALTER SEQUENCE t_lot_lot_lot_identifiant_seq RESTART WITH 1;
ALTER SEQUENCE t_operation_ope_ope_identifiant_seq RESTART WITH 1;
ALTER SEQUENCE t_ouvrage_ouv_ouv_identifiant_seq RESTART WITH 1;
ALTER SEQUENCE tg_dispositif_dis_dis_identifiant_seq RESTART WITH 1;
ALTER SEQUENCE tj_stationmesure_stm_stm_identifiant_seq RESTART WITH 1;
ALTER SEQUENCE tr_valeurparametrequalitatif_val_val_identifiant_seq RESTART WITH 1;
ALTER SEQUENCE t_bilanmigrationjournalier_bjo_bjo_identifiant_seq RESTART WITH 1;
ALTER SEQUENCE t_bilanmigrationmensuel_bme_bme_identifiant_seq RESTART WITH 1;


CREATE ROLE logrami LOGIN NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE;
GRANT ALL ON SCHEMA logrami TO logrami;
GRANT SELECT,INSERT,UPDATE ON logrami.t_station_sta TO logrami;
GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.t_bilanmigrationjournalier_bjo TO logrami;
GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.t_bilanmigrationmensuel_bme TO logrami;
GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.tj_caracteristiquelot_car TO logrami;
GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.tj_conditionenvironnementale_env TO logrami;
GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.tj_stationmesure_stm TO logrami;
GRANT SELECT ON ref.tr_valeurparametrequalitatif_val TO logrami;
GRANT SELECT ON ref.tr_parametrequalitatif_qal TO logrami;
GRANT SELECT ON ref.tr_parametrequantitatif_qan TO logrami;
GRANT SELECT ON ref.tg_parametre_par TO logrami;
GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.tj_dfesttype_dft TO logrami;
GRANT SELECT ON ref.tr_typedf_tdf TO logrami;
GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.tj_dfestdestinea_dtx TO logrami;
GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.tj_tauxechappement_txe TO logrami;
GRANT SELECT ON ref.tr_niveauechappement_ech TO logrami;
GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.tj_coefficientconversion_coe TO logrami;
GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.tj_pathologieconstatee_pco TO logrami;
GRANT SELECT ON ref.tr_pathologie_pat TO logrami;
GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.tj_actionmarquage_act TO logrami;
GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.t_marque_mqe TO logrami;
GRANT SELECT ON ref.tr_localisationanatomique_loc TO logrami;
GRANT SELECT ON ref.tr_naturemarque_nmq TO logrami;
GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.t_operationmarquage_omq TO logrami;
GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.t_lot_lot TO logrami;
GRANT SELECT ON ref.tr_devenirlot_dev TO logrami;
GRANT SELECT ON ref.tr_typequantitelot_qte TO logrami;
GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.ts_taxonvideo_txv TO logrami;
GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.ts_taillevideo_tav TO logrami;
GRANT SELECT ON ref.tr_stadedeveloppement_std TO logrami;
GRANT SELECT ON ref.tr_taxon_tax TO logrami;
GRANT SELECT ON ref.tr_niveautaxonomique_ntx TO logrami;
GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.t_operation_ope TO logrami;
GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.t_periodefonctdispositif_per TO logrami;
GRANT SELECT ON ref.tr_typearretdisp_tar TO logrami;
GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.t_dispositifcomptage_dic TO logrami;
GRANT SELECT ON ref.tr_typedc_tdc TO logrami;
GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.t_dispositiffranchissement_dif TO logrami;
GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.tg_dispositif_dis TO logrami;
GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.t_ouvrage_ouv TO logrami;
GRANT SELECT ON ref.tr_natureouvrage_nov TO logrami;
GRANT SELECT ON ref.tr_prelevement_pre TO logrami;
GRANT SELECT,INSERT,UPDATE,DELETE ON ref.ts_sequence_seq TO logrami;
GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.tj_stationmesure_stm TO logrami;
GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.tj_prelevementlot_prl TO logrami;
GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.t_marque_mqe TO logrami;

GRANT SELECT, UPDATE ON logrami.tg_dispositif_dis_dis_identifiant_seq TO logrami;
GRANT SELECT, UPDATE ON logrami.t_lot_lot_lot_identifiant_seq TO logrami;
GRANT SELECT, UPDATE ON logrami.t_operation_ope_ope_identifiant_seq TO logrami;
GRANT SELECT, UPDATE ON logrami.t_ouvrage_ouv_ouv_identifiant_seq TO logrami;
GRANT SELECT, UPDATE ON logrami.tj_stationmesure_stm_stm_identifiant_seq TO logrami;
GRANT SELECT, UPDATE ON logrami.t_bilanmigrationmensuel_bme_bme_identifiant_seq TO logrami;
GRANT SELECT, UPDATE ON logrami.t_bilanmigrationjournalier_bjo_bjo_identifiant_seq TO logrami;

GRANT SELECT ON ref.ts_sequence_seq TO logrami;
GRANT SELECT ON TABLE ref.ts_sequence_seq TO invite;

-- vues
GRANT SELECT, UPDATE, INSERT ON TABLE logrami.v_taxon_tax TO logrami;
GRANT SELECT, UPDATE, INSERT ON TABLE logrami.vue_lot_ope_car TO logrami;
GRANT SELECT, UPDATE, INSERT ON TABLE logrami.vue_lot_ope_car_qan TO logrami;
GRANT SELECT, UPDATE, INSERT ON TABLE logrami.vue_ope_lot_ech_parqual TO logrami;
GRANT SELECT, UPDATE, INSERT ON TABLE logrami.vue_ope_lot_ech_parquan TO logrami;


-- GRANT select to invite for logrami
GRANT SELECT ON logrami.t_station_sta TO invite;
GRANT SELECT ON logrami.t_bilanmigrationjournalier_bjo TO invite;
GRANT SELECT ON logrami.t_bilanmigrationmensuel_bme TO invite;
GRANT SELECT ON logrami.tj_caracteristiquelot_car TO invite;
GRANT SELECT ON logrami.tj_conditionenvironnementale_env TO invite;
GRANT SELECT ON logrami.tj_stationmesure_stm TO invite;
GRANT SELECT ON logrami.tj_dfesttype_dft TO invite;
GRANT SELECT ON logrami.tj_dfestdestinea_dtx TO invite;
GRANT SELECT ON logrami.tj_tauxechappement_txe TO invite;
GRANT SELECT ON logrami.tj_coefficientconversion_coe TO invite;
GRANT SELECT ON logrami.tj_pathologieconstatee_pco TO invite;
GRANT SELECT ON logrami.tj_actionmarquage_act TO invite;
GRANT SELECT ON logrami.t_marque_mqe TO invite;
GRANT SELECT ON logrami.t_operationmarquage_omq TO invite;
GRANT SELECT ON logrami.t_lot_lot TO invite;
GRANT SELECT ON logrami.ts_taxonvideo_txv TO invite;
GRANT SELECT ON logrami.ts_taillevideo_tav TO invite;
GRANT SELECT ON logrami.t_operation_ope TO invite;
GRANT SELECT ON logrami.t_periodefonctdispositif_per TO invite;
GRANT SELECT ON logrami.t_dispositifcomptage_dic TO invite;
GRANT SELECT ON logrami.t_dispositiffranchissement_dif TO invite;
GRANT SELECT ON logrami.tg_dispositif_dis TO invite;
GRANT SELECT ON logrami.t_ouvrage_ouv TO invite;
GRANT SELECT ON logrami.t_station_sta TO invite;
GRANT SELECT ON logrami.tj_stationmesure_stm TO invite;
GRANT SELECT ON logrami.tj_prelevementlot_prl TO invite;
GRANT SELECT ON logrami.t_marque_mqe TO invite;
--vues
GRANT SELECT ON logrami.v_taxon_tax TO invite;
GRANT SELECT ON logrami.vue_lot_ope_car TO invite;
GRANT SELECT ON logrami.vue_lot_ope_car_qan TO invite;
GRANT SELECT ON logrami.vue_ope_lot_ech_parqual TO invite;
GRANT SELECT ON logrami.vue_ope_lot_ech_parquan TO invite;


GRANT SELECT, UPDATE ON logrami.tg_dispositif_dis_dis_identifiant_seq TO invite;
GRANT SELECT, UPDATE ON logrami.t_lot_lot_lot_identifiant_seq TO invite;
GRANT SELECT, UPDATE ON logrami.t_operation_ope_ope_identifiant_seq TO invite;
GRANT SELECT, UPDATE ON logrami.t_ouvrage_ouv_ouv_identifiant_seq TO invite;
GRANT SELECT, UPDATE ON logrami.tj_stationmesure_stm_stm_identifiant_seq TO invite;
GRANT SELECT, UPDATE ON logrami.t_bilanmigrationmensuel_bme_bme_identifiant_seq TO invite;
GRANT SELECT, UPDATE ON logrami.t_bilanmigrationjournalier_bjo_bjo_identifiant_seq TO invite;