Implementar a Segurança em Nível de Linha em um modelo de tabela do Analysis Services localImplement row-level security in an on-premises Analysis Services tabular model

Usando um conjunto de dados de exemplo para trabalhar com as etapas abaixo, este tutorial mostra como implementar a Segurança em Nível de Linha em um modelo de tabela do Analysis Services local e usá-lo em um relatório do Power BI.Using a sample dataset to work through the steps below, this tutorial shows you how to implement row-level security in an on-premises Analysis Services Tabular Model and use it in a Power BI report.

  • Crie uma tabela de segurança no banco de dados AdventureworksDW2012Create a new security table in the AdventureworksDW2012 database
  • Criar o modelo de tabela com as tabelas de fatos e dimensões necessáriasBuild the tabular model with necessary fact and dimension tables
  • Definir as permissões e as funções de usuárioDefine user roles and permissions
  • Implantar o modelo em uma instância da tabela do Analysis ServicesDeploy the model to an Analysis Services tabular instance
  • Criar um relatório do Power BI Desktop que mostra dados personalizados ao usuário que está acessando o relatórioBuild a Power BI Desktop report that displays data tailored to the user accessing the report
  • Implantar o relatório no serviço do Power BIDeploy the report to Power BI service
  • Criar um novo dashboard baseado no relatórioCreate a new dashboard based on the report
  • Compartilhar o dashboard com seus colegasShare the dashboard with your coworkers

Este tutorial requer o banco de dados AdventureworksDW2012.This tutorial requires the AdventureworksDW2012 database.

Tarefa 1: criar a tabela de segurança de usuário e definir a relação de dadosTask 1: Create the user security table and define data relationship

Há vários artigos publicados que descrevem como definir a segurança dinâmica em nível de linha com o modelo tabular do SSAS (SQL Server Analysis Services) .You can find many articles describing how to define row-level dynamic security with the SQL Server Analysis Services (SSAS) tabular model. Para nossa amostra, usamos Implementar a segurança dinâmica usando filtros de linha.For our sample, we use Implement Dynamic Security by Using Row Filters.

Estas etapas exigem o uso do banco de dados relacional AdventureworksDW2012.The steps here require using the AdventureworksDW2012 relational database.

  1. No AdventureworksDW2012, crie a tabela DimUserSecurity conforme mostrado abaixo.In AdventureworksDW2012, create the DimUserSecurity table as shown below. É possível usar o SSMS (SQL Server Management Studio) para criar a tabela.You can use SQL Server Management Studio (SSMS) to create the table.

    Criar tabela DimUserSecurity

  2. Depois de criar e salvar a tabela, você precisa estabelecer a relação entre a coluna SalesTerritoryID da tabela de DimUserSecurity e a coluna SalesTerritoryKey da tabela DimSalesTerritory, conforme mostrado abaixo.Once you create and save the table, you need to establish the relationship between the DimUserSecurity table's SalesTerritoryID column and the DimSalesTerritory table's SalesTerritoryKey column, as shown below.

    No SSMS, clique com o botão direito do mouse em DimUserSecurity e selecione Design.In SSMS, right-click DimUserSecurity, and select Design. Em seguida, selecione Designer de Tabela > Relações... . Quando terminar, salve a tabela.Then select Table Designer > Relationships.... When done, save the table.

    Relações de Chaves Estrangeiras

  3. Adicione usuários à tabela.Add users to the table. Clique com o botão direito do mouse em DimUserSecurity e selecione Editar as 200 Primeiras Linhas.Right-click DimUserSecurity and select Edit Top 200 Rows. Depois de adicionar os usuários, a tabela DimUserSecurity deve ser semelhante ao seguinte exemplo:Once you've added users, the DimUserSecurity table should appear similar to the following example:

    Tabela DimUserSecurity com usuários de exemplo

    Você verá esses usuários em tarefas futuras.You'll see these users in upcoming tasks.

  4. Em seguida, faça uma junção interna com a tabela DimSalesTerritory, que mostra os detalhes da região associados ao usuário.Next, do an inner join with the DimSalesTerritory table, which shows the user associated region details. O código SQL aqui faz a junção interna e a imagem mostra como a tabela é exibida.The SQL code here does the inner join, and the image shows how the table then appears.

    select b.SalesTerritoryCountry, b.SalesTerritoryRegion, a.EmployeeID, a.FirstName, a.LastName, a.UserName from [dbo].[DimUserSecurity] as a join [dbo].[DimSalesTerritory] as b on a.[SalesTerritoryID] = b.[SalesTerritoryKey]
    

    A tabela unida mostra quem responsável por cada região de vendas, graças à relação criada na etapa 2.The joined table shows who is responsible for each sales region, thanks to the relationship created in Step 2. Por exemplo, você pode ver que Rita Santos é responsável pela Austrália.For example, you can see that Rita Santos is responsible for Australia.

