TABLE : UTILISATEURS
Requête de création de la table :
CREATE TABLE UTILISATEURS (IDENT_PERS INTEGER
,CODEACCES CHAR(8)
, NOM VARCHAR(50)
, PRENOM VARCHAR(50)
, SERVICE VARCHAR(40), 
DATE_CREAT DATE
, ACTIF CHAR(1));

Requêtes d'insertion des données :
INSERT INTO UTILISATEURS VALUES 
(1,'ASMITH','SMITH','ALAN','COURRIER','03/04/2022','O'), 
(2,'GPEROC','PEROC','GEORGES','COMMERCIAL','04/05/2016','O'),
(3,'MBALTOP','BALTOP','MICHELE','DIRECTION','01/02/2018','O'), 
(4,'SVERSACE','VERSACE','SANDRINE','INFORMATIQUE DEV','05/02/2019','O'),
(5,'XMARTIN','MARTIN','XAVIER','CONTROLE DE GESTION','06/11/2017','O'),
(6,'RSANCHEZ','SANCHEZ','ROBERTO','FACTURATION','08/09/2021','O'),
(7,'MSEGAFRE','SEGAFREDI','MANUELLA','ADMINISTRATEUR','15/05/2025','O');

INSERT INTO UTILISATEURS VALUES 
(1,'ASMITH','SMITH','ALAN','COURRIER','03/04/2022','O'); 
INSERT INTO UTILISATEURS VALUES 
(2,'GPEROC','PEROC','GEORGES','COMMERCIAL','04/05/2016','O'); 
INSERT INTO UTILISATEURS VALUES 
(3,'MBALTOP','BALTOP','MICHELE','DIRECTION','01/02/2018','O'); 
INSERT INTO UTILISATEURS VALUES 
(4,'SVERSACE','VERSACE','SANDRINE','INFORMATIQUE DEV','05/02/2019','O'); 
INSERT INTO UTILISATEURS VALUES 
(5,'XMARTIN','MARTIN','XAVIER','CONTROLE DE GESTION','06/11/2017','O'); 
INSERT INTO UTILISATEURS VALUES 
(6,'RSANCHEZ','SANCHEZ','ROBERTO','FACTURATION','08/09/2021','O');
INSERT INTO UTILISATEURS VALUES 
(7,'MSEGAFRE','SEGAFREDI','MANUELLA','ADMINISTRATEUR','15/05/2025','O');

