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 :


● 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;

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%' IDENTIFIED BY 'secret';
CREATE USER 'login_user3'@'10.248%' IDENTIFIED BY 'secret';
-- ou sur un portable 

CREATE USER 'login_user4'@'serveurmysql2' IDENTIFIED BY 'secret';
CREATE USER login_user5 IDENTIFIED BY 'secret';
CREATE USER login_user6;
CREATE USER 'login_user7'@'%login%' 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 ORDER BY 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

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)

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

DESCRIBE user;


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' 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 )
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;
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.

➢ 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

Jeu de test pour GRANT

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;

SHOW GRANTS FOR login;
SHOW GRANTS FOR login_admin;


-- depuis l'autre terminale

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

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
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)

-- etape 1

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;

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

SHOW GRANTS FOR login_admin;
REVOKE GRANT  OPTION ON *.* 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 

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/ 

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