データベースエンジンのアクセス許可の概要

[アーティクル]
10/07/2023

適用対象: SQL Server Azure SQL データベース Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

データベースエンジンの権限は、サーバーレベルではログインとサーバーロール、データベースレベルではデータベースユーザーとデータベースロールを通じて管理されます。 SQL Database のモデルは、各データベース内の同じシステムを公開しますが、サーバーレベルのアクセス許可は使用できません。この記事では、セキュリティの基本的な概念についていくつか確認し、アクセス許可の一般的な実装について説明します。

Note

Microsoft Entra ID は以前に Azure Active Directory(Azure AD)として知られていました。

セキュリティプリンシパル

セキュリティプリンシパルとは、SQL Server を使用する ID の正式名であり、アクションを実行するように権限を割り当てることができます。基本的にはユーザーまたはユーザーのグループですが、ユーザーとして扱われるエンティティでもかまいません。セキュリティプリンシパルは、リストされている Transact-SQL を使用するか、SQL Server Management Studio を使用して作成および管理できます。

ログインとは、SQL Server データベースエンジンにログオンするための個々のユーザーアカウントです。 SQL Server と SQL Database では、Windows 認証に基づくログインと、SQL Server 認証に基づくログインがサポートされます。 2 種類のログインの詳細については、「 Choose an Authentication Mode」を参照してください。

固定サーバーロール

SQL Server では、固定サーバーロールは構成済みの一連ロールで、サーバーレベルの権限の便利なグループを提供します。ロールには ALTER SERVER ROLE ... ADD MEMBER ステートメントを使用してログインを追加できます。詳細については、「ALTER SERVER ROLE (Transact-SQL)」を参照してください。 SQL Database は固定サーバーロールをサポートしていませんが、loginmanager データベースにサーバーロールとして機能する 2 つのロール (master および dbmanager) があります。

ユーザー定義サーバーロール

SQL Server では、独自のサーバーロールを作成して、サーバーレベルの権限を割り当てることができます。サーバーロールには ALTER SERVER ROLE ... ADD MEMBER ステートメントを使用してログインを追加できます。詳細については、「ALTER SERVER ROLE (Transact-SQL)」を参照してください。 SQL Database はユーザー定義サーバーロールをサポートしていません。

データベースユーザー

データベースにデータベースユーザーを作成してそのデータベースユーザーをログインにマッピングすることで、サインインにデータベースへのアクセスが付与されます。通常、データベースユーザー名はログイン名と同じですが、同じにする必要はありません。各データベースユーザーは、単一のログインにマッピングされます。ログインはデータベース内の 1 つのユーザーにのみマッピングできますが、異なる複数のデータベースにデータベースユーザーとしてマッピングできます。

対応するログインがないデータベースユーザーも作成できます。これらのユーザーは、包含データベースユーザーと呼ばれます。 Microsoft では包含データベースユーザーの使用をお勧めしています。ログインと同様に、包含データベースユーザーは Windows 認証または SQL Server 認証のいずれかを使用できます。詳細については、「包含データベースユーザー - データベースの可搬性を確保する」を参照してください。

12 種類のユーザーはそれぞれ認証方法がわずかに異なり、それぞれ何を代表するかも異なります。ユーザーの一覧は「CREATE USER (Transact-SQL)」で確認してください。

固定データベースロール

固定データベースロールは構成済みの一連のロールで、データベースレベルの権限の便利なグループを提供します。固定データベースロールには、ALTER ROLE ... ADD MEMBER ステートメントを使用してデータベースユーザーとユーザー定義データベースロールを追加できます。詳細については、「ALTER ROLE (Transact-SQL)」を参照してください。

ユーザー定義データベースロール

CREATE ROLE の権限を持つユーザーは、一般的な権限を持つユーザーのグループを代表する、新しいユーザー定義データベースロールを作成できます。通常、権限の管理と監視を簡略化するために、権限はロール全体に対して付与または拒否されます。データベースロールには、 ALTER ROLE ... ADD MEMBER ステートメントを使用してデータベースユーザーを追加できます。詳細については、「ALTER ROLE (Transact-SQL)」を参照してください。

