Le Post Infeeny

Les articles des consultants et experts Infeeny

Sécurité dynamique dans les cubes SSAS avec SQL Server 2012

Avec l’ouverture des données Power BI Self-Service vers les utilisateurs (vu dans plusieurs de mes missions) les métiers veulent gérer eux même les droits ou périmètres sur de nombreux utilisateurs finaux (>1000).

En effet dans certains domaines, nous avons une forte contrainte de confidentialité des données contenues dans les cubes SSAS. Les juridictions qui doivent s’appliquer peuvent être changées avec des cadences variables allant de la journée, à des cadences inférieures (temps réel). Pour cela, l’ensemble de la sécurité mise en œuvre est stocké dans la source de données du cube, qui détermine qui peut voir quoi.

A travers cet article, nous allons voir comment rendre dynamique une sécurité de cube SSAS basée sur l’appel à une procédure stockée.

Utilisation des rôles

La sécurité SSAS est basée sur un modèle robuste par filtrage via l’utilisation de rôles.
Un rôle permet d’associer à des utilisateurs des autorisations sur les différents objets d’une base de données multidimensionnelle.
Vous pouvez lire l’article Gestion des rôles avec Analyses Services (SSAS) qui traite du sujet.
Cette solution déclarative ne fonctionne bien que pour un nombre relativement restreint d’utilisateur où les règles d’autorisation sont assez statiques.
Idéalement, on crée des groupes Windows dans l’Active Directory, on affecte des utilisateurs à ces groupes, puis on ajoute les groupes Windows aux rôles de cube.

Nous allons voir comment mettre en place une sécurité dynamique souple pouvant répondre aux contraintes suivantes :

  • Prise en charge d’une sécurité existante dans la source de données pour l’appliquer à celle du cube.
  • Définir une juridiction d’accès très fine pour les utilisateurs.
  • Modifier immédiatement dans les cubes, les demandes de changements de périmètre du métier.

Session utilisateur

Le mécanisme de sécurité dans un cube Analyse Services repose sur un gestionnaire de sécurité. C’est lui lorsqu’un utilisateur se connecte vérifie si une session existe. Si aucune session n’est ouverte alors le gestionnaire de sécurité va créer le contexte de sécurité de l’utilisateur en fonction des règles à attribuer à l’utilisateur.

DynSecu_01

A l’issue de l’ouverture de session, l’utilisateur a chargé la définition avec laquelle il va interroger le cube.

Plus de flexibilité : Dynamiser l’accès aux données du cube

Sécurité embarquée dans le cube

Cette méthode consiste à ajouter dans le Dataware House et le cube multidimensionnel les éléments suivants :

  • Une nouvelle table contenant la liste des utilisateurs avec leur identifiant Active Directory.
  • Une nouvelle table de relation entre la table des utilisateurs et la dimension à sécuriser.
  • Une dimension utilisateur créée à partir de la table utilisateur précédemment créé .
  • Un groupe de mesure basé sur la table de relation entre les utilisateurs et la dimension à sécuriser.

DynSecu_02
Gestion de la sécurité par la mise en place de Many To Many sur les dimensions à sécuriser.

Dans le rôle, on sécurise l’attribut de la dimension avec l’exemple MDX :

Exists( 
[DimProduct].[Attribut Clé].Members
, StrToMember("[User].[User Login].&["+UserName()+"]"),
"FactUserProduct")

Ce modèle a fait ses preuves.

  • Les plus : facile à gérer en SSAS, transparent pour les utilisateurs, gestion des droits simple.
  • Les moins : ajout d’un certain nombre de table rendant la structure du cube complexe.

Comment rendre la gestion des droits dans les cubes plus rapidement ?

Interroger une base de données relationnelle.

Cette méthode consiste à appeler un référentiel de sécurité pour filtrer les utilisateurs du cube

  • Utiliser une procédure stockée T-SQL pour récupérer un jeu de données.
  • Créer une assembly .Net DLL pour récupérer l’appel à la base de données et répondre à la création de la session utilisateur.
  • Déployer l’assembly dans le cube SSAS.
  • Mettre en place l’appel depuis le rôle afin de le rendre dynamique la sécurité en fonction de l’utilisateur avec une expression MDX.

L’utilisateur interroge le cube à travers un contexte de sécurité qui lui est propre et pouvant changer à tout moment.

Schéma de l’approche :
DynSecu_03

