Sécurisation de base de données SQL

 

 

Sécurisation de base de données SQL
ContexteTerra, une entreprise de conception et réalisation de constructions artisanales a besoin d’une structure informatique pour garder un suivi précis de l’avancée des divers projets dont elle s’occupe.
ObjectifMettre en place une base de données répondant aux besoins de l’entreprise et qui permette une gestion des effectifs de l’entreprise et des projets qu’elle entreprend. Les architectes peuvent ajouter des commentaires spécifiques à chaque projet et aussi ajouter divers média (photos). Cette base de données pourra ensuite être exploitée par un site intranet.

 

Compétences mises en œuvre
C24C31C37 Installer un SGBDAssurer les fonctions de base de l’administration d’un réseauAdministrer une base de données

 

 

Conditions de réalisation
Matériel Logiciels
–       1 serveur-       1 poste client –       VMware Workstation 7.1-       Windows Server 2008-       Windows 7 Ultimate-       SQL Server 2008

–       SQL Express

Autre : –

 

 

Description de l’activité réalisée
Situation initialeIl n’y a pas de base de données et aucun mécanisme de supervision n’existe.
Situation finaleUne structure de base de données est mise en place. Des données sont entrées via un script. Des rôles sont créés avec des droits qui leur sont propres et la création d’utilisateur est facilitée par l’introduction d’une procédure stockée.

 

 

 

Introduction

 

Voici le schéma conceptuel de la base de données CHANTIER :

securisationsql

 

Schéma relationnel

ARCHITECTE (NumArchitecte, NomArchitecte, PnomArchitecte)

OUVRIER (NumOuvrier, NomOuvrier, PnomOuvrier)

