アクセス許可の管理のための Azure SQL Database サーバー ロール

注意

この記事の固定サーバー レベルのロールは、Azure SQL Database でパブリック プレビュー段階にあります。 これらのサーバーレベルのロールも、SQL Server 2022 のリリースの一部です。

適用対象: Azure SQL Database

Azure SQL Database では、サーバーは論理的な概念であり、許可をサーバー レベルで付与することはできません。 アクセス許可の管理を簡素化するために、Azure SQL Database では、論理的なサーバー上のアクセス許可の管理に使用でいる、一連の固定サーバーレベルのロールが提供されています。 ロールとは、ログインをグループ化するセキュリティ プリンシパルです。

Note

本記事における "ロール" というコンセプトは、Windows オペレーティング システムの "グループ" に似ています。

通常のユーザーが作成した他のプリンシパルと区別するため、これらの特別な固定サーバーレベルのロールには、プレフィックス ##MS_ とサフィックス が使用されています。

オンプレミスの SQL Server と同様に、サーバーのアクセス許可は階層的に編成されています。 これらのサーバーレベルのロールによって保持されるアクセス許可は、データベースのアクセス許可に反映されることがあります。 アクセス許可がデータベース レベルで効果的に役立つには、ログインがサーバーレベルのロール ##MS_DatabaseConnector## のメンバーである必要があります。このロールでは、すべてのデータベースに CONNECT を付与するか、個々のデータベースにユーザー アカウントを持っている必要があります。 これは、仮想 master データベースにも当てはまります。

たとえば、サーバーレベルのロール ##MS_ServerStateReader## に、VIEW SERVER STATE という権限が付与されているとします。 このロールのメンバーであるログイン ユーザーが、データベース masterWideWorldImporters のユーザー アカウントを持っている場合、このユーザーはこれら 2 つのデータベースに対する VIEW DATABASE STATE というアクセス許可を持つことになります。

Note

ユーザー データベース内では、どのようなアクセス許可でも拒否することができ、その場合は、ロール メンバーシップによってサーバー全体の許可を上書きすることができます。 ただし、システム データベース master で、アクセス許可を許可したり、拒否することはできません。

Azure SQL Database では、現在、7 つの固定サーバー ロールが提供されています。 固定サーバー ロールに付与されるアクセス許可は変更できません。また、これらのロールに他の固定ロールをメンバーとして含めることはできません。 サーバーレベルのログインを、サーバーレベルのロールのメンバーとして追加することができます。

重要

固定サーバー ロールの各メンバーは、そのロールに他のログインを追加することができます。

Azure SQL Database のログインとユーザーについては、「SQL Database、SQL Managed Instance、Azure Synapse Analytics へのデータベース アクセスを承認する」を参照してください。

固定サーバー レベル ロール

次の表に、固定サーバー レベル ロールとその機能を示します。

固定サーバー レベル ロール 説明
##MS_DatabaseConnector## ##MS_DatabaseConnector## 固定サーバー ロールのメンバーは、データベース内のユーザーアカウントの接続先を必要とせずに、任意のデータベースに接続できます。

特定のデータベースに対する CONNECT アクセス許可を拒否するには、ユーザーはこのログインに対応するユーザー アカウントをデータベースに作成し、このデータベースユーザーに対する CONNECT アクセス許可を拒否 (DENY) します。 この DENY アクセス許可によって、このロールからの GRANT CONNECT アクセス許可が無効になります。
##MS_DatabaseManager## ##MS_DatabaseManager## 固定サーバー ロールのメンバーは、データベースを作成および削除できます。 データベースを作成する ##MS_DatabaseManager## ロールのメンバーは、そのデータベースの所有者になります。これにより、ユーザーは dbo ユーザーとしてそのデータベースに接続できるようになります。 dbo ユーザーには、データベースでのすべてのデータベース権限があります。 ##MS_DatabaseManager## ロールのメンバーには、所有していないデータベースへのアクセス権が必ずしもあるとは限りません。 このサーバー ロールは、master に存在する dbmanager データベース レベルのロールに対して使用することをお勧めします。
##MS_DefinitionReader## 固定サーバー ロール ##MS_DefinitionReader## のメンバーは、このロールのメンバーがユーザー アカウントを持っているすべてのデータベース上の VIEW ANY DEFINITION、またはそれぞれの VIEW DEFINITION でカバーされているすべてのカタログ ビューを読み取ることができます。
##MS_LoginManager## ##MS_LoginManager## 固定サーバー ロールのメンバーは、ログインを作成および削除できます。 このサーバー ロールは、master に存在する loginmanager データベース レベルのロールに対して使用することをお勧めします。
##MS_SecurityDefinitionReader## 固定サーバー ロール ##MS_SecurityDefinitionReader## のメンバーは、このロールのメンバーがユーザー アカウントを持っているすべてのデータベース上の VIEW ANY SECURITY DEFINITION、およびそれぞれの VIEW SECURITY DEFINITION アクセス許可でカバーされているすべてのカタログ ビューを読み取ることができます。 これは、##MS_DefinitionReader## サーバー ロールがアクセスできる小さなサブセットです。
##MS_ServerStateReader## 固定サーバー ロール ##MS_ServerStateReader## のメンバーは、このロールのメンバーがユーザー アカウントを持っているすべてのデータベース上の VIEW SERVER STATE、またはそれぞれの VIEW DATABASE STATE でカバーされているすべての動的管理ビュー (DMV) および機能を読み取ることができます。
##MS_ServerStateManager## 固定サーバー ロール ##MS_ServerStateManager## のメンバーは、 ##MS_ServerStateReader## ロールと同じアクセス許可を持っています。 また、DBCC FREESYSTEMCACHE ('ALL')DBCC SQLPERF() などの複数の管理操作へのアクセス許可を付与する ALTER SERVER STATE も保持します

