TP General PDF

PROMO: TP DE PREPARATION DE L’INTERROGATION
COURS: ADMINISTRATION DES BASES DES DONNEES AVEC SQL SERVER

TRAVAIL PRATIQUE : DDL, DML, TCL, CONFIG NETWORK, PROCEDURES & FONCTIONS
EXIGENCES : GROUPE DE 4 étudiants avec 2 Ordinateurs
SUJET: DDL, DML, SELECT
Créer deux bases de données dont les tables sont reprises dans les deux modèles ci-dessous.
NB : La première sera créée en utilisant les commandes SQL et la seconde en utilisant les commandes
graphiques de SQL Server Management Studio.
Nom de la base des données ESIS_DB
Exigences :
Toutes les colonnes les clés primaires sont de type int et auto-incréments.
Table ETUDIANT
1. Ajouter la colonne Tel (numéro de téléphone) par défaut la valeur est 0990000000
2. L’âge de l’étudiant ne peut pas être inférieur à 19
3. L’adresse Etudiant est non Null
Table INSCRIPTION
Après création de la table

1. Ajouter la colonne DateInscription de type date
2. Ajouter une contrainte sur cette colonne de telle sorte que la date par défaut soit la date
courante de votre système d’exploitation
NB : La date courante est renvoyée par la fonction GETDATE()
Table PROMOTION
Après création de la table

1. Ajouter la colonne filiere
2. Ajouter une contrainte sur cette colonne de telle sorte la valeur soit parmi les valeurs
suivantes PREPA, G1, GL, AS, TLC, DESIGN
INSERER AU MOINS 10 lignes dans chaque table.
Nom de la base des données BIBLIO_DB
Pour cet exercice, il vous est demandé d’utiliser les commandes graphiques de SSMS pour la création
de la base des données et des tables.
Veuillez au sens de chacune des relations présentées dans la figure ( Toutes les clés primaires sont
auto-incrément)
Bibliothèque
1. Sélectionner l’auteur qui a le plus écrit

2. Sélectionner les auteurs qui ont plus de 5 livres
3. Sélectionner l’auteur le plus lu
4. Sélectionner le livre le plus emprunté
5. Sélectionner le plus grand emprunteur de l’année 2016
SUJET : GESTION DES UTILISATEURS ET DES PRIVILEGES
Bases des données de travail : BIBLIO_DB et ESIS_DB.
Exigences : commandes
Travail demandé:
Login
Créer trois logins C_SQL, NOMDeVotrePC_PC\Nom_Compte, loginPublic dont les spécifications de

chacun sont présentées ci-dessous.
1. Le login C_SQL a comme mot de passe G2AS et la base des données par défaut ESIS_DB.
Affecter à ce login le rôle sysadmin et essayer de se connecter au serveur.
2. Le login NOMDeVotrePC_PC\Nom_Compte doit être mappé à un compte Windows de votre
machine. Ce login est du rôle public.
3. Le loginPublic est du rôle public, base des données par défaut ESIS_DB
Pour attribuer un rôle serveur à un login on utilise la commande:

 EXECUTE sp_addsrvrolemember ‘nomLogin', ‘serverRole';
 SQL Server offre des rôles prédéfinis au niveau serveur dont voici la liste :
– sysadmin : droit de vie ou de mort sur le serveur (privilège le plus haut au niveau serveur)
– securityadmin : autorisé à gèrer les connexions et leurs propriétés (assignation
d’autorisations GRANT, DENY et REVOKE de niveau du serveur, mais aussi au niveau de
chaque base de données. Ils peuvent aussi réinitialiser les mots de passe pour des connexions
SQL)
– serveradmin : autorisé à modifier les options de configuration du serveur (via sp_configure)
et arrêter le serveur.
– setupadmin : autorisé à ajouter et supprimer des serveurs liés et exécuter certaines
procédures stockées du système
– processadmin : autorisé à lire et arrêter les processus en cours d’exécution dans le moteur
de base de données
– diskadmin : autorisé à gérer les fichiers des bases de données sur les disques.
– dbcreator : autorisé à modifier, supprimer et restaurer n’importe quelle base de données. la
restauration étant une forme de création de base de données.
– bulkadmin : autorisé à exécuter l’instruction BULK INSERT.
Utilisateur
Créer trois utilisateurs u_admin, u_utilisateur et u_biblio
1. U_admin appartient à la base des données ESIS_DB, a le rôle prédéfini db_owner et est lié au
Login C_SQL
2. U_biblio appartient à la base des données BIBLIO_DB et est lié au Login mappé à un compte
Windows
3. U_utilisateur appartient à la base des données ESIS_DB et est lié à la connexion loginPublic
Le mappage d’un utilisateur à un login se fait à l’aide la commande suivant:

 USE nom_base;
CREATE USER nom_utilisateur FOR LOGIN nom_connexion;
L’attribution des rôles à un utilisateur se fait à l’aide de:
 USE bddCible;
