Changes between Initial Version and Version 1 of Ticket #28


Ignore:
Timestamp:
Mar 20, 2010 10:43:43 PM (15 years ago)
Author:
cedric
Comment:

Legend:

Unmodified
Added
Removed
Modified

  • Ticket #28 – Description

    initial v1  
    11script sql permettant la création du schéma et le grant de toutes les autorisations 
     2= Notes pour mémoire pour Cédric = 
     3Remplacer le nom logrami  par n'importe quel autre organisme 
     4Sauvegarde et création d'un schéma vide 
     5{{{ 
     6CD C:\base 
     7C:\"Program Files"\PostgreSQL\8.4\bin\pg_dump  -U postgres -p 5433 --schema=iav BD_CAPT_NAT > sauv_schema.sql 
     8CREATE SCHEMA logrami GRANT ALL ON SCHEMA logrami; 
     9C:\"Program Files"\PostgreSQL\8.4\bin\psql -d CCM_EDA -p 5433 -U postgres -f stationsp2.sql 
     10}}} 
     11 
     12{{{ 
     13DELETE FROM  tj_caracteristiquelot_car 
     14; 
     15DELETE FROM tj_prelevementlot_prl; 
     16 
     17DELETE FROM  tj_conditionenvironnementale_env 
     18; 
     19--DELETE FROM  tr_valeurparametrequalitatif_val 
     20--; 
     21--DELETE FROM  tr_parametrequalitatif_qal 
     22--; 
     23--DELETE FROM  tr_parametrequantitatif_qan 
     24--; 
     25--DELETE FROM  tg_parametre_par 
     26--; 
     27DELETE FROM  tj_actionmarquage_act 
     28; 
     29DELETE FROM  t_marque_mqe 
     30; 
     31DELETE FROM  t_operationmarquage_omq 
     32; 
     33--DELETE FROM  tr_naturemarque_nmq 
     34--; 
     35DELETE FROM  tj_pathologieconstatee_pco 
     36; 
     37--DELETE FROM  tr_pathologie_pat 
     38--; 
     39--DELETE FROM  tr_localisationanatomique_loc 
     40--; 
     41DELETE FROM  t_lot_lot 
     42; 
     43--DELETE FROM  tr_devenirlot_dev 
     44--; 
     45DELETE FROM  tj_coefficientconversion_coe 
     46; 
     47--DELETE FROM  tr_typequantitelot_qte 
     48--; 
     49DELETE FROM  tj_tauxechappement_txe 
     50; 
     51--DELETE FROM  tr_niveauechappement_ech 
     52--; 
     53--DELETE FROM  tr_stadedeveloppement_std 
     54--; 
     55DELETE FROM  tj_dfestdestinea_dtx 
     56; 
     57--DELETE FROM  tr_taxon_tax 
     58--; 
     59--DELETE FROM  tr_niveautaxonomique_ntx 
     60; 
     61DELETE FROM  t_operation_ope 
     62; 
     63DELETE FROM  t_periodefonctdispositif_per 
     64; 
     65--DELETE FROM  tr_typearretdisp_tar 
     66--; 
     67DELETE FROM ts_taillevideo_tav; 
     68 
     69DELETE FROM  t_dispositifcomptage_dic 
     70; 
     71--DELETE FROM  tr_typedc_tdc 
     72--; 
     73DELETE FROM  tj_dfesttype_dft 
     74; 
     75--DELETE FROM  tr_typedf_tdf 
     76--; 
     77DELETE FROM  t_dispositiffranchissement_dif 
     78; 
     79 
     80DELETE FROM  tg_dispositif_dis 
     81; 
     82DELETE FROM  t_ouvrage_ouv 
     83; 
     84--DELETE FROM  tr_natureouvrage_nov 
     85--; 
     86DELETE FROM  tj_stationmesure_stm  
     87; 
     88DELETE FROM  t_station_sta  
     89; 
     90DELETE FROM  ts_description_donnee_dsc; 
     91DELETE FROM  tj_prelevementlot_prl; 
     92 
     93DELETE FROM t_bilanmigrationjournalier_bjo; 
     94DELETE FROM t_bilanmigrationmensuel_bme; 
     95 
     96ALTER SEQUENCE t_lot_lot_lot_identifiant_seq RESTART WITH 1; 
     97ALTER SEQUENCE t_operation_ope_ope_identifiant_seq RESTART WITH 1; 
     98ALTER SEQUENCE t_ouvrage_ouv_ouv_identifiant_seq RESTART WITH 1; 
     99ALTER SEQUENCE tg_dispositif_dis_dis_identifiant_seq RESTART WITH 1; 
     100ALTER SEQUENCE tj_stationmesure_stm_stm_identifiant_seq RESTART WITH 1; 
     101ALTER SEQUENCE tr_valeurparametrequalitatif_val_val_identifiant_seq RESTART WITH 1; 
     102ALTER SEQUENCE t_bilanmigrationjournalier_bjo_bjo_identifiant_seq RESTART WITH 1; 
     103ALTER SEQUENCE t_bilanmigrationmensuel_bme_bme_identifiant_seq RESTART WITH 1; 
     104 
     105 
     106CREATE ROLE logrami LOGIN NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE; 
     107GRANT ALL ON SCHEMA logrami TO logrami; 
     108GRANT SELECT,INSERT,UPDATE ON logrami.t_station_sta TO logrami; 
     109GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.t_bilanmigrationjournalier_bjo TO logrami; 
     110GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.t_bilanmigrationmensuel_bme TO logrami; 
     111GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.tj_caracteristiquelot_car TO logrami; 
     112GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.tj_conditionenvironnementale_env TO logrami; 
     113GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.tj_stationmesure_stm TO logrami; 
     114GRANT SELECT ON ref.tr_valeurparametrequalitatif_val TO logrami; 
     115GRANT SELECT ON ref.tr_parametrequalitatif_qal TO logrami; 
     116GRANT SELECT ON ref.tr_parametrequantitatif_qan TO logrami; 
     117GRANT SELECT ON ref.tg_parametre_par TO logrami; 
     118GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.tj_dfesttype_dft TO logrami; 
     119GRANT SELECT ON ref.tr_typedf_tdf TO logrami; 
     120GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.tj_dfestdestinea_dtx TO logrami; 
     121GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.tj_tauxechappement_txe TO logrami; 
     122GRANT SELECT ON ref.tr_niveauechappement_ech TO logrami; 
     123GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.tj_coefficientconversion_coe TO logrami; 
     124GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.tj_pathologieconstatee_pco TO logrami; 
     125GRANT SELECT ON ref.tr_pathologie_pat TO logrami; 
     126GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.tj_actionmarquage_act TO logrami; 
     127GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.t_marque_mqe TO logrami; 
     128GRANT SELECT ON ref.tr_localisationanatomique_loc TO logrami; 
     129GRANT SELECT ON ref.tr_naturemarque_nmq TO logrami; 
     130GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.t_operationmarquage_omq TO logrami; 
     131GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.t_lot_lot TO logrami; 
     132GRANT SELECT ON ref.tr_devenirlot_dev TO logrami; 
     133GRANT SELECT ON ref.tr_typequantitelot_qte TO logrami; 
     134GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.ts_taxonvideo_txv TO logrami; 
     135GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.ts_taillevideo_tav TO logrami; 
     136GRANT SELECT ON ref.tr_stadedeveloppement_std TO logrami; 
     137GRANT SELECT ON ref.tr_taxon_tax TO logrami; 
     138GRANT SELECT ON ref.tr_niveautaxonomique_ntx TO logrami; 
     139GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.t_operation_ope TO logrami; 
     140GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.t_periodefonctdispositif_per TO logrami; 
     141GRANT SELECT ON ref.tr_typearretdisp_tar TO logrami; 
     142GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.t_dispositifcomptage_dic TO logrami; 
     143GRANT SELECT ON ref.tr_typedc_tdc TO logrami; 
     144GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.t_dispositiffranchissement_dif TO logrami; 
     145GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.tg_dispositif_dis TO logrami; 
     146GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.t_ouvrage_ouv TO logrami; 
     147GRANT SELECT ON ref.tr_natureouvrage_nov TO logrami; 
     148GRANT SELECT ON ref.tr_prelevement_pre TO logrami; 
     149GRANT SELECT,INSERT,UPDATE,DELETE ON ref.ts_sequence_seq TO logrami; 
     150GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.tj_stationmesure_stm TO logrami; 
     151GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.tj_prelevementlot_prl TO logrami; 
     152GRANT SELECT,INSERT,UPDATE,DELETE ON logrami.t_marque_mqe TO logrami; 
     153 
     154GRANT SELECT, UPDATE ON logrami.tg_dispositif_dis_dis_identifiant_seq TO logrami; 
     155GRANT SELECT, UPDATE ON logrami.t_lot_lot_lot_identifiant_seq TO logrami; 
     156GRANT SELECT, UPDATE ON logrami.t_operation_ope_ope_identifiant_seq TO logrami; 
     157GRANT SELECT, UPDATE ON logrami.t_ouvrage_ouv_ouv_identifiant_seq TO logrami; 
     158GRANT SELECT, UPDATE ON logrami.tj_stationmesure_stm_stm_identifiant_seq TO logrami; 
     159GRANT SELECT, UPDATE ON logrami.t_bilanmigrationmensuel_bme_bme_identifiant_seq TO logrami; 
     160GRANT SELECT, UPDATE ON logrami.t_bilanmigrationjournalier_bjo_bjo_identifiant_seq TO logrami; 
     161 
     162GRANT SELECT ON ref.ts_sequence_seq TO logrami; 
     163GRANT SELECT ON TABLE ref.ts_sequence_seq TO invite; 
     164 
     165-- vues 
     166GRANT SELECT, UPDATE, INSERT ON TABLE logrami.v_taxon_tax TO logrami; 
     167GRANT SELECT, UPDATE, INSERT ON TABLE logrami.vue_lot_ope_car TO logrami; 
     168GRANT SELECT, UPDATE, INSERT ON TABLE logrami.vue_lot_ope_car_qan TO logrami; 
     169GRANT SELECT, UPDATE, INSERT ON TABLE logrami.vue_ope_lot_ech_parqual TO logrami; 
     170GRANT SELECT, UPDATE, INSERT ON TABLE logrami.vue_ope_lot_ech_parquan TO logrami; 
     171 
     172 
     173-- GRANT select to invite for logrami 
     174GRANT SELECT ON logrami.t_station_sta TO invite; 
     175GRANT SELECT ON logrami.t_bilanmigrationjournalier_bjo TO invite; 
     176GRANT SELECT ON logrami.t_bilanmigrationmensuel_bme TO invite; 
     177GRANT SELECT ON logrami.tj_caracteristiquelot_car TO invite; 
     178GRANT SELECT ON logrami.tj_conditionenvironnementale_env TO invite; 
     179GRANT SELECT ON logrami.tj_stationmesure_stm TO invite; 
     180GRANT SELECT ON logrami.tj_dfesttype_dft TO invite; 
     181GRANT SELECT ON logrami.tj_dfestdestinea_dtx TO invite; 
     182GRANT SELECT ON logrami.tj_tauxechappement_txe TO invite; 
     183GRANT SELECT ON logrami.tj_coefficientconversion_coe TO invite; 
     184GRANT SELECT ON logrami.tj_pathologieconstatee_pco TO invite; 
     185GRANT SELECT ON logrami.tj_actionmarquage_act TO invite; 
     186GRANT SELECT ON logrami.t_marque_mqe TO invite; 
     187GRANT SELECT ON logrami.t_operationmarquage_omq TO invite; 
     188GRANT SELECT ON logrami.t_lot_lot TO invite; 
     189GRANT SELECT ON logrami.ts_taxonvideo_txv TO invite; 
     190GRANT SELECT ON logrami.ts_taillevideo_tav TO invite; 
     191GRANT SELECT ON logrami.t_operation_ope TO invite; 
     192GRANT SELECT ON logrami.t_periodefonctdispositif_per TO invite; 
     193GRANT SELECT ON logrami.t_dispositifcomptage_dic TO invite; 
     194GRANT SELECT ON logrami.t_dispositiffranchissement_dif TO invite; 
     195GRANT SELECT ON logrami.tg_dispositif_dis TO invite; 
     196GRANT SELECT ON logrami.t_ouvrage_ouv TO invite; 
     197GRANT SELECT ON logrami.t_station_sta TO invite; 
     198GRANT SELECT ON logrami.tj_stationmesure_stm TO invite; 
     199GRANT SELECT ON logrami.tj_prelevementlot_prl TO invite; 
     200GRANT SELECT ON logrami.t_marque_mqe TO invite; 
     201--vues 
     202GRANT SELECT ON logrami.v_taxon_tax TO invite; 
     203GRANT SELECT ON logrami.vue_lot_ope_car TO invite; 
     204GRANT SELECT ON logrami.vue_lot_ope_car_qan TO invite; 
     205GRANT SELECT ON logrami.vue_ope_lot_ech_parqual TO invite; 
     206GRANT SELECT ON logrami.vue_ope_lot_ech_parquan TO invite; 
     207 
     208 
     209GRANT SELECT, UPDATE ON logrami.tg_dispositif_dis_dis_identifiant_seq TO invite; 
     210GRANT SELECT, UPDATE ON logrami.t_lot_lot_lot_identifiant_seq TO invite; 
     211GRANT SELECT, UPDATE ON logrami.t_operation_ope_ope_identifiant_seq TO invite; 
     212GRANT SELECT, UPDATE ON logrami.t_ouvrage_ouv_ouv_identifiant_seq TO invite; 
     213GRANT SELECT, UPDATE ON logrami.tj_stationmesure_stm_stm_identifiant_seq TO invite; 
     214GRANT SELECT, UPDATE ON logrami.t_bilanmigrationmensuel_bme_bme_identifiant_seq TO invite; 
     215GRANT SELECT, UPDATE ON logrami.t_bilanmigrationjournalier_bjo_bjo_identifiant_seq TO invite;