その他のプリンシパル

ここで取り上げていないその他のセキュリティポリシーには、アプリケーションロールのほか、証明書や非対称キーに基づくログインやユーザーなどがあります。

Windows ユーザー、Windows グループ、ログイン、データベースユーザー間の関係を示す図については、「 Create a Database User」を参照してください。

典型的なシナリオ

次の例は、権限を構成する一般的な方法および推奨される方法を示します。

Windows Active Directory または Microsoft Entra ID の場合

一人ひとりにユーザーを作成します。
作業単位と職務を表す Windows グループを作成します。
Windows ユーザーを Windows グループに追加します。

接続するユーザーが多数のデータベースに接続する場合

Windows グループのログインを作成します。 (SQL Server 認証を使用している場合は、Active Directory の手順をスキップし、ここで SQL Server 認証ログインを作成します。)
ユーザーデータベースで、Windows グループを表すログインのデータベースユーザーを作成します。
ユーザーデータベースで、それぞれ類似した職務を表すユーザー定義データベースロールを 1 つ以上作成します。たとえば、財務アナリストやセールスアナリストなどです。
データベースユーザーを 1 つ以上のユーザー定義データベースロールに追加します。
ユーザー定義データベースロールに権限を付与します。

接続するユーザーが 1 つのデータベースにのみ接続する場合

ユーザーデータベースで、Windows グループの包含データベースユーザーを作成します。 (SQL Server 認証を使用する場合は、Active Directory の手順をスキップし、包含データベースユーザーの SQL Server 認証をここで作成します)。
ユーザーデータベースで、それぞれ類似した職務を表すユーザー定義データベースロールを 1 つ以上作成します。たとえば、財務アナリストやセールスアナリストなどです。
データベースユーザーを 1 つ以上のユーザー定義データベースロールに追加します。
ユーザー定義データベースロールに権限を付与します。

この時点での一般的な結果としては、Windows ユーザーは Windows グループのメンバーです。 Windows グループには、SQL Server または SQL Database へのログインがあります。ログインは、ユーザーデータベース内のユーザー ID にマップされます。ユーザーはデータベースロールのメンバーです。次に、ロールに権限を追加する必要があります。

アクセス許可の割り当て

権限のほとんどのステートメントには型が存在します。

AUTHORIZATION PERMISSION ON SECURABLE::NAME TO PRINCIPAL;

AUTHORIZATION は、 GRANT型、 REVOKE 型、または DENY型のいずれかである必要があります。
PERMISSION は許可または禁止されるアクションを確立します。アクセス許可の正確な数は、SQL Server と SQL Database で異なります。アクセス許可は、「アクセス許可 (データベースエンジン)」の記事と以下で参照される表にリストされています。
ON SECURABLE::NAME は、セキュリティ保護可能な型 (サーバー、サーバーオブジェクト、データベース、データベースオブジェクト) とその名前です。一部の権限は、不明瞭でありコンテキストで不適切であるため、ON SECURABLE::NAME を必要としません。たとえば、CREATE TABLE アクセス許可には ON SECURABLE::NAME 句 (GRANT CREATE TABLE TO Mary; が Mary にテーブルの作成を許可する) は必要ありません。
PRINCIPAL は権限を受け取るまたは失うセキュリティプリンシパル (ログイン、ユーザー、ロール) です。ロールに権限を付与できるタイミングで付与します。

次の例の GRANT ステートメントでは、 UPDATE スキーマに含まれている Parts テーブルまたはビュー上の Production 権限を PartsTeamという名前のロールに付与します。

GRANT UPDATE ON OBJECT::Production.Parts TO PartsTeam;

