Row-Level security (RLS) is a technology that enables you to use group membership or execution context to control who has access to each row entry within a dataset. When paired with an Azure Synapse environment, we are able to utilize this technology on an external table. For our example today, we will use an external table sourced from a parquet file, stored in an Azure Data Lake Storage Account Gen2.
How does RLS work?
We will be using a parquet file filled with fake Personal Health Information (PHI) stored in an Azure Storage Account to populate our external table. Notice there is a column named ‘Viewer’. This column will drive the row-level access for our table. When a user with a matching email accesses this data, Synapse will automatically filter the data to display only the rows which match with the user email.
For more information on External Tables within Synapse and how to create one, click here.
When paired with an Azure Synapse environment, we are able to utilize RLS technology on an external table.
Configuring Row-Level Security on an External Table
- Once your file is loaded into an external table, open a blank script within Synapse. We need to first set up our credentials for each user within our dedicated SQL pool environment. We follow the CREATE USER command with our AAD principal username and specify the authentication is from an external provider. We need to run these three lines in the master database, as well as our dedicated SQL pool database. You can easily switch and run the script by changing the dropdown, boxed in yellow.
2. Next, we need to create the Security function for our RLS implementation. To do this we will create a function which returns a table, filtering the current logged in username against the Viewer column in the dataset.
3. Now we need to bind the security function above to our external table. To do this we need to create a security policy, set the filter to our security function, and specify our table. We include ‘WITH (STATE = ON)’ to activate the security policy. Lines 25 and 26 represent the way to disable RLS.
4. RLS is now configured. To test the configuration, I ran a couple queries as different users. Notice on the first query, no data is returned. This is because the username I used to execute the query as is not present in the dataset.
5. When we execute the query, and the security function finds a match in our dataset and only displays rows which contained a match.
Configuring Column-Level Security on a View of an External Table
- Natively, external tables do not support Column-Level security. However, we can easily create a view, based on our external table, and then implement Column-Level security on the view itself.
2. After the view is created, we can start granting access to the columns each user is allowed to see.
3. Now when a specific user tries to query a column which they do not have permission to see, they are met with a permissions error. One would expect a simple ‘Select *’ to only return the columns the logged in user has access to, instead we see the same permissions error stating the columns we are not allowed to see.
As an added bonus, since the Row-Level Security is implemented on the external table, our view will inherit the RLS functionality, giving us cell level control!