TP6 SGBD1: Système de gestion de base de données 1(Notion de sécurité SQL SEVER) | Exam-Lib
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

TP6 SGBD1: Système de gestion de base de données 1(Notion de sécurité SQL SEVER)

abdelouafiMar 22, 2018

    1. abdelouafi

      abdelouafi Administrator Staff Member

      Messages:
      617
      Likes Received:
      12
      Trophy Points:
      18
      Joined
      Sep 13, 2016
      Exercice 1 :
      • Client (Nclt, Nom, Adresse, Ville)
      • Abonnement (Code, Type, Tarif)
      • Souscrire(NumTel, Nclt, Code, Date)
      1) Créer la base de donnée en précisant les clés primaires et étrangères.
      2) Remplir les tables par des données.
      3) Créer un compte de connexion ‘visiteur’ avec le mot de passe ‘1234’ et la base de données par défaut ‘S8EXO1’.
      4) Créer une vue AbonClt qui affiche pour chaque client (nom) les types et les numéros de téléphone auxquels il est souscrit.
      5) Interdire à ‘visiteur’ l’accès en sélection et modification à toutes les tables sauf à la vue AbonClt.
      6) Connecter vous en tant que ‘visiteur’ et tester.
      7) Reconnecter vous avec votre session initiale et créer un rôle ‘Agent’, attribuer lui le droit de sélection sur la table Client, et retirer lui ce droit de la table Abonnement puis interdire la sélection à ‘Agent’ pour la table Souscrire.
      8) Créer un utilisateur ‘Agent1’ avec le mot de passe ‘4321’ et la base de données par défaut ‘S8EXO1’.
      9) Ajouter l’utilisateur ‘Agent1’ au rôle ‘Agent’.
      10) Attribuer à l’utilisateur ‘Agent1’ le droit de sélection sur la table Abonnement et Souscrire.
      11) Connectez vous en tant que ‘Agent1’ et essayez d’afficher le type et tarif des abonnements puis le Nclt, le Code et le numéro de téléphone des clients, qu’est ce que vous remarquez et comment vous expliquez les résultats ?

      Exercice 2 :
      Soit le schéma conceptuel suivant :
      mcd.png


      Questions :

      1) Créer la base de données sous le nom Mabase.
      2) Ajouter à votre base Trois utilisateurs : USER1, USER2 et USER3 mappés respectivement sur les logins LOGIN1, LOGIN2 et LOGIN3.
      3) Créer un schéma SC qui doit contenir les tables Proposer, Contrat et Déplacement déjà créées.
      4) Définir ce SC comme schéma par défaut des utilisateurs USER2 et USER3
      5) Donner le doit de sélection et de modification de ces trois tables aux utilisateurs USER1 et USER2
      6) Comment tester le résultat de 6) ?
      7) Ajouter un autre utilisateur USER4 mappé sur une connexion L4 puis lui interdire la sélection, la mise à jour et la suppression sur la table contrat
      8) Créer une vues V_Contrat permettant d’afficher les n-uplets de la table contrat
      9) Donner à USER4 les droits : sélection, mise à jour et suppression sur la vue V_Contrat
      10) Dans une session ouverte avec la connexion LOGIN4, réaliser le test pour vérifier les droits déjà attribués, que peut-on déduire ?
      11) Ajouter à votre Base les éléments suivants :
      • a. Un Rôle SelectRole qui doit avoir le droit de sélection sur toutes les tables du schéma SC.
      • b. Un Rôle UpdateRole qui doit avoir le droit de mise à jour sur les tables du schéma SC.
      12) Comment donner le droit de sélection et de modification pour l’utilisateur USER3 sur toutes les tables? Proposer deux solutions et tester chacune.
      13) Utiliser l’instruction Deny pour interdire à l’utilisateur USER3 de faire la sélection sur la table Contrat
      14) Tester le résultat.
       
      Loading...
      Exercice 1
      Create DataBase TPSecurite
      Go
      use TPSecurite
      Create Table Client(NClt int primary key,Nom Varchar(30),Adresse Varchar(30),Ville Varchar(30))
      Create Table Abonnement(Code int primary key,Type Varchar(30),Tarif float)
      Drop Table Abonnement
      Drop Table Souscrire
      Create Table Souscrire(NumTel int primary key,NClt int Constraint fk1 Foreign key (NClt) references Client(NClt),Code int Constraint fk2 Foreign key (Code) references Abonnement(Code),Date date)
      Alter Table Souscrire Alter Column NumTel varchar(30)
      ---2
      insert into Client values(1,'Tamma','Massira1','Marrakech')
      insert into Client values(2,'Chakir','Sokoma','Tanger')
      insert into Client values(3,'Elazizi','Mhamid','Marrakech')
      insert into Client values(4,'Nasiri','Syba','Fes')
      insert into Client values(5,'Nachit','Asif','Rabat')
      insert into Abonnement values(1,'type1',2000)
      insert into Abonnement values(2,'type2',1000)
      insert into Abonnement values(3,'type3',500)
      insert into Souscrire values('0608354031',1,1,'08/11/2016')
      insert into Souscrire values('0608174128',3,2,'25/10/2016')
      insert into Souscrire values('0615235614',2,3,'18/09/2016')
      insert into Souscrire values('0624152698',4,1,'17/12/2016')
      --3
      Create Login visiteur
      with Password ='1234', Default_DataBase=TPSecurite
      --4
      Create View AbonClt
      as
      Select c.Nom,a.Type,s.NumTel
      From Client c inner join Souscrire s on c.NClt=s.NClt inner join Abonnement a on s.Code=a.Code
      Group By c.Nom,a.Type,s.NumTel
      --5
      Create User Visiteur For Login Visiteur
      Deny Select,Update on Client To Visiteur
      Deny Select,Update on Abonnement To Visiteur
      Deny Select,Update on Souscrire To Visiteur
      Grant Select,Update,Delete on AbonClt To Visiteur
      --6
      Se Connecter avec Le Compte Visiteur et tester les permissions
      --7
      Use TPSecurite
      Grant Role Agent
      Grant Select on Client To Agent
      Revoke Select on Abonnement To Agent
      Deny Select On Souscrire To Agent
      --8
      Create Login Agent1 with Password='4321'
      Default_DataBase=TPSecurite
      Create User Agent1 For Login Agent1
      --9
      Esec SP_ADDROLENUMBER 'Agent','Agent1'
      --10
      Grant Select on Abonnement To Agent1
      Grant Select on Souscrire To Agent1
      --11
      --Se Connecter au Compte Agent1
      Select Type,Tarif
      From Abonnement
      Select NClt,Code,NumTel
      From Souscrire
      EXERCICE 2
      Create DataBase Mabase
      Go
      use Mabase
      select CURRENT_USER
      --2
      Create Login Login1 with PassWord='1234'
      Create Login Login2 with PassWord='1234'
      Create Login Login3 with PassWord='1234'
      Create User USER1
      for Login LOGIN1
      Create User USER2
      for Login LOGIN2
      Create User USER3
      for Login LOGIN3
      Create Table Deplacement(NumDep int primary key,VilleDep varchar(30),VilleArr varchar(30))
      Create Table Contrat(NumContrat int primary key,Date date)
      Create Table Catgorie(CodeCtg int primary key ,LibelleCtg varch(30))
      Create Table Compagnie(CodeCp int primary key, NomCp varchar(30))
      Create Table Proposer(NumDep int ,CodeCp int ,primary key(NumDep,CodeCp))
      Create Table Negocier(CodeCtg int foreign key , NumContrat int foreign key,NbrPalce int,PrixPlace float,primary key (CodeCtg,NumContrat))
      --3
      Create Schema SC
      Alter Schema SC Transfer dbo.Contrat
      Alter Schema SC Transfer dbo.Departement
      Alter Schema SC Transfer dbo.Proposer
      --4
      Alter User USER3 with Default_Schema=SC
      Alter User USER2 with Default_Schema=SC
      --5
      Grant Select,Update on Schema=SC To USER1,ESER2
      --6
      Tester
      --7
      Use master
      Go
      Create Login Login4 with PassWord='1234'
      Use master
      Go
      Create Use USER4 for Login Login4
      Deny Select,Update,Delete on SC.Contrat To USER4
      --8
      Create View V_Contrat
      AS
      Select *
      From SC.Contrat
      --9
      Grant Select,Update,Delete on V_Contrat To USER4
      --10 Se COnnecter avec User4
      Select *
      From SC.Contrat
      Select *
      From V_Contrat
      --11
      Use Mabase
      go
      Create Role SelectRole
      Grant Select on Schema= SC To SelectRole
      Create Role UpdateRole
      Grant Upadate on Schema= SC To UpdateRole
      --12
      Exec sp_ADDROLENUMBER 'SelectRole','USER3'
      Exec sp_ADDROLENUMBER 'UpdateRole','USER3'
      --13
      Deny Select On SC.Contrat To USER3
      --14
      --Se Connecter avec USER3 et tester

Share This Page

Share