Retrieve Azure Synapse role-based access control (RBAC) Information using PowerShell

Published Jun 13 2022 08:00 AM 1,891 Views
Microsoft

 

Azure Synapse Analytics is a limitless analytics service that brings together data integration, enterprise data warehousing and big data analytics. It gives you the freedom to query data on your terms, using either serverless or dedicated resources—at scale. Azure Synapse brings these worlds together with a unified experience to ingest, explore, prepare, manage and serve data for immediate BI and machine learning needs.

Synapse RBAC extends the capabilities of Azure RBAC for Synapse workspaces and their content.

 

Synapse RBAC is used to manage who can:

  • Publish code artifacts and list or access published code artifacts,
  • Execute code on Apache Spark pools and Integration runtimes,
  • Access linked (data) services protected by credentials
  • Monitor or cancel job execution, review job output, and execution logs.

Azure Synapse RBAC has built-in roles and scopes that helps to manage permissions in Azure Synapse Analytics - 

 

Role

Permissions

Scopes

Synapse Administrator

Full Synapse access to SQL pools, Data Explorer pools, Apache Spark pools, and Integration runtimes. Includes create, read, update, and delete access to all published code artifacts. Includes Compute Operator, Linked Data Manager, and Credential User permissions on the workspace system identity credential. Includes assigning Synapse RBAC roles. In addition to Synapse Administrator, Azure Owners can also assign Synapse RBAC roles. Azure permissions are required to create, delete, and manage compute resources.

 

Can read and write artifacts

Can do all actions on Spark activities.

Can view Spark pool logs

Can view saved notebook and pipeline output

Can use the secrets stored by linked services or credentials

Can assign and revoke Synapse RBAC roles at current scope

Workspace

Spark pool

Integration runtime

Linked service

Credential

Synapse Apache Spark Administrator

Full Synapse access to Apache Spark Pools. Create, read, update, and delete access to published Spark job definitions, notebooks and their outputs, and to libraries, linked services, and credentials.  Includes read access to all other published code artifacts. Doesn't include permission to use credentials and run pipelines. Doesn't include granting access.

 

Can do all actions on Spark artifacts

Can do all actions on Spark activities

Workspace

Spark pool

Synapse SQL Administrator

Full Synapse access to serverless SQL pools. Create, read, update, and delete access to published SQL scripts, credentials, and linked services.  Includes read access to all other published code artifacts.  Doesn't include permission to use credentials and run pipelines. Doesn't include granting access.

 

Can do all actions on SQL scripts

Can connect to SQL serverless endpoints with SQL db_datareaderdb_datawriterconnect, and grant permissions

Workspace

Synapse Contributor

Full Synapse access to Apache Spark pools and Integration runtimes. Includes create, read, update, and delete access to all published code artifacts and their outputs, including credentials and linked services.  Includes compute operator permissions. Doesn't include permission to use credentials and run pipelines. Doesn't include granting access.

 

Can read and write artifacts

Can view saved notebook and pipeline output

Can do all actions on Spark activities

Can view Spark pool logs

Workspace

Spark pool

Integration runtime

Synapse Artifact Publisher

Create, read, update, and delete access to published code artifacts and their outputs. Doesn't include permission to run code or pipelines, or to grant access.

 

Can read published artifacts and publish artifacts

Can view saved notebook, Spark job, and pipeline output

Workspace

Synapse Artifact User

Read access to published code artifacts and their outputs. Can create new artifacts but can't publish changes or run code without additional permissions.

Workspace

Synapse Compute Operator

Submit Spark jobs and notebooks and view logs.  Includes canceling Spark jobs submitted by any user. Requires additional use credential permissions on the workspace system identity to run pipelines, view pipeline runs and outputs.

 

Can submit and cancel jobs, including jobs submitted by others

Can view Spark pool logs

Workspace

Spark pool

Integration runtime

Synapse Monitoring Operator

Read published code artifacts, including logs and outputs for notebooks and pipeline runs. Includes ability to list and view details of serverless SQL pools, Apache Spark pools, Data Explorer pools, and Integration runtimes. Requires additional permissions to run/cancel pipelines, Spark notebooks, and Spark jobs.

Workspace

Synapse Credential User

Runtime and configuration-time use of secrets within credentials and linked services in activities like pipeline runs. To run pipelines, this role is required, scoped to the workspace system identity.

 

Scoped to a credential, permits access to data via a linked service that is protected by the credential (also requires compute use permission)

Allows execution of pipelines protected by the workspace system identity credential(with additional compute use permission)

Workspace

Linked Service

Credential

Synapse Linked Data Manager

Creation and management of managed private endpoints, linked services, and credentials. Can create managed private endpoints that use linked services protected by credentials

Workspace

Synapse User

List and view details of SQL pools, Apache Spark pools, Integration runtimes, and published linked services and credentials. Doesn't include other published code artifacts.  Can create new artifacts but can't run or publish without additional permissions.

 

Can list and read Spark pools, Integration runtimes.

Workspace, Spark pool

Linked service

Credential

 

There are multiple ways that the RBAC roles can be configured.
The easiest and most friendly way is to perform this action using Azure Synapse workspace. (more)

In PowerShell, there are number of PowerShell cmdlets that helps to manage or retrieve the information in Synapse RBAC. 

 

Example:

  • Get-AzSynapseRoleAssignment - Gets a Synapse Analytics role assignment. (more)
  • New-AzSynapseRoleAssignment - Creates a Synapse Analytics role assignment. (more)
  • Remove-AzSynapseRoleAssignment - Deletes a Synapse Analytics role assignment. (more)

One of the key use cases that most customers face difficulties while retrieving or assigning the role-based access control in Azure Synapse Analytics, that they cannot find the correct usernames, group names or the service principal names using the PowerShell cmdlet "Get-AzSynapseRoleAssignment". The PowerShell cmdlet only provides limited information and it's difficult to understand since that contains the object IDs. 

 

Example: (Following output is captured from “Get-AzSynapseRoleAssignment”)

 

CharithCaldera_0-1654281769763.png

 

In order to retrieve the additional information, users can have following example scripts that provides the username and other relevant information for Azure Synapse RBAC. 

 

Get all the Synapse RBAC Information: 


Below PowerShell script helps to map the RBAC Object IDs with usernames, groups and the service principals. 
The script output provides all the RBAC information in Azure Synapse Analytics Workspace. 

Note: This is only an example to retrieve the information and not to use any production code

Script Name: GetSynapseRBACInfo.ps1

 

 

<#   
.NOTES     
    Author: Charith Caldera
    LinkedIn: https://www.linkedin.com/in/charith-caldera-52590a10b/
    Email: ccaldera@microsoft.com
    Last Updated: 2022-06-03

.SYNOPSIS   
    Verify the usernames in Synapse RBAC
    
#UPDATES
#> 

Clear-Host

# Map Role Assignment IDs with Roles
function GetASynapseRoleDefinition{
    param($roleid)

    try
    {
        Get-AzSynapseRoleDefinition -id $roleid -WorkspaceName $workspacename -ErrorAction:SilentlyContinue
    }
    catch
    {
        Write-Host "Error Occured while getting Synapse RBAC Definitions"
    }
    
}

# Get Synapse RBAC Users
function GetSynapseRBACUsers{
    try
    {
        foreach($profile in $profiles)
        {
    
        
            $aduser = Get-AzADUser -ObjectId $profile.ObjectId -ErrorAction:SilentlyContinue
            if($aduser)
            {
                $assignments = Get-AzSynapseRoleAssignment -WorkspaceName $workspacename |?{$_.ObjectId -eq $profile.ObjectId}
                Write-Host "----------------------------------------"
                Write-Host " "
                Write-Host "AAD User Identified :" -ForegroundColor Green
                Write-Host " "
                Write-Host "  User object Found on Object ID" $profile.ObjectId -ForegroundColor Cyan
                Write-Host "    AAD User Principal Name  :" $aduser.UserPrincipalName
                Write-Host "    Role Assignment Id       :" $assignments.RoleAssignmentId
                Write-Host "    Role Definition Id       :" $assignments.RoleDefinitionId
                $rolename = GetASynapseRoleDefinition $assignments.RoleDefinitionId 
                Write-Host "    Synapse Role             :" $rolename.Name
                Write-Host "    Principal Type           :" $assignments.principalType
                Write-Host "    Scope                    :" $assignments.Scope
                Write-Host " "
            } 
        }
    }
    catch
    {
        Write-Host "Error Occured while getting Synapse RBAC users"
    }
}