EXECUTE sp_addrolemember ‘dbRole', ‘Utilisateur';
La liste des rôles prédéfinis:
 db_owner : droit de vie ou de mort sur la base de données, y compris suppression. (plus haut
privilège au niveau bases de données).
db_securityadmin : autorise de gérer la sécurité (appartenance au rôle, gestion des
privilèges)
db_accessadmin : autorise d’ajouter ou supprimer l’accès à la base de données des
connexions Windows, des groupes Windows et des connexions SQL Server.
db_backupoperator : autorise d’effectuer des sauvegardes de la base de données.
db_ddladmin : autorise d’exécuter n’importe quelle commande DDL (Data Definition
Language – CREATE, ALTER, DROP …) dans une base de données.
db_datawriter : autorise d’ajouter, supprimer et modifier des données dans toutes les tables
ou vues utilisateur. Attention : ils ne peuvent pas lire (SELECT) dans ces mêmes tables ou
vues.
db_datareader : autorise de lire toutes les données de toutes les tables utilisateur.
db_denydatawriter : interdit d’ajouter, modifier ou supprimer les données des tables ou vues
utilisateur d’une base de données. Mais autorise à les lire.
db_denydatareader : interdit de lire les données des tables utilisateur d’une base de
données. Mais autorise à les écrire.
Rôles
A l’aide de la commande CREATE role NomDuRole
Créer le rôle R_UTILISATEUR sur la base des données ESIS_DB.
1. Affecter à ce rôle les privilèges suivant :

a. INSERT
b. UPDATE
2. Attribuer ce rôle à u_utilisateur
3. Essayer d’insérer et de faire la mise à jour dans la table ETUDIANT
4. Comme administrateur, lister les privilèges accordés à u_utilisateur grâce à
INFORMATION.SCHEMA
Privilèges individuels
1. Accorder à u_utilisateur le privilège de suppression des lignes de la table ETUDIANT

2. Accorder à u_utilisateur le privilège de sélection sur la table ETUDIANT avec la possibilité
de redistribution
3. Révoquer les privilèges d’insertion et de sélections à u_utilisateur sur la table ETUDIANT
4. Tester la sélection
5. Tester l’insertion
Sujet: CONFIGURATION RESEAU
Faire la configuration réseau des 2 machines du groupe. Et tester la connexion en réseau avec les
logins et utilisateurs créés à l’exercice précédent
Sujet: PROCEDURES STOCKEES

Exercice 1 Affichage des étudiants et promotions
1. Ecrire une procédure selectionPromo qui affiche toutes les promo et filières de l’école.
2. Ecrire une procédure stockée etudiantPromo qui prend en paramètre une année et qui
affiche tous les étudiants( nom, age, adresse) et leurs promotions (nom de la promotion)
cette année académique.
Exercice 2 Inscription d’un étudiant
1. Ecrire une procédure stockée inserePromo qui prend en paramère le nom de la promotion,
la filière et insère dans la table promotion.
Test: utiliser votre procédure pour insérer (‘master 1’,’AS’)
2. Ecrire une procédure stockée inscrireEtudiant qui prend en paramètre le nom, l’âge,
l’adresse de l’étudiant, l’année d’inscription et le nom de sa promotion et sa filière qui inscrit
cet étudiant c.-à-d. insérer dans les tables (Etudiant et Inscription) les bonnes informations.

TP General PDF

Transféré par

Droits d'auteur :

Formats disponibles

TP General PDF

Transféré par

Informations du document

Titre original

Copyright

Formats disponibles

Partager ce document

Partager ou intégrer le document

Options de partage

Avez-vous trouvé ce document utile ?

Ce contenu est-il inapproprié ?

Droits d'auteur :

Formats disponibles

TP General PDF

Transféré par

Droits d'auteur :

Formats disponibles

PROMO: TP DE PREPARATION DE L’INTERROGATION

COURS: ADMINISTRATION DES BASES DES DONNEES AVEC SQL SERVER

SUJET: DDL, DML, SELECT

Nom de la base des données ESIS_DB

Après création de la table

Après création de la table

INSERER AU MOINS 10 lignes dans chaque table.

Nom de la base des données BIBLIO_DB

1. Sélectionner l’auteur qui a le plus écrit

Bases des données de travail : BIBLIO_DB et ESIS_DB.

Créer trois logins C_SQL, NOMDeVotrePC_PC\Nom_Compte, loginPublic dont les spécifications de

Pour attribuer un rôle serveur à un login on utilise la commande:

Créer trois utilisateurs u_admin, u_utilisateur et u_biblio

Le mappage d’un utilisateur à un login se fait à l’aide la commande suivant:

La liste des rôles prédéfinis:

A l’aide de la commande CREATE role NomDuRole

Créer le rôle R_UTILISATEUR sur la base des données ESIS_DB.

1. Affecter à ce rôle les privilèges suivant :

1. Accorder à u_utilisateur le privilège de suppression des lignes de la table ETUDIANT

Sujet: PROCEDURES STOCKEES

Exercice 2 Inscription d’un étudiant

Vous aimerez peut-être aussi