固定サーバー ロールのアクセス許可

固定サーバー レベルのロールごとに、特定の権限が割り当てられます。 次の表は、サーバーレベルのロールに割り当てられているアクセス許可を示しています。 また、ユーザーが個々のデータベースに接続できれば継承されるデータベースレベルのアクセス許可も表示されます。

固定サーバー レベル ロール サーバーレベルのアクセス許可 データベースレベルの権限 (ログインに一致するデータベース ユーザーが存在する場合)
##MS_DatabaseConnector## CONNECT ANY DATABASE CONNECT
##MS_DatabaseManager## CREATE ANY DATABASE
ALTER ANY DATABASE
ALTER
##MS_DefinitionReader## VIEW ANY DATABASE、VIEW ANY DEFINITION、VIEW ANY SECURITY DEFINITION VIEW DEFINITION、VIEW SECURITY DEFINITION
##MS_LoginManager## CREATE LOGIN
ALTER ANY LOGIN
該当なし
##MS_SecurityDefinitionReader## VIEW ANY SECURITY DEFINITION VIEW SECURITY DEFINITION
##MS_ServerStateReader## VIEW SERVER STATE、VIEW SERVER PERFORMANCE STATE、VIEW SERVER SECURITY STATE VIEW DATABASE STATE、VIEW DATABASE PERFORMANCE STATE、VIEW DATABASE SECURITY STATE
##MS_ServerStateManager## ALTER SERVER STATE、VIEW SERVER STATE、VIEW SERVER PERFORMANCE STATE、VIEW SERVER SECURITY STATE VIEW DATABASE STATE、VIEW DATABASE PERFORMANCE STATE、VIEW DATABASE SECURITY STATE

サーバーレベルのロールの操作

次の表では、Azure SQL Database でサーバー レベルのロールを操作するためのシステム、ビュー、および関数について説明します。

機能 Type 説明
IS_SRVROLEMEMBER (Transact-SQL) Metadata SQL ログインが、指定されたサーバー レベルのロールのメンバーであるかどうかを示します。
sys.server_role_members (Transact-SQL) Metadata 各サーバー レベルのロールのメンバーごとに 1 行のデータを返します。
sys.sql_logins (Transact-SQL) Metadata SQL ログインごとに 1 行のデータを返します。
ALTER SERVER ROLE (Transact-SQL) コマンド サーバー ロールのメンバーシップを変更します。

このセクションの例では、Azure SQL Database のサーバーレベルのロールを使用する方法が示されています。

A. SQL ログインをサーバーレベルのロールに追加する

次の例では、SQL ログイン 'Jiao' をサーバーレベルのロール ##MS_ServerStateReader## に追加します。 このステートメントは、仮想 master データベースで実行する必要があります。

ALTER SERVER ROLE ##MS_ServerStateReader##
	ADD MEMBER Jiao;  
GO

B. サーバーレベルのロールのメンバーであるプリンシパル (SQL 認証) の一覧を表示する

次のステートメントでは、カタログ ビュー sys.server_role_memberssys.sql_logins を使用して、任意の固定サーバーレベルのロールのすべてのメンバーが返されます。 このステートメントは、仮想 master データベースで実行する必要があります。

SELECT
		sql_logins.principal_id			AS MemberPrincipalID
	,	sql_logins.name					AS MemberPrincipalName
	,	roles.principal_id				AS RolePrincipalID
	,	roles.name						AS RolePrincipalName
FROM sys.server_role_members AS server_role_members
INNER JOIN sys.server_principals AS roles
    ON server_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.sql_logins AS sql_logins 
    ON server_role_members.member_principal_id = sql_logins.principal_id