Tarefa 2: criar o modelo de tabela com tabelas de fatos e dimensãoTask 2: Create the tabular model with facts and dimension tables

Depois de implementar o data warehouse relacional, será necessário definir o modelo tabular.Once your relational data warehouse is in place, you need to define the tabular model. É possível criar o modelo usando o SSDT (SQL Server Data Tools).You can create the model using SQL Server Data Tools (SSDT). Para obter mais informações, confira Criar um novo projeto de modelo tabular.For more information, see Create a New Tabular Model Project.

  1. Importe todas as tabelas necessárias no modelo, conforme mostrado abaixo.Import all the necessary tables into the model as shown below.

    SQL Server importado para uso com ferramentas de dados

  2. Depois de importar as tabelas necessárias, você precisa definir uma função chamada SalesTerritoryUsers com a permissão Leitura.Once you've imported the necessary tables, you need to define a role called SalesTerritoryUsers with Read permission. Selecione o menu Modelo no SQL Server Data Tools e, em seguida, selecione Funções.Select the Model menu in SQL Server Data Tools, and then select Roles. No Gerenciador de Funções, selecione Novo.In Role Manager, select New.

  3. Em Membros no Gerenciador de Funções, adicione os usuários que você definiu na tabela DimUserSecurity na Tarefa 1.Under Members in the Role Manager, add the users that you defined in the DimUserSecurity table in Task 1.

    Adicionar usuários no Gerenciador de Funções

  4. Em seguida, adicione as funções apropriadas às tabelas DimSalesTerritory e DimUserSecurity, conforme mostrado abaixo na guia Filtros de Linha.Next, add the proper functions for both DimSalesTerritory and DimUserSecurity tables, as shown below under Row Filters tab.

    Adicionar funções a Filtros de Linha

  5. A função LOOKUPVALUE retorna valores para uma coluna na qual o nome de usuário do Windows corresponde ao que a função USERNAME retorna.The LOOKUPVALUE function returns values for a column in which the Windows user name matches the one the USERNAME function returns. Em seguida, você pode restringir as consultas para as quais os valores retornados de LOOKUPVALUE correspondem aos da mesma tabela ou da tabela relacionada.You can then restrict queries to where the LOOKUPVALUE returned values match ones in the same or related table. Na coluna Filtro DAX, digite a seguinte fórmula:In the DAX Filter column, type the following formula:

        =DimSalesTerritory[SalesTerritoryKey]=LOOKUPVALUE(DimUserSecurity[SalesTerritoryID], DimUserSecurity[UserName], USERNAME(), DimUserSecurity[SalesTerritoryID], DimSalesTerritory[SalesTerritoryKey])
    

    Nesta fórmula, a função LOOKUPVALUE retorna todos os valores para a coluna DimUserSecurity[SalesTerritoryID], em que o DimUserSecurity[UserName] é igual ao nome de usuário do Windows conectado no momento e DimUserSecurity[SalesTerritoryID] é igual a DimSalesTerritory[SalesTerritoryKey].In this formula, the LOOKUPVALUE function returns all values for the DimUserSecurity[SalesTerritoryID] column, where the DimUserSecurity[UserName] is the same as the current logged on Windows user name, and DimUserSecurity[SalesTerritoryID] is the same as the DimSalesTerritory[SalesTerritoryKey].

    Importante

    Ao usar a segurança em nível de linha, a função DAX USERELATIONSHIP não é compatível.When using row-level security, the DAX function USERELATIONSHIP is not supported.

    O conjunto de retornos LOOKUPVALUE de SalesTerritoryKey de vendas é usado para restringir as linhas mostradas no DimSalesTerritory.The set of Sales SalesTerritoryKey's LOOKUPVALUE returns is then used to restrict the rows shown in the DimSalesTerritory. Somente as linhas em que o valor SalesTerritoryKey está nas IDs que a função LOOKUPVALUE retorna são exibidas.Only rows where the SalesTerritoryKey value is in the IDs that the LOOKUPVALUE function returns are displayed.

  6. Para a tabela DimUserSecurity, na coluna Filtro DAX, adicione a seguinte fórmula:For the DimUserSecurity table, in the DAX Filter column, add the following formula:

        =FALSE()
    

    Essa fórmula especifica que todas as colunas são resolvidas para false. Ou seja, as colunas da tabela DimUserSecurity não podem ser consultadas.This formula specifies that all columns resolve to false; meaning DimUserSecurity table columns can't be queried.