CONSTRUCTION (NumConstruction, NomConstruction, #NumArchitecte, DescConstruction)

MESSAGE (NumMessage, Jour, #NumArchitecte, #NumConstruction, TexteMessage)

MEDIA (NumMedia, #NumConstruction, LienMedia)

TRAVAILLER (#NumConstruction, #NumOuvrier, #Jour)

 

 

Scripts pour la création de la base de données

 

Pour mettre en place la base de données CHANTIER, nous utilisons ici deux scripts préalablement rédigés.

 

Nous nous servons du script script tables.sql qui contient la structure de la base de données CHANTIER. Ce script contient aussi l’ensemble des relations entre les tables (et leurs colonnes) et leurs contraintes associées (entre autre Primary Keys et Foreign Keys).

 

Un script script data.sql contenant les données nécessaires à l’entreprise est ensuite à exécuter pour peupler les différentes tables. On aura pris soin auparavant de vérifier que les données saisies respectent les contraintes des tables.

 

 

 

 

Définitions de permissions par des rôles

 

sp_addrole

 

On ajoute deux rôles permettant d’effectuer une gestion des permissions des différentes personnes pouvant accéder à la base de données.

 

USE CHANTIER

EXEC sp_addrole ‘RoleVisualisation’

EXEC sp_addrole ‘RoleArchitecte’

GO

GRANT SELECT ON ARCHITECTE TO RoleVisualisation

GRANT SELECT ON OUVRIER TO RoleVisualisation

GRANT SELECT ON TRAVAILLER TO RoleVisualisation

GRANT SELECT ON CONSTRUCTION TO RoleVisualisation

GRANT SELECT ON MESSAGE TO RoleVisualisation

GRANT SELECT ON MEDIA TO RoleVisualisation

GO

 

GRANT UPDATE, INSERT, DELETE ON CONSTRUCTION TO RoleArchitecte

GRANT UPDATE, INSERT, DELETE ON MEDIA TO RoleArchitecte

GRANT UPDATE, INSERT, DELETE ON MESSAGE TO RoleArchitecte

GO

 

Le rôle Visualisation permet à chacun de ses membres de pouvoir effectuer des requêtes concernant toutes les tables de la base de données CHANTIER.

 

Le rôle Architecte permet aux personnes en bénéficiant de mettre à jour, d’insérer et de supprimer des données dans 3 tables précises : CONSTRUCTION, MEDIA, MESSAGE.

 

Avec cette configuration, seul l’administrateur de la base de données peut créer des utilisateurs, attribuer des rôles et des permissions à ces rôles et ajouter, modifier ou supprimer des données des tables ARCHITECTE, OUVRIER et TRAVAILLER.

 

 

Création simplifiée d’utilisateur via une procédure stockée

 

La mise en place de procédures stockées permet de simplifier l’exécution d’une requête plus ou moins longue et de se servir d’une ou plusieurs variables pour orienter la requête et l’adapter à ses besoins.

 

Pour simplifier la tâche d’administration concernant la création d’utilisateur et de connexion SQL aux architectes, une procédure stockée a ainsi été créé. Celle-ci ajoute également les droits d’accès nécessaires à leur fonction.

 

CREATE PROCEDURE sp_creationuserarch @nomuser varchar(50)

AS

EXEC(‘CREATE LOGIN ‘ + ‘[TERRA’ + ‘\’ + @nomuser + ‘]’ + ‘ FROM WINDOWS’)

EXEC(‘CREATE USER ‘ + @nomuser + ‘ FOR LOGIN ‘ + ‘[TERRA’ + ‘\’ + @nomuser + ‘]’)

EXEC sp_addrolemember ‘RoleVisualisation’, @nomuser       /* ajout utilisateur dans rôle RoleVisualisation */

EXEC sp_addrolemember ‘RoleArchitecte’, @nomuser   /* ajout utilisateur dans rôle RoleArchitecte */

GO

— on effectue le GO qu’une fois que l’on n’a plus besoin des variables

 

Cette procédure stockée permet donc la création d’une connexion SQL sur le serveur SGBD-SV01 pour tout utilisateur du domaine TERRA qu’on spécifiera dans la variable @nomuser lors de l’exécution de cette procédure.

On se sert du paramètre FROM WINDOWS pour la création de la connexion. Aussi, on ne définit pas de mot de passe pour cette connexion, l’authentification Windows pouvant suffire dans ce cas.

La création de l’utilisateur se fait de la même manière, en se servant toujours de la variable @nomuser.

Ensuite, on ajoute l’utilisateur au rôle RoleVisualisation et au rôle RoleArchitecte. Les architectes seront ainsi facilement ajoutés en tant qu’utilisateurs avec des droits spécifiques leur permettant de modifier, insérer ou ajouter des données dans certaines tables et de pouvoir faire des requêtes en SELECT sur toutes les tables. Il suffira à l’administrateur d’exécuter la procédure (dans l’environnement CHANTIER comme suit pour ajouter le compte Windows de l’architecte Sophie Chanta :

 

EXEC sp_creationuserarch SChanta

 

Des procédures similaires peuvent être faites pour simplifier la création d’utilisateur et l’attribution de droits spécifiques à des contrôleurs de gestion sur la table TRAVAILLER par exemple.

 

 

Requêtes SQL

 

Tout architecte qui a une connexion SQL peut ainsi ajouter des données via des requêtes comme suit :

— Insertion dans la table CONSTRUCTION

— NumConstruction ; NomConstruction ; NumArchitecte ; DescConstruction

INSERT INTO CONSTRUCTION

VALUES (6, ‘Jardins suspendus’, 3, ‘200 m² de jardins aux abords du Château de Ferini’)

 

Il aura des erreurs s’il tente d’exécuter des requêtes insérant par exemple des données dans des tables sur lesquelles il n’a pas de permissions.

 

D’autres requêtes d’affichage sont possibles par chaque utilisateur ayant un compte Windows et une connexion SQL et ayant les droits nécessaires. Celles-ci peuvent permettre de visualiser plus facilement la gestion des jours de travail de chaque ouvrier par exemple :

 

SELECT TRAVAILLER.NumOuvrier, NomOuvrier, COUNT(*) AS nb_jour_travaillé

FROM OUVRIER, TRAVAILLER

WHERE OUVRIER.NumOuvrier = TRAVAILLER.NumOuvrier

GROUP BY TRAVAILLER.NumOuvrier, NomOuvrier

HAVING COUNT(NomOuvrier) >=ALL

(SELECT COUNT(*) FROM OUVRIER GROUP BY NomOuvrier)
Cette requête affiche le nombre de jours travaillés au total pour chaque ouvrier.

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *