Leçon supplémentaire - Sécurité dynamiqueSupplemental lesson - Dynamic security

S’applique à : SQL Server 2017 et versions ultérieures Analysis Services Azure Analysis Services Power bi Premium

Dans cette leçon supplémentaire, vous allez créer un rôle supplémentaire qui implémente la sécurité dynamique.In this supplemental lesson, you create an additional role that implements dynamic security. La sécurité dynamique offre une sécurité au niveau des lignes basée sur le nom ou sur l’ID de connexion de l’utilisateur actuellement connecté.Dynamic security provides row-level security based on the user name or login id of the user currently logged on.

Pour implémenter la sécurité dynamique, vous ajoutez à votre modèle une table contenant les noms des utilisateurs qui peuvent se connecter au modèle et parcourir les données et objets du modèle.To implement dynamic security, you add a table to your model containing the user names of those users that can connect to the model and browse model objects and data. Le modèle que vous créez à l’aide de ce didacticiel est dans le contexte d’Adventure Works. Toutefois, pour suivre cette leçon, vous devez ajouter une table contenant les utilisateurs de votre propre domaine.The model you create using this tutorial is in the context of Adventure Works; however, to complete this lesson, you must add a table containing users from your own domain. Vous n’avez pas besoin des mots de passe correspondant aux noms d’utilisateur qui sont ajoutés.You do not need the passwords for the user names that are added. Pour créer une table EmployeeSecurity, avec un petit échantillon d’utilisateurs de votre propre domaine, vous utilisez la fonctionnalité Coller pour coller les données des employés d’une feuille de calcul Excel.To create an EmployeeSecurity table, with a small sample of users from your own domain, you use the Paste feature, pasting employee data from an Excel spreadsheet. Dans la vraie vie, la table contenant les noms d’utilisateur serait généralement une table d’une vraie base de données comme source de données ; par exemple, une vraie table DimEmployee.In a real-world scenario, the table containing user names would typically be a table from an actual database as a data source; for example, a real DimEmployee table.

Pour implémenter la sécurité dynamique, vous utilisez deux fonctions DAX :la fonction USERNAME (DAX) et la fonction LOOKUPVALUE (DAX).To implement dynamic security, you use two DAX functions: USERNAME Function (DAX) and LOOKUPVALUE Function (DAX). Ces fonctions, appliquées dans une formule de filtre de lignes, sont définies dans un nouveau rôle.These functions, applied in a row filter formula, are defined in a new role. En utilisant la fonction LOOKUPVALUE, la formule spécifie une valeur à partir de la table EmployeeSecurity.By using the LOOKUPVALUE function, the formula specifies a value from the EmployeeSecurity table. Puis la formule transmet cette valeur à la fonction USERNAME, qui spécifie que le nom de l’utilisateur connecté appartient à ce rôle.The formula then passes that value to the USERNAME function, which specifies the user name of the user logged on belongs to this role. L’utilisateur peut ensuite parcourir uniquement les données spécifiées par les filtres de lignes du rôle.The user can then browse only data specified by the role's row filters. Dans ce scénario, vous spécifiez que les représentants commerciaux peuvent parcourir uniquement les données des ventes sur Internet pour les secteurs de ventes dont ils sont membres.In this scenario, you specify that sales employees can only browse Internet sales data for the sales territories in which they are a member.

Les tâches qui sont propres à ce scénario de modèle tabulaire Adventure Works, mais qui ne s’appliqueraient pas nécessairement dans la vraie vie, sont identifiées comme telles.Those tasks that are unique to this Adventure Works tabular model scenario, but would not necessarily apply to a real-world scenario are identified as such. Chaque tâche inclut des informations supplémentaires décrivant son objectif.Each task includes additional information describing the purpose of the task.

Durée estimée pour effectuer cette leçon : 30 minutesEstimated time to complete this lesson: 30 minutes

PrérequisPrerequisites

Cet article de la leçon supplémentaire fait partie d’un didacticiel de modélisation tabulaire, qui doit être exécuté dans l’ordre.This supplemental lesson article is part of a tabular modeling tutorial, which should be completed in order. Avant d’effectuer les tâches de cette leçon supplémentaire, vous devez avoir suivi toutes les leçons précédentes.Before performing the tasks in this supplemental lesson, you should have completed all previous lessons.