# Get Synapse RBAC Groups
function GetSynapseRBACGroups{
    try
    {
        foreach($profile in $profiles)
        {
    
        
            $adgroup = Get-AzADGroup -ObjectId $profile.ObjectId -ErrorAction:SilentlyContinue
            if($adgroup)
            {
                $assignments = Get-AzSynapseRoleAssignment -WorkspaceName $workspacename |?{$_.ObjectId -eq $profile.ObjectId}
                Write-Host "----------------------------------------"
                Write-Host " "
                Write-Host "AAD Group Identified:" -ForegroundColor Green
                Write-Host " "
                Write-Host "  Group object Found on Object ID" $profile.ObjectId -ForegroundColor Cyan
                Write-Host "    AAD Group Name            :" $adgroup.DisplayName
                Write-Host "    Role Assignment Id        :" $assignments.RoleAssignmentId
                Write-Host "    Role Definition Id        :" $assignments.RoleDefinitionId
                $rolename = GetASynapseRoleDefinition $assignments.RoleDefinitionId 
                Write-Host "    Synapse Role              :" $rolename.Name
                Write-Host "    Principal Type            :" $assignments.principalType
                Write-Host "    Scope                     :" $assignments.Scope
                Write-Host " "
            } 
        }
    }
    catch
    {
        Write-Host "Error Occured while getting Synapse RBAC Groups"
    }
}

# Get Synapse RBAC Service Principals
function GetSynapseRBACSPs{
    try
    {
        foreach($profile in $profiles)
        {
    
        
            $adsps = Get-AzADServicePrincipal -ObjectId $profile.ObjectId -ErrorAction:SilentlyContinue
            if($adsps)
            {
                $assignments = Get-AzSynapseRoleAssignment -WorkspaceName $workspacename |?{$_.ObjectId -eq $profile.ObjectId}
                Write-Host "----------------------------------------"
                Write-Host " "
                Write-Host "Service Principal Identified:" -ForegroundColor Green
                Write-Host " "
                Write-Host "  Service Principal Found on Object ID" $profile.ObjectId -ForegroundColor Cyan
                Write-Host "    AAD Group Name            :" $adsps.DisplayName
                Write-Host "    Role Assignment Id        :" $assignments.RoleAssignmentId
                Write-Host "    Role Definition Id        :" $assignments.RoleDefinitionId
                $rolename = GetASynapseRoleDefinition $assignments.RoleDefinitionId 
                Write-Host "    Synapse Role              :" $rolename.Name
                Write-Host "    Principal Type            :" $assignments.principalType
                Write-Host "    Scope                     :" $assignments.Scope
                Write-Host " "
            } 
        }
    }
    catch
    {
        Write-Host "Error Occured while getting Synapse RBAC Service Principals"
    }
}


####################################################
# Main

Write-Host "Executing PowerShell Script" -ForegroundColor Green
$workspacename = Read-Host "Synapse Workspace Name"
$workspacenameob = Get-AzSynapseWorkspace -WorkspaceName $workspacename -ErrorAction:SilentlyContinue

if($workspacenameob.Name)
    {
        $profiles = Get-AzSynapseRoleAssignment -WorkspaceName $workspacenameob.Name | select ObjectId
        Write-Host " "

        if(GetSynapseRBACUsers)
        {
           GetSynapseRBACUsers 
        }

        if(GetSynapseRBACGroups)
        {
            GetSynapseRBACGroups
        }

        if(GetSynapseRBACSPs)
        {
            GetSynapseRBACSPs
        }
    } else
    {
        Write-Host "Sorry, workspace not found" -ForegroundColor Yellow
        Write-Host " "
    }

Write-Host "PowerShell Script Completed" -ForegroundColor Green
Write-Host " "

 

 

Example: (Following is the output of above script)

CharithCaldera_1-1654282019300.png

 

Get Specific user RBAC information: 

 

Below PowerShell script helps to map a specific user with Synapse RBAC. 
The script provides the information for a specific username, group or a service principal. 

Note: This is only an example to retrieve the information and not to use any production code

Script Name: GetSynapseRBACUser.ps1

 

 

<#   
.NOTES     
    Author: Charith Caldera
    LinkedIn: https://www.linkedin.com/in/charith-caldera-52590a10b/
    Email: ccaldera@microsoft.com
    Last Updated: 2022-06-03

.SYNOPSIS   
    Find the username in Synapse RBAC
    
#UPDATES
#> 

Clear-Host

# Map Role Assignment IDs with Roles
function GetASynapseRoleDefinition{
    param($roleid)

    Get-AzSynapseRoleDefinition -id $roleid -WorkspaceName $workspacename
    
}