Agora, é necessário processar e implantar o modelo.Now you need to process and deploy the model. Para saber mais, confira Implantar.For more information, see Deploy.

Tarefa 3: adicionar fontes de dados ao Gateway de dados localTask 3: Add Data Sources within your On-premises data gateway

Depois que o modelo tabular for implantado e estiver pronto para consumo, você precisará adicionar uma conexão de fonte de dados ao servidor tabular do Analysis Services local.Once your tabular model is deployed and ready for consumption, you need to add a data source connection to your on-premises Analysis Services tabular server.

  1. Para permitir que o serviço do Power BI acesse o serviço de análise local, é necessário ter um gateway de dados local instalado e configurado no seu ambiente.To allow the Power BI service access to your on-premises analysis service, you need an on-premises data gateway installed and configured in your environment.

  2. Depois de configurar o gateway corretamente, é necessário criar uma conexão de fonte de dados à instância de tabela do Analysis Services.Once the gateway is correctly configured, you need to create a data source connection for your Analysis Services tabular instance. Para mais informações, confira Gerenciar sua fonte de dados – Analysis Services.For more information, see Manage your data source - Analysis Services.

    Criar conexão de fonte de dados

Após a conclusão desse procedimento, o gateway está configurado e pronto para interagir com sua fonte de dados do Analysis Services local.With this procedure complete, the gateway is configured and ready to interact with your on-premises Analysis Services data source.

Tarefa 4: criar relatórios baseados no modelo tabular do Analysis Services usando o Power BI DesktopTask 4: Create report based on analysis services tabular model using Power BI desktop

  1. Inicie o Power BI Desktop e selecione Obter Dados > Banco de Dados.Start Power BI Desktop and select Get Data > Database.

  2. Na lista de fontes de dados, selecione o Banco de Dados do SQL Server Analysis Services e selecione Conectar.From the data sources list, select the SQL Server Analysis Services Database and select Connect.

    Conectar-se ao banco de dados do SQL Server Analysis Services

  3. Preencha os detalhes da instância de tabela do Analysis Services e selecione Conexão dinâmica.Fill in your Analysis Services tabular instance details and select Connect live. Selecione OK.Then select OK.

    Detalhes do Analysis Services

    Com o Power BI, a segurança dinâmica funciona apenas com uma conexão dinâmica.With Power BI, dynamic security works only with a live connection.

  4. Você pode ver que o modelo implantado está na instância do Analysis Services.You can see that the deployed model is in the Analysis Services instance. Selecione o respectivo modelo e selecione OK.Select the respective model and then select OK.

    Agora, o Power BI Desktop exibe todos os campos disponíveis à direita da tela no painel Campos.Power BI Desktop now displays all the available fields, to the right of the canvas in the Fields pane.

  5. No painel Campos, selecione a medida SalesAmount na tabela FactInternetSales e a dimensão SalesTerritoryRegion na tabela SalesTerritory.In the Fields pane, select the SalesAmount measure from the FactInternetSales table and the SalesTerritoryRegion dimension from the SalesTerritory table.

  6. Para manter esse relatório simples, não adicionaremos mais nenhuma coluna.To keep this report simple, we won't add any more columns right now. Para ter uma representação mais significativa dos dados, altere a visualização para Gráfico de rosca.To have a more meaningful data representation, change the visualization to Donut chart.

    Visualização de gráfico de rosca

  7. Quando o relatório estiver pronto, você poderá publicá-lo diretamente no portal do Power BI.Once your report is ready, you can directly publish it to the Power BI portal. Na faixa de opções Página Inicial do Power BI Desktop, selecione Publicar.From the Home ribbon in Power BI Desktop, select Publish.