Ajouter la table DimSalesTerritory au projet de modèle tabulaire AW Internet SalesAdd the DimSalesTerritory table to the AW Internet Sales Tabular Model Project

Pour implémenter la sécurité dynamique dans ce scénario Adventure Works, vous devez ajouter deux tables supplémentaires à votre modèle.To implement dynamic security for this Adventure Works scenario, you must add two additional tables to your model. La première table que vous ajoutez est DimSalesTerritory (comme secteur de vente) à partir de la même base de données AdventureWorksDW.The first table you add is DimSalesTerritory (as Sales Territory) from the same AdventureWorksDW database. Par la suite, vous appliquez un filtre de lignes dans la table SalesTerritory qui définit les données spécifiques que l’utilisateur connecté peut parcourir.You later apply a row filter to the SalesTerritory table that defines the particular data the logged on user can browse.

Pour ajouter la table DimSalesTerritoryTo add the DimSalesTerritory table

  1. Dans l’Explorateur de modèles tabulaires > Sources de données, cliquez avec le bouton droit sur votre connexion, puis cliquez sur Importer de nouvelles tables.In Tabular Model Explorer > Data Sources, right-click your connection, and then click Import New Tables.

    Si la boîte de dialogue Informations d’identification s’affiche, tapez les informations d’emprunt d’identification que vous avez utilisé dans la leçon 2 : Ajouter des données.If the Impersonation Credentials dialog box appears, type the impersonation credentials you used in Lesson 2: Add Data.

  2. Dans le navigateur, sélectionnez la table DimSalesTerritory, puis cliquez sur OK.In Navigator, select the DimSalesTerritory table, and then click OK.

  3. Dans l’éditeur de requête, cliquez sur la requête DimSalesTerritory, puis supprimez la colonne SalesTerritoryAlternateKey.In Query Editor, click the DimSalesTerritory query, and then remove SalesTerritoryAlternateKey column.

  4. Cliquez sur Importer.Click Import.

    La nouvelle table est ajoutée à l’espace de travail du modèle.The new table is added to the model workspace. Les objets et les données de la table DimSalesTerritory source sont ensuite importés dans votre modèle tabulaire AW Internet Sales.Objects and data from the source DimSalesTerritory table are then imported into your AW Internet Sales Tabular Model.

  5. Une fois la table importée, cliquez sur Fermer.After the table has been imported successfully, click Close.

Ajouter une table avec des données de noms d’utilisateurAdd a table with user name data

La table DimEmployee de l’exemple de base de données AdventureWorksDW contient des utilisateurs du domaine AdventureWorks.The DimEmployee table in the AdventureWorksDW sample database contains users from the AdventureWorks domain. Ces noms d’utilisateur n’existent pas dans votre propre environnement.Those user names do not exist in your own environment. Vous devez donc créer dans votre modèle une table qui contient un petit échantillon (au moins trois) d’utilisateurs réels de votre organisation.You must create a table in your model that contains a small sample (at least three) of actual users from your organization. Vous ajoutez ensuite ces utilisateurs comme membres au nouveau rôle.You then add these users as members to the new role. Vous n’avez pas besoin des mots de passe correspondant aux exemples de noms d’utilisateur, mais vous avez besoin de noms d’utilisateurs Windows réels de votre propre domaine.You do not need the passwords for the sample user names, but you do need actual Windows user names from your own domain.

Pour ajouter une table EmployeeSecurityTo add an EmployeeSecurity table

  1. Ouvrez Microsoft Excel, ce qui crée une feuille de calcul.Open Microsoft Excel, creating a worksheet.

  2. Copiez la table suivante, notamment la ligne d’en-tête, puis collez-la dans la feuille de calcul.Copy the following table, including the header row, and then paste it into the worksheet.

      |EmployeeId|SalesTerritoryId|FirstName|LastName|LoginId|  
      |---------------|----------------------|--------------|-------------|------------|  
      |1|2|<user first name>|<user last name>|\<domain\username>|  
      |1|3|<user first name>|<user last name>|\<domain\username>|  
      |2|4|<user first name>|<user last name>|\<domain\username>|  
      |3|5|<user first name>|<user last name>|\<domain\username>|  
    
  3. Remplacez le prénom, le nom et le domaine\nom d’utilisateur par les noms et ID de connexion des trois utilisateurs de votre organisation.Replace the first name, last name, and domain\username with the names and login ids of three users in your organization. Pour EmployeeId 1, placez le même utilisateur sur les deux premières lignes. Cela indique que cet utilisateur appartient à plusieurs secteurs de vente.Put the same user on the first two rows, for EmployeeId 1, showing this user belongs to more than one sales territory. Laissez les champs EmployeeId et SalesTerritoryId inchangés.Leave the EmployeeId and SalesTerritoryId fields as they are.

  4. Enregistrez la feuille de calcul sous SampleEmployee.Save the worksheet as SampleEmployee.

  5. Dans la feuille de calcul, sélectionnez toutes les cellules contenant des données d’employés, notamment les en-têtes, cliquez avec le bouton droit sur les données sélectionnées, puis cliquez sur Copier.In the worksheet, select all the cells with employee data, including the headers, then right-click the selected data, and then click Copy.

  6. Dans SSDT, cliquez sur le menu Edition, puis sur Coller.In SSDT, click the Edit menu, and then click Paste.

    Si l’option Coller apparaît en grisé, cliquez sur une colonne de n’importe quelle table dans la fenêtre du Concepteur de modèles, puis réessayez.If Paste is grayed out, click any column in any table in the model designer window, and try again.

  7. Dans la boîte de dialogue Aperçu de collage dans Nom de la table, tapez EmployeeSecurity.In the Paste Preview dialog box, in Table Name, type EmployeeSecurity.

  8. Dans Données à coller, vérifiez que les données incluent l’ensemble des données et des en-têtes utilisateur de la feuille de calcul SampleEmployee.In Data to be pasted, verify the data includes all the user data and headers from the SampleEmployee worksheet.

  9. Vérifiez que la case Utiliser la première ligne comme en-têtes de colonne est cochée, puis cliquez sur OK.Verify Use first row as column headers is checked, and then click Ok.

    Une table nommée EmployeeSecurity et contenant les données des employés copiées à partir de la feuille de calcul SampleEmployee est créée.A new table named EmployeeSecurity with employee data copied from the SampleEmployee worksheet is created.

Créer des relations entre les tables FactInternetSales, DimGeography et DimSalesTerritoryCreate relationships between FactInternetSales, DimGeography, and DimSalesTerritory table

Les tables FactInternetSales, DimGeography et DimSalesTerritory contiennent toutes une colonne commune, SalesTerritoryId.The FactInternetSales, DimGeography, and DimSalesTerritory table all contain a common column, SalesTerritoryId. La colonne SalesTerritoryId de la table DimSalesTerritory contient des valeurs avec un ID différent pour chaque secteur de vente.The SalesTerritoryId column in the DimSalesTerritory table contains values with a different Id for each sales territory.

Pour créer des relations entre les tables FactInternetSales, DimGeography et DimSalesTerritoryTo create relationships between the FactInternetSales, DimGeography, and the DimSalesTerritory table

  1. Dans la vue de diagramme, dans la table DimGeography, cliquez sur la colonne SalesTerritoryId en maintenant le bouton de la souris enfoncé, faites glisser le curseur jusqu’à la colonne SalesTerritoryId de la table DimSalesTerritory, puis relâchez le bouton de la souris.In Diagram View, in the DimGeography table, click, and hold on the SalesTerritoryId column, then drag the cursor to the SalesTerritoryId column in the DimSalesTerritory table, and then release.

  2. Dans la table FactInternetSales, cliquez sur la colonne SalesTerritoryId en maintenant le bouton enfoncé, puis faites glisser le curseur jusqu’à la colonne SalesTerritoryId de la table DimSalesTerritory, puis relâchez.In the FactInternetSales table, click, and hold on the SalesTerritoryId column, then drag the cursor to the SalesTerritoryId column in the DimSalesTerritory table, and then release.

    Notez que la propriété Active de cette relation présente la valeur False, ce qui signifie qu’elle est inactive.Notice the Active property for this relationship is False, meaning it's inactive. En effet, la table FactInternetSales a déjà une autre relation active.The FactInternetSales table already has another active relationship.

Masquer la table EmployeeSecurity pour les applications clientesHide the EmployeeSecurity Table from client applications

Dans cette tâche, vous masquez la table EmployeeSecurity, en l’empêchant d’apparaître dans la liste de champs d’une application cliente.In this task, you hide the EmployeeSecurity table, keeping it from appearing in a client application's field list. N’oubliez pas que le fait de masquer une table ne garantit pas sa sécurité.Keep in mind that hiding a table does not secure it. Les utilisateurs peuvent toujours interroger les données de la table EmployeeSecurity s’ils savent comment procéder.Users can still query EmployeeSecurity table data if they know how. Pour sécuriser les données de la table EmployeeSecurity en empêchant les utilisateurs d’en interroger les données, vous appliquez un filtre lors d’une tâche ultérieure.To secure the EmployeeSecurity table data, preventing users from being able to query any of its data, you apply a filter in a later task.

Pour masquer la table EmployeeSecurity pour les applications clientesTo hide the EmployeeSecurity table from client applications

  • Dans le Concepteur de modèles, dans la vue de diagramme, cliquez sur l’en-tête de la table Employee, puis cliquez sur Masquer dans les outils clients.In the model designer, in Diagram View, right-click the Employee table heading, and then click Hide from Client Tools.

Créer un rôle d’utilisateur Sales Employees by Territory (Représentants commerciaux par secteur)Create a Sales Employees by Territory user role

Dans cette tâche, vous allez créer un rôle d’utilisateur.In this task, you create a user role. Ce rôle comprend un filtre de lignes définissant les lignes de la table DimSalesTerritory qui sont visibles aux utilisateurs.This role includes a row filter defining which rows of the DimSalesTerritory table are visible to users. Le filtre est ensuite appliqué dans la direction de relation un-à-plusieurs à toutes les autres tables associées à DimSalesTerritory.The filter is then applied in the one-to-many relationship direction to all other tables related to DimSalesTerritory. Vous appliquez également un filtre qui empêche tout utilisateur membre du rôle d’interroger l’ensemble de la table EmployeeSecurity.You also apply a filter that secures the entire EmployeeSecurity table from being queryable by any user that is a member of the role.

Notes

Le rôle Sales Employees by Territory (Représentants commerciaux par secteur) que vous créez dans cette leçon limite les membres à parcourir (ou à interroger) uniquement les données des ventes pour le secteur de vente auquel ils appartiennent.The Sales Employees by Territory role you create in this lesson restricts members to browse (or query) only sales data for the sales territory to which they belong. Si vous ajoutez au rôle Sales Employees by Territory un utilisateur comme membre qui existe également comme membre d’un rôle créé dans la leçon 11 : Créer des rôles, vous obtenez une combinaison d’autorisations.If you add a user as a member to the Sales Employees by Territory role that also exists as a member in a role created in Lesson 11: Create Roles, you get a combination of permissions. Quand un utilisateur est membre de plusieurs rôles, les autorisations et les filtres de lignes définis pour chaque rôle sont cumulatifs.When a user is a member of multiple roles, the permissions, and row filters defined for each role are cumulative. Autrement dit, l’utilisateur dispose des autorisations supérieures déterminées par la combinaison des rôles.That is, the user has the greater permissions determined by the combination of roles.

