16/11/2021

Chapitre 4
Notion de tablespace
Gestion des utilisateurs et droit d’accès
Vues
Dictionnaire de données

FSTF-LL-INFO

Tablespace (espace de stockage logique)

Afin de mieux gérer l’espace de stockage et la manière dont les tables et index sont organisés
physiquement, la notion de tablespace est apparue en version 5.7.6. Cette fonctionnalité permet de
regrouper, dans une même entité logique, des tables de différents formats (redundant, compact,
compressed et dynamic) au sein d’un fichier dont on choisit le nom et l’emplacement (qui peut être
extérieur au répertoire par défaut des données). La création d’un espace s’opère à l’aide de
l’instruction suivante :

CREATE TABLESPACE nom_espace_logique

ADD DATAFILE 'emplacement_et_nom_fichier.ibd'
[FILE_BLOCK_SIZE = valeur] [ENGINE [=] nom_moteur]

FSTF-LL-INFO

1
 16/11/2021

Remarque: un espace logique peut être supprimé (avec DROP TABLESPACE) à la condition qu’il
soit vidé de toute donnée.
FSTF-LL-INFO

Gestion des utilisateurs et droit d’accès

On peut classifier les utilisateurs de la manière suivante :
• Le DBA (DataBase Administrator). Il en existe au moins un. 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 ;
– gestion des utilisateurs et de leurs objets
– optimisation des performances ;
– sauvegardes, restaurations et archivages ;
– contact avec le support technique.

• L’administrateur réseau (qui peut être le DBA) se charge de la configuration des couches client 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 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).

FSTF-LL-INFO

2
 16/11/2021

Création d’un utilisateur [CREATE USER]

• Pour pouvoir créer un utilisateur, vous devez posséder le privilège
CREATE USER ou INSERT sur la base système mysql (car c’est la table
mysql.user qui stockera l’existence de ce nouvel arrivant).
• La syntaxe de création d’un utilisateur est la suivante :
CREATE USER utilisateur
[IDENTIFIED BY [PASSWORD] 'motdePasse']
[,utilisateur2 [IDENTIFIED BY [PASSWORD] 'motdePasse2'] ...];
• Exemple: CREATE USER etudiant1@localhost IDENTIFIED BY ‘motdepass';
• Par défaut, les utilisateurs, une fois créés, n’ont aucun droit sur
aucune base de données (à part en lecture écriture sur la base test et
en lecture seule sur la base information_schema).

FSTF-LL-INFO

Gestion des utilisateurs

• Trouver les informations relatives aux utilisateurs:
SELECT User,Host FROM mysql.user;
• Pour changer un mot de pass:
ALTER USER 'utilisateur'@'serveur' IDENTIFIED BY 'mot_de_passe';
• Renommer un user:
RENAME USER utilisateur TO nouveauNom;
Ex: RENAME USER user1@localhost TO utilisateur1@192.0.0.2;
• Verouillage d’un utilisateur:
ALTER USER etudiant1@localhost ACCOUNT UNLOCK;

FSTF-LL-INFO

3
 16/11/2021

• Suppression d’un utilisateur:

DROP USER utilisateur [,utilisateur2 ...];

Remarque:
Aucune donnée d’aucune table que l’utilisateur aura mis à jour durant
toutes ses connexions ne sera supprimée. Il n’y a pas de notion
d’appartenance d’objets (tables, index, procédure, etc.) à un utilisateur.
Tout ceci est relatif à la base de données (database).

FSTF-LL-INFO

Gestion des privilèges

• Un privilège est un droit d’exécuter une action, attribué ou retiré avec
Grant et Revoke. On distingue deux catégories:

• Privilège système: est un droit d’exécuter une certaine instruction SQL.

• Privilège objet : un droit relatif aux données des tables situées dans
différentes bases de données. (Exemple : autorisation de modifier la colonne
nomComp de la table Compagnie)

FSTF-LL-INFO

4
 16/11/2021

Les niveaux des privilèges

FSTF-LL-INFO

