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

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

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

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

セキュリティ プリンシパルとは、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 の場合

1. 一人ひとりにユーザーを作成します。

2. 作業単位と職務を表す Windows グループを作成します。

3. Windows ユーザーを Windows グループに追加します。

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

1. Windows グループのログインを作成します。 (SQL Server 認証を使用している場合は、Active Directory の手順をスキップし、ここで SQL Server 認証ログインを作成します。)

2. ユーザー データベースで、Windows グループを表すログインのデータベース ユーザーを作成します。

3. ユーザー データベースで、それぞれ類似した職務を表すユーザー定義データベース ロールを 1 つ以上作成します。 たとえば、財務アナリストやセールス アナリストなどです。

4. データベース ユーザーを 1 つ以上のユーザー定義データベース ロールに追加します。

5. ユーザー定義データベース ロールに権限を付与します。

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

1. ユーザー データベースで、Windows グループの包含データベース ユーザーを作成します。 (SQL Server 認証を使用する場合は、Active Directory の手順をスキップし、包含データベース ユーザーの SQL Server 認証をここで作成します)。

2. ユーザー データベースで、それぞれ類似した職務を表すユーザー定義データベース ロールを 1 つ以上作成します。 たとえば、財務アナリストやセールス アナリストなどです。

3. データベース ユーザーを 1 つ以上のユーザー定義データベース ロールに追加します。

4. ユーザー定義データベース ロールに権限を付与します。

この時点での一般的な結果としては、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 ステートメントを使用します。 権限は累積的であるため、ユーザーはユーザー、ログイン、すべてのグループ メンバーシップに付与された権限をすべて受け取ります。ただし、権限の拒否はすべての付与をオーバーライドします。

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 権限は拒否されます。

権限の階層

権限には、親子階層があります。 つまり、データベースに 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 形式でダウンロードできます。

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

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

固定サーバー ロールおよび固定データベース ロールの権限は似ていますが、詳細な権限がまったく同じということではありません。 たとえば、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;

関連項目

• SQL Server データベース エンジンと Azure SQL Database のセキュリティ センター
• セキュリティ関数 (Transact-SQL)
• セキュリティ関連の動的管理ビューおよび関数 (Transact-SQL)
• セキュリティ カタログ ビュー (Transact-SQL)
• sys.fn_builtin_permissions (Transact-SQL)
• データベース エンジンの有効なアクセス許可の決定

次のステップ

• チュートリアル:データベース エンジンの概要
• データベースの作成 (チュートリアル)
• チュートリアル:SQL Server Management Studio
• チュートリアル:Transact-SQL ステートメントの作成