IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

MS SQL Server Discussion :

DELETE sur vue + jointure [2008R2]


Sujet :

MS SQL Server

  1. #1
    Membre averti
    Femme Profil pro
    Inscrit en
    Janvier 2013
    Messages
    48
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France

    Informations forums :
    Inscription : Janvier 2013
    Messages : 48
    Par défaut DELETE sur vue + jointure
    Bonjour,

    J'ai une question concernant un DELETE sur une Vue avec une jointure:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    CREATE view [dbo].[V_MISSION_DATA_USER_SELF]
    AS
    SELECT   T_MISSION_DATA.*,T_MISSION.ROLE FROM    dbo.T_MISSION_DATA LEFT OUTER JOIN
                       dbo.T_MISSION ON  dbo.T_MISSION.ID = dbo.T_MISSION_DATA.ID WHERE    (IS_MEMBER(ROLE) = 1)
    Cette vue récupère une colonne ROLE dans la table T_MISSION. Si l'utilisateur est membre de ce rôle il peut visualiser les données.
    J'aimerais pouvoir faire une DELETE sur cette vue mais SQL Server ne me l'autorise pas à moins que je ne donne les droits SELECT et DELETE sur la table T_MISSION_DATA et je ne veux pas que l’utilisateur puissent visualiser ou supprimer toutes les données de la table T_MISSION_DATA.

    L'instruction DELETE qui fonctionne avec les droits SELECT et DELETE sur T_MISSION_DATA

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    DELETE T_MISSION_DATA FROM V_MISSION_DATA_USER_SELF WHERE T_MISSION_DATA.id = 10;
    Merci par avance

  2. #2
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 998
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 998
    Billets dans le blog
    6
    Par défaut
    le principe des privilèges (et non des droits) est qu'ils se superposent. Autrement dit pour pouvoir faire une DELETE dans une vue contenant une table A vous devez disposer du privilège DELETE sur la table...

    Vous pouvez donc interdire le SELECT sur la table et l'autoriser sur la vue ce qui restreindra de facto les possibilité de delete.

    une autre façon de faire est d'utiliser un déclencheur INSTEAD OF et en SQL dynamique avec un EXECUTE AS OWNER

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  3. #3
    Membre averti
    Femme Profil pro
    Inscrit en
    Janvier 2013
    Messages
    48
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France

    Informations forums :
    Inscription : Janvier 2013
    Messages : 48
    Par défaut
    Je préféré éviter le SQL dynamique avec un EXECUTE AS OWNER.
    Je vais me renseigner sur INSTEAD OF que je ne connais pas.

    Par contre je dois bien avoir le privilège SELECT sur T_MISSION_DATA sinon une erreur est levé :L'autorisation SELECT a été refusée sur l'objet 'T_MISSION_DATA'

  4. #4
    Membre averti
    Femme Profil pro
    Inscrit en
    Janvier 2013
    Messages
    48
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France

    Informations forums :
    Inscription : Janvier 2013
    Messages : 48
    Par défaut
    J'ai donc crée un Trigger :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    CREATE TRIGGER DEL_JOIN
     ON V_MISSION_DATA_USER_SELF INSTEAD OF DELETE
     
    AS
      DELETE T_MISSION_DATA
        WHERE ID IN (SELECT ID FROM deleted)
     GO
    après je fais un

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    DELETE from V_MISSION_DATA_USER_SELF where ID=1399

  5. #5
    Modérateur

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Janvier 2005
    Messages
    5 826
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 43
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Janvier 2005
    Messages : 5 826
    Par défaut
    Bonjour,

    Vous pouvez écrire votre déclencheur un peu plus élégamment :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    CREATE TRIGGER DEL_JOIN
    	ON dbo.V_MISSION_DATA_USER_SELF INSTEAD OF DELETE
    AS
    BEGIN
    	SET NOCOUNT ON
     
    	DELETE FROM	dbo.T_MISSION_DATA
    	FROM		dbo.T_MISSION_DATA AS MD
    	INNER JOIN	deleted AS D
    				ON D.ID = MD.ID
    END
    l'instruction SET NOCOUNT ON est une option de session qui indique à SQL Server de ne pas retourner au client le nombre de lignes affectées par les instructions.

    @++

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Problème jointure sur vue
    Par Slayes dans le forum Langage SQL
    Réponses: 1
    Dernier message: 18/07/2013, 15h36
  2. DELETE sur tables avec jointure
    Par iDaaX dans le forum Langage SQL
    Réponses: 1
    Dernier message: 08/03/2013, 08h46
  3. delete sur une table avec jointure
    Par Jarod51 dans le forum Requêtes
    Réponses: 2
    Dernier message: 30/09/2011, 10h26
  4. Delete sur une vue 2008 => 2008 R2
    Par toto9o dans le forum Développement
    Réponses: 3
    Dernier message: 08/09/2011, 11h00
  5. delete sur une vue: rule
    Par Bouboubou dans le forum PostgreSQL
    Réponses: 8
    Dernier message: 18/05/2004, 18h58

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo