如何使用Azure应用程序appRoles（在应用程序清单中定义）作为Azure SQL中的角色

Question

我们的团队希望在Azure门户内注册应用程序时使用在应用程序清单内定义的应用程序角色。以下是我们TestApp清单中的一个示例。

"appRoles": [ 
    { "allowedMemberTypes": [ 
     "User", 
     "Application"], 
    "displayName": "My App Role 2", 
    "id": "0993b354-6b2f-471d-bba2-f7467a1bbbf2", 
    "isEnabled": true, 
    "description": "My App Role description for MyAppRole2", 
    "value": "MyAppRole2" }, 
    { "allowedMemberTypes": [ 
     "User" ], 
    "displayName": "My App Role 1", 
    "id": "0993b354-6b2f-471d-bba2-f7467a1baaf2", 
    "isEnabled": true, 
    "description": "My App Role description for MyAppRole1", 
    "value": "MyAppRole1" }] 

这些应用程序角色出现在web应用程序允许开发人员通过如下装饰端点控制访问终端的角色要求。

[Authorize (Roles = "MyAppRole2")] 
public class AdministrationController : Controller 
{ 
} 

通过Azure的门户网站（在企业应用程序），我们可以分配用户和/或组角色的具体应用和一切正常。但是，当试图直接访问数据库（来自SSMS）时，应用程序角色的成员资格不起作用。

CREATE USER [TestApp] FROM EXTERNAL PROVIDER 
CREATE ROLE [MyAppRole2] AUTHORIZATION [TestApp1] 

我们也尝试

CREATE ROLE [My App Role 2] AUTHORIZATION [TestApp1] 

在这两种情况下，检查在数据库级别返回 '0' 假隶属。

SELECT IS_MEMBER('MyAppRole2') [My App Role 2] 

然而，如果我们创建了一个AAD组（例如，“我的组对于TestApp我的应用角色2”），分配通过企业应用程序界面中的“我的应用角色2”角色到组，并指定用户通过AAD用户和组的界面...我们可以通过使用SQL中的组来看到成员到组。

以下内容将创建Azure SQL与AAD中组安全主体之间的关系。

CREATE USER [My Group For TestApp My App Role 2] FROM EXTERNAL PROVIDER 

任何用户登录到SQL直接谁是该组中，将显示为该组的成员。以下内容对于同时也是AAD组成员的经过身份验证的SSMS用户返回true。

SELECT IS_MEMBER('My Group For TestApp My App Role 2') 

尽管如此，如果我们从组中删除应用程序角色（在Azure企业应用程序下），上述查询仍然返回true。这是因为即使该组不再有权访问应用程序角色，该用户仍然是该组的有效成员。因此，此解决方法无效，因为数据库实际上未验证用户是否参与应用程序角色。

我们如何直接将应用程序角色与数据库角色相关联以确保适当的安全性？

Answer 1

根据我的理解，您将Azure AD应用中定义的应用程序角色与Application Roles in SQL Server混合在一起。

如果要使用角色管理Azure AD服务主体。我们可以使用命令下面创建一个数据库角色和分配角色的服务主体创建Azure的AD：

CREATE ROLE customRole2 

alter role customRole2 add member FeiTestApp 
--FeiTestApp is the service principal in Azure AD 
EXECUTE AS USER = 'FeiTestApp'; 

SELECT IS_MEMBER('customRole2') 
REVERT; 

此命令应返回1，然后我们还可以使用访问令牌检查结果如下图所示：

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(); 
builder["Data Source"] = "xxxx.database.windows.net"; // replace with your server name 
builder["Initial Catalog"] = "DBTest"; // replace with your database name 
builder["Connect Timeout"] = 30; 

string authority = "https://login.microsoftonline.com/{0}"; 
string tenantId = "microsoft.onmicrosoft.com"; 
string clientId = ""; 
string secrect = ""; 
string resourceId = "https://database.windows.net/"; 
AuthenticationContext authContext = new AuthenticationContext(string.Format(authority,tenantId)); 
var accessToken= authContext.AcquireTokenAsync(resourceId, new ClientCredential(clientId, secrect)).Result.AccessToken; 

using (SqlConnection connection = new SqlConnection(builder.ConnectionString)) 
{ 
    try 
    { 
     connection.AccessToken = accessToken; 
     connection.Open();   
     SqlCommand cmd = new SqlCommand("SELECT IS_MEMBER('customRole2')", connection); 

     var reader = cmd.ExecuteReader(); 
     while (reader.Read()) 
     { 
      Console.WriteLine($"{reader[0]}");    
     } 
    } 
    catch (Exception ex) 
    { 
     Console.WriteLine(ex.Message); 
    } 
} 

低于

关于Azure的SQL数据库角色管理的更多细节，可以参考文献：

Principals (Database Engine)