• Global level: privilèges s’appliquant à toutes les bases du serveur. Ces privilèges sont stockés dans la
table mysql.user ( GRANT CREATE ON *.*).
• Database level: privilèges s’appliquant à tous les objets d’une base de données en particulier. Ces
privilèges sont stockés dans les tables mysql.db et mysql.host (exemple GRANT SELECT ON bd1.*).
• Table level : privilèges s’appliquant à la globalité d’une table d’une base de données en particulier. Ces
privilèges sont stockés dans la table mysql.tables_priv (exemple d’attribution d’un privilège table :
GRANT INSERT ON bd1.Etudiant).
• Column level : privilèges s’appliquant à une des colonnes d’une table d’une base de données en
particulier. Ces privilèges sont stockés dans la table mysql.columns_priv
(exemple: GRANT UPDATE(nom)ON bd1.Etudiant).
• Routine level : privilèges globaux ou au niveau d’une base (CREATE ROUTINE, ALTER ROUTINE, EXECUTE,
et GRANT) s’appliquant aux procédures cataloguées. Ces privilèges sont stockés dans la table
mysql.procs_priv de la base mysql (exemple : GRANT EXECUTE ON PROCEDURE bd1.pro1…).

FSTF-LL-INFO

5
 16/11/2021

• la base de données mysql contient des tables systèmes pour stocker

les privilèges (système et objet) de tous les utilisateurs.

FSTF-LL-INFO

Privilèges objet (LMD) sur toutes les bases de données: la table user
SELECT Host, User, Select_priv, Insert_priv, Update_priv, Delete_priv
FROM mysql.user where user ='root';

Privilèges objet (LDD) sur toutes les bases de données

SELECT Host, User, Create_priv, Drop_priv,Index_priv, Alter_priv
FROM mysql.user where user ='root';

FSTF-LL-INFO

6
 16/11/2021

Privilèges système (LCD) sur toutes les bases de données

SELECT Host,User, Create_user_priv, Grant_priv, Show_db_priv FROM
mysql.user where user=‘root’;

FSTF-LL-INFO

Attribution de privilèges [GRANT]

• L’instruction GRANT permet d’attribuer un (ou plusieurs) privilège(s) à propos
d’un objet à un (ou plusieurs) bénéficiaire(s). L’utilisateur qui exécute cette
commande doit avoir reçu lui-même le droit de transmettre ces privilèges (reçu
avec la directive GRANT OPTION).
• Syntaxe:
GRANT privilège [ (col1 [, col2...])] [,privilège2 ... ]
ON [ {TABLE | FUNCTION | PROCEDURE} ]
{nomTable | * | *.* | nomBase.*}
TO utilisateur [IDENTIFIED BY [PASSWORD] 'password']
[,utilisateur2 ...]
[ WITH [ GRANT OPTION ]
[ MAX_QUERIES_PER_HOUR nb ]
[ MAX_UPDATES_PER_HOUR nb2 ]
[ MAX_CONNECTIONS_PER_HOUR nb3 ]
[ MAX_USER_CONNECTIONS nb4 ] ];

FSTF-LL-INFO

7
 16/11/2021

Exemple de privilèges pour Grant et Revoke

FSTF-LL-INFO

Exemple:

1. GRANT CREATE, DROP ON bd1.* TO ‘user1'@'localhost';

Privilège système database level : user1 (en accès local) peut créer ou supprimer des tables dans la base
bd1.
2. GRANT INSERT, SELECT, DELETE, UPDATE(adresse) ON bd1.client TO ‘user1'@'localhost';
Privilège objet table level : user1 peut insérer, extraire, supprimer et modifier la colonne adresse de la
table client contenue dans la base bd1.
3. GRANT ALTER ON bd1.client TO user1'@'localhost' ;
Privilège système table level : user1 peut modifier la structure ou les contraintes de la table client
contenue dans la base bd1.
4. GRANT SELECT(adresse) ON bd1.client TO ‘user1'@‘localhost‘ WITH GRANT OPTION;
Jules peut extraire seulement la colonne adresse de la table client contenue dans la base bd1. Il pourra
par la suite retransmettre éventuellement ce droit.
5. GRANT ALL PRIVILEGES ON *.* TO 'user1'@'localhost' WITH GRANT OPTION;
Accorder tous les privileges possibles à l’utilisateur user1

FSTF-LL-INFO

8
 16/11/2021

Vérification des privilèges accordés

• La commande SHOW GRANTS FOR liste les différents privilèges
accordés à un utilisateur
SHOW GRANTS FOR utilisateur;

FSTF-LL-INFO

Révocation de privilèges [REVOKE]