L’environnement de cet article est AdventureWorks 2012 téléchargeable ici : http://msftdbprodsamples.codeplex.com/

Dans l’exemple ci-dessous, j’ai repris la base de données, ainsi que le cube AdventureWorksDW2012.

Structure du cube après simplification de adventureWorksDW2012
DynSecu_04
Structure des données
DynSecu_05
1°) Création d’une procédure stockée dans la source de données

Dans la source de données, on crée deux tables pour la sécurité :

  • Secu.Users qui contient les utilisateurs
  • Secu.User_SalesTerritory pour les correspondances entre les utilisateurs et la dimension SalesTerritory

La procédure stockée ci-dessous retourne la liste des territoires qui ont été définit aux comptes d’utilisateur.

CREATE PROCEDURE [secu].[FilterSalesTerritory] @UserAccount nvarchar(50) as 
BEGIN
set nocount on; 
SELECT distinct
 '[Sales Territory].[Sales Territory Country].&' 
 + QUOTENAME(cast(UT.[SalesTerritoryCountry] as nvarchar)) as UserSalesterritory
,[SalesTerritoryCountry] as UserSalesTerritoryCountry
, U.UserAccount
FROM [secu].[Users] U
inner join [secu].[Users_SalesTerritory] UT on UT.userKey = U.userKey
WHERE upper(U.UserAccount) = upper(@UserAccount)
END

Retour : exec [secu].[FilterSalesTerritory] ‘mcnext\spages’;
DynSecu_06

On a faire le choix de constituer la chaine MDX qui sera renvoyée par l’assembly au rôle.

2°) Création de l’assembly qui appelle la procédure stockée

Le composant DLL .Net doit appeler la procédure stockée et renvoyer les paramètres de sécurité de l’utilisateur. AMO (Analysis Management Object) est une librairie.Net qui assure les échanges entre Analysis Services avec du code .Net. Les objets AMO vont générer du XMLA et l’envoyer au serveur. C’est par cet intermédiaire que nous allons modifier la sécurité des rôles en fonction de l’utilisateur courant.

Référence au projet C# Class Library

ADOMD.Net : prendre les composants client et serveur.

  • Microsoft.AnalysisServices.AdomdClient :

C:\Program Files (x86)\Microsoft.NET\ADOMD.NET\110\ Microsoft.AnalysisServices.AdomdClient.dll

  • Microsoft.AnalysisServices.AdomdServer :

C:\Program Files\Microsoft Analysis Services\AS OLEDB\110\msmgdsrv.dll

Dans le code ci-dessous de notre projet de composant DLL, nous allons appeler la procédure stockée secu.FilterSalesTerritory sur la base de données AdventureWorksDW2012.

public static Set FilterSalesTerritory(string UserAccount)
{
string connectionString = 
       string.Format("Data Source={0};Initial Catalog={1};Integrated Security={2};"
                    ,"localhost", "AdventureWorksDW2012", "SSPI");
    Expression expr = new Expression();
    SetBuilder sb = new SetBuilder();
    try {
        using (SqlConnection connect = new SqlConnection())
        {
            connect.ConnectionString = connectionString;
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "secu.FilterSalesTerritory";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = connect;

                SqlParameter paramUsername = new SqlParameter();
                    paramUsername.SqlDbType = SqlDbType.NVarChar;
                    paramUsername.Size = 50;
                    paramUsername.ParameterName = "@UserAccount";
                    paramUsername.Value = UserAccount;
                cmd.Parameters.Add(paramUsername);

                connect.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows){
                 WHILE (reader.Read())
                 {
                   //récupération d'une ligne MDX pour chaque membre (voir ProcStock T-SQL)
                   expr.ExpressionText = string.Format("{0}", reader[0].ToString());                                                         
                   Member m = expr.CalculateMdxObject(null).ToMember();
                   TupleBuilder tb = new TupleBuilder();
                   tb.Add(m);
                   sb.Add(tb.ToTuple()); // ajout des membres dans un set MDX
                 }
                }
            }
        }
        return sb.ToSet();   // retourne le set MDX 
    }
    catch (Exception ex){throw ex;}
}

En résumé à cette étape, on appelle une procédure stockée qui donne la liste des valeurs auquelles l’utilisateur à le droit d’accéder sous la forme d’un Set MDX.
Le Set MDX représente la liste des membres « Sales Territory Country » autorisés de la dimension « Sales Territory » pour un utilisateur donné.

3°) Ajouter l’assembly à la base SSAS
Après avoir compilé le composant .Net en une classe DLL, il doit être référencé et déployé en même temps que le cube SSAS.
Voyons les étapes :

Dans l’explorateur de la solution SSAS du projet, clic droit sur l’entrée des « Assemblies », pour ajouter une nouvelle référence. DynSecu_07
Dans la fenêtre rechercher votre projet .Net pour sélectionner votre composant DLL. DynSecu_08
Après avoir ajouté le composant au projet, afficher les propriétés et modifier :
– Impersonation Mode : Service Account
– Permission Set : Unrestricted
C’est le service account qui appellera le composant .Net. Il faut donner les droits au compte de service pour un accès en lecture à la source de données.
DynSecu_09
Une fois l’assembly rattachée au projet SSAS, lors du déploiement du projet sur le serveur OLAP, celle-ci sera installée.
Clic droit sur le projet dans l’exploreur de solution et sélectionner « Deploy » dans le menu.
Le composant d’appel à une procédure stockée peut-être enregistré aussi directement au cube en utilisant SSMS.
DynSecu_10

4°) Création d’un rôle avec une expression MDX faisant appel à une fonction de l’assembly.

Nous avons créé une procédure stockée qui retourne un set de la dimension « [Sales Territory] » sur le membre « [Sales Territory Country] ». Maintenant nous allons mettre en place dans un rôle la sécurité sur la dimension.

Clic droit sur Roles dans l’explorateur du projet SSAS, pour créer un nouveau rôle. Sur l’onglet Membership ajouter des utilisateurs ou groupes qui sont authentifiés.

DynSecu_11

Cliquer sur l’onglet Cube et donner au rôle un droit de lecture au cube

DynSecu_12

Cliquer sur l’onglet Dimension Data et dérouler les dimensions pour sélectionner [Sales Territory]

DynSecu_13

Cliquer dans la fenêtre sur l’onglet Advanced pour renseigner l’appel à la procédure stockée pour déterminer les membres de la dimension autorisés.

DynSecu_14

  • [DynSecu] est le nom de la class dans le projet .Net de DLL.
  • [FilterSalesTerritory] est le nom de la méthode dans la class .Net.
  • UserName est une fonction MDX qui revoit l’utilisateur courant sous la forme de « Domaine\Utilisateur », passé en tant que paramètre de la méthode FilterSalesTerritory.

5°) Test de cette sécurité dynamique.

Pour tester la sécurité basée sur l’utilisation de rôle, nous avons plusieurs moyens à notre disposition pour comparer la définition et la restitution.

En premier lieu l’appel de la procédure stockée permet d’avoir le périmètre alloué à un utilisateur.

Exemple d’appel à la procédure stockée :

DynSecu_15

Celle-ci retourne la liste des SalesTerritory authorisés à l’utilisateur passé en paramètre. Le retour est sous la forme d’une chaine MDX, d’un libellé et du nom d’utilisateur.

A partir d’un utilisateur ayant suffisamment de droit sur le cube, on peut contrôler les droits en exécutant une requête MDX sous SSMS.

Exemple d’appel au composant de filtrage dynamique dans une requête MDX :

// Test pour un utilisateur donné
SELECT [Measures].[Reseller Order Count]  on 0,
[DynSecu].[FilterSalesTerritory]('mcnext\spages')  on 1
FROM [SSASDynSecuProcStock]
 
// Utilisation de la fonction UserName
SELECT [Measures].[Reseller Order Count]   on 0,
[DynSecu].[FilterSalesTerritory](UserName) on 1
FROM [SSASDynSecuProcStock]

DynSecu_16

Pour tester les droits en condition réelle, on peut utiliser Excel en l’exécutant avec un utilisateur donné. Pour cela il suffit dans une ligne de commande Windows d’appeler avec la commande ci-dessous :

RUNAS /user:mcnext\spages « C:\Program Files (x86)\Microsoft Office\Office15\excel.exe »

Il suffit par la suite de créer une connexion au cube avec le rôle, d’ajouter les champs à partir de la pivote table. Ne pas oublier dans la définition de chaine de renseigner le rôle.

DynSecu_17

Nous pouvons voir ci-dessous que les restrictions du rôle sont appliquées ici avec Excel 2013 et l’utilisateur ‘Mcnext\spages’.

DynSecu_18

