CODEWITHSUNDEEP
azure-synapseazure-synapse-analyticsazure-role-environment
Moody_girl
Moody_girl

Reputation: 85

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 [[email protected]];
GRANT CONNECT TO [[email protected]];
GRANT SELECT ON OBJECT::dbo.tbl_a TO [[email protected]];

Any guidance would be greatly appreciated!

Upvotes: 0

Views: 425

Answers (1)

Dileep Raj Narayan Thumula
Dileep Raj Narayan Thumula

Reputation: 3250

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
[email protected]    SQL_USER    SELECT  GRANT   taxi_zone_View  VIEW
[email protected]    SQL_USER    CONNECT GRANT

enter image description here

Upvotes: 0

Related Questions