次の grant ステートメントの例では、UPDATE のアクセス許可を Production スキーマに付与します。また、拡張によって、このスキーマに含まれるすべてのテーブルまたはビューに対するアクセス許可を ProductionTeam という名前のロールに付与しています。これは、個々のオブジェクトレベルでアクセス許可を付与するよりも効果的で手ごろなアプローチです。

GRANT UPDATE ON SCHEMA::Production TO ProductionTeam;

権限をセキュリティプリンシパル (ログイン、ユーザー、ロール) に付与するには、 GRANT ステートメント使用します。権限を明示的に拒否するには、DENY コマンドを使用します。以前に付与または拒否された権限を削除するには、 REVOKE ステートメントを使用します。権限は累積的であるため、ユーザーはユーザー、ログイン、すべてのグループメンバーシップに付与された権限をすべて受け取ります。ただし、権限の拒否はすべての付与をオーバーライドします。

ヒント

よくある間違いは、 GRANT の代わりに DENY を使用して REVOKEの削除を試行することです。これにより、ユーザーが複数のソースから権限を受け取る際に問題が発生することがあります。次の例は、このプリンシパルを示しています。

Sales グループは、 SELECT ステートメントを通じて OrderStatus テーブル上の GRANT SELECT ON OBJECT::OrderStatus TO Sales;権限を受け取ります。ユーザー Jae は、Sales ロールのメンバーです。 SELECT ステートメントを通じて、Jae にも OrderStatus テーブルに対する GRANT SELECT ON OBJECT::OrderStatus TO Jae; 権限が自身の名前の下に付与されます。管理者が Sales ロールに対する GRANT を削除するとします。

管理者が REVOKE SELECT ON OBJECT::OrderStatus TO Sales;を正しく実行すると、Jae には個別の SELECT ステートメントを通じて OrderStatus テーブルに対する GRANT アクセスが保持されます。
管理者が DENY SELECT ON OBJECT::OrderStatus TO Sales; を間違った方法で実行すると、Sales に対する SELECT によって Jae 個人の DENY がオーバーライドされるため、Sales ロールのメンバーである Jae の GRANT 権限は拒否されます。

Note

権限は Management Studio を使用して構成できます。オブジェクトエクスプローラーでセキュリティ保護可能なリソースを探し、セキュリティ保護可能なリソース名を右クリックして、[プロパティ]を選択します。 [権限] ページを選択します。権限ページの使用に関するヘルプについては、「 Permissions or Securables Page」を参照してください。

権限の階層

権限には、親子階層があります。つまり、データベースに SELECT 権限を付与すると、その権限にデータベース内のすべての (子) スキーマの SELECT 権限が含まれます。スキーマに SELECT 権限を付与すると、その権限にスキーマ内のすべての (子) テーブルとビューの SELECT 権限が含まれます。権限は推移的です。つまり、 SELECT 権限をデータベースに付与すると、その権限にすべての (子) スキーマとすべての (孫) テーブルとビューの SELECT 権限が含まれます。

権限には、包含権限もあります。オブジェクトに対する CONTROL 権限は通常、そのオブジェクトに対するその他すべての権限を提供します。

同じ権限に親子階層と包含階層の両方が存在することがあるため、権限のシステムは複雑になります。たとえば、データベース (SalesDB) 内のスキーマ (Customers) にあるテーブル (Region) を見てみましょう。

