Premier niveau de l’administration des SGBD : utilisateurs, rôles, droits

utilisateurs : classification

Le DBA (DataBase Administrator). Il en existe au moins un. Une petite base peut n’avoir qu’un seul administrateur. Une base importante peut en regrouper plusieurs qui se partagent les tâches suivantes :

installation et mises à jour de la base et des outils éventuels ;
gestion de l’espace disque et des espaces pour les données (tablespaces) ;
gestion des utilisateurs et de leurs objets (s’ils ne les gèrent pas eux-mêmes) ;
optimisation des performances ;
sauvegardes, restaurations et archivages ;
contact avec le support technique.

● L’administrateur réseaux (qui peut être le DBA) se charge de la configuration des couches clients pour les accès distants.

● Les développeurs qui conçoivent et mettent à jour la base. Ils peuvent aussi agir sur leurs objets (création et modification des tables, index, séquences, etc.). Ils transmettent au DBA leurs demandes spécifiques (stockage, optimisation, sécurité).

● Les administrateurs d’applications qui gèrent les données manipulées par l’application ou les applications. Pour les petites et les moyennes bases, le DBA joue ce rôle.

● Les utilisateurs qui se connectent et interagissent avec la base à travers les applications ou à l’aide d’outils (interrogations pour la génération de rapports, ajouts, modifications ou suppressions d’enregistrements).
Tous seront des utilisateurs (au sens Oracle) avec des privilèges différents.

mysql : structure

SHOW DATABASES;

information_schema : cette base de données stocke les informations sur toutes les bases de données. Les tables, les colonnes, le type des colonnes, les procédures des bases de données y sont recensées, avec leurs caractéristiques. Nous verrons cette base de données plus en détail dans le prochain chapitre.
performance_schema : permet de stocker des informations sur les actions effectuées sur le serveur (temps d’exécution, temps d’attente dus aux verrous, etc.)
mysql : qui contient de nombreuses informations sur le serveur. Entre autres, c’est dans cette base que sont stockés les utilisateurs et leurs privilèges.

créer un (des) utilisateurs

Depuis sur l’IUT la machine utilisée est serveurmysql2 :

Se connecter en tant que root :

-- sur un poste de l'IUT
mysql --user=root --host=serveurmysql2 --database=mysql --password=secret
-- avec VPN à l'IUT
mysql --user=root --host=serveurmysql2.iut-bm.univ-fcomte.fr --database=mysql --password=secret

-- sur une machine personnelle
sudo mysql -u root -p

Créer des utilisateurs préfixés de votre login de l’iut

L’hôte est l’adresse à partir de laquelle l’utilisateur va se connecter.

login@912e002-01:~/Desktop$ hostname -i
172.20.176.%

ip address
# recherche adresse IP


-- remplacer l'adresse IP par localhost sur vos machines personnelles

CREATE USER 'login'@'%' IDENTIFIED BY 'secret';

CREATE USER 'login_user2'@'172.20.178.%' IDENTIFIED BY 'secret';
CREATE USER 'login_user3'@'172.20%' IDENTIFIED BY 'secret';
CREATE USER 'login_user4'@'serveurmysql2' IDENTIFIED BY 'secret';
CREATE USER login_user5 IDENTIFIED BY 'secret';
CREATE USER login_user6;
CREATE USER 'login_user7'@'10.248%' IDENTIFIED BY 'secret';

CREATE USER login_admin IDENTIFIED BY 'secret';

CREATE USER 'login_user8'@'%912e007-0%' IDENTIFIED BY 'secret';
CREATE USER 'login_user9'@'912e007-03.iut-bm.univ-fcomte.fr' IDENTIFIED BY 'secret';   
                  -- remplacer 007-03 par le numéro de votre poste

Tester les connexions dans un terminal

mysql --user=login --password=secret --host=serveurmysql2
mysql --user=login_user2 --password=secret --host=serveurmysql2 
mysql --user=login_user3  --password=secret --host=serveurmysql2
mysql --user=login_user4  --password=secret --host=serveurmysql2 
mysql --user=login_user5  --password=secret --host=serveurmysql2
mysql --user=login_user6                    --host=serveurmysql2