;  
GO  

C. 完全な例: サーバーレベルのロールへログインを追加し、ロール メンバーシップとアクセス許可のメタデータを取得し、テスト クエリを実行する

パート 1: ロール メンバーシップとユーザー アカウントの準備

仮想 master データベースからこのコマンドを実行します。

ALTER SERVER ROLE ##MS_ServerStateReader##
	ADD MEMBER Jiao

-- check membership in metadata:
select IS_SRVROLEMEMBER('##MS_ServerStateReader##', 'Jiao')
--> 1 = Yes

SELECT
		sql_logins.principal_id			AS MemberPrincipalID
	,	sql_logins.name					AS MemberPrincipalName
	,	roles.principal_id				AS RolePrincipalID
	,	roles.name						AS RolePrincipalName
FROM sys.server_role_members AS server_role_members
INNER JOIN sys.server_principals AS roles
    ON server_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.sql_logins AS sql_logins 
    ON server_role_members.member_principal_id = sql_logins.principal_id
;   
GO  

結果セットは次のとおりです。

MemberPrincipalID MemberPrincipalName RolePrincipalID RolePrincipalName        
------------- ------------- ------------------ -----------   
6         Jiao      11            ##MS_ServerStateReader##   

ユーザー データベースからこのコマンドを実行します。

-- Creating a database-User for 'Jiao'
CREATE USER Jiao
	FROM LOGIN Jiao
;   
GO  

パート 2: ロール メンバーシップのテスト

ログイン Jiao としてログインし、この例で使用されているユーザー データベースに接続します。

-- retrieve server-level permissions of currently logged on User
SELECT * FROM sys.fn_my_permissions(NULL, 'Server')
;  

-- check server-role membership for `##MS_ServerStateReader##` of currently logged on User
SELECT USER_NAME(), IS_SRVROLEMEMBER('##MS_ServerStateReader##')
--> 1 = Yes

-- Does the currently logged in User have the `VIEW DATABASE STATE`-permission?
SELECT HAS_PERMS_BY_NAME(NULL, 'DATABASE', 'VIEW DATABASE STATE'); 
--> 1 = Yes

-- retrieve database-level permissions of currently logged on User
SELECT * FROM sys.fn_my_permissions(NULL, 'DATABASE')
GO 

-- example query:
SELECT * FROM sys.dm_exec_query_stats
--> will return data since this user has the necessary permission

D. Azure AD ログインのサーバーレベルのロールを確認する

仮想 master データベースで次のコマンドを実行して、SQL Database のサーバー レベルのロールの一部であるすべての Azure AD ログインが表示されます。 Azure AD サーバー ログイン の詳細については、「Azure Active Directory サーバー プリンシパル」参照してください。

SELECT roles.principal_id AS RolePID,roles.name AS RolePName,
       server_role_members.member_principal_id AS MemberPID, members.name AS MemberPName
       FROM sys.server_role_members AS server_role_members
       INNER JOIN sys.server_principals AS roles
       ON server_role_members.role_principal_id = roles.principal_id
       INNER JOIN sys.server_principals AS members 
       ON server_role_members.member_principal_id = members.principal_id;

E. 特定のログインのための仮想マスター データベース ロールを確認する

仮想 master データベースで次のコマンドを実行し、bob が持っているロールを確認するか、または、その値を自分のプリンシパルに合わせて値を変更します。

SELECT DR1.name AS DbRoleName, isnull (DR2.name, 'No members')  AS DbUserName
   FROM sys.database_role_members AS DbRMem RIGHT OUTER JOIN sys.database_principals AS DR1
    ON DbRMem.role_principal_id = DR1.principal_id LEFT OUTER JOIN sys.database_principals AS DR2
     ON DbRMem.member_principal_id = DR2.principal_id
      WHERE DR1.type = 'R' and DR2.name like 'bob%'

サーバーレベルのロールの制限事項

  • ロールの割り当てが有効になるまでに、最大で 5 分かかる場合があります。 また、既存のセッションの場合、サーバー ロールの割り当てを変更しても、接続を閉じて再度開くまでは有効になりません。 これは、"master" データベースと、同じ論理サーバー上の他のデータベースとの間の分散アーキテクチャが原因です。

    • 部分的な回避策: 待ち時間を短縮し、データベース内のサーバー ロールの割り当てが最新であることを確認するには、サーバー管理者または Azure AD 管理者のアクセス許可を使用し、ログインがアクセスできるユーザー データベースで DBCC FLUSHAUTHCACHE を実行します。 現在ログオンしているユーザーは、メンバーシップの変更を有効にするために、DBCC FLUSHAUTHCACHE の実行後に再接続する必要があります。
  • IS_SRVROLEMEMBER() は、master データベースではサポートされていません。

関連項目