Lesson Learned #135: Row Level Security based on Is_RoleMember

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

Today, I worked on a service request that our customer needs to avoid that some users could retrieve some rows depending on a specific role. In this situation, we are going to use Is_RoleMember function

 

For example, we have an Azure Active Directory group called MyAADGroup that contains all the users that we will have access to the data.

 

1) We are going to create the group in Azure SQL Database, running the following query:

 

 

 

CREATE USER [MyAADGroup] FROM EXTERNAL PROVIDER

 

 

 

2) We're going to create the role, for example, auditors that will assign to the group MyAADGroup, running the following command:

 

 

sp_addrolemember 'auditors','MyAADGroup'

 

 

 

3) The next step, would be to create the security function, running the following command:

 

 

CREATE or alter Function [dbo].[fn_securitypredicateOrder] (@UserName sysname) returns table with Schemabinding as return select 1 as [fn_securityPredicateOrder_result] where IS_ROLEMEMBER('auditors')=1

 

 

 

4) Finally, we need to create the security policy running the following command:

 

 

create security Policy fn_securitydata add Filter Predicate dbo.fn_securitypredicateOrder(suser_name()) on MyTableDummy

 

 

 

Enjoy!

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.