CONTROL 権限には、テーブル Region に対するその他のすべての権限 ( ALTER、 SELECT、 INSERT、 UPDATE、 DELETE、 and some other permissions.
SELECT には、Region テーブルに対する SELECT 権限が含まれます。

したがって、Region テーブルに対する SELECT 権限は、次の 6 つのステートメントを通じて取得できます。

GRANT SELECT ON OBJECT::Region TO Jae;

GRANT CONTROL ON OBJECT::Region TO Jae;

GRANT SELECT ON SCHEMA::Customers TO Jae;

GRANT CONTROL ON SCHEMA::Customers TO Jae;

GRANT SELECT ON DATABASE::SalesDB TO Jae;

GRANT CONTROL ON DATABASE::SalesDB TO Jae;

最小権限の付与

上に示した最初の権限 (GRANT SELECT ON OBJECT::Region TO Jae;) が最も詳細であり、 SELECTを付与する最小のステートメントです。下位のオブジェクトに対する権限はありません。常に可能な限り最小限のアクセス許可を付与することは良い原則ですが (最小特権の原則の詳細についてはこちらを参照してください)、同時に (それとは矛盾しますが) より高いレベルで付与して、付与システムを簡略化するようにしてください。このため、Jae がスキーマ全体への権限を必要とする場合、SELECT をテーブルまたはビューレベルで複数回付与するのではなく、SELECT をスキーマレベルで 1 回付与します。データベースの設計は、この戦略の成功に大きく影響する可能性があります。一意の権限を必要とするオブジェクトが単一のスキーマに含まれるようにデータベースを設計する際には、この戦略が最適です。

ヒント

データベースとそのオブジェクトを設計するときは、最初から、誰が、またはどのアプリケーションがどのオブジェクトにアクセスするかを計画し、それに基づいてオブジェクト、つまりテーブルだけでなく、ビュー、関数、ストアドプロシージャなどを、できるだけアクセスの種類のバケットに従ってスキーマに配置します。

アクセス許可のダイアグラム

次の画像は、アクセス許可とそれらの関連性を示します。一部の高いレベルの許可 ( CONTROL SERVERなど) は複数回列挙されています。この記事のポスターは、読み取るには小さすぎます。画像をクリックすると、フルサイズのデータベースエンジンのアクセス許可ポスターを PDF 形式でダウンロードできます。

データベースエンジンアクセス許可 PDF のスクリーンショット。

データベースエンジンプリンシパルとサーバーおよびデータベースオブジェクト間の関係を示す図については、「アクセス許可の階層 (データベースエンジン)」を参照してください。

アクセス許可対固定サーバーと固定データベースロール

固定サーバーロールおよび固定データベースロールの権限は似ていますが、詳細な権限がまったく同じということではありません。たとえば、sysadmin 固定サーバーロールのメンバーには SQL Server のインスタンスのすべての権限があり、CONTROL SERVER 権限のログインも同様です。ただし、CONTROL SERVER アクセス許可を付与してもログインは sysadmin 固定サーバーロールのメンバーにはならず、sysadmin 固定サーバーロールにログインを追加しても、ログインに CONTROL SERVER アクセス許可は明示的には付与されません。ストアドプロシージャは、詳細な権限は確認せずに固定ロールを確認することがあります。たとえば、データベースをデタッチするには、db_owner 固定データベースロールのメンバーシップが必要です。同じ CONTROL DATABASE アクセス許可では不十分です。これらの 2 つのシステムは並行して運用されますが、互いに干渉することはほとんどありません。マイクロソフトでは、可能な限り固定ロールではなくより新しい詳細な権限システムを使用することをお勧めしています。

アクセス許可の監視

次のビューは、セキュリティ情報を返します。

サーバー上のログインとユーザー定義サーバーロールは、 sys.server_principals ビューを使用して調べることができます。 SQL Database では、このビューは使用できません。
データベース上のユーザーとユーザー定義ロールは、 sys.database_principals ビューを使用して調べることができます。
ログインやユーザー定義固定サーバーロールに付与された権限は、 sys.server_permissions ビューを使用して調べることができます。 SQL Database では、このビューは使用できません。
ユーザーやユーザー定義固定データベースロールに付与された権限は、 sys.database_permissions ビューを使用して調べることができます。
データベースロールのメンバーシップは、 sys.database_role_members ビューを使用して調べることができます。
サーバーロールのメンバーシップは、 sys.server_role_members ビューを使用して調べることができます。 SQL Database では、このビューは使用できません。
追加のセキュリティ関連のビューについては、セキュリティカタログビュー (Transact-SQL) を使用して作成および管理できます。

例

次のステートメントでは、権限に関する有用な情報を返します。

A. 各ユーザーのデータベース権限の一覧

データベースで明示的に許可または拒否された権限を返す (SQL Server と SQL Database) には、データベースで次のステートメントを実行します。

SELECT
    perms.state_desc AS State,
    permission_name AS [Permission],
    obj.name AS [on Object],
    dp.name AS [to User Name]
FROM sys.database_permissions AS perms
JOIN sys.database_principals AS dp
    ON perms.grantee_principal_id = dp.principal_id
JOIN sys.objects AS obj
    ON perms.major_id = obj.object_id;

B. サーバーロールメンバーの一覧

サーバーロールのメンバーを返す (SQL Server のみ) には、次のステートメントを実行します。

SELECT roles.principal_id AS RolePrincipalID,
    roles.name AS RolePrincipalName,
    server_role_members.member_principal_id AS MemberPrincipalID,
    members.name AS MemberPrincipalName
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
LEFT JOIN sys.server_principals AS members
    ON server_role_members.member_principal_id = members.principal_id;

C: データベースレベルのロールのメンバーであるすべてのデータベースプリンシパルを一覧表示する

データベースロールのメンバーを返す (SQL Server と SQL Database) には、データベースで次のステートメントを実行します。

SELECT dRole.name AS [Database Role Name], dp.name AS [Members]
FROM sys.database_role_members AS dRo
JOIN sys.database_principals AS dp
    ON dRo.member_principal_id = dp.principal_id
JOIN sys.database_principals AS dRole
    ON dRo.role_principal_id = dRole.principal_id;

次の方法で共有

データベースエンジンのアクセス許可の概要

セキュリティプリンシパル

ログイン

固定サーバーロール

ユーザー定義サーバーロール

データベースユーザー

固定データベースロール

ユーザー定義データベースロール

その他のプリンシパル

典型的なシナリオ

Windows Active Directory または Microsoft Entra ID の場合

接続するユーザーが多数のデータベースに接続する場合

接続するユーザーが 1 つのデータベースにのみ接続する場合

アクセス許可の割り当て

権限の階層

最小権限の付与

アクセス許可のダイアグラム

アクセス許可対固定サーバーと固定データベースロール

アクセス許可の監視

例

A. 各ユーザーのデータベース権限の一覧

B. サーバーロールメンバーの一覧

C: データベースレベルのロールのメンバーであるすべてのデータベースプリンシパルを一覧表示する

関連項目

次のステップ

フィードバック

その他のリソース

次の方法で共有

データベース エンジンのアクセス許可の概要

セキュリティ プリンシパル

ログイン

固定サーバー ロール

ユーザー定義サーバー ロール

データベース ユーザー

固定データベース ロール

ユーザー定義データベース ロール

その他のプリンシパル

典型的なシナリオ

Windows Active Directory または Microsoft Entra ID の場合

接続するユーザーが多数のデータベースに接続する場合

接続するユーザーが 1 つのデータベースにのみ接続する場合

アクセス許可の割り当て

権限の階層

最小権限の付与

アクセス許可のダイアグラム

アクセス許可対固定サーバーと固定データベース ロール

アクセス許可の監視

例

A. 各ユーザーのデータベース権限の一覧

B. サーバーロール メンバーの一覧

C: データベース レベルのロールのメンバーであるすべてのデータベース プリンシパルを一覧表示する

関連項目

次のステップ

フィードバック

その他のリソース

データベースエンジンのアクセス許可の概要

セキュリティプリンシパル

固定サーバーロール

ユーザー定義サーバーロール

データベースユーザー

固定データベースロール

ユーザー定義データベースロール

アクセス許可対固定サーバーと固定データベースロール

B. サーバーロールメンバーの一覧

C: データベースレベルのロールのメンバーであるすべてのデータベースプリンシパルを一覧表示する