Sécurité sur une base SQLSERVER TP 1

Sécurité sur une base SQLSERVER TP 1
Problématique
Mettre de la sécurité d’accès sur les objets d’une base de données SQL SERVER.
Pré requis
Vous savez créer une base de données dans l’environnement de SQL SERVER avec
les commandes du langage de définition de données LDD (ex CREATE TABLE …).
Vous savez manipuler les données avec les commandes de manipulation de données
LMD (Ex SELECT * FROM…). Vous avez vu qu’une interface graphique permet de
faire les mêmes opérations plus rapidement et dans un meilleur confort, pensez à la
commande INSERT INTO pour implémenter les données et en vis à vis la grille de
saisie des données. Vous savez écrire des scripts pour définir l’environnement de la
base, clés primaires, clés étrangères, index, intégrité référentielle.
Objet du TP
La sécurité via l’authentification par nom d’utilisateur et mot de passe. On utilisera le
langage DCL, Data Control Language.
Moyens mis en oeuvre
Le logiciel SQL SERVER 7 ou SQL SERVER 2000, Windows2000 professionnel. ou
Windows XP professionnel.
Il faut d’abord migrer les données de la base ACCESS CONTACT.MDB vers la base SQL
SERVER CONTACTSEC. Il vous faut donc procéder en faisant une importation de données.
La structure des tables et les données sont importées. Évidement les autres objets ne sont pas
importés.
Pour mettre de la sécurité sur une base nous allons utiliser les comptes utilisateurs de
Windows2000 ou de Windows XP professionnel.
Lors de l’installation de SQL SERVER, lors de la création d’une base de données il faut faire
le choix entre les utilisateurs administrés par W2000 ou XP et ceux de SQL SERVER.
A°) PREPARATION du Contexte
TRES IMPORTANT LA COHERENCE DES DONNEES, intégrité référentielle
Pour argumenter nous allons faire des opérations sur la base de données avant la mise des
liens d’intégrité puis nous allons faire les mêmes opérations après avoir mis les liens
d’intégrité.
Avant / Après
1°) Définition des clés
- Les clés ne sont pas définies lors d’une migration de ACCESS vers SQL SERVER, il faut
donc les définir. On utilisera l’outil graphique.
- Définissez les clés primaires dans l’ordre requis. Il faut bien sûr que les attributs qui vont
être définis comme clé ne puissent pas prendre de valeur null. Vous pouvez faire l’essai de
créer dans la table contact une clé primaire sur [num-ele] et [num-ent]. Cela est impossible,
vérifiez-le. Il faut le faire sur : [num-ele] et [num-ent] et [date-contact]. La création des clés
primaires avec l’outil graphique peut être enregistrée comme script SQL. Ceci peut-être très
intéressant pour écrire l’ébauche de scripts qui mettent à jour la structure d’une base de
données.
2°) Définition de l’intégrité référentielle
- Dans CONTACTSEC, créez l’occurrence 15, 1011, 12/12/2004, ANGE.
Vous avez créé cette occurrence alors que l’étudiant 15 n’existe pas. Votre base de données
n’est plus cohérente.
- Supprimez cette occurrence soit graphiquement soit dans analyseur de requête : DELETE
FROM CONTACT WHERE [num-ele] = 15. vérifiez. Vous remarquerez qu’il faut mettre les
crochets, parce que les attributs ont le caractère trait d’union - Mettez les liens d’intégrité référentielle soit en construisant le diagramme avec les liens soit
en lançant les scripts appropriés (avec les verbes FKindex FOREIGN KEY attribut
REFERENCES).
Vous noterez, qu’en cochant l’option vérification lors de la création ou de la mise à jour ou de
la suppression, cela devient effectif (réel). Attention il vous faut être patient et faire après
chaque opération ACTUALISER dans SQL pour que cela devienne effectif.
Refaites l’essai de l’insertion de l’occurrence 15, 1011, 12/12/2004, ANGE dans la table
contact. Cela est désormais impossible, sinon c’est que vous n’avez pas mis l’intégrité
référentielle. Pourquoi ? Parce que l’occurrence 15 n’existe pas dans ETUDIANT.
Remarque : l’intégrité référentielle sert pour les créations, les mises à jour, les suppressions.
Elle n’est pas utile si on ne fait que des consultations.
B°) Mise en place d’une sécurité Utilisateur sur la base de données
Il faut créer plusieurs utilisateurs sur W2000 ou XP.. Vous avez par exemple :
Administrateur, Invité, IGN qui est également administrateur, Pierre, Paul, Dominique et
lambda qui sont utilisateurs.
Administrateur
Ign
Pierre
Paul
Invité
Lambda
Dominique
Administrateur
OUI
OUI
Invité
Utilisateur ordinaire
OUI
OUI
OUI
OUI
OUI
REALISATION
Vous êtes loggé en tant qu’administrateur.
Vérifiez que vous pouvez tout faire sur CONTACTSEC, SELECT par exemple.
Loggez-vous en tant que Paul
Vérifiez que vous n’avez pas accès à CONTACTSEC. Il est marqué accès refusé.
Retourner en administrateur. Vous pouvez avant toute autre manipulation constater qu’il n’y a
rien dans l’objet utilisateur si ce n’est l’utilisateur DBO et rien dans l’objet connexion.
Etape 1 créer des connexions et des utilisateurs propres à CONTACTSEC.
A l’aide de l’icône sécurité, connexion, nouvelle connexion etc.
Créer une connexion Paul pour la base CONTACTSEC.
Il faut créer une connexion en faisant nouvelle connexion sur l’onglet sécurité. Vous utilisez
ainsi l’assistant. Nom_poste\paul pour la base CONTACTSEC, compte utilisateur de NT,
n’oubliez pas de précisez une seconde fois par l’onglet Autorisation le nom de la base.
Vérifier qu’une connexion est ajoutée dans l’objet connexion commun à toutes les bases
définies dans SQL SERVER ainsi que l’ utilisateur Paul dans l’objet utilisateur relatif à
CONTACTSEC
Vérifiez si maintenant Paul à accès.
Etape 2.Vérification de l’accès.
- Loggez-vous en tant que Paul.
- Est-ce que vous avez accès à la base CONTACTSEC. OUI
- Est-ce que vous pouvez faire un select sur la table ETUDIANT. Non
- Est-ce que vous avez accès à la base PUBS. OUI
- Est-ce que vous pouvez faire un select sur la table AUTHORS. Oui
La base de données PUBS est une base de l’environnement SQL SERVER, elle
servait à la formation pour les versions antérieures de SQL SERVER.
Maintenant c’est plutôt la base NORTHWIND qui est utilisée. Il faut savoir que
Microsoft donne l’accès à ces 2 bases pour tout utilisateur de SQL SERVER. En
fait lors de la création de l’utilisateur Paul celui-ci est mis par défaut dans le
rôle PUBLIC, rôle qui donne les autorisations des opérations du LMD (ex
SELECT) aux objets de ces 2 bases.
Etape 3 Mise en place des autorisations pour CONTACTSEC, CNC,
éléments de réponse.
- Vérifier (interface graphique) et noter les opérations (SELECT) que vous
pouvez effectuer sur PUBS, NORTHWIND, CONTACTSEC selon que vous
ouvrez une session en administrateur, IGN, Pierre, Paul. Ceci est un résumé de
l’étape 2.
- Quels icônes devez-vous utiliser pour parcourir dans la base CONTACTSEC
les autorisations allouées ?
modifier la connexion Paul afin d’étendre le lien de la connexion à la
base CLINIQUES, autorisations que l’on obtient en faisant bouton droit
sur la connexion. Y-a-t-il un utilisateur Paul de créé dans l’objet
CLINIQUES ?
-Qui peut utiliser la clause SELECT ? Réponse : personne par défaut
- Qui peut créer de nouvelles tables ? Qui peut gérer les autorisations ?
-L’administrateur
- Qui a le droit à tout ? Réponse : l’administrateur
- Est-ce que Paul à accès à la base CLINIQUES ? Réponse : Non
- Que faut-il faire pour que Pierre ait accès à CLINIQUES ? Réponse :
créer une connexion.
- Quelles autres modifications faut-il faire pour que Pierre puisse faire un
SELECT sur une table de CLINIQUES ? - allouer les autorisations SELECT,
INSERT, UPDATE, DELETE.