Simple Access Management for Multi Tenant Environment in Azure Synapse

Hi there,

Hope you are doing well and having a great start of 2021!

Recently several of our customers were posing a very interesting question. How to manage access to data in case of multi-tenant solution?

In a nutshell the idea is simple. There is a model which can be shared between several B2B customers. The model should be accessible via ODBC applications supporting MFA and various authentication methods. Apparently, the data should only be visible to data owners. Moreover, the data owners may have multiple roles with various access patterns and rights. Plus, user management is implemented in the 3rd party IdP.

Several main technologies in Azure Synapse represent data-level security and access controls, including:

-         Row Level Security

-         Column Level Security

-         Dynamic Data Masking

-         Transparent Data Encryption

The question remains – how can I manage it at scale? And Azure Active Directory is the simplest answer.

The whole process might look like this

Below is a detailed description with some small example.

0.      We are defining two security models for our data – permissions at the object level (aka tables, views, columns, and masking) and permissions at the row level (predicate which will be used to distinguish tenants and restrict access only to the certain subset of data.

Let us say that for the sake of simplicity we have some very plain data structure like a sales table below.

CREATE TABLE Sales 

( 

   OrderID int, 

   SalesRep varchar(100) NOT NULL, 

   Product varchar(10),

   Qty int,

   FirstName varchar(100) NULL,

   LastName varchar(100) NOT NULL,

   Phone varchar(12) NULL,

   Email varchar(100) NULL,

   SSN char(9) NULL

);

In this table multiple SalesRep records will be stored and they should be only accessible by the sales representative. Moreover, we would like to have two different types of users – one with and another one without access to personal data.

So, we would use SalesRep field as a input for the predicate for the row-level security policy.

-- Example of data

INSERT INTO Sales VALUES (3, 'Sales1', 'Valve', 4, 'Andrei2', 'Zaichikov2', '+35212323456', '[email protected]', '123456789');


INSERT INTO Sales VALUES (4, 'Sales2', 'Bracket', 2, 'Andrei3', 'Zaichikov3', '+35212323456', '[email protected]', '123456789');

At the same time, we will use combination of Column-Level security and Dynamic-Data Masking to restrict users access to the PII.

1.      We are registering a user in Azure Active Directory (AAD) using his / her existing credentials.

We can use multiple ways, one of them is to use Power Shell as described here.

2.      We are creating AAD Groups which are corresponding to customers (for row-level security – I call them data groups) and Groups corresponding to object-level permissions (which I call user groups).

For example, using AAD as described in the docs.

New-AzureADGroup -Description "Sales1 Customer" -DisplayName "Sales1" -MailEnabled $false -SecurityEnabled $true

New-AzureADGroup -Description "Sales2 Customer" -DisplayName "Sales2" -MailEnabled $false -SecurityEnabled $true

New-AzureADGroup -Description "Power User" -DisplayName "PowerUser" -MailEnabled $false -SecurityEnabled $true


New-AzureADGroup -Description "Common User" -DisplayName "CommonUser" -MailEnabled $false -SecurityEnabled $true

3.      We are creating a user in DB service from AAD Group.

CREATE USER [PowerUser] FROM EXTERNAL PROVIDER;

CREATE USER [CommonUser] FROM EXTERNAL PROVIDER;

CREATE USER [Sales1] FROM EXTERNAL PROVIDER;


CREATE USER [Sales2] FROM EXTERNAL PROVIDER;

4.      We are granting permissions to newly created users. For user groups it is done via GRANT mainly while for data groups we are defining the security policies and predicates.

-- Applying Data Masking

ALTER TABLE Sales ALTER COLUMN LastName varchar(100) MASKED WITH (FUNCTION = 'partial(2,"XXX",0)');



-- Granting Permissions to CommonUser

GRANT SELECT ON Sales(OrderID, SalesRep, Product, Qty, FirstName, LastName) TO CommonUser; -- We are allowing to select only particular column

 

-- Granting Permissions to PowerUser

GRANT SELECT ON Sales TO PowerUser; -- Allowing to select all data


GRANT UNMASK TO PowerUser; -- Allowing to remove masking

NOTE. The interesting thing is in case when the user is assigned to user or data group only, he/she won’t obtain any access to data. The access is defined by the combination of both. Indeed, user group does not have proper security predicate for security policy while data group does not have object-level permissions.

Also we need to define security function and security policy for our data groups.

CREATE FUNCTION Security.fn_securitypredicate_sales(@SalesRep AS varchar(20))  
    RETURNS TABLE  
WITH SCHEMABINDING  
AS  
    RETURN SELECT 1 AS fn_securitypredicate_result
    WHERE IS_MEMBER(@SalesRep) = 1    

CREATE SECURITY POLICY SalesFilter  
ADD FILTER PREDICATE Security.fn_securitypredicate_sales(SalesRep)
ON dbo.Sales  
WITH (STATE = ON);

Above we are using the simplest possible approach which relies on the naming convention. If data group name (where our authenticated user is a member) equals the content of SalesRep field, then the user can view the data of these rows.

We can also build much more sophisticated functions.

5.      We are adding users to appropriate user groups to assign proper permissions to access objects: tables, views and columns.

Add-AzureADGroupMember -ObjectId "<your user group id>" -RefObjectId "<your user id>"

More information can be found here.

6.      We are adding users to appropriate data groups to allow them to get some data from database.

Add-AzureADGroupMember -ObjectId "<your data group id>" -RefObjectId "<your user id>"

NOTE. Please remember that User Group and Data Group are not official terms and are used in this short article to distinguish different roles which there groups are playing in access management.

7.      Our users are accessing their data with their ODBC-backed applications using their own credentials. The latest version of the driver is preferred since it allows you to use most of the advanced authentication capabilities. Please refer to Microsoft documentation.

One of the last things to consider is integration with the 3rd party Identity Provider. That can be achieved by creating your SCIM endpoint and integrating it with AAD as described in Microsoft documentation.

P.S. These capabilities are much more advanced than just a typical RBAC and can also be used with External Tables allowing to treat Synapse SQL Pool as SQL Endpoint for providing access control to information stored in ADLS.