James Hoffman

Techniques de l'Informatique - Cégep de Shawinigan

Menu

Base de données 1

1.1- Introduction à MariaDB

1.2- Utilisateurs et privilèges

1.3- Exercices

La configuration initiale du serveur peut varier selon le paquet utilisé pour l'installation. Les distributions Debian et Ubuntu maintiennent une variante du script d'installation.

Pour les autres systèmes d'exploitation, il est recommandé d'exécuter le script intégré pour un serveur utilisé en production.

bash
copier
mysql_secure_installation

Commandes administratives

Plusieurs commandes sont disponibles pour la gestion des utilisateurs et leurs privilèges.

📚 Documentation

  • Mot de passe, Méthode d'authentification
  • Plusieurs niveaux de privilèges( grants ) offrent un accès granulaire
    • Bases de données > Tables > Colonnes
    • Fonctions / Procédures

Il est également possible de regrouper plusieurs privilèges dans un rôle pour faciliter la réutilisation.

Utilisateurs

Chaque utilisateur, account name, est défini par un identifiant ET un hôte

Account name = 'username'@'host'

  • username identifie l'utilisateur
  • host la source de la connexion

Par exemple, l'utilisateur root créé par défaut est défini 'root'@'localhost'. Seules les connexions en provenance de la machine elle-même, localhost, seront autorisées pour root.

Les manipulations des utilisateurs doivent utiliser explicitement le account name complet.

📚 Account names

Créer les utilisateurs

sql
CREATE [OR REPLACE] USER [IF NOT EXISTS] user_specification [,user_specification ...];

user_specification:
username@host [authentication_option]

authentication_option:
IDENTIFIED BY 'password'
| IDENTIFIED BY PASSWORD 'password_hash'
| IDENTIFIED {VIA|WITH} authentication_rule
}

authentication_rule:
authentication_plugin
| authentication_plugin {USING|AS} 'authentication_string'
| authentication_plugin {USING|AS} PASSWORD('password')
sql
Exemples de création d'utilisateurs
copier
create user 'james';
-- Correspond a CREATE USER 'james'@'%'
-- % est un wildcard pour le host

create user 'james'@'localhost';

create user 'james'@'a.b.c.d/s';


-- -- --


-- Securiser l'utilisateur, avec un mot de passe
create user 'mathieu'@'localhost' identified by 'pwd';

-- ou avec le plugin unix_socket
create user 'mathieu'@'localhost' identified via unix_socket;


-- -- --


-- Pour eviter une erreur en creant un duplicata
create user if not exists 'nick'@'localhost';

-- Pour reinitialiser un account name avec de nouvelles caracteristiques
create or replace user 'nick'@'localhost' identified by 'pwd';

-- ou PLUSIEURS mecanismes (10.4+)
create user 'etd'@'localhost' identified via unix_socket or mysql_native_password using password('pwd');

📚 create user

Gérer les utilisateurs

sql
copier
-- Supprimer un utilisateur
drop user [if exists] username@host;

-- Modifier les identifiants
rename user username@host to newusername@newhost;

-- Attribuer/Modifier/Retirer un mot de passe
-- Mettre le mot de passe a vide pour le retirer ''
set password [for username@host] = password('clear_password');

-- Modifie un utilisateur, avec la syntaxe du CREATE USER
alter user [if exists] username@host [...];

Voir les utilisateurs

sql
copier
-- Voir les utilisateurs existants, en tant que root ou equivalent
select user, host, plugin, password from mysql.user;

-- Pour voir si plusieurs mécanismes d'authentification (10.4+)
select * from mysql.global_priv;

-- Voir la commande de création d'un utilisateur
show create user username@host;

La nomenclature username@host offre beaucoup de flexibilité pour définir les utilisateurs. Toutefois, MariaDB utilise plusieurs règles pour déterminer à quel utilisateur correspond une demande de connexion.

📚 Règles

sql
copier
-- Voir quel utilisateur a été résolu par MariaDB à partir des identifiants fourni
select user() as 'fourni', current_user() as 'resolu';

Connexion externe

Bien qu'il soit possible de définir des utilisateurs autorisé à se connecter de différents host, il faut configurer le service MariaDB pour être à l'écoute des connexions externes(et ouvrir le pare-feu si nécessaire).

📚 Règles

Décommenter et mettre à jour la ligne suivante avec l'IP du serveur dans le fichier de configuration de MariaDB

conf
/etc/mysql/mariadb.conf.d/50-server.cnf
bind-address = A.B.C.D

Puis, redémarrer le service

bash
copier
systemctl restart mariadb

Clients SQL GUI

Selon vos préférences personnelles, certaines fonctionnalités (gestion des serveurs, création de fichiers, réadaction de requêtes, consultation du résultat, etc.) peuvent être plus agréable via une application avec interface graphique

Privilèges

Pour exploiter le SGBD, un utilisateur fraîchement créé doit posséder des permissions.

📚 Grants

sql
copier
-- Voir les permissions
show grants [for username@host];
sql
Attribuer les permissions
GRANT priv_type [(column_list)] [, priv_type [(column_list)], ...]
ON db_name.obj_name
TO username@host [WITH GRANT OPTION];

-- Le priv_type représente l'élément autorisé
-- https://mariadb.com/kb/en/grant/#privilege-levels

-- Le wildcard * peut être utilisé pour le db_name et obj_name

-- WITH GRANT OPTION permet à l'utilisateur d'attributer a d'autres utilisateurs
-- les permissions qu'il possède

Pour retirer une permission, on l'identifie avec la même structure que lors de l'attribution.

  • Attention, on GRANT ... TO et on REVOKE ...FROM
sql
-- Retirer les permissions
REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON [object_type] priv_level
FROM username@host [, username@host ...]


-- Retirer TOUTES les permissions
REVOKE ALL PRIVILEGES, GRANT OPTION
FROM username@host [, username@host ...];

Il est également possible de faire un retrait partiel des permissions.

sql
MariaDB [(none)]> show grants for james@localhost;

+--------------------------------------------------------+
| Grants for james@localhost |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO 'james'@'localhost' |
| GRANT ALL ON `testdb`.* TO 'james'@'localhost' |
+--------------------------------------------------------+


-- -- --


MariaDB [(none)]> revoke drop on testdb.* from james@localhost;


-- -- --


MariaDB [(none)]> show grants for james@localhost;
+--------------------------------------------------------+
| Grants for james@localhost |
+--------------------------------------------------------+
| Grants for james@localhost: GRANT USAGE ON *.* TO 'james'@'localhost'
| Grants for james@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `testdb`.* TO 'james'@'localhost'
+--------------------------------------------------------+

Créer une base de données

Pour nous permettre de valider au minimum nos permissions, nous allons créer des bases données que nous pourrons assigner aux utilisateurs.

sql
copier
create database [if not exists] db_name;

On peut préciser l'accès aux bases de données

sql
copier
grant all on db_name.* to username@host;

Puis, lister les bases données disponibles à l'utilisateur connecté

sql
copier
show databases;