Search code examples
azureazure-active-directoryazure-synapseazure-cli

Azure Synapse: How to grant access to specific schemas using Az CLI and AAD groups?

How to manage access to Az Synapse schemas via az cli.

Hi, I'm trying to set up RBAC accesss to our Synapse views with AAD groups. So in practice I have a schema A with sensitive data and schema B and C that are without any sensitive data and everyone can access them and I have two AAD group G1 and G2. Users in group G1 can see data in all schemas (A,B,C) Users in group G2 can see data in just in non-sensitive schemas (B,C)

I want to use Az cli to manage it.

What I found out is this command that could hopefully work:

az synapse role assignment create --workspace-name SynapseID --role "Reader" --asignee AAD_group_ID --scope ??schema_ID??

But I don't know how to find my schema IDs and I'm not even sure if I'm understanding this correctly and scope should be something else so I would appreciate some help here please.

I tried this common for looking up schemas ID but it didn't give me satisfying answer:

az synapse role scope list --workspace-name

Anybody tried this or is my approach not ideal and there is better way how to link schema access to AAD groups?

Ps. I'm using MAC so Azure Synapse studio isn't working for me so that's why Az CLI

Solution

  • When checked for the following to check current role assignments using scope.Got invalid scope error:

     az synapse role assignment list --workspace-name mynewwspace  --scope “/subscriptions/xxxx/resourceGroups/xxxx/providers/Microsoft.Synapse/workspaces/mynewwspace/sqlPools/kavyasqlpool"

    (ScopeNotAllowed) Scope not allowed: workspaces/mynewwspace/sqlPools/kavyasqlpool. Allowed scopes: Workspace, LinkedService, BigDataPool, ScopePool, IntegrationRuntime, Credential.

    Check the following to check the schemaId using rest API:

    GET https://management.azure.com/subscriptions/ xxxxx/resourceGroups/myrg/providers/Microsoft.Synapse/workspaces/ mynewwspace /sqlPools/kavyasqlpool/schemas?api-version=2021-06-01

    Powershell commands to get the list of schemas where the response will have schemaId .

    Connect-AzAccount
$azContext = Get-AzContext
$azProfile = [Microsoft.Azure.Commands.Common.Authentication.Abstractions.AzureRmProfileProvider]::Instance.Profile
$profileClient = New-Object -TypeName Microsoft.Azure.Commands.ResourceManager.Common.RMProfileClient -ArgumentList ($azProfile)
$token = $profileClient.AcquireAccessToken($azContext.Subscription.TenantId)
$authHeader = @{
    'Content-Type'='application/json'
    'Authorization'='Bearer ' + $token.AccessToken
}

# Invoke the REST API
#$restUri = 'https://management.azure.com/subscriptions/xxxx?api-version=2020-01-01'
$restUri='https://management.azure.com/subscriptions/xxxx/resourceGroups/xxxx/providers/Microsoft.Synapse/workspaces/mynewwspace/sqlPools/kavyasqlpool/schemas?api-version=2021-06-01'
$response = Invoke-RestMethod -Uri $restUri -Method Get -Headers $authHeader | Verbose
$response

    Response:

    value

    {@{id=/subscriptions/xxxx/resourceGroups/xxx/providers/Microsoft.Synapse/workspaces/mynewwspace/sqlPools/kavyasqlpool/schemas/db...

    enter image description here

    Sql Pool Schemas - List - REST API (Azure Synapse) | Microsoft Learn