Tarefa 5: Criar e compartilhar um dashboardTask 5: Create and share a dashboard

Você criou o relatório e fez a publicação no serviço do Power BI.You've created the report and published it to the Power BI service. Agora você pode usar o exemplo criado nas etapas anteriores para demonstrar o cenário de segurança do modelo.Now you can use the example created in previous steps to demonstrate the model security scenario.

Na função de Gerente de vendas, a usuária Graça pode ver os dados de todas as diferentes regiões de vendas.In the role as Sales Manager, the user Grace can see data from all the different sales regions. Graça cria esse relatório e publica-o no serviço do Power BI.Grace creates this report and publishes it to the Power BI service. Esse relatório foi criado nas tarefas anteriores.This report was created in the previous tasks.

Depois de Graça publicar o relatório, a próxima etapa é criar um dashboard no serviço do Power BI chamado TabularDynamicSec com base no relatório.Once Grace publishes the report, the next step is to create a dashboard in the Power BI service called TabularDynamicSec based on that report. Na imagem a seguir, observe que Graça consegue ver os dados correspondentes a todas as regiões de vendas.In the following image, notice that Grace can see the data corresponding to all the sales region.

Dashboard de serviço do Power BI

Agora, Graça compartilha o dashboard com sua colega, Rita, responsável pelas vendas na região da Austrália.Now Grace shares the dashboard with a colleague, Rita, who is responsible for the Australia region sales.

Compartilhar um dashboard do Power BI

Quando o Rita faz logon no serviço do Power BI e vê o dashboard compartilhado que Graça criou, somente as vendas da região da Austrália ficam visíveis.When Rita logs in to the Power BI service and views the shared dashboard that Grace created, only sales from the Australia region are visible.

Parabéns!Congratulations! O serviço do Power BI mostra a segurança em nível de linha dinâmica definida no modelo tabular do Analysis Services local.The Power BI service shows the dynamic row-level security defined in the on-premises Analysis Services tabular model. O Power BI usa a propriedade EffectiveUserName para enviar as atuais credenciais de usuário do Power BI à fonte de dados local para executar as consultas.Power BI uses the EffectiveUserName property to send the current Power BI user credential to the on-premises data source to run the queries.

Tarefa 6: Entender o que acontece nos bastidoresTask 6: Understand what happens behind the scenes

Esta tarefa pressupõe que você esteja familiarizado com o SQL Server Profiler, já que você precisa capturar um rastreamento do criador de perfil do SQL Server na instância tabular do SSAS local.This task assumes you're familiar with SQL Server Profiler, since you need to capture a SQL Server profiler trace on your on-premises SSAS tabular instance.