• Syntaxe:
REVOKE privilège [ (col1 [, col2...])] [,privilège2 ... ]
ON [ {TABLE | FUNCTION | PROCEDURE} ]
{nomTable | * | *.* | nomBase.*}
FROM utilisateur [,utilisateur2 ... ];
Exemple:
• REVOKE CREATE ON bd1.* FROM ‘user1'@'localhost';
Privilège système database level : user1(en accès local) ne peut plus créer de tables
dans la base bd1.

• REVOKE ALTER,INSERT,UPDATE(adresse)
ON bd1.client FROM ‘user1'@'localhost';
Privilège objet table level : user1ne peut plus modifier la structure (ou les
contraintes), insérer et modifier la colonne adresse de la table client contenue dans
la base bd1. FSTF-LL-INFO

9
 16/11/2021

Suppression de tous les droits

REVOKE ALL PRIVILEGES, GRANT OPTION FROM utilisateur [,
utilisateur2 ...] ;

FSTF-LL-INFO

5. GRANT USAGE ON bd1.* TO ‘user1'@'localhost'

WITH
MAX_QUERIES_PER_HOUR 50
MAX_UPDATES_PER_HOUR 20
MAX_CONNECTIONS_PER_HOUR 6
MAX_USER_CONNECTIONS 3;
Privilège système database level : user1 ne peut lancer, chaque heure, que 50 SELECT, 20
UPDATE, se connecter 6 fois (dont 3 connexions simultanées) sur la base de données bd1.

FSTF-LL-INFO

10
 16/11/2021

Notion de rôle
Un rôle (role) est un ensemble nommé de privilèges (système ou
objets). Un rôle est accordé à un ou plusieurs utilisateurs ou à un autre
rôle. Ce mécanisme facilite la gestion des privilèges.
Syntaxe pour créer un rôle:
CREATE ROLE [IF NOT EXISTS] nom_role [,nom_role ...];

FSTF-LL-INFO

Rôle
• les rôles permettent d’associer facilement un ensemble de privilèges
à des utilisateurs classés par catégories.
• La chronologie des actions à entreprendre pour travailler avec des
rôles est la suivante :
1. Définir des catégories d’utilisateurs;
2. créer un rôle (avec CREATE ROLE) pour chaque catégorie ;
3. alimenter le rôle avec des privilèges système ou objets (avec GRANT) ;
4. attribuer le rôle à d’autres rôles ou à des utilisateurs selon leur catégorie (avec GRANT) ;
5. faire évoluer le rôle dans le temps en ajoutant ou en enlevant des privilèges (avec GRANT
ou REVOKE).

FSTF-LL-INFO

11
 16/11/2021

FSTF-LL-INFO

Conservation des rôles dans les sessions

Affecter un rôle à un utilisateur ne suffit pas à rendre effectif ce rôle pour
les sessions à venir. Dès la prochaine connexion de l’utilisateur sylvain, la
fonction CURRENT_ROLE() retournera NONE pour préciser que l’utilisateur
n’est associé à aucun rôle.
Pour assigner effectivement un ou plusieurs rôles à un utilisateur,
l’administrateur doit utiliser la commande SET DEFAULT ROLE. L’option
NONE annule les assignations précédentes. L’option ALL assigne tous les
rôles. Il est aussi possible d’assigner des rôles choisis pour la session à
suivre.
SET DEFAULT ROLE {NONE | ALL | nom_role [,nom_role ...]} TO user [, user
...];
Setting a default role for another user. The role has to have been granted to the user before it can be set as
default: FSTF-LL-INFO

12
 16/11/2021

Suppression d’un rôle

• La suppression d’un rôle s’opère à l’aide de l’instruction DROP ROLE.
Les effets sont immédiats sur toutes les sessions des utilisateurs
concernés.
DROP ROLE [IF EXISTS] nom_role [, nom_role ...];

• Exemple: DROP ROLE 'admin_vols', 'operation', 'planification' ;

FSTF-LL-INFO

Vérification d’un rôle

• Pour vérifier la constitution d’un rôle, vous devrez utiliser la commande
SHOW GRANTS FOR en précisant éventuellement le nom des rôles avec
USING pour avoir le détail de chaque privilège.
• Exemple:
//affichage des privilèges élémentaires et des rôles affectés à l’utilisateur
user1
SHOW GRANTS FOR
'sylvain'@'localhost' ;
//affichage des privilèges élémentaires et des rôles ,avec leurs privilèges, affectés à
l’utilisateur user1
SHOW GRANTS FOR
'sylvain'@'localhost'
USING 'planification',
'admin_vols';
FSTF-LL-INFO