Par la suite, je vous laisse voir les changements de périmètre fonctionnel d’un utilisateur dans la définition des données qui lui sont visible. Une nouvelle session doit être créé pour prendre en compte les changements et appeler la procédure stockée via le composant .Net.

 

Comment rechercher une session d’utilisateur sur le serveur SSAS et supprimer celle-ci.

Ci-dessous deux requêtes DMX pour supprimer une session

// Liste des sessions SSAS
SELECT session_spid
, session_user_name
, session_last_command
, session_current_database
, session_cpu_time_ms
, session_elapsed_time_ms
, session_start_time
, session_last_command_start_time
, session_last_command_end_time
FROM $system.discover_sessions
WHERE session_status = 1
AND session_user_name = 'mcnext\spages' //session à rechercher
ORDER BY session_start_time desc

// DMX pour supprimer une session : reporter le session_spid  
<Cancel xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<SPID>1683</SPID>
</Cancel>

 

Aller encore plus loin : Sécurité en Cell Data

Une sécurité sur les faits peut-être mise en place sur un même principe, mais NON allo quoi pour une question de performance.

A relire :

 

Conclusion

Nous avons vu comment à partir d’un cube, on peut gérer l’accès des utilisateurs dynamiquement avec l’appel d’une procédure stockée.

On peut s’appuyer sur la mise en œuvre d’une sécurité existante dans la source de données pour l’appliquer à celle du cube et répercuter immédiatement toutes demandes de changement de périmètre utilisateur par une intervention en base de données.

 

Méthode d’appel à une procédure stockée pour une sécurité dynamique de cube.

Les plus :

  • Sécurité basé sur un référentiel existant
  • Déploiement en temps réel des nouvelles règles de sécurité à l’ouverture de la nouvelle session
  • Granularité très fine par utilisateur
  • Moins de rôles à gérer.
  • Gestion de nombreux utilisateur avec leur juridiction complexe
  • Structure du cube moins complexe

Les moins :

  • Monter en charge des appels au composant .Net
  • Temps d’ouverture de la session utilisateur.
  • Pas possible d’utiliser le cache d’d’Analysis Services, en appelant la fonction « UserName ».

 

Sylvain Pagès
Consultant BI MCNEXT

Une réponse à “Sécurité dynamique dans les cubes SSAS avec SQL Server 2012

  1. fbrossard 13 novembre 2015 à 0 12 07 110711

    Salut Sylvain,

    Merci pour cet article très complet.
    Personnellement, j’ai pris l’habitude d’utiliser la technique de l’assembly qui est bien meilleur en terme de performance, et bien plus souple à gérer en T-SQL lorsqu’il y a des règles de gestion alambiquées à mettre en œuvre.
    Il faut néanmoins savoir, que même si cette technique est la plus performance, elle n’est pas complètement dynamique. En effet, lorsque l’utilisateur se connecte, si son contexte de sécurité n’est pas présent dans le cache, il y est inséré par l’intermédiaire de la procédure stockée retournant le set de donnée associé à l’utilisateur. Tant que le set associé à l’utilisateur est présent dans le cache, il y reste et toutes les requêtes exécutées par notre utilisateur ne font que le réutiliser, contrairement à la technique n°1 qui elle teste à chaque fois l’existence des membres dans le groupe de mesure intermédiaire et qui doit le faire qui plus au niveau fin (M2M oblige).
    Malheureusement, dès lors que vous modifier le contenu de votre table de sécurité, vous n’aurez pas d’autre choix que de vider le cache du cube pour appliquer. Ce qui peut avoir des incidences non négligeables sur les performances (il faudra alors s’assurer des designs d’aggrégation et faire de la chauffe de cache si besoin).
    Dans le second cas, il ne faudra que re processer le groupe de mesure intermédiaire monté sur votre table de gestion de droit.
    Pour conclure, si vous devez fréquemment mettre à jour les droits, je vous conseille la première méthode, sinon partez sur la dll. Pour ma part, dans la quasi-totalité des projets sur lesquels je suis intervenu, une fois la sécurité mise en place et initialisée, elle n’est que très rarement modifiée et vous ?
    Pour étayer mes propos, je vous invite à lire le blog de Chris Webb à ce sujet.
    http://blog.crossjoin.co.uk/2011/05/09/why-not-to-use-the-external-assembly-approach-for-dynamic-security/
    N.B : j’aurai dû te répondre par un autre article 🙂
    @+
    Fred.

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s

%d blogueurs aiment cette page :