INSERT INTO UTILISATEURS VALUES 
(1,'ASMITH','SMITH','ALAN','COURRIER',TO_DATE('03/04/2022','DD/MM/ 
YYYY'),'O'); 
INSERT INTO UTILISATEURS VALUES 
(2,'GPEROC','PEROC','GEORGES','COMMERCIAL',TO_DATE('04/05/2016', 
'DD/MM/YYYY'),'O'); 
INSERT INTO UTILISATEURS VALUES 
(3,'MBALTOP','BALTOP','MICHELE','DIRECTION',TO_DATE('01/02/2018', 
'DD/MM/YYYY'),'O'); 
INSERT INTO UTILISATEURS VALUES 
(4,'SVERSACE','VERSACE','SANDRINE','INFORMATIQUE 
DEV',TO_DATE('05/02/2019','DD/MM/YYYY'),'O'); 
INSERT INTO UTILISATEURS VALUES 
(5,'XMARTIN','MARTIN','XAVIER','CONTROLE DE 
GESTION',TO_DATE('06/11/2017','DD/MM/YYYY'),'O'); 
INSERT INTO UTILISATEURS VALUES 
(6,'RSANCHEZ','SANCHEZ','ROBERTO','FACTURATION',TO_DATE('08/09/2021',
'DD/MM/YYYY'),'O');
INSERT INTO UTILISATEURS VALUES 
(7,'MSEGAFRE','SEGAFREDI','MANUELLA','ADMINISTRATEUR',TO_DATE('15/ 
05/2025','DD/MM/YYYY'),'O');


TABLE : FONCTIONS
Requête de création de la table :
CREATE TABLE FONCTIONS (IDENT_FONCT INTEGER
, METIER VARCHAR(50)
,LIBELLE VARCHAR(80));

Requêtes d'insertion des données :
INSERT INTO FONCTIONS VALUES 
(1,'GESTION COMMERCIALE','SAISIE COMMANDE'),
(2,'GESTION COMMERCIALE','SAISIE CLIENT'),
(3,'FACTURATION','GESTION FACTURE'),
(4,'FACTURATION','CONTROLE FACTURE'),
(5,'STOCK','CONSULTATION'),
(6,'STOCK','AFFECTATION EMPLACEMENT'),
(7,'GESTION COMMERCIALE','CONSULTATION CLIENT');

INSERT INTO FONCTIONS VALUES (1,'GESTION COMMERCIALE','SAISIE 
COMMANDE'); 
INSERT INTO FONCTIONS VALUES (2,'GESTION COMMERCIALE','SAISIE 
CLIENT'); 
INSERT INTO FONCTIONS VALUES (3,'FACTURATION','GESTION FACTURE'); 
INSERT INTO FONCTIONS VALUES (4,'FACTURATION','CONTROLE FACTURE');
INSERT INTO FONCTIONS VALUES (5,'STOCK','CONSULTATION');
INSERT INTO FONCTIONS VALUES (6,'STOCK','AFFECTATION 
EMPLACEMENT');  
INSERT INTO FONCTIONS VALUES (7,'GESTION 
COMMERCIALE','CONSULTATION CLIENT');

TABLE : ATTRIB_FONC
Requête de création de la table :
CREATE TABLE ATTRIB_FONC (IDENT_PERS INTEGER
, IDENT_FONCT INTEGER);

Requêtes d'insertion des données :
INSERT INTO ATTRIB_FONC VALUES (1,1); 
INSERT INTO ATTRIB_FONC VALUES (1,4); 
INSERT INTO ATTRIB_FONC VALUES (1,7); 
INSERT INTO ATTRIB_FONC VALUES (2,2); 
INSERT INTO ATTRIB_FONC VALUES (2,3); 
INSERT INTO ATTRIB_FONC VALUES (3,4); 
INSERT INTO ATTRIB_FONC VALUES (4,1); 
INSERT INTO ATTRIB_FONC VALUES (4,7); 
INSERT INTO ATTRIB_FONC VALUES (5,2); 
INSERT INTO ATTRIB_FONC VALUES (6,3); 
INSERT INTO ATTRIB_FONC VALUES (7,1); 
INSERT INTO ATTRIB_FONC VALUES (7,2); 
INSERT INTO ATTRIB_FONC VALUES (7,3); 
INSERT INTO ATTRIB_FONC VALUES (7,4); 
INSERT INTO ATTRIB_FONC VALUES (7,5); 
INSERT INTO ATTRIB_FONC VALUES (7,6);

TABLE : TABLES
Requête de création de la table :
CREATE TABLE TABLES (IDENT_TABLE INTEGER, NOM_TABLE VARCHAR(40));
Requêtes d'insertion des données :
INSERT INTO TABLES VALUES (1,'CLIENT'); 
INSERT INTO TABLES VALUES (2,'FOURNISSEUR'); 
INSERT INTO TABLES VALUES (3,'FACTURE'); 
INSERT INTO TABLES VALUES (4,'COMMANDE'); 
INSERT INTO TABLES VALUES (5,'STOCK'); 
INSERT INTO TABLES VALUES (6,'COMM_FACT'); 
INSERT INTO TABLES VALUES (7,'ACHETEUR'); 
INSERT INTO TABLES VALUES (8,'ARTICLE'); 
INSERT INTO TABLES VALUES (999,'ALL');

TABLE : ATTRIB_DROITS
Requête de création de la table :
CREATE TABLE ATTRIB_DROITS (IDENT_FONCT INTEGER
, IDENT_TABLE INTEGER
, DROITS VARCHAR(30));
Requêtes d'insertion des données :
INSERT INTO ATTRIB_DROITS VALUES (1,1,'SELECTION'); 
INSERT INTO ATTRIB_DROITS VALUES (1,1,'SUPPRESSION'); 
INSERT INTO ATTRIB_DROITS VALUES (1,1,'MODIFICATION'); 
INSERT INTO ATTRIB_DROITS VALUES (1,2,'ALL'); 
INSERT INTO ATTRIB_DROITS VALUES (1,3,'ALL'); 
INSERT INTO ATTRIB_DROITS VALUES (2,4,'SELECTION'); 
INSERT INTO ATTRIB_DROITS VALUES (2,5,'SELECTION'); 
INSERT INTO ATTRIB_DROITS VALUES (2,4,'INSERTION'); 
INSERT INTO ATTRIB_DROITS VALUES (3,1,'ALL'); 
INSERT INTO ATTRIB_DROITS VALUES (3,2,'ALL'); 
INSERT INTO ATTRIB_DROITS VALUES (3,3,'ALL'); 
INSERT INTO ATTRIB_DROITS VALUES (4,999,'ALL'); 
INSERT INTO ATTRIB_DROITS VALUES (5,999,'SELECTION'); 
INSERT INTO ATTRIB_DROITS VALUES (6,999,'ALL'); 
INSERT INTO ATTRIB_DROITS VALUES (7,1,'SELECTION');

--SQL Server
USE master;
GO
CREATE LOGIN ASMITH
	WITH PASSWORD = '@Smith.72';
	
USE master;
GO
CREATE LOGIN GPEROC 
	WITH PASSWORD = 'Gp€roc.72';

USE RESAHOTEL;
GO
CREATE USER ASMITH FROM LOGIN ASMITH;

USE RESAHOTEL;
GO
CREATE USER GPEROC FROM LOGIN GPEROC;		

USE [master];
GO
ALTER LOGIN ASMITH WITH PASSWORD='@Smith.50';

ALTER ROLE [db_owner] ADD MEMBER [ASMITH]

SELECT CODEACCES, ATFONC.IDENT_FONCT, NOM_TABLE, DROITS 
FROM UTILISATEURS UTIL 
INNER JOIN ATTRIB_FONC ATFONC ON UTIL.IDENT_PERS = ATFONC.IDENT_PERS
INNER JOIN FONCTIONS FONC ON ATFONC.IDENT_FONCT = FONC.IDENT_FONCT
INNER JOIN ATTRIB_DROITS ATDROIT ON FONC.IDENT_FONCT = ATDROIT.IDENT_FONCT
INNER JOIN TABLES TAB ON ATDROIT.IDENT_TABLE = TAB.IDENT_TABLE 
ORDER BY CODEACCES,ATFONC.IDENT_FONCT, NOM_TABLE, DROITS;

GRANT SELECT ON CLIENT TO ASMITH;

GRANT VIEW DEFINITION ON CLIENT TO GPEROC
GRANT VIEW CHANGE TRACKING ON CLIENT TO GPEROC
GRANT CONTROL ON CLIENT TO GPEROC
GRANT INSERT ON CLIENT TO GPEROC
GRANT UPDATE ON CLIENT TO GPEROC
GRANT ALTER ON CLIENT TO GPEROC
GRANT TAKE OWNERSHIP ON CLIENT TO GPEROC
GRANT REFERENCES ON CLIENT TO GPEROC
GRANT SELECT ON CLIENT TO GPEROC
GRANT DELETE ON CLIENT TO GPEROC

GRANT SELECT, DELETE, UPDATE ON CLIENT TO AVERSACE;

GRANT UPDATE (IDENT_PERS,NOM,PRENOM) ON UTILISATEURS TO ASMITH;

GRANT UPDATE (IDENT_PERS,NOM,PRENOM) ON UTILISATEURS TO PUBLIC;

GRANT CREATE TABLE TO ASMITH;

DENY VIEW DEFINITION ON ATTRIB_DROITS TO GPEROC
DENY VIEW CHANGE TRACKING ON ATTRIB_DROITS TO GPEROC
DENY CONTROL ON ATTRIB_DROITS TO GPEROC
DENY INSERT ON ATTRIB_DROITS TO GPEROC
DENY UPDATE ON ATTRIB_DROITS TO GPEROC
DENY ALTER ON ATTRIB_DROITS TO GPEROC
DENY TAKE OWNERSHIP ON ATTRIB_DROITS TO GPEROC
DENY REFERENCES ON ATTRIB_DROITS TO GPEROC
DENY SELECT ON ATTRIB_DROITS TO GPEROC
DENY DELETE ON ATTRIB_DROITS TO GPEROC

REVOKE SELECT ON UTILISATEURS FROM ASMITH;

CREATE ROLE CONTROLE_GESTION;

GRANT UPDATE ON FOURNISSEUR TO CONTROLE_GESTION

exec sp_addrolemember [CONTROLE_GESTION], ASMITH;

ALTER ROLE CONTROLE_GESTION DROP MEMBER ASMITH

DROP ROLE CONTROLE_GESTION;
	
--PostgreSQL

CREATE USER ASMITH

ALTER USER ASMITH PASSWORD '12345'

GRANT SELECT ON TABLE CLIENT TO ASMITH;

GRANT ALL PRIVILEGES ON CLIENT TO ASMITH; 
GRANT ALL PRIVILEGES ON FOURNISSEUR TO ASMITH; 
GRANT ALL PRIVILEGES ON FACTURE TO ASMITH; 
GRANT ALL PRIVILEGES ON COMMANDE TO ASMITH; 
GRANT ALL PRIVILEGES ON STOCK TO ASMITH; 
GRANT ALL PRIVILEGES ON COMM_FACT TO ASMITH; 
GRANT ALL PRIVILEGES ON ACHETEUR TO ASMITH; 
GRANT ALL PRIVILEGES ON ARTICLE TO ASMITH;

GRANT SELECT, DELETE, UPDATE ON CLIENT TO AVERSACE;

GRANT UPDATE (IDENT_PERS,NOM,PRENOM) ON UTILISATEURS TO ASMITH;

GRANT UPDATE (IDENT_PERS,NOM,PRENOM) ON UTILISATEURS TO PUBLIC;

REVOKE SELECT ON UTILISATEURS FROM ASMITH;

REVOKE ALL PRIVILEGES ON UTILISATEURS FROM ASMITH;

REVOKE ALL PRIVILEGES ON UTILISATEURS FROM PUBLIC;

CREATE ROLE CONTROLE_GESTION; 

GRANT CONTROLE_GESTION TO ASMITH;

-- Oracle

CREATE USER ASMITH IDENTIFIED BY ASMITH;

ALTER USER ASMITH IDENTIFIED BY ABCD12E;

GRANT SELECT ON TABLE CLIENT TO ASMITH;

GRANT ALL PRIVILEGES ON CLIENT TO ASMITH; 
GRANT ALL PRIVILEGES ON FOURNISSEUR TO ASMITH; 
GRANT ALL PRIVILEGES ON FACTURE TO ASMITH; 
GRANT ALL PRIVILEGES ON COMMANDE TO ASMITH; 
GRANT ALL PRIVILEGES ON STOCK TO ASMITH; 
GRANT ALL PRIVILEGES ON COMM_FACT TO ASMITH; 
GRANT ALL PRIVILEGES ON ACHETEUR TO ASMITH; 
GRANT ALL PRIVILEGES ON ARTICLE TO ASMITH;

GRANT SELECT, DELETE, UPDATE ON CLIENT TO AVERSACE;

GRANT UPDATE (IDENT_PERS,NOM,PRENOM) ON UTILISATEURS TO ASMITH;

GRANT UPDATE (IDENT_PERS,NOM,PRENOM) ON UTILISATEURS TO PUBLIC;

GRANT CREATE TABLE TO ASMITH;

GRANT ALTER ANY TABLE TO ASMITH;

GRANT CREATE TABLE,
DROP ANY TABLE,
ALTER ANY TABLE
TO ASMITH; 

GRANT CREATE USER TO ASMITH;

GRANT CREATE ROLE, DROP ANY ROLE TO ASMITH;

REVOKE SELECT ON UTILISATEURS FROM ASMITH;

REVOKE ALL PRIVILEGES ON UTILISATEURS FROM ASMITH;

REVOKE ALL PRIVILEGES ON UTILISATEURS FROM PUBLIC;

REVOKE CREATE TABLE FROM ASMITH;

REVOKE CREATE TABLE, ALTER ANY TABLE, DROP ANY TABLE FROM ASMITH;

CREATE ROLE CONTROLE_GESTION; 

GRANT CONTROLE_GESTION TO ASMITH;

--MySQL

CREATE USER 'ASMITH' IDENTIFIED BY 'ASMITH';

SET PASSWORD FOR ASMITH@localhost=PASSWORD('ABCD12E');

GRANT SELECT ON TABLE CLIENT TO ASMITH;

GRANT ALL PRIVILEGES ON CLIENT TO ASMITH; 
GRANT ALL PRIVILEGES ON FOURNISSEUR TO ASMITH; 
GRANT ALL PRIVILEGES ON FACTURE TO ASMITH; 
GRANT ALL PRIVILEGES ON COMMANDE TO ASMITH; 
GRANT ALL PRIVILEGES ON STOCK TO ASMITH; 
GRANT ALL PRIVILEGES ON COMM_FACT TO ASMITH; 
GRANT ALL PRIVILEGES ON ACHETEUR TO ASMITH; 
GRANT ALL PRIVILEGES ON ARTICLE TO ASMITH;

GRANT SELECT, DELETE, UPDATE ON CLIENT TO AVERSACE;

GRANT UPDATE (IDENT_PERS,NOM,PRENOM) ON UTILISATEURS TO ASMITH;

GRANT UPDATE (IDENT_PERS,NOM,PRENOM) ON UTILISATEURS TO PUBLIC;

GRANT CREATE ON * TO 'ASMITH';

REVOKE CREATE ON * ON 'ASMITH';

REVOKE CREATE, ALTER, DROP ON * TO 'ASMITH';