INSIA - ING 1
Bases de données
Piscine MySQL – Cours et TP n° 09
Utilisateur – Droits – Vue – Sauvegarde
LA BIBLE : MySQL 5.0 Reference Manual
Site officiel MySql :
Documentation MySQL :
La Base de Données Open Source la plus Populaire au Monde
Bertrand LIAUDET
| SOMMAIRE UTILISATEURS - DROIT – VUE - SAUVEGARDE |
1
2
1. Les utilisateurs et leurs droits 2
2. La notion de vue 5
3. Sauvegarde 7
4. Organisation physique des données 8
Première édition : décembre 2007
INSIA - BASES DE DONNÉES – ING 1 – Cours 09 - 2007-2008 - page 1/8 - Bertrand LIAUDET
UTILISATEURS - DROIT – VUE - SAUVEGARDE
PRINCIPALES NOTIONS
Create user Drop user
Grant Revoke
Vue
Sauvegarde mysqldump
Show create table
| 1. | Les utilisateurs et leurs droits |
Un utilisateur est caractérisé par son nom et par son hôte : host, user.
mysql> select user() ;
La commande CREATE USER crée un utilisateur qui n’a aucun droit. Il ne peut donc rien faire avec la BD. Il faudra ensuite donner des droits avec un GRANT.
CREATE USER nomUtilisateur[@nomHote] [IDENTIFIED BY motDePasse]
Pour créer un utilisateur, il faut être un utilisateur qui a le droit de créer des utilisateurs (with grant option).
Quand on ne précise pas le nom de l’utilisateur, la commande vaut pour tout hôte. Tous les hôtes : ‘%’ (entre quotes simples)
La commande GRANT appliquée à un utilisateur n’existant pas crée cet utilisateur.
Exemple de création d’un utilisateur sans droitsPar la commande DROP USER mysql> GRANT USAGE ON *.* TO toto IDENTIFIED BY ‘mdpToto’;
L’utilisateur toto peut se connecter sur n’importe quelle machine et n’a aucun droits. Il accède uniquement à la BD information_schema.
Suppression d’un utilisateur
DROP USER nomUtilisateur[@nomHote]
mysql> SET PASSWORD FOR nomUtilisateur = PASSWORD(‘motDePasse’)
La fonction PASSWORD( ) permet de crypter le mot de passe.
Consultation directe de la table des USER
mysql> select host, user, password from ;
Les principaux privilèges sont les suivants :
| Nom | Classe | Niveau | Droit |
| USAGE | Serveur | User | Pour créer un utilisateur sans droits. |
| ALL [PRIVILEGES] | Serveur | User | Tous les privilèges, sauf WITH GRANT OPTION |
| WITH GRANT OPTION | Serveur | User | Autorise l’utilisation de GRANT |
| Nom | Classe | Niveau | Droit |
| CREATE | Serveur | BD, Table | Création (toutes les créations possible) |
| DROP | Serveur | BD, Table | Destruction de bases ou de tables |
| ALTER | Serveur | Table | Autorise l'utilisation de ALTER. |
| Nom | Classe | Niveau | Droit |
| SHOW DATABASES | Client | User | Autorise l'utilisation de SHOW DATABASES. |
| SELECT | Client | Tuples | Autorise l'utilisation de SELECT. |
| INSERT | Client | Tuples | Autorise l'utilisation de INSERT. |
| UPDATE | Client | Tuples | Autorise l'utilisation de UPDATE. |
| DELETE | Client | Tuples | Autorise l'utilisation de DELETE. |
GRANT privilege [,privilege] ON composant TO nomUtilisateur
[IDENTIFIED BY motDePasse] [WITH GRANT OPTION]
Exemple de création d’un super-utilisateur
mysql> GRANT ALL PRIVILEGES ON *.* TO [email protected] IDENTIFIED BY ‘mdpAdmin’ WITH GRANT OPTION;
INSIA - BASES DE DONNÉES – ING 1 – Cours 09 - 2007-2008 - page 3/8 - Bertrand LIAUDET
Donner le droit à l’utilisateur 1 de consulter la table emp :
GRANT SELECT, INSERT ON emp TOutilisateur1; GRANT SELECT ON emp TOutilisateur1;
Consultation et insertion :
Tous les droits pour deux utilisateurs :
GRANT ALL PRIVILEGES ON emp TOutilisateur1, utilisateur2;
Limiter les droits à certains attributs
GRANT SELECT (ename, job, deptno) ON emp TOutilisateur1;
REVOKE privilege [,privilege] ON composant FROM nomUtilisateur [IDENTIFIED BY motDePasse] [WITH GRANT OPTION]
Suppression de tous les droits sauf le grant option: ‘mdpAdmin’;
mysql> revoke all privileges on *.* from [email protected] identified by
‘mdpAdmin’; equivalent à : mysql> revoke all on *.* from [email protected] identified by ‘mdpAdmin’;
Suppression du droit de grant : mysql> revoke grant option on *.* from [email protected] identified by
Suppression du droit d’insertion de l’utilisateur 1 :
REVOKE INSERT ON emp TOutilisateur1;
Pour voir la commande de création des droits :
mysql> SHOW GRANTS FOR user ;
| 2. | La notion de vue |
Une commande select peut être conservée dans une variable appelée "vue".
Une vue est donc une table virtuelle :
• elle n'a pas d'existence physique ;
• elle est recalculée à chaque utilisation ;
• elle est équivalente à une requête.
La syntaxe de la création d'une vue est la suivante :
CREATEVIEW nom_vue ASselect …
ou
CREATE or REPLACE VIEW nom_vue ASselect …
Utilisation d’une vue
Une vue s’utilise comme une table.
Consultation du code d’une vue
mysql> SHOW CREATE VIEW nom_vue;
Pour supprimer une vue, on écrira :
DROPVIEW nom_vue ;
Tous les employés travaillant dans un département qui contient au moins un 'ANALYST' (c'est un métier) :
| CREATE or REPLACE VIEW vue1 as Selectdistinct nd from emp where job = 'ANALYST' order by nd; Select ne, nom from emp where nd in (select nd from vue1 ); ou Select distinct e.ne, e.nom from emp e, vue1 v where e.nd ; |
• Pour décomposer les requêtes en sous-requêtes
• Pour mieux gérer la protection des données en limitant la visibilité des données par les utilisateurs.
| 3. | Sauvegarde |
Mysqldump est une commande qui permet de produire le code SQL permettant de recréer entièrement la BD.
Sauvegarder des BD
shell> mysqldump [options] --databases DB1 [DB2 DB3 ]
Sauvegarder toutes les BD
shell> mysqldump [options] --all-databases
shell> mysqldump –uroot –p nomBD
La commande affiche le code SQL de la BD nomBD.
shell> mysqldump –uroot –p nomBD > nomFichier
La commande écrit le code SQL de la BD nomBD dans le fichier nomFichier.
Commande usuelle de sauvegarde d’une BD : shell> mysqldump --opt nomBD >
L’option --opt inclut l’option --quick et l’option --lock-tables, entre autres.
Pour recharger le fichier de sauvegarde :
shell> mysql nomBD <
Commande usuelle de sauvegarde de toutes les BD :
shell> mysqldump --opt --all-databases >
Pour recharger le fichier de sauvegarde de toutes les BD :
shell> mysql < allBDql
La commande :
SELECT attributs INTO OUTFILE ‘nomFichier’ FROM etc.
Permet de sauvegarder le résultat d’un select dans un fichier texte.
Cela permet de réaliser des dumps rapides des tables C’est le complément de LOAD DATA INFILE.
La commande :
LOAD DATA INFILE ‘nomFichier’ INTO TABLE nomTableOUTFILE
Permet charger les données d’un fichier texte dans une table.
Avec les options REPLACE ou IGNORE, quand une donnée rentre avec une clé primaire existant déjà, elle remplacera l’ancienne ou elle sera ignorée. Sinon, l’opération s’arrête en erreur.
LOAD DATA INFILE ‘nomFichier’ REPLACE INTO TABLE nomTableOUTFILE
| 4. | Organisation physique des données |
SHOW VARIABLES like ‘%data%’
ou
SHOW VARIABLES like ‘datadir’
Permet de voir le répertoire où se trouvent les données de la BD.
Chaque BD est rangée dans le DATADIR dans un répertoire du nom de la BD.
Chaque table d’une BD MyISAM est stockée sur disque dans trois fichiers.
Les fichiers portent le nom de la table, et ont une extension qui spécifie le type de fichier.
Le fichier .frm stocke la définition de la table.
Le fichier des données possède l'extension .MYD (MYData).
Le fichier d'index possède l'extension .MYI (MYIndex),
Pour spécifier explicitement que vous souhaitez une table MyISAM, indiquez le avec l'option ENGINE ou TYPE lors de la création de la table.
MySQL crée toujours un fichier .frm pour stocker le type de la table et les informations de définition. Les données et les index de la table peuvent être stockés ailleurs, en fonction du type de tables.