Pour créer un rôle d’utilisateur Sales Employees by Territory (Représentants commerciaux par secteur)To create a Sales Employees by Territory user role

  1. Dans SSDT, cliquez sur le menu Modèle, puis sur Rôles.In SSDT, click the Model menu, and then click Roles.

  2. Dans le Gestionnaire de rôles, cliquez sur Nouveau.In Role Manager, click New.

    Un nouveau rôle avec l’autorisation Aucune est ajouté à la liste.A new role with the None permission is added to the list.

  3. Cliquez sur le nouveau rôle, puis dans la colonne Nom, renommez le rôle Sales Employees by Territory.Click the new role, and then in the Name column, rename the role to Sales Employees by Territory.

  4. Dans la colonne Autorisations, cliquez sur la liste déroulante et sélectionnez l’autorisation Lecture.In the Permissions column, click the dropdown list, and then select the Read permission.

  5. Cliquez sur l’onglet Membres, puis sur Ajouter.Click the Members tab, and then click Add.

  6. Dans la boîte de dialogue Sélectionner un utilisateur ou un groupe, dans Entrer le nom de l’objet à sélectionner, tapez le premier exemple de nom d’utilisateur que vous avez utilisé lors de la création de la table EmployeeSecurity.In the Select User or Group dialog box, in Enter the object named to select, type the first sample user name you used when creating the EmployeeSecurity table. Cliquez sur Vérifier les noms pour vérifier si le nom d’utilisateur est valide, puis cliquez sur OK.Click Check Names to verify the user name is valid, and then click Ok.

    Répétez cette étape en ajoutant les autres exemples de noms d’utilisateur que vous avez utilisés lors de la création de la table EmployeeSecurity.Repeat this step, adding the other sample user names you used when creating the EmployeeSecurity table.

  7. Cliquez sur l’onglet Filtres de lignes.Click the Row Filters tab.

  8. Pour la table EmployeeSecurity, dans la colonne Filtre DAX, tapez la formule suivante :For the EmployeeSecurity table, in the DAX Filter column, type the following formula:

      =FALSE()  
    

    Cette formule indique que toutes les colonnes se résolvent en condition booléenne false.This formula specifies that all columns resolve to the false Boolean condition. Par conséquent, aucune colonne de la table EmployeeSecurity ne peut être interrogée par un membre du rôle d’utilisateur Sales Employees by Territory.No columns for the EmployeeSecurity table can be queried by a member of the Sales Employees by Territory user role.

  9. Pour la table DimSalesTerritory , tapez la formule suivante :For the DimSalesTerritory table, type the following formula:

    ='DimSalesTerritory'[SalesTerritoryKey]=LOOKUPVALUE('EmployeeSecurity'[SalesTerritoryId], 
      'EmployeeSecurity'[LoginId], USERNAME(), 
      'EmployeeSecurity'[SalesTerritoryId], 'DimSalesTerritory'[SalesTerritoryKey]) 
    

    Dans cette formule, la fonction LOOKUPVALUE retourne toutes les valeurs de la colonne DimEmployeeSecurity[SalesTerritoryId], où la valeur EmployeeSecurity[LoginId] est identique au nom de l’utilisateur Windows actuellement connecté et la valeur EmployeeSecurity[SalesTerritoryId] est identique à la valeur DimSalesTerritory[SalesTerritoryId].In this formula, the LOOKUPVALUE function returns all values for the DimEmployeeSecurity[SalesTerritoryId] column, where the EmployeeSecurity[LoginId] is the same as the current logged on Windows user name, and EmployeeSecurity[SalesTerritoryId] is the same as the DimSalesTerritory[SalesTerritoryId].

    L’ensemble des ID de secteurs de vente retournés par LOOKUPVALUE sont ensuite utilisés pour limiter les lignes affichées dans la table DimSalesTerritory.The set of sales territory IDs returned by LOOKUPVALUE is then used to restrict the rows shown in the DimSalesTerritory table. Seules les lignes où la valeur SalesTerritoryID de la ligne figure dans l’ensemble d’ID retournés par la fonction LOOKUPVALUE sont affichées.Only rows where the SalesTerritoryID for the row is in the set of IDs returned by the LOOKUPVALUE function are displayed.

  10. Dans le Gestionnaire de rôles, cliquez sur OK.In Role Manager, click Ok.

Tester le rôle d’utilisateur Sales Employees by Territory (Représentants commerciaux par secteur)Test the Sales Employees by Territory User Role

Dans cette tâche, vous allez utiliser la fonctionnalité Analyser dans Excel dans SSDT pour tester l’efficacité du rôle d’utilisateur Sales Employees by Territory.In this task, you use the Analyze in Excel feature in SSDT to test the efficacy of the Sales Employees by Territory user role. Vous allez spécifier l’un des noms d’utilisateur que vous avez ajoutés à la table EmployeeSecurity et comme membre du rôle.You specify one of the user names you added to the EmployeeSecurity table and as a member of the role. Ce nom d’utilisateur est ensuite utilisé comme le nom d’utilisateur effectif dans la connexion créée entre Excel et le modèle.This user name is then used as the effective user name in the connection created between Excel and the model.

