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!

Leave a Reply Cancel reply