RLS in synapse serverless SQL pool using views - Stack Overflow

admin管理员组

文章数量:1400195

Issue: In synapse serverless SQL pool when I try to access view data created from file stored in ADLS with AD/Entra users having "Synapse SQL pool admin" rights on the synapse workspace and no direct access to files in ADLS, I am getting error that "event.csv cannot be opened because it does not exist or it is used by another process". How can this be resolved so that row level security(RLS) works on top of the view without allowing ad/entra users to access adls files directly?

Below is what I have implemented

1)In azure synapse serverless SQL pool I have created a database "events_db"

2)I have two entra/AD users user1 and user2

3)I have created an external table "user_access" in synapse serverless sql pool with 2 columns "username" and "can_see_hidden" of type varchar(25) from user_access.csv file stored in ADLS. "can_see_hidden" can be either 0 or 1. "username" are populated with /company email values.

4)I then created a view "vw_events_rls" from file events.csv stored in ADLS and joined the external table created in step-3 as below"

create view vw_events_rls
SELECT v.*
FROM OPENROWSET(
      BULK '/events.csv',
      DATA_SOURCE = 'events_src',
      FORMAT = 'CSV',
      FIRSTROW = 2,
      FIELDTERMINATOR = ',',
      ROWTERMINATOR = '\n',
      PARSER_VERSION = '2.0'
)
WITH (
     event_id INT,
     event_date VARCHAR(25),
     event_name VARCHAR(25),
     event_show VARCHAR(25)
    ) AS v
JOIN user_access u ON u.user_name = SUSER_SNAME()
WHERE (v.event_show = 'show')  OR
      (v.event_show = 'hide' AND u.can_see_hidden = 1);

5)The views has columns "event_id", "event_date", "event_name", "event_show". If event_show = "hide" these rows should be hidden from entra user "user2" entra user "user1" must see all the rows irrespective of "event_show = show" or "event_show = hide"

6)Azure synapse User Managed identity has "storage data contributor" rights on ADLS tied to synapse

7)AD/Entra user1 and user2 has "Synapse SQL administrator" rights on the synapse workspace

8)AD/Entra user1 and user2 should not go and directly access the files stored in ADLS. They should only access data in the files via the view. And they indeed dont have access to ADLS.

Thanks in advance

Issue: In synapse serverless SQL pool when I try to access view data created from file stored in ADLS with AD/Entra users having "Synapse SQL pool admin" rights on the synapse workspace and no direct access to files in ADLS, I am getting error that "event.csv cannot be opened because it does not exist or it is used by another process". How can this be resolved so that row level security(RLS) works on top of the view without allowing ad/entra users to access adls files directly?

Below is what I have implemented

1)In azure synapse serverless SQL pool I have created a database "events_db"

2)I have two entra/AD users user1 and user2

3)I have created an external table "user_access" in synapse serverless sql pool with 2 columns "username" and "can_see_hidden" of type varchar(25) from user_access.csv file stored in ADLS. "can_see_hidden" can be either 0 or 1. "username" are populated with /company email values.

4)I then created a view "vw_events_rls" from file events.csv stored in ADLS and joined the external table created in step-3 as below"

create view vw_events_rls
SELECT v.*
FROM OPENROWSET(
      BULK '/events.csv',
      DATA_SOURCE = 'events_src',
      FORMAT = 'CSV',
      FIRSTROW = 2,
      FIELDTERMINATOR = ',',
      ROWTERMINATOR = '\n',
      PARSER_VERSION = '2.0'
)
WITH (
     event_id INT,
     event_date VARCHAR(25),
     event_name VARCHAR(25),
     event_show VARCHAR(25)
    ) AS v
JOIN user_access u ON u.user_name = SUSER_SNAME()
WHERE (v.event_show = 'show')  OR
      (v.event_show = 'hide' AND u.can_see_hidden = 1);

5)The views has columns "event_id", "event_date", "event_name", "event_show". If event_show = "hide" these rows should be hidden from entra user "user2" entra user "user1" must see all the rows irrespective of "event_show = show" or "event_show = hide"

6)Azure synapse User Managed identity has "storage data contributor" rights on ADLS tied to synapse

7)AD/Entra user1 and user2 has "Synapse SQL administrator" rights on the synapse workspace

8)AD/Entra user1 and user2 should not go and directly access the files stored in ADLS. They should only access data in the files via the view. And they indeed dont have access to ADLS.

Thanks in advance

• serverless
• azure-synapse

Share Improve this question Follow asked Mar 24 at 13:43 SriSri 7988 bronze badges 2

• Grant Synapse Managed Identity "Storage Blob Data Reader" Role on ADLS – Dileep Raj Narayan Thumula Commented Mar 24 at 16:35
• Hi Dileep, Synapse manged identity already has "storage blob data contributor" role in ADLS – Sri Commented Mar 25 at 4:39

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

I have received the same ERROR: Error:

Msg 16562, Level 16, State 1, Line 2 External table 'dbo.smplingexttbl' is not accessible because location does not exist or it is used by another process.

In order to resolve the issue

USE MASTER;  
CREATE LOGIN [<YOUR ACTIVE DIRECTORY GROUP>] FROM EXTERNAL PROVIDER;  
CREATE USER [Dilip02] FROM LOGIN [synpdileep02];  
GRANT SELECT ON dbo.smplexttbl TO [Dilip02];  
GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::[credentialobject200] TO [Dilip02];

In the above code

Creating a login using an external Active Directory group
Created a database user linked to the login
Granted SELECT permission on a specific table
Granted REFERENCES permission on a database-scoped credential

• Create a login directly in the master database of the Serverless SQL Pools service and grant permissions to the user.
• Also, establish a database role to handle permissions and assign users to this role.

Reference: User Permissions in Serverless SQL Pools: External Tables vs Views

本文标签： RLS in synapse serverless SQL pool using viewsStack Overflow