mysql --user=login_user7 --password=secret --host=serveurmysql2  -- NON
mysql --user=login_user8  --password=secret --host=serveurmysql2   -- ok
mysql --user=login_user9  --password=secret --host=serveurmysql2  -- ok
mysql --user=login_admin  --password=secret --host=serveurmysql2

voir

mysql --user=login --password=secret --host=serveurmysql2.iut-bm.univ-fcomte.fr
mysql --user=login_user2 --password=secret --host=serveurmysql2.iut-bm.univ-fcomte.fr 
mysql --user=login_user3  --password=secret --host=serveurmysql2.iut-bm.univ-fcomte.fr
mysql --user=login_user4  --password=secret --host=serveurmysql2.iut-bm.univ-fcomte.fr 
mysql --user=login_user5  --password=secret --host=serveurmysql2.iut-bm.univ-fcomte.fr
mysql --user=login_user6                    --host=serveurmysql2.iut-bm.univ-fcomte.fr

mysql --user=login_user7 --password=secret --host=serveurmysql2.iut-bm.univ-fcomte.fr  -- NON
mysql --user=login_user8  --password=secret --host=serveurmysql2.iut-bm.univ-fcomte.fr   -- ok
mysql --user=login_user9  --password=secret --host=serveurmysql2.iut-bm.univ-fcomte.fr  -- ok
mysql --user=login_admin  --password=secret --host=serveurmysql2.iut-bm.univ-fcomte.fr

on peut l’adresse ip qui est refusé

Liste des utilisateurs

SELECT User,Host FROM mysql.user;

SHOW CREATE USER 'login'@'%';

SELECT User,Host FROM mysql.user WHERE User LIKE '%login%';

Remarque : en remplaçant 'login'@'hostclient' par 'login'@'%'

lien

information sur l’utilisateur

Connaître l’identité effective : select user()

mysql> SELECT user() ;

Le résultat peut être : moi@localhost ou moi@maMachine. C’est le nom de l’utilisateur et son hôte (sa machine)

Connaître le profil de connexion : select current_user()

mysql> SELECT current_user() ;

Le current_user est un profil d’utilisateur et pas l’utilisateur avec son nom et sa machine. On peut par exemple avoir un profil de connexion correspondant à :

• un nom d’utilisateur pouvant se connecter de n’importe quelle machine : moi@’%’
• un nom d’utilisateur pouvant se connecter de la machine du serveur : moi@localhost
• un nom d’utilisateur pouvant se connecter de sa machine uniquement : moi@maMachine
• n’importe quel utilisateur se connectant à partir d’une machine particulière : ‘%’@cetteMachine
• un nom d’utilisateur pouvant se connecter de sa machine uniquement : moi@maMachine

afficher la liste des utilisateurs

SELECT Host,User
,Select_priv
-- ,max_connections
-- ,max_user_connections
, plugin,authentication_string
, password_expired
, password_last_changed
-- , password_lifetime
, account_locked
FROM user
-- WHERE User LIKE '%login%'
;

Renommer un utilisateur

SELECT User,Host FROM mysql.user  WHERE User LIKE '%login%';

RENAME USER 'login_user4' TO 'login_user4'@'%';         -- NON
RENAME USER 'login_user4'@'serveurmysql2.iut-bm.univ-fcomte.fr' TO 'login_user4'@'%';

SELECT User,Host FROM mysql.user  WHERE User LIKE '%login%';

Supprimer un utilisateur

DROP USER 'login_user4';
DROP USER 'login_user3'@'172.20%';
CREATE USER 'login_user3'@'%' IDENTIFIED BY 'secret';

SELECT User,Host FROM mysql.user;
SELECT User,Host FROM mysql.user  WHERE User LIKE '%login%';

Modifier un mot de passe

L’instruction dépend de la version de mysql (mysql --version dans un terminal)

SELECT VERSION();  -- SELECT @@version;

ALTER USER 'login' IDENTIFIED BY 'secret3';                               -- (OK mint+mariadb)(OK iut)
-- test de la connexion
-- mysql --user=login  --password=secret3 --host=serveurmysql2.iut-bm.univ-fcomte.fr


ALTER USER 'login' IDENTIFIED WITH plugin_authentification BY 'secret2';  -- (NON mint+mariadb)(NON iut ?)

-- jusqu'à la version 5.7.6

SET PASSWORD FOR 'login' = PASSWORD('secret2');                           -- (OK mint+mariadb)(OK iut)

-- test de la connexion
-- mysql --user=login  --password=secret2 --host=serveurmysql2.iut-bm.univ-fcomte.fr

sur mariadb - sur mysql

ALTER USER 'login' IDENTIFIED VIA mysql_native_password  USING PASSWORD('secret'); -- (Ok mint+mariadb)(NON iut )
ALTER USER 'login' IDENTIFIED WITH 'mysql_native_password' BY 'secret';            -- sur mysql 8 ?  (Non mint+mariadb)(OK iut )

avec un UPDATE ( pas recommandé, déprécié, pas conforme aux autres SGBDR )

USE mysql;
DESCRIBE user;
UPDATE user SET Password=PASSWORD('secret4') WHERE User='login';   -- MySQL 5.7.5 and earlier version
-- ne fonctionne plus 
UPDATE user SET authentication_string=PASSWORD('secret4') WHERE User='login';    -- MySQL 5.7.6 et versions ultérieures(pas ok mint)
FLUSH PRIVILEGES;           -- ne fonctionne pas avec 10.5.15-MariaDB-0+deb11u1     (ok IUT car old version)

PostgreSQL

CREATE USER user_name WITH encrypted password 'mypassword';
ALTER USER user_name WITH PASSWORD 'new_password';

Oracle
CREATE USER username IDENTIFIED BY password;
ALTER USER user_name IDENTIFIED BY 'new_password';

SQLserver
CREATE USER user_name WITH PASSWORD = 'new_password';
ALTER LOGIN user_name WITH PASSWORD = 'new_password';

Base de données

CREATE DATABASE BDD_login;
CREATE DATABASE BDD_login2
    DEFAULT CHARACTER SET utf8
    COLLATE utf8_bin;
CREATE DATABASE BDD_login3;
CREATE DATABASE BDD_login4;


SELECT * FROM db;

SELECT SCHEMA_NAME , default_character_set_name 'charset', DEFAULT_COLLATION_NAME 'collation' FROM information_schema.SCHEMATA;

SELECT SCHEMA_NAME , default_character_set_name 'charset', DEFAULT_COLLATION_NAME 'collation' FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME LIKE '%login%';

DROP DATABASE BDD_login4;

utilisation ou changement de base de données

USE BDD_login;

https://zestedesavoir.com/tutoriels/730/administrez-vos-bases-de-donnees-avec-mysql/954_gestion-des-utilisateurs-et-configuration-du-serveur/3962_gestion-des-utilisateurs/

-- compte login  (remplacer login et motdepasse) dans tout le script
-- mysql --user=login --password=motdepasse --host=localhost  --database=BDD_login

CREATE DATABASE BDD_login;
CREATE USER 'login' IDENTIFIED BY  'motdepasse';

GRANT ALL PRIVILEGES ON  BDD_login.* TO 'login'@'%';
FLUSH PRIVILEGES;

-- pb avec LOAD DATA
-- GRANT ALL PRIVILEGES ON  BDD_login.* TO 'login'@'%';

priviléges (droits)

Consulter les droits d’un utilisateur : ➢ Pour n’importe quel utilisateur :

SHOW GRANTS [FOR user];

➢ Pour soi-même :

mysql> SHOW GRANTS;

➢ Droit d’USAGE Quand on crée un utilisateur, il n’a qu’un seul droit : le droit d’usage. Ca lui permet de se connecter mais il ne peut rien faire d’autre !

mysql> SHOW GRANTS;
+------------------------------------------+
| Grants for nouvelUtilisateur@localhost
|
+------------------------------------------+
| GRANT USAGE ON *.* TO ` nouvelUtilisateur `@`localhost` |
+------------------------------------------+

info : pour voir tous les priviléges

GRANT

La commande GRANT appliquée à un utilisateur n’existant pas crée cet utilisateur.

GRANT privilege [,privilege] ON composant TO nomUtilisateur [WITH GRANT OPTION]

➢ Exemple de création d’un utilisateur sans droits

mysql> GRANT USAGE ON *.* TO login@localhost;

L’utilisateur login peut se connecter sur n’importe quelle machine et n’a aucun droit. Il accède uniquement à la BD information_schema.

On donne d’abord l’USAGE, puis des droits particulier.
ON *.* veut dire : toutes les BD . toutes les tables

➢ Exemple de création d’un utilisateur qui peut tout consulter

mysql> GRANT SELECT ON *.* TO login ;

➢ Exemple de création d’un super-utilisateur

mysql> GRANT ALL PRIVILEGES ON *.* TO login_admin  WITH GRANT OPTION;

L’utilisateur admin peut se connecter sur la machine du serveur et a les mêmes droits que l’administrateur root. Il faut éviter de multiplier les administrateurs sur une machine en production !!!

https://zestedesavoir.com/tutoriels/730/administrez-vos-bases-de-donnees-avec-mysql/954_gestion-des-utilisateurs-et-configuration-du-serveur/3962_gestion-des-utilisateurs/#3-11933_les-privileges-introduction

GRANT ALL PRIVILEGES ON  BDD_login.* TO 'login';
GRANT ALL PRIVILEGES ON  BDD_login2.* TO 'login'@'%';
SHOW GRANTS FOR login;

GRANT SELECT,INSERT, UPDATE, DELETE ON  BDD_login3.* TO 'login';
SHOW GRANTS FOR login;

GRANT SELECT ON *.* TO login ;
SHOW GRANTS FOR login;

FLUSH PRIVILEGES;
 
GRANT ALL PRIVILEGES ON *.* TO login_admin  WITH GRANT OPTION;

FLUSH PRIVILEGES;


GRANT SELECT,INSERT, UPDATE, DELETE ON  BDD_login2.* TO 'login';
FLUSH PRIVILEGES;

mysql --user=login3 --password=secret --host=serveurmysql2.iut-bm.univ-fcomte.fr --database=BDD_login
mysql --user=login4 --password=secret --host=serveurmysql2.iut-bm.univ-fcomte.fr --database=BDD_login

SHOW GRANTS FOR login;
SHOW GRANTS FOR login_admin;

ip address

hostname -i