# Get Synapse RBAC Users
function GetSynapseRBACUsers{
    
    $profile = Get-AzADUser -UserPrincipalName $username
    $roleassignment = Get-AzSynapseRoleAssignment -WorkspaceName $workspacename |?{$_.ObjectId -eq $profile.id}
    if($roleassignment)
    {
       Write-Host "AAD Users Identified :" -ForegroundColor Green
       Write-Host " "
       Write-Host "    Username                 :" $username
       Write-Host "    Role Assignment Id       :" $roleassignment.RoleAssignmentId
       Write-Host "    Role Definition Id       :" $roleassignment.RoleDefinitionId
       $rolename = GetASynapseRoleDefinition $roleassignment.RoleDefinitionId 
       Write-Host "    Synapse Role             :" $rolename.Name
       Write-Host "    Principal Type           :" $roleassignment.principalType
       Write-Host "    Scope                    :" $roleassignment.Scope
       Write-Host " "
    } 

}

# Get Synapse RBAC Groups
function GetSynapseRBACGroups{
    
    $profile = Get-AzADGroup -DisplayName $username
    $roleassignment = Get-AzSynapseRoleAssignment -WorkspaceName $workspacename |?{$_.ObjectId -eq $profile.id}
    if($roleassignment)
    {
       Write-Host "AAD Groups Identified:" -ForegroundColor Green
       Write-Host " "
       Write-Host "    AAD Group Name           :" $username
       Write-Host "    Role Assignment Id       :" $roleassignment.RoleAssignmentId
       Write-Host "    Role Definition Id       :" $roleassignment.RoleDefinitionId
       $rolename = GetASynapseRoleDefinition $roleassignment.RoleDefinitionId 
       Write-Host "    Synapse Role             :" $rolename.Name
       Write-Host "    Principal Type           :" $roleassignment.principalType
       Write-Host "    Scope                    :" $roleassignment.Scope
       Write-Host " "
    } 

}

# Get Synapse RBAC Service Principals
function GetSynapseRBACSPs{
    
    $profile = Get-AzADServicePrincipal -DisplayName $username
    $roleassignment = Get-AzSynapseRoleAssignment -WorkspaceName $workspacename |?{$_.ObjectId -eq $profile.id}
    if($roleassignment)
    {
       Write-Host "Service Principals Identified:" -ForegroundColor Green
       Write-Host " "
       Write-Host "    Service Principal Name   :" $username
       Write-Host "    Role Assignment Id       :" $roleassignment.RoleAssignmentId
       Write-Host "    Role Definition Id       :" $roleassignment.RoleDefinitionId
       $rolename = GetASynapseRoleDefinition $roleassignment.RoleDefinitionId 
       Write-Host "    Synapse Role             :" $rolename.Name
       Write-Host "    Principal Type           :" $roleassignment.principalType
       Write-Host "    Scope                    :" $roleassignment.Scope
       Write-Host " "
    } 

}

Write-Host "Executing PowerShell Script" -ForegroundColor Green
Write-Host " "
$workspacename = Read-Host "Synapse Workspace Name"
$workspacenameob = Get-AzSynapseWorkspace -WorkspaceName $workspacename -ErrorAction:SilentlyContinue

if($workspacenameob.Name)
    {
        $username = Read-Host "User Name (name@domain) or Service Principal Display Name" 
        Write-Host " "

        if(GetSynapseRBACUsers)
        {
           GetSynapseRBACUsers 
        }

        if(GetSynapseRBACGroups)
        {
            GetSynapseRBACGroups
        }

        if(GetSynapseRBACSPs)
        {
            GetSynapseRBACSPs
        }

    } else
    {
        Write-Host "Sorry, workspace not found" -ForegroundColor Yellow
        Write-Host " "
    }

Write-Host "PowerShell Script Completed" -ForegroundColor Green
Write-Host " "

 

 

Example: (Following output is the output of above script)

CharithCaldera_2-1654282176291.png

 

How to execute the scripts? 

 

There are multiple ways you can execute the scripts.

  1. From local host - 

    Copy the commands to a PowerShell script
    Rename the PowerShell scripts as "GetSynapseRBACInfo.ps1" & "GetSynapseRBACUser.ps1"
    Execute the PowerShell script

    Note: Az.Synapse module & Az.Resources modules needs to be installed

  2. From Azure Cloud Shell. 

    Upload both scripts to Azure Cloud Shell
    Execute the scripts as #1.

    CharithCaldera_3-1654282586610.png

The workspace name, AAD username, AAD user group or Service principal name is required as parameters. 
Example: (Following output captured while the script is requesting the necessary information)

CharithCaldera_4-1654282788823.png

 

         

Co-Authors
Version history
Last update:
‎Jun 08 2022 05:41 AM
Updated by: