BDD_project/role.sql at master · ojbruce/BDD_project · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
/**
 * Projet BDD
 * Base de donnée de la NSA
 *
 * @author : Olivia Bruce
 * @author : Matthieu Riou
 * @author : Brice Thomas
 */

/**
  * Role pour la NSA
  * Role attribue aux membres de la NSA
  *
  **/
CREATE ROLE NSA;

GRANT all on Personne to NSA;
GRANT all on ENTREPRISE to NSA;
GRANT all on EMPLOYE to NSA;
GRANT all on PRIORITAIRE to NSA;
GRANT all on RELATION to NSA;
GRANT all on MAIL to NSA;
GRANT all on COMPTEBANCAIRE to NSA;
GRANT all on CONVERSATION to NSA;
GRANT all on TELEPHONE to NSA;

GRANT NSA to L3_19;
--GRANT NSA to L3_18;


/**
  * Role pour la police
  * Possibilite de regarder uniquement sur une vue
  **/
CREATE ROLE FBI;
GRANT select on fbi_view to FBI;


/**
 * Role pour les fournisseurs d'accès internet nos partenaires
 * Acces complet à la vue correspondante.
 * Free acces a mail_free_view et telephone_free_view
 * Orange acces a mail_orange_view et telephone_orange_view
 * Laposte acces a mail
 * SFR acces a telephone
 * Bouygues acces a telephone
 * Numericable telephone
 */

CREATE ROLE FREE;
GRANT all on mail_free_view to FREE;
GRANT all on telephone_free_view to FREE;

CREATE ROLE ORANGE;
GRANT all on mail_orange_view to ORANGE;
GRANT all on telephone_orange_view to ORANGE;

CREATE ROLE LAPOSTE;
GRANT all on mail_laposte_view to LAPOSTE;

CREATE ROLE SFR;
GRANT all on telephone_sfr_view to SFR;

CREATE ROLE BOUYGUES;
GRANT all on telephone_bouygues_view to BOUYGUES;

CREATE ROLE NUMERICABLE;
GRANT all on telephone_numericable_view to NUMERICABLE;