Pour tester le rôle d’utilisateur Sales Employees by Territory (Représentants commerciaux par secteur)To test the Sales Employees by Territory user role

  1. Dans SSDT, cliquez sur le menu Modèle, puis sur Analyser dans Excel.In SSDT, click the Model menu, and then click Analyze in Excel.

  2. Dans la boîte de dialogue Analyser dans Excel, dans Spécifier le nom d’utilisateur ou le rôle à utiliser pour se connecter au modèle, sélectionnez Autre utilisateur Windows, puis cliquez sur Parcourir.In the Analyze in Excel dialog box, in Specify the user name or role to use to connect to the model, select Other Windows User, and then click Browse.

  3. Dans la boîte de dialogue Sélectionner un utilisateur ou un groupe, dans la zone Entrer le nom de l’objet à sélectionner, tapez un nom d’utilisateur que vous avez inclus dans la table EmployeeSecurity, puis cliquez sur Vérifier les noms.In the Select User or Group dialog box, in Enter the object name to select, type a user name you included in the EmployeeSecurity table, and then click Check Names.

  4. Cliquez sur OK pour fermer la boîte de dialogue Sélectionner un utilisateur ou un groupe, puis cliquez sur OK pour fermer la boîte de dialogue Analyser dans Excel.Click Ok to close the Select User or Group dialog box, and then click Ok to close the Analyze in Excel dialog box.

    Excel s’ouvre avec un nouveau classeur.Excel opens with a new workbook. Un tableau croisé dynamique est automatiquement créé.A PivotTable is automatically created. La liste des champs du tableau croisé dynamique inclut la plupart des champs de données disponibles dans votre nouveau modèle.The PivotTable Fields list includes most of the data fields available in your new model.

    Notez que la table EmployeeSecurity n’est pas visible dans la liste Champs de tableau croisé dynamique.Notice the EmployeeSecurity table is not visible in the PivotTable Fields list. En effet, vous avez masqué cette table dans les outils clients lors d’une tâche précédente.You hid this table from client tools in a previous task.

  5. Dans la liste Champs dans ∑ Internet Sales (mesures), sélectionnez la mesure InternetTotalSales.In the Fields list, in ∑ Internet Sales (measures), select the InternetTotalSales measure. La mesure est entrée dans les champs Valeurs.The measure is entered into the Values fields.

  6. Sélectionnez la colonne SalesTerritoryId dans la table DimSalesTerritory.Select the SalesTerritoryId column from the DimSalesTerritory table. La colonne est entrée dans les champs Étiquettes de lignes.The column is entered into the Row Labels fields.

    Notez que les chiffres des ventes sur Internet s’affichent uniquement pour la région à laquelle le nom d’utilisateur effectif que vous avez utilisé appartient.Notice Internet sales figures appear only for the one region to which the effective user name you used belongs. Si vous sélectionnez une autre colonne, telle que City, dans la table DimGeography en tant que champ Étiquette de ligne, seules les villes du secteur de vente auquel appartient l’utilisateur effectif sont affichées.If you select another column, like City from the DimGeography table as Row Label field, only cities in the sales territory to which the effective user belongs are displayed.
    Cet utilisateur ne peut parcourir ou interroger aucune donnée des ventes sur Internet pour les secteurs autres que celui auquel il appartient.This user cannot browse or query any Internet sales data for territories other than the one they belong to. Cette restriction est due au fait que le filtre de lignes défini pour la table DimSalesTerritory, dans le rôle d’utilisateur Sales Employees by Territory, protège efficacement toutes les données liées à d’autres secteurs de vente.This restriction is because the row filter defined for the DimSalesTerritory table, in the Sales Employees by Territory user role, secures data for all data related to other sales territories.

Voir aussiSee Also

Fonction USERNAME (DAX)USERNAME Function (DAX)
LOOKUPVALUE, fonction (DAX)LOOKUPVALUE Function (DAX)
Fonction CUSTOMDATA (DAX)CUSTOMDATA Function (DAX)