Blog ENI : Toute la veille numérique !
Jusqu'à ce soir ! : -25€ dès 75€ sur les livres en ligne, vidéos... code FUSEE25. J'en profite !
Accès illimité 24h/24 à tous nos livres & vidéos ! 
Découvrez la Bibliothèque Numérique ENI. Cliquez ici
  1. Livres et vidéos
  2. SQL
  3. La sécurité des données (DCL)
Extrait - SQL Les fondamentaux du langage (avec exercices et corrigés) - (5e édition)
Extraits du livre
SQL Les fondamentaux du langage (avec exercices et corrigés) - (5e édition)
1 avis
Revenir à la page d'achat du livre

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.

Images/04RI01.PNG

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