Azure SQL with Azure Active Directory authentication - use in security function for row-level security

Question

We have a Azure SQL database using Azure AD authentication. This all works fine for both Member and Guest AD users, and with AD Groups.

We need to apply row-level security to some tables, based on:

whether the user has ownership (or at least CREATE USER permission) over the database, or
based on the AD group

We have created a table-valued function for use in the appropriate security policy. Problem:

how can we establish whether the user has ownership over the database within the function (which has to be defined with schemabinding)? Looking up permissions using sys.fn_my_permissions is refused as sys functions can't be used with schemabinding.
how can we query what AD groups the user is a member of?

Or do we need to maintain tables of the relationships within the database (with attendant multiple maintenance - AD + several databases)?

sch56 · Accepted Answer · 2022-03-17 20:06:22Z

The system function is_member('<AD Group>') can be used to verify whether the current user is a member of an AD group as long as that AD Group has been added to the database as a principal. This function also works for database roles.

So security function like this works:

CREATE FUNCTION [fUserCanAccess](@Group varchar(50))
   RETURNS TABLE
   WITH SCHEMABINDING
AS RETURN (
   SELECT 1 as canAccess WHERE
      is_member('db_owner') = 1
   or
      is_member(@Group) = 1
;

The first is_member tests for a database role that allows access to all rows. The second is_member tests for the user being in the same (AD) Group.

is_member(<AD Group>) returns:

1 if the AD Group is a user in the database and the logged on user is a member of the AD Group (in Active Directory)
0 if the AD Group is a user in the database and the logged on user is not a member of the AD Group (in Active Directory)
NULL if the AD Group does not exist or is not a user in the database

The Group name is NOT case sensitive.

Collectives™ on Stack Overflow

Azure SQL with Azure Active Directory authentication - use in security function for row-level security

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related