azure-synapseazure-synapse-analyticsazure-role-environment

How to Assign Roles in Synapse to Restrict Data Access?


I'm working on a project in Synapse and need to assign roles to users such that certain people can see specific data while others cannot. Here's what I've tried so far: Creating a role using the CREATE ROLE "Basic Access" command in SQL script, which gives the user access to one of 30 views. We then assigned a user to the role in order to test it, the user could unfortunately still access everything. Although when he used Execute as [his user] at the start it works, however this is not what we want going forward. We would like the SQL database (serverless) to recognise the user and enforce restrictions straight away.

Something that may be the cause of the issue is that he has the Synapse SQL administrator in the roles, we feel this might override any restrictions on his permissions in the database. However, if we were to remove the SQL administrator role and give him something else like Synapse Contributor, he loses the ability to even see the SQL database.

It seems to suggest he does not have access to the SQL pool, but I see no option to give permissions in the SQL built in pool.

Please see some of the code we used below (It ran fine)

USE database; 
Create Role "Basic Access";
Alter Role "Basic Access" Add Member [john doe] ;
GRANT VIEW DATABASE STATE TO [john.doe@contoso.com];
GRANT CONNECT TO [john.doe@contoso.com];
GRANT SELECT ON OBJECT::dbo.tbl_a TO [john.doe@contoso.com];

Any guidance would be greatly appreciated!


Solution

  • As you mentioned you need Perform and provide BASIC ACCESS to User after removing the SQL administrator role. Synapse User would be suitable since it allows to workspaces/read.

    As you mentioned that you want to allow user to access 1 of 30 Views created in serverless pool.

    I have tried the below approach:

    use master
    GO
    Create Login [USER] WITH PASSWORD='PASSWORD'
    use db02
    Create User [USER] from login [USER]
    

    enter image description here

    GRANT SELECT ON [dbo].[taxi_zone_View] TO [USER]
    

    Results:

    PrincipalName   PrincipalType   PermissionName  PermissionState ObjectName  ObjectType
    xxx@yyyy.com    SQL_USER    SELECT  GRANT   taxi_zone_View  VIEW
    xxx@yyyy.com    SQL_USER    CONNECT GRANT       
    

    enter image description here