13
 16/11/2021

Les vues
• Dans la majorité des SGBD, la confidentialité est renforcée par l’utilisation de
vues (views) qui agissent comme des fenêtres sur la base de données.
• Les vues correspondent à ce qu’on appelle « le niveau externe » qui reflète la
partie visible de la base de données pour chaque utilisateur.
• Une vue est considérée comme une table virtuelle car elle n’a pas d’existence
propre. Seule sa structure est stockée dans le dictionnaire. Ses données seront
extraites de la mémoire à partir des tables source, à la demande.
• Une vue est créée à l’aide d’une instruction SELECT appelée « requête de
définition ». Cette requête interroge une (ou plusieurs) table(s) ou vue(s). Une
vue se recharge chaque fois qu’elle est interrogée.

FSTF-LL-INFO

Création d’une vue

Sysntaxe:

CREATE [OR REPLACE] VIEW [nomBase.]nomVue [(aliaslistecolonnes)]

AS requêteSELECT
[WITH [CASCADED | LOCAL] CHECK OPTION];

FSTF-LL-INFO

14
 16/11/2021

• nomBase: désigne le nom de la base de données qui hébergera la vue. En l’absence de ce

paramètre, la vue est créée dans la base en cours d’utilisation.
• requêteSELECT : requête de définition interrogeant une (ou des) table(s) ou vue(s) pour
charger les données dans la vue. La requête de définition ne peut interroger une table
temporaire, ni contenir de paramètres ou de variables de session. Si la requête de définition
sélectionne toutes les colonnes d’un objet source (SELECT * FROM…), et si des colonnes sont
ajoutées par la suite à cet objet, la vue ne contiendra pas ces colonnes définies
ultérieurement à elle. Il faudra recréer la vue pour prendre en compte l’évolution structurelle
de l’objet source.
• WITH CHECK OPTION: garantit que toute mise à jour de la vue par INSERT ou UPDATE s’effectuera
conformément au prédicat contenu dans la requête de définition.
• Les paramètres LOCAL et CASCADED (par défaut): déterminent la portée de la vérification quand
une vue est définie à partir d’une autre vue. LOCAL restreint la vérification du prédicat à la vue
elle-même. CASCADED permet d’étendre la vérification.
• Les noms de colonnes, s’ils sont utilisés, désignent le nom de chaque colonne de la vue. Ce
mécanisme permet de mieux contrôler les noms de colonnes. Quand un alias n’est pas présent, la
colonne prend le nom de l’expression renvoyée par la requête de définition. Ce mécanisme sert à
masquer les noms des colonnes de l’objet source.

FSTF-LL-INFO

Vue modifiable
Pour qu’une vue soit modifiable (possibilité d’effectuer des
modification des données avec update ou insert), sa requête de
définition doit respecter les critères suivants :
• pas de directive DISTINCT, de fonction (AVG, COUNT, MAX, MIN, SUM,
ou VARIANCE), d’expression dans le SELECT ;
• pas de GROUP BY, ORDER BY ou HAVING.

FSTF-LL-INFO

15
 16/11/2021

Exemple:
• Création de la vue: • Ajout d’un pilote

CREATE OR REPLACE VIEW PilotesAF INSERT INTO PilotesAF VALUES

AS SELECT * FROM pilote ('PL-9','Caboche',600,'Rennes',‘RAM');
WHERE compa = 'AF'
WITH CHECK OPTION; ERROR 1369 (HY000): CHECK OPTION failed

• Insertion d’un nouveau pilote: • Modification de pilotes:

INSERT INTO PilotesAF VALUES UPDATE PilotesAF SET compa=‘RAM';

('PL-11','Teste',900,'Revel', 'AF');
ERROR 1369 (HY000): CHECK OPTION failed
Query OK, 1 row affected (0.03 sec)

On peut interdire les modifications par la clause ’ALGORITHM=TEMPTABLE’:

CREATE OR REPLACE ALGORITHM=TEMPTABLE

VIEW PilotesAF
AS SELECT * FROM pilote
WHERE compa = 'AF'
WITH CHECK OPTION;
FSTF-LL-INFO

Modification d’une vue [ALTER VIEW]

ALTER [ALGORITHM TEMPTABLE]

VIEW [nomBase.]nomVue [(listecolonnes)]
AS requêteSELECT
[WITH [CASCADED | LOCAL] CHECK OPTION];

FSTF-LL-INFO

16
 16/11/2021

Visualisation d’une vue [SHOW CREATE VIEW]

Pour pouvoir visualiser la requête de définition d’une vue, l’instruction
que MySQL propose est la suivante :

SHOW CREATE VIEW [nomBase.]nomVue;

FSTF-LL-INFO

Suppression d’une vue [DROP VIEW]

La suppression d’une vue n’entraîne pas la destruction des données qui
résident toujours dans les tables.
La syntaxe SQL est la suivante :

DROP VIEW [IF EXISTS]

[nomBase.]nomVue [,nomBase2.]nomVue2...;

FSTF-LL-INFO

17
 16/11/2021

Dictionnaire de données
Le dictionnaire des données (metadata ou data dictionary) est une partie
majeure d’une base de données MySQL qu’on peut assimiler à une structure
centralisée.
Selon la version de MySQL, le dictionnaire des données est composé d’une
vingtaine à une quarantaine de vues (issues de tables système non visibles).
Les versions antérieures à 5.0 (3.23, 4.0 et 4.1) ne mentionnaient même pas
ces vues dans la documentation. Depuis la version 5, chaque nouvelle version
du serveur apporte son lot de nouveautés notamment concernant le moteur
de stockage InnoDB (18 vues en version 5.0, 40 en version 5.5, une
soixantaine en version 5.7).
Ces vues, qui sont appelées tables par abus de langage dans la documentation
officielle sont situées dans la base INFORMATION_SCHEMA. Elles permettent
de stocker toute information décrivant tous les objets contenus dans toute
base de données.

FSTF-LL-INFO

Contenu
Le dictionnaire des données contient :
• la définition des tables, vues, index, séquences, procédures, fonctions et
déclencheurs ;
• la description de l’espace disque alloué et occupé par chaque objet ;
• les valeurs par défaut des colonnes (DEFAULT) ;
• la description des contraintes d’intégrité référentielle, de vérification;
• le nom des utilisateurs de la base ;
• les privilèges pour chaque utilisateur ;
• des informations d’audit (accès aux objets) et d’autre(commentaires, par
exemple).
FSTF-LL-INFO

18
 16/11/2021

FSTF-LL-INFO

Vues du dictionnaire de données

• Views: permet de lister les vues d’une base de données
SELECT TABLE_SCHEMA,TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS;
• SCHEMATA: permet de retrouver les bases de données hébergées dans le serveur
SELECT SCHEMA_NAME AS 'Base de donnees‘ FROM INFORMATION_SCHEMA.SCHEMATA;
• Tables: permet de lister les tables et les vues
SELECT TABLE_SCHEMA,TABLE_NAME, TABLE_TYPE,DATE(CREATE_TIME)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('information_schema','test','mysql');
• Table_contraints: permet de lister les contraintes définies
SELECT CONSTRAINT_SCHEMA,CONSTRAINT_NAME,CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = ‘parc' AND TABLE_NAME='Installer';
• USER_PRIVILEGES, SCHEMA_PRIVILEGES, TABLE_PRIVILEGES, COLUMNS_PRIVILEGES, …

FSTF-LL-INFO

19
 16/11/2021

Estimation de la taille d’une table ou une base de données

Il est possible d’afficher le détail de stockage d’une base ou d’une table spécifique
en utilisant la vue du dictionnaire TABLES.

SELECT ENGINE, AUTO_INCREMENT, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH

FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = ‘parc' AND TABLE_NAME='Installer';

• la colonne ENGINE pour désigner le type de moteur de stockage de la table en question.

• la colonne AUTO_INCREMENT pour désigner la prochaine valeur de la séquence.
• la colonne TABLE_ROWS pour donner le nombre d’enregistrements de la table
• la colonne AVG_ROW_LENGTH pour désigner la taille moyenne d’une ligne en octets.
• la colonne DATA_LENGTH pour désigner la taille de la table en octets.

FSTF-LL-INFO

La commande SHOW
• La commande SHOW permet d’extraire facilement des informations
provenant du dictionnaire des données.

SHOW TABLES;
SHOW TABLES FROM parc;
SHOW CREATE TABLE parc.Installer;
SHOW DATABASES;
SHOW PRIVILEGES;
SHOW GRANTS FOR ‘user1'@'localhost';
...
FSTF-LL-INFO

20