Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

1 UMBB, Département Informatique Cours Master 1 – BDA Responsable : A. AIT-BOUZ

1 UMBB, Département Informatique Cours Master 1 – BDA Responsable : A. AIT-BOUZIAD Le 06 Décembre 2011 CHAPITRE 2 CONTRÖLE DE DONNEES DANS SQL I Gestion des utilisateurs et de leurs privilèges I.1 Gestion des utilisateurs - Pour accéder à un SGBD, il faut disposer d’un compte donc d’un mot de passe et un ensemble de droits identifiés par le SGBD. - Seul un administrateur peut créer des utilisateurs. La commande de création d’un utilisateur est la suivante : GRANT [CONNECT][,RESOURCE][,DBA] TO <utilisateur> [IDENTIFIED BY <mot de passe>] ; CONNECT, RESSOURCE et DBA défissent des classes d’utilisateurs (appelées rôles) avec des droits prédéfinis : a) CONNECT : droits de connexion et d’exécution d’ordre LMD sur des données sur lesquelles une autorisation a été attribuée au préalable. Droits aussi à la création de vues et synonymes sur les objets autorisés. Par contre ne donne pas les droits de créer des tables, index ou d’autres types d’objets. b) RESOURCE : (ie. création de ressources) Donne les droits de CONNECT plus les droits d’exécution des ordres LDD. Autrement dit, les droits : a. de ‘CONNECT’, b. de création de tables, index, et de regroupement (clusters), c. d’attribution et restitution de privilèges sur les tables, index et regroupement à d’autres utilisateurs. c) DBA : (DataBase Administrator) donne tous les droits. Autrement dit, les droits : a. de ‘CONNECT’, b. de ‘RESOURCE’, c. d’accès à toutes les données de tous les utilisateurs, d. de création et de suppression d’utilisateurs et de droits, e. d’exécuter toutes les opérations d’administration sur les partitions, de sauvegarde, de restauration, etc . Exemple 1 : Créer l’utilisateur ‘Bouziad’ avec le mot de passe ‘Ahmed’ avec le droit de connexion. GRANT CONNECT TO Bouziad IDENTIFIED BY Ahmed; ← Obligatoire si création d’un user ou modification du mot de passe Rq : Ne peut être exécuté que par le DBA 2 Exemple 2 : Donner à ‘Bouziad’ créer ci-dessus la possibilité de créer des tables. GRANT RESOURCE TO Bouziad ; I.2 Gestion des droits des utilisateurs sur les objets - Toute table n’est initialement accessible que par l’utilisateur qui la créée. - Le propriétaire d’un objet telle qu’une table peut accordé des droits sur cet objet (table) à d’autres utilisateurs. La syntaxe de la commande est la suivante : GRANT <droit1>, <droit2>, … ON <Objet> TO <user1>, <user2>, … [WITH GRANT OPTION]; ← Octroi le droit d’accorder ce droit à d’autres utilisateurs Exemple 3 : Attribuer à ‘Bouziad’ le droit de sélection et de mise à jour de la table ‘CLIENT’. GRANT SELECT, UPDATE ON CLIENT TO Bouziad ; La commande inverse de l’attribution des droits a la syntaxe suivante : REVOKE <droit1>, <droit2>, … ON <Objet> FROM <user1>, <user2>, … Un droit ne peut être retiré que par l’utilisateur qui l’a accordé ou par le DBA. Remarque : on peut utiliser les options - ALL pour désigner tous les droits, - PUBLIC pour désigner tous les utilisateurs. Exemple 4 : Interdire toute les opérations à tous les utilisateurs sur la table CLIENT. REVOKE ALL ON CLIENT FROM PUBLIC ; Rq: La table CLIENT devient privée. 3 I.2.1 Utilisation des rôles Définition : ‘Rôle’ Un rôle est un objet auquel on peut donner ou retirer des privilèges. On peut le voir comme un nom de groupe décrivant un ensemble de privilèges. Caractéristiques : - On peut attribuer ou retirer des privilèges à un rôle comme pour un utilisateur - Le rôle est attribué à un utilisateur comme s’il s’agissait d’un privilège élémentaire. Cet utilisateur bénéficiera des privilèges contenus dans le rôle. But d’un rôle : - Réduire la complexité de l’administration des privilèges - Gestion dynamique des privilèges - Sélection des privilèges actifs. La syntaxe de création de rôles est la suivante : CREATE ROLE <rôle> ; Il faut avoir le privilège create role Exemple 5 : CREATE ROLE AGENTS ; CREATE ROLE RESPONSABLES ; Exemple 6 : Attribuer le droit de lecture sur la BD ‘Client-Fournisseur’ au rôle AGENTS GRANT SELECT ON CLIENT TO AGENTS; GRANT SELECT ON COMPTE TO AGENTS; GRANT SELECT ON CC TO AGENTS; GRANT SELECT ON FOURNISSEURS TO AGENTS; Exemple 7: Attribuer tous les droits au rôle RESPONSABLES GRANT ALL ON CLIENT TO RESPONSABLES; GRANT ALL ON COMPTE TO RESPONSABLES; GRANT ALL ON CC TO RESPONSABLES; GRANT ALL ON FOURNISSEURS TO RESPONSABLES; Exemple 8: Attribuer le rôle AGENTS à ‘Bouziad’ GRANT AGENTS TO Bouziad; Exemple 9: Attribuer le rôle RESPONSABLES à ‘TOTO’ GRANT RESPONSABLES TO TOTO; 4 II Manipulation des données à travers des vues Définition : - C’est une perception particulière des données d’une ou de plusieurs tables, - Elle est stockée sous forme de requête de sélection, La vue permet de : - manipuler les données autrement qu’à travers les tables définies dans la base. - restreindre l’accès à certaines colonnes ou certaines lignes d’une table en autorisant un ou plusieurs utilisateurs à manipuler cette table qu’à travers une vue - simplifier la tâche de l’utilisateur en le déchargeant de la formulation de requêtes complexes. Remarque : On manipule une vue comme on manipule une table (voir plus loin) II.1 Création d’une vue La syntaxe de création d’une vue est la suivante : CREATE VIEW <nom_vue> [(colonne1, …)] AS <Requête_SQL> ← sans GROUP BY ni Sous-requête [WITH CHECK OPTION] ; Exemple 10 : CREATE VIEW CLIENTS_ALGER AS SELECT * FROM CLIENT WHERE Adresse = ‘ALGER’; Note: Une vue est affectée par toutes les mises à jour des tables servant à sa création. II.2 Suppression d’une vue La syntaxe est la suivante : DROP VIEW <Nom_vue> ; Exemple 11 : DROP VIEW CLIENTS_ALGER ; Note : Supprime uniquement sa définition du dictionnaire de données de la base. II.3 Mise à jour de vue Les commandes INSERT, DELETE et UPDATE ne s’appliques pas à une vue si : 1. la vue est définie à partir de plus d’une table, 2. une expression ou une fonction agrégat sont utilisées dans la définition de la vue (exp. AVG) II.4 Consultation d’une table La consultation d’une vue se fait de la même façon qu’une table. Exemple12 : Soit les tables T1(a1,a2,a3,a4,a5) et T2(b1,b2,b3) Question1) Limiter l’accès pour l’utilisateur U1 aux colonnes a1 et a2 et aux lignes vérifiant la condition ‘COND’ sur la table T1. 5 Solution: CREATE VIEW V1 AS SELECT a1,a2 FROM T1 WHERE COND ; et GRANT SELECT ON V1 TO U1; Question2) Limiter l’accès pour l’utilisateur U2 aux colonnes a4 et a5 et sans condition sur la table T1. Solution: CREATE VIEW V2 AS SELECT a4,a5 FROM T1 ; et GRANT SELECT ON V2 TO U2; Question3) Si a4=b1, créer la vue V3 limitant l’accès pour U3 sur les colonnes a4,a5 de V2 et b1,b2 de T2 et tel que a4=b1. Solution: CREATE VIEW V3 AS SELECT a4, a5, b1,b2 FROM V2,T2 WHERE a4 = b1 ; et GRANT SELECT ON V3 TO U3; On peut représenter les opérations précédentes par la figure suivante : Remarque : si on fait ‘SELECT * FROM V1’, on ne verra que les lignes satisfaisant la condition du WHERE des colonnes a1 et a2. Exemple13 : On veut restreindre l’accès à la table T que pendant les heures de travail, par exemple entre 8h00 et 18h00. CREATE VIEW V AS SELECT * FROM T WHERE to_char (sysdate(), ‘HH24’) BETWEEN ‘08’ AND ‘18’; Ensuite on attibut les droits sur la vue V : REVOKE ALL ON T FROM PUBLIC; GRANT SELECT ON V TO PUBLIC; Exemple14 : On veut restreindre l’accès à la table T à partir du terminal ‘T100’ uniquement. CREATE VIEW V AS SELECT * FROM T WHER userenv(‘TERMINAL’) = ‘T100’ ; Où userenv(VAR) est une fonction qui renvoi la valeur de la variable d’environnement ‘VAR’. a1 a2 a3 a4 a5 b1 b2 b3 V2 V1 V3 6 III Encore plus loin dans le contrôle de données : Commande ‘AUDIT’ Certains SGBD fournissent des moyens pour vérifier la manière dont est utilisée la BD. Autrement dit, si on veut vérifier si la gestion des droits qu’on a mis en place correspond à ce qui est attendu, ORACLE par exemple fournit la commande « AUDIT » de contrôle d’accès. Dans cette partie, nous montrons les possibilités de contrôle offertes par une telle commande. L’opération d’ « AUDIT » permet de connaitre par exemple : - le nombre d’utilisateurs qui se sont connectés durant une période de la journée, - les tables que les utilisateurs ont manipulés, ainsi que le type d’utilisation des tables (lecture, écriture) - les terminaux à partir desquels il y a eu des tentatives de connexions non réussies. Ces informations permettront ensuite au DBA : - d’identifier les traitements consommateurs de charge : ce qui lui permettra d’opérer les optimisations nécessaires ainsi qu’un bon ‘tuning’ de la BD, - renforcer la sécurité en cas d’utilisation douteuse de la BD, - établir des statistiques sur l’utilisation des objets de la BD, pour éventuellement les reconfigurer. Attention : Cette commande d’audit insère une ligne (tuple) dans une vue du système pour chaque action ‘auditée’. Cela implique une surcharge de uploads/S4/ 02-controle-de-donnees-dans-sql.pdf