A sessão é inicializada assim que a usuária, Rita, acessa o dashboard no serviço do Power BI.The session gets initialized as soon as the user, Rita, accesses the dashboard in the Power BI service. Você pode ver que a função salesterritoryusers funciona imediatamente com o nome de usuário efetivo rita@contoso.comYou can see that the salesterritoryusers role takes an immediate effect with the effective user name as rita@contoso.com

       <PropertyList><Catalog>DefinedSalesTabular</Catalog><Timeout>600</Timeout><Content>SchemaData</Content><Format>Tabular</Format><AxisFormat>TupleFormat</AxisFormat><BeginRange>-1</BeginRange><EndRange>-1</EndRange><ShowHiddenCubes>false</ShowHiddenCubes><VisualMode>0</VisualMode><DbpropMsmdFlattened2>true</DbpropMsmdFlattened2><SspropInitAppName>PowerBI</SspropInitAppName><SecuredCellValue>0</SecuredCellValue><ImpactAnalysis>false</ImpactAnalysis><SQLQueryMode>Calculated</SQLQueryMode><ClientProcessID>6408</ClientProcessID><Cube>Model</Cube><ReturnCellProperties>true</ReturnCellProperties><CommitTimeout>0</CommitTimeout><ForceCommitTimeout>0</ForceCommitTimeout><ExecutionMode>Execute</ExecutionMode><RealTimeOlap>false</RealTimeOlap><MdxMissingMemberMode>Default</MdxMissingMemberMode><DisablePrefetchFacts>false</DisablePrefetchFacts><UpdateIsolationLevel>2</UpdateIsolationLevel><DbpropMsmdOptimizeResponse>0</DbpropMsmdOptimizeResponse><ResponseEncoding>Default</ResponseEncoding><DirectQueryMode>Default</DirectQueryMode><DbpropMsmdActivityID>4ea2a372-dd2f-4edd-a8ca-1b909b4165b5</DbpropMsmdActivityID><DbpropMsmdRequestID>2313cf77-b881-015d-e6da-eda9846d42db</DbpropMsmdRequestID><LocaleIdentifier>1033</LocaleIdentifier><EffectiveUserName>rita@contoso.com</EffectiveUserName></PropertyList>

Com base na solicitação de nome de usuário efetivo, o Analysis Services converte a solicitação para a credencial real contoso\rita depois de consultar o Active Directory local.Based on the effective user name request, Analysis Services converts the request to the actual contoso\rita credential after querying the local Active Directory. Assim que o Analysis Services obtém a credencial, o Analysis Services retorna os dados que o usuário tem permissão para exibir e acessar.Once Analysis Services gets the credential, Analysis Services returns the data the user has permission to view and access.

Se ocorrer mais atividades com o dashboard, com o SQL Profiler, você verá uma consulta específica voltada para o modelo de tabela do Analysis Services como uma consulta DAX.If more activity occurs with the dashboard, with SQL Profiler you would see a specific query coming back to the Analysis Services tabular model as a DAX query. Por exemplo, se Rita passar do dashboard para o relatório subjacente, a consulta a seguir ocorrerá.For example, if Rita goes from the dashboard to the underlying report, the following query occurs.

A consulta DAX volta para o modelo do Analysis Services

Você também pode ver abaixo a consulta DAX sendo executada para popular os dados do relatório.You can also see below the DAX query that is getting executed to populate report data.

EVALUATE
  ROW(
    "SumEmployeeKey", CALCULATE(SUM(Employee[EmployeeKey]))
  )

<PropertyList xmlns="urn:schemas-microsoft-com:xml-analysis">``
          <Catalog>DefinedSalesTabular</Catalog>
          <Cube>Model</Cube>
          <SspropInitAppName>PowerBI</SspropInitAppName>
          <EffectiveUserName>rita@contoso.com</EffectiveUserName>
          <LocaleIdentifier>1033</LocaleIdentifier>
          <ClientProcessID>6408</ClientProcessID>
          <Format>Tabular</Format>
          <Content>SchemaData</Content>
          <Timeout>600</Timeout>
          <DbpropMsmdRequestID>8510d758-f07b-a025-8fb3-a0540189ff79</DbpropMsmdRequestID>
          <DbPropMsmdActivityID>f2dbe8a3-ef51-4d70-a879-5f02a502b2c3</DbPropMsmdActivityID>
          <ReturnCellProperties>true</ReturnCellProperties>
          <DbpropMsmdFlattened2>true</DbpropMsmdFlattened2>
          <DbpropMsmdActivityID>f2dbe8a3-ef51-4d70-a879-5f02a502b2c3</DbpropMsmdActivityID>
        </PropertyList>

ConsideraçõesConsiderations

  • A segurança em nível de linha local com o Power BI só está disponível com a conexão dinâmica.On-premises row-level security with Power BI is only available with live connection.

  • As alterações nos dados após o processamento do modelo seriam imediatamente disponibilizadas para os usuários que estão acessando o relatório com a conexão dinâmica por meio do serviço do Power BI.Any changes in the data after processing the model would be immediately available for the users accessing the report with live connection from the Power BI service.