documentation

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_specification [, user_specification] ...
    [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
    [WITH with_option ...]

object_type:
    TABLE
  | FUNCTION
  | PROCEDURE

priv_level:
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name

SELECT * FROM db;


CREATE DATABASE BDD_login;
CREATE USER "login" IDENTIFIED BY  by "motdepasse"; 
GRANT ALL PRIVILEGES ON  BDD_login.* TO 'login'@'%';
FLUSH PRIVILEGES;

mysql --user=login --host=serveurmysql2.iut-bm.univ-fcomte.fr --password=motdepasse BDD_login

La liste des droits

La liste des droits

Voici la liste des droits fréquemment utilisés :

Droit	Signification
`ALL` ou `ALL PRIVILEGES`	TOus les droits sauf `WITH GRANT OPTION`.
`USAGE`	Synonyme de “pas de droits”.
`ALTER`	Autorise l’utilisation de `ALTER TABLE`.
`CREATE`	Autorise l’utilisation de `CREATE TABLE`.
`DELETE`	Autorise l’utilisation de `DELETE`.
`DROP`	Autorise l’utilisation de `DROP TABLE`.
`INSERT`	Autorise l’utilisation de `INSERT`.
`INDEX`	Autorise l’utilisation de `CREATE INDEX` et `DROP INDEX`.
`SELECT`	Autorise l’utilisation de `SELECT`.
`UPDATE`	Autorise l’utilisation de `UPDATE`.
`GRANT OPTION`	Synonyme pour `WITH GRANT OPTION`

autres droits

Droit	Signification
`CREATE TEMPORARY TABLES`	Autorise l’utilisation de `CREATE TEMPORARY TABLE`.
`EXECUTE`	Autorise l’utilisateur à exécuter des procédures stockées (pour MySQL 5.0).
`FILE`	Autorise l’utilisation de `SELECT ... INTO OUTFILE` et `LOAD DATA INFILE`.
`LOCK TABLES`	Autorise l’utilisation de `LOCK TABLES` sur les tables pour lesquelles l’utilisateur a les droits de `SELECT`.
`PROCESS`	Autorise l’utilisation de `SHOW FULL PROCESSLIST`.
`REFERENCES`	Réservé pour le futur.
`RELOAD`	Autorise l’utilisation de `FLUSH`.
`REPLICATION CLIENT`	Donne le droit à l’utilisateur de savoir où sont les maîtres et esclaves.
`REPLICATION SLAVE`	Nécessaire pour les esclaves de réplication (pour lire les historiques binaires du maître).
`SHOW DATABASES`	`SHOW DATABASES` affiche toutes les bases de données.
`SHUTDOWN`	Autorise l’utilisation de `mysqladmin shutdown`.
`SUPER`	Autorise une connexion unique même si `max_connections` est atteint, et l’exécution des commandes `CHANGE MASTER`, `KILL thread`, `mysqladmin debug`, `PURGE MASTER LOGS` et `SET GLOBAL`.

source

retirer des priviléges (droits)

SHOW GRANTS FOR login;  
REVOKE UPDATE ON BDD_login2.* FROM login; 
SHOW GRANTS FOR login;

REVOKE ALL PRIVILEGES ON BDD_login.* FROM login;
SHOW GRANTS FOR login; 

REVOKE ALL PRIVILEGES ON BDD_login2.* FROM login; -- supprime la ligne

SHOW GRANTS FOR login_admin;
REVOKE GRANT OPTION FROM login_admin ;
SHOW GRANTS FOR login_admin;

créer des groupes

CREATE ROLE '<role>';
CREATE ROLE '<role>'@'<host>';

GRANT <privileges> ON <database>.<object> TO '<role>'@'<host>';

SELECT CURRENT_ROLE();

https://www.prisma.io/dataguide/mysql/authentication-and-authorization/role-management

session root

CREATE ROLE developer;
GRANT developer TO login;
GRANT SELECT,INSERT  ON BDD_login.* TO developer;
SELECT CURRENT_ROLE();

https://mariadb.com/kb/en/set-role/

session client

SELECT CURRENT_ROLE();
SET ROLE developer;
SELECT CURRENT_ROLE();
SHOW GRANTS;
SET ROLE NONE;
SELECT CURRENT_ROLE();
SHOW GRANTS;

exercice 1

autre exo

Exercice 3 : Droits d’accès
On considère trois utilisateurs Alice, Bob et Caroline ayant chacun respectivement un compte
A,B,C sur une base de données.
— Alice dispose de trois tables TA1, TA2, TA3
— Bob dispose de deux tables TB1, TB2 et une vue VB1.
— Caroline dispose d’une table TC1.
A partir des hypothèses suivantes, écrire les commandes SQL permettant de définir la politique
de gestion des droits d’accès.
Alice souhaite que :
— TA1 soit visible pour Bob et Caroline mais que seul Bob puisse mettre à jour ses données.
— TA2 soit privée
— TA3 soit accessible en lecture pour tout le monde (tous les utilisateurs du SGBD)
Bob souhaite que :
— TB1 soit modifiable (insertion, mise à jour et suppression) par Alice et Caroline.
— TB2 soit accessible en lecture pour tout le monde sauf pour Alice.
— VB1 soit visible uniquement pour Caroline.
Caroline souhaite que les attributs AT1 et AT2 soient visibles par tous les utilisateurs de la
base et que les attributs AT3 et AT4 soient visibles uniquement par Bob.


CREATE DATABASE login_bdd;


CREATE USER login_Alice;
CREATE USER login_Bob;
CREATE USER login_Caroline;

USE login_bdd;
CREATE TABLE IF NOT EXISTS login_bdd.login_TA1 (
no_ta1 varchar(50);
);
CREATE TABLE IF NOT EXISTS login_bdd.login_TA2 (
no_ta2 varchar(50);
);
CREATE TABLE IF NOT EXISTS login_bdd.login_TA3 (
no_ta3 varchar(50);
);

CREATE TABLE IF NOT EXISTS login_bdd.login_TB1 (
no_tb1 varchar(50);
);
CREATE TABLE IF NOT EXISTS login_bdd.login_TB2 (
no_tb2 varchar(50);
);
CREATE VIEW VB1 
AS SELECT * 
FROM login_bdd.login_TB1
WITH CHECK OPTION;

CREATE TABLE IF NOT EXISTS login_bdd.login_TC1 (
AT1 varchar(50);
AT2 varchar(50);
AT3 varchar(50);
AT4 varchar(50);
);

-- droit pour les tables de login_Alice

GRANT SELECT, INSERT, UPDATE, DELETE  ON  login_bdd.login_TA1 TO login_Bob;
GRANT SELECT  ON  login_bdd.login_TA1 TO login_Caroline, login_Bob;
GRANT SELECT  ON  login_bdd.login_TA3 TO 'login_Caroline'@'%','login_Bob'@'%';

GRANT ALL PRIVILEGES ON  login_bdd.login_TA1 TO 'login_Alice'@'%';
GRANT ALL PRIVILEGES ON  login_bdd.login_TA2 TO 'login_Alice'@'%';
GRANT ALL PRIVILEGES ON  login_bdd.login_TA1 TO 'login_Alice'@'%';

REVOKE SELECT ON login_bdd.login_TA1 FROM login_Caroline, login_Bob;
REVOKE INSERT, UPDATE, DELETE  ON  login_bdd.login_TA1 FROM login_Bob;
REVOKE SELECT ON login_bdd.login_TA3 FROM login_Caroline, login_Bob;

SHOW GRANTS FOR login_Bob;
SHOW GRANTS FOR login_Caroline;
SHOW GRANTS FOR login_Alice;

-- droit pour les tables de login_Bob

GRANT SELECT, INSERT, UPDATE, DELETE  ON  login_bdd.login_TA1 TO login_Bob;
GRANT SELECT  ON  login_bdd.login_TA1 TO login_Caroline, login_Bob;
GRANT SELECT  ON  login_bdd.login_TA3 TO 'login_Caroline'@'%','login_Bob'@'%';

GRANT ALL PRIVILEGES ON  login_bdd.login_TB1 TO login_Bob;
GRANT ALL PRIVILEGES ON  login_bdd.login_TB2 TO login_Bob;



SHOW GRANTS FOR login_Caroline;
SHOW GRANTS FOR login_Alice;
SHOW GRANTS FOR login_Bob; 

-- droit pour les tables de login_Caroline

-- GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost';


SHOW GRANTS FOR login_Alice;
SHOW GRANTS FOR login_Bob; 
SHOW GRANTS FOR login_Caroline; 

DROP DATABASE login_bdd;
DROP USER login_Alice;
DROP USER login_Bob;
DROP USER login_Caroline;

https://stackoverflow.com/questions/45596542/grant-permission-to-all-columns-individually-in-mysql

exercice 2

Soit les 2 tables ci-dessous

Soit le schéma relationnel suivant:

Enseignant (numEns, nomEns, prenomEns, Ville) Article (numArt, titre, genre, dateSortie, #numEns)

DROP TABLE IF EXISTS Enseignant;
CREATE TABLE Enseignant 
(
    numEns INT NOT NULL, 
    nomEns VARCHAR(30), 
    prenomEns VARCHAR(30), 
    ville VARCHAR(30),
    CONSTRAINT pk_Enseignant PRIMARY KEY (numEns)
);

INSERT INTO Enseignant VALUES (1, 'DOMAS', 'STEPHANE', 'BELFORT');
INSERT INTO Enseignant VALUES (2, 'COUTURIER', 'INGRID', 'BELFORT');
INSERT INTO Enseignant VALUES (3, 'COUTURIER', 'RAPHAEL', 'BELFORT');
INSERT INTO Enseignant VALUES (4, 'AMBERT', 'FABRICE', 'BELFORT');

DROP TABLE IF EXISTS Salaire, Cours, Salle;
DROP TABLE IF EXISTS Article;
CREATE TABLE Article 
(
    numArt INT, 
    titre VARCHAR(30), 
    genre VARCHAR(30), 
    dateSortie DATE, 
    type VARCHAR(20) DEFAULT 'NoType',
    numEns INT NOT NULL, 
    PRIMARY KEY (numArt)
    , FOREIGN KEY (numEns) REFERENCES Enseignant(numEns)
);
INSERT INTO Article VALUES (1, 'prog. obj. 1', 'ALGO', NOW(), NULL, 1);
INSERT INTO Article VALUES (2, 'vueJs', 'WEB', NOW(),NULL, 1);
INSERT INTO Article VALUES (3, 'anglais', 'anglais', NOW(), NULL, 2);

CREATE TABLE Salaire 
(
    numSalaire INT

);
INSERT INTO Salaire VALUES (1),(2),(3);
CREATE TABLE Cours 
(
    numCours INT

);
INSERT INTO Cours VALUES (1),(2),(3);
CREATE TABLE Salle 
(
    numSalle INT
    , nb_places INT
    , commentaire_perso VARCHAR(10)
);
INSERT INTO Salle VALUES (1,30,'perso'),(2,30,'perso'),(3,30,'perso');

remplacer login par votre login

Créer une base de données cours_login, créer les 2 tables ci-dessus dans cette base

Ajouter toutes les tables ci-dessus dans cette base de données

Donnez la commande pour créer les utilisateurs adm_login identifié par le mot de passe secret et visit_login sans mot de passe, adm_login a le privilège d’accorder des privilèges à d’autres utilisateurs.
tester les 2 connexions dans un terminal
Créer une vue art_view permettant de consulter les champs: nom, ville, titre et dateSortie des tables Article et Enseignant
Attribuer le privilège SELECT sur la vue art_view à l’utilisateur visit_login
Se connecter en tant que visit_login et vérifier
Donnez la commande pour accorder le droit à adm_login d’afficher le contenu de la table cours_login.Enseignant et de créer des index sur cette table (liste des droits plus bas)
Donnez à adm_login les droits de lire et d’éditer (ajouter, supprimer, modifier les données) la table cours_login.Article
Donnez à adm_login les droits de lecture sur toutes les tables de cours_login, sauf sur la table salaires :

QUESTION : si on crée une nouvelle table, ens_login a t’il le droit en lecture sur cette table

Se connecter en tant que adm_login et vérifier
Donnez à visit_login les droits de lecture sur tous les champs de la table cours_login.Salle, sauf sur le champ commentaire_perso
Créer un rôle admin_ens_login
Attribuer tous les privilèges sur la table Enseignant pour le rôle admin_ens_login (tuto)
Créer un utilisateur userAdmin_ens_login, donner le rôle à cet utilisateur

idée de sujet

Tester le code ci-dessous :

-- DELETE FROM Enseignant WHERE numEns >= 5;
SET autocommit=1;
START TRANSACTION;
INSERT INTO Enseignant (numEns, nomEns) VALUES (5, 'NomEns5');
INSERT INTO Enseignant (numEns, nomEns) VALUES (6, 'NomEns6');
COMMIT;
INSERT INTO Enseignant (numEns, nomEns) VALUES (7, 'NomEns7');
UPDATE Enseignant SET ville = 'Montbeliard' WHERE numEns = 1;
DELETE FROM Enseignant WHERE numEns = 5;
SELECT count(numEns) FROM Enseignant;
SAVEPOINT P1;
UPDATE Enseignant SET ville = 'BESANCON' WHERE numEns = 3;
INSERT INTO Enseignant (numEns, nomEns) VALUES (8, 'NomEns8');
SELECT count(numEns) FROM Enseignant;
-- SELECT * FROM Enseignant;
ROLLBACK TO SAVEPOINT P1;
SELECT count(numEns) FROM Enseignant;
SELECT * FROM Enseignant;

donner l’affichage de ce script
Donner le nombre de mises à jour validées si autocommit = 1 ou autocommit = 0

CREATE DATABASE cours_login;

CREATE USER ‘adm_login’@‘%’ IDENTIFIED BY ‘secret’; CREATE USER ‘visit_login’@‘%’;

– mysql –user=adm_login –host=localhost –password=secret

– mysql –user=visit_login –host=localhost

CREATE VIEW art_view(nom,ville,titre,dateSortie) AS SELECT nom,ville,titre,dateSortie FROM Enseignant INNER JOIN Article ON Enseignant.numEns = Article.numEns; WITH CHECK OPTION ;

GRANT SELECT ON art_view TO visit_login;

GRANT SELECT,INDEX ON cours_login.Enseignant TO admin_login;

GRANT SELECT,INSERT, UPDATE,DELETE ON cours_login.Article TO admin_login;

GRANT SELECT ON cours_login.* TO admin_login;

REVOKE ALL PRIVILEGES ON cours_login.* TO admin_login;

SHOW GRANTS FOR admin_login;