Editions ENI Livres | Vidéos | e-Formations
La sécurité des données (DCL)
Introduction
Le langage SQL permet d’attribuer des droits aux utilisateurs à travers les commandes du DCL pour Data Control Language ou en français langage de contrôle des données (LCD).
En général, les commandes du DCL sont utilisées principalement par le DBA. La sécurité des données est très importante dans une entreprise, ces fonctions sont donc à manier avec précaution.
Ce chapitre traite de la mise en place de la sécurité au niveau d’une base de données. Il ne s’agit pas d’un cours d’administration d’un serveur de bases de données.
Pourquoi définir des droits ?
Au sein d’une entreprise, il existe des typologies d’utilisateurs très différentes. Certains vont simplement consulter quelques tables, d’autres vont être amenés à insérer et à modifier des données, les personnes du service informatique vont avoir des besoins plus étendus leur permettant quelquefois de créer ou de supprimer des tables.
Il faut donc pouvoir contrôler tous les accès à la base de données et permettre à chacun de réaliser ses fonctions sans empiéter sur les fonctions d’une autre personne.
Avant d’attribuer des droits sur telle ou telle table, il faut classer tous les utilisateurs de la base de données par fonction et par métier et déterminer pour chaque fonction quelles sont les tables utilisées et pour chacune des tables indiquer si l’accès doit se faire en mise à jour ou en lecture uniquement. En général, ces informations font partie d’un document d’architecture générale ou technique.
Ce travail doit être fait par les personnes de l’informatique et par les personnes en charge des applications métier (souvent appelés la MOA).
Une fois l’ensemble des informations récoltées, on peut imaginer les classer dans une table afin de rendre la gestion des droits évolutive et facilement maintenable par une personne qui peut être l’administrateur de la base ou une autre personne du service informatique dédiée à cette tâche.
Il est préférable d’utiliser un contrôleur de domaine dans lequel on définit des comptes utilisateurs, membres de groupes. On donne accès aux bases, tables ou enregistrements à ces groupes. Les utilisateurs SQL sont généralement créés pour être dédiés à une application et non à une personne physique.
Le fait d’utiliser des tables dans ce chapitre va permettre de comprendre la mise en place de la sécurité dans une base.
Par exemple
Description d’un modèle de données permettant de gérer les droits des utilisateurs.
Modèle de données utilisé pour illustrer le DCL
TABLE : UTILISATEURS
Requête de création...
Créer une connexion
Les SGDBR gèrent différemment leurs droits d’accès même si les syntaxes sont similaires. Avec SQL Server, il est nécessaire de créer une connexion au serveur SQL (qui peut être créée à partir de l’annuaire du contrôleur de domaine). SQL Server est un moteur de bases de données, indépendant du système d’exploitation.
Syntaxe SQL Server
CREATE LOGIN <login> WITH PASSWORD = '<Mot de passe>'; Ou création de la connexion à partir d’un compte Windows :
CREATE LOGIN <domainName>\<login> WITH PASSWORD = '<Mot de passe>'; Exemple avec SQL Server
Création de la connexion SQL au serveur à partir de la base ‘master’ qui est une base système :
USE master;
GO
CREATE LOGIN ASMITH
WITH PASSWORD = '@Smith.72'; Créer un utilisateur
Les utilisateurs sont souvent créés par l’administrateur de la base selon des règles de sécurité propres à chaque entreprise. La commande de création d’un user est assez simple. Avec SQL Server, l’utilisateur est créé à partir de la connexion.
Syntaxe Oracle
CREATE <user> IDENTIFIED BY <Mot de passe>; Syntaxe MySQL
CREATE '<user>' IDENTIFIED BY '<Mot de passe>'; Syntaxe SQL Server
CREATE '<user>' FROM LOGIN <login> Syntaxe PostgreSQL
CREATE USER '<user>' Exemple avec Oracle
CREATE USER ASMITH IDENTIFIED BY ASMITH; Exemple avec MySQL
CREATE USER 'ASMITH' IDENTIFIED BY 'ASMITH'; Autre méthode pour déclarer un utilisateur avec MySQL
GRANT ALL PRIVILEGES ON *.* TO ASMITH@localhost
IDENTIFIED BY 'ASMITH' WITH GRANT OPTION; Exemple avec SQL Server
USE RESAHOTEL;
GO
CREATE USER ASMITH FROM LOGIN ASMITH; Exemple avec PostgreSQL
CREATE USER ASMITH; M. Smith pourra donc se connecter à la base de données en utilisant ASMITH/ASMITH@<Nom de la base>.
La création d’un utilisateur ne lui permet pas d’accéder aux tables et de faire des sélections. Il faut ensuite lui attribuer des droits spécifiques en fonction de son profil.
Changer le mot de passe d’un utilisateur
Pour des raisons de sécurité, il peut être nécessaire de modifier le mot de passe d’un utilisateur. Dans la plupart des systèmes, c’est le DBA qui peut utiliser cette commande.
Syntaxe Oracle
ALTER USER <user> IDENTIFIED BY <Nouveau Mot de passe>; Syntaxe MySQL
SET PASSWORD FOR '<user>'@'<host>' = PASSWORD('mypass'); Syntaxe SQL Server
ALTER LOGIN <user> WITH PASSWORD='mypass'; Syntaxe PostgreSQL
ALTER USER <user> PASSWORD 'mypass'; Exemple Oracle
ALTER USER ASMITH IDENTIFIED BY ABCD12E; Exemple MySQL
SET PASSWORD FOR ASMITH@localhost=PASSWORD('ABCD12E'); Exemple SQL Server
USE [master];
GO
ALTER LOGIN ASMITH WITH PASSWORD='@Smith.50'; Exemple PostgreSQL
USE [master];
GO
ALTER USER ASMITH PASSWORD '@Smith.50'; Attribuer des droits (GRANT)
1. Attribuer des droits sur la manipulation d’une table
À partir de ces quelques tables d’exemples, nous allons pouvoir attribuer les droits aux utilisateurs avec les ordres GRANT et REVOKE.
L’attribution des droits est réservée aux créateurs de la table, néanmoins l’administrateur de la base peut donner l’autorisation à un autre user la possibilité de gérer les droits sur les tables.
La commande GRANT permet d’attribuer par utilisateur de la base de données des accès sur une ou plusieurs tables.
Les droits les plus couramment utilisés sont :
-
SELECT : autorise la sélection de données.
-
UPDATE : autorise la modification de données.
-
DELETE : autorise la suppression de données.
-
INSERT : autorise l’insertion de données.
La syntaxe est la suivante
GRANT <droit1>, <droit2>, ...
ON TABLE <nom table>
TO <user1>, <user2> ...
[ WITH GRANT OPTION] Pour attribuer tous les droits, il faut utiliser la syntaxe suivante :
Syntaxe Oracle et PostgreSQL
GRANT ALL PRIVILEGES
ON <table1>, <table2>, ...
[ WITH GRANT OPTION] TO <user1>, <user2> ... Syntaxe MySQL
GRANT ALL PRIVILEGES
ON TABLE <table1>, <table2>, ...
[ WITH GRANT OPTION] TO <user1>, <user2> ... Exemple SQL Server
ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [ASMITH] Le ALL PRIVILEGES ou db_owner donne vraiment tous les droits, donc il doit être utilisé avec précaution et de préférence attribué à des personnes de profil administrateur.
La clause WITH GRANT OPTION donne l’autorisation aux users désignés d’attribuer des droits sur ces tables à d’autres utilisateurs.
Avec SQL Server, on affecte le rôle db_owner pour attribuer tous les droits à une base de données.
L’analyse des tables d’exemples permet de sortir les éléments qui vont nous indiquer comment attribuer ou pas des droits aux utilisateurs avec l’ordre GRANT.
Sélection des droits par utilisateur :
SELECT CODEACCES, ATFONC.IDENT_FONCT, NOM_TABLE, DROITS
FROM UTILISATEURS UTIL
INNER JOIN ATTRIB_FONC ATFONC ON UTIL.IDENT_PERS = ATFONC.IDENT_PERS
INNER JOIN FONCTIONS...Interdire l’accès (DENY)
1. Interdire l’accès à certains objets de la base
Avec SQL Server, il est possible d’interdire l’accès à certains objets de la base avec l’ordre DENY.
Exemple SQL Server
Interdit à M. Peroc de modifier la table ATTRIB_DROITS :
DENY ALTER ON ATTRIB_DROITS TO GPEROC Interdit tout à M. Peroc sur la table ATTRIB_DROITS :
DENY VIEW DEFINITION ON ATTRIB_DROITS TO GPEROC
DENY VIEW CHANGE TRACKING ON ATTRIB_DROITS TO GPEROC
DENY CONTROL ON ATTRIB_DROITS TO GPEROC
DENY INSERT ON ATTRIB_DROITS TO GPEROC
DENY UPDATE ON ATTRIB_DROITS TO GPEROC
DENY ALTER ON ATTRIB_DROITS TO GPEROC
DENY TAKE OWNERSHIP ON ATTRIB_DROITS TO GPEROC
DENY REFERENCES ON ATTRIB_DROITS TO GPEROC
DENY SELECT ON ATTRIB_DROITS TO GPEROC
DENY DELETE ON ATTRIB_DROITS TO GPEROC Supprimer des droits (REVOKE)
1. Supprimer des droits sur la manipulation d’une table
Une fois les droits attribués, il faut pouvoir les enlever si l’utilisateur quitte l’entreprise par exemple ou change de service. Pour ceci, nous utiliserons l’ordre REVOKE. Ne pas confondre DENY, qui interdit, avec REVOKE qui supprime un droit, sans interdire un accès.
La syntaxe Oracle est la suivante
REVOKE <droit1>, <droit2>, ...
ON TABLE <nom table>
FROM <user1>, <user2> ...; La syntaxe MySQL est la suivante
REVOKE <droit1>, <droit2>, ...
ON [TABLE <nom table>],[*]
FROM <user1>, <user2> ...; Pour enlever les droits de lecture de la table UTILISATEURS à Mr SMITH par exemple :
REVOKE SELECT ON UTILISATEURS FROM ASMITH; Pour enlever tous les droits à Mr Smith :
REVOKE ALL PRIVILEGES ON UTILISATEURS FROM ASMITH; De la même façon que le GRANT, si on ajoute le mot PUBLIC, on supprime les droits sur cette table pour tous les utilisateurs de la base de données.
REVOKE ALL PRIVILEGES ON UTILISATEURS FROM PUBLIC; 2. Supprimer des droits sur les objets de la base
Comme pour la manipulation d’une table, on peut supprimer des droits que l’on a attribués sur la création de table ou la création d’index par exemple.
Exemple Oracle
REVOKE CREATE TABLE FROM ASMITH; Exemple MySQL
REVOKE CREATE ON * ON 'ASMITH'; Pour...
Utilisation des rôles
Un rôle est un groupe auquel on va attribuer les mêmes droits. Au lieu d’attribuer les droits individuellement à chaque utilisateur, on peut créer des groupes qui auront des droits et ensuite affecter les utilisateurs dans un groupe.
Cette notion de rôle n’existe pas à l’heure actuelle dans MySQL.
Syntaxe Oracle
CREATE ROLE <nom rôle>; Exemple Oracle
CREATE ROLE CONTROLE_GESTION; Puis, il faut attribuer des droits au rôle que l’on vient de créer de la même façon que pour un utilisateur lambda.
GRANT ALL PRIVILEGES ON CLIENT TO CONTROLE_GESTION;
GRANT ALL PRIVILEGES ON FOURNISSEUR TO CONTROLE_GESTION;
GRANT ALL PRIVILEGES ON FACTURE TO CONTROLE_GESTION; Pour SQL Server
GRANT UPDATE ON FOURNISSEUR TO CONTROLE_GESTION Il faut ensuite attribuer ce rôle aux utilisateurs :
GRANT CONTROLE_GESTION TO ASMITH;
GRANT CONTROLE_GESTION TO BMARTIN;
etc ...
exec sp_addrolemember [CONTROLE_GESTION], ASMITH; La notion de rôle est assez intéressante dans les sites avec beaucoup d’utilisateurs, elle permet de simplifier la gestion des différents profils.
Il faut bien définir en amont les différents rôles dans l’utilisation de la base de données et dans les applications existantes et ensuite classer les utilisateurs par rôle. À chaque nouvel arrivant...
Supprimer un rôle
Pour SQL Server, il est nécessaire de supprimer les membres du rôle avant de supprimer ce dernier.
Syntaxe SQL Server
ALTER ROLE <nom rôle> DROP MEMBER <user>; Exemple SQL Server
ALTER ROLE CONTROLE_GESTION DROP MEMBER ASMITH; Syntaxe
DROP ROLE <nom rôle>; Exemple
DROP ROLE CONTROLE_GESTION; Exercices
Premier exercice
Créer un utilisateur ALFRED et lui attribuer les droits de créer une session et de sélectionner des données dans la table CASTING.
Deuxième exercice
Attribuer à tous les utilisateurs le droit de sélectionner des données dans la table FILM.
Troisième exercice
Attribuer des droits à l’utilisateur ALFRED afin qu’il puisse modifier dans la table FILM uniquement les colonnes TITRE et RESUME.
Quatrième exercice
Supprimer pour l’utilisateur ALFRED les droits sur la modification de la colonne RESUME.
Solutions des exercices
Premier exercice
Créer l’utilisateur :
CREATE USER ALFRED IDENTIFIED BY ALFRED; (Oracle)
CREATE USER ALFRED WITH PASSWORD (Postgresql);
Syntaxe SQL Server
USE master;
GO
CREATE LOGIN ALFRED
WITH PASSWORD = 'SqlServer.72';
USE CINEMA
Go
CREATE USER ALFRED FROM LOGIN ALFRED; L’autoriser à se connecter :
GRANT CREATE SESSION TO ALFRED; Lui permettre de sélectionner :
GRANT SELECT ON CASTING TO ALFRED; Deuxième exercice
Utilisation du mot PUBLIC :
GRANT SELECT ON FILM TO PUBLIC; Troisième exercice
Attribution des droits :
GRANT UPDATE (TITRE,RESUME) ON FILM TO ALFRED; Si maintenant l’utilisateur ALFRED essaye de modifier une autre colonne, il aura un message d’erreur lui indiquant qu’il n’a pas les droits :
UPDATE FILM SET GENRE1='Toto' WHERE IDENT FILM =1
*
ERREUR à la ligne 1 :
ORA-01031: privilèges insuffisants Sur la colonne TITRE en revanche, il est autorisé :
UPDATE TITRE SET TITRE='SUBWAY-' WHERE IDENT =1 ;
1 ligne(s) mise(s) à jour. Quatrième exercice
Suppression des droits UPDATE sur la colonne TITRE.
On ne peut pas supprimer les droits uniquement sur une colonne, le REVOKE s’applique sur toutes...
