データベース エンジンの権限の概要

適用対象: はいSQL Server (サポートされているすべてのバージョン) はいAzure SQL データベース はいAzure SQL Managed Instance はいAzure Synapse Analytics はいParallel Data Warehouse

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

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

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

Login

ログインとは、 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 は固定サーバー ロールをサポートしていませんが、マスター データベースにサーバー ロールとして機能する 2 つのロール (dbmanagerloginmanager) があります。

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

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」を参照してください。

標準のシナリオ

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

Active Directory または Azure Active Directory の場合:

  1. 各ユーザーに Windows ユーザーを作成します。

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

ヒント

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

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

  • 管理者が REVOKE SELECT ON OBJECT::OrderStatus TO Sales;を正しく実行すると、Ted には個別の SELECT ステートメントを通じて OrderStatus テーブルに対する GRANT アクセスが保持されます。

  • 管理者が DENY SELECT ON OBJECT::OrderStatus TO Sales; を間違った方法で実行すると、Sales に対する SELECT によって Ted 個人の DENY がオーバーライドされるため、Sales ロールのメンバーである Ted の GRANT権限は拒否されます

注意

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

権限の階層

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

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

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

  • CONTROL 権限には、テーブル Region に対するその他のすべての権限 ( ALTERSELECTINSERTUPDATEDELETE、 and some other permissions.

  • SELECT には、Region テーブルに対する SELECT 権限が含まれます。

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

GRANT SELECT ON OBJECT::Region TO Ted;   
  
GRANT CONTROL ON OBJECT::Region TO Ted;   
  
GRANT SELECT ON SCHEMA::Customers TO Ted;   
  
GRANT CONTROL ON SCHEMA::Customers TO Ted;   
  
GRANT SELECT ON DATABASE::SalesDB TO Ted;   
  
GRANT CONTROL ON DATABASE::SalesDB TO Ted;  

最小限の権限の付与

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

ヒント

データベースとそのオブジェクトを設計するときは、最初から、誰が、またはどのアプリケーションがどのオブジェクトにアクセスするかを計画し、それに基づいてオブジェクト、つまりテーブルだけでなく、ビュー、関数、ストアド プロシージャなどを、できるだけアクセスの種類のバケットに従ってスキーマに配置します。 このアプローチの詳細については、Andreas Wolter によるこのブログ投稿「Schema-design for SQL Server: recommendations for Schema design with security in mind (SQL Server のスキーマ設計: セキュリティを考慮したスキーマ設計の推奨事項)」を参照してください。

アクセス許可の図

次の図は、権限とそれらの関連性を示します。 一部の高いレベルの許可 ( 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. sys.database_role_members ビューを使用して調べることができます。

  • サーバー ロールのメンバーシップは、 sys.server_role_members ビューを使用して調べることができます。 このビューは、 SQL Databaseでは使用できません。

  • 追加のセキュリティ関連のビューについては、「 セキュリティ カタログ ビュー (Transact-SQL) を使用して作成および管理できます。

便利な Transact-SQL ステートメント

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

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

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

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

SELECT sRole.name AS [Server Role Name] , sPrinc.name AS [Members]  
FROM sys.server_role_members AS sRo  
JOIN sys.server_principals AS sPrinc  
    ON sRo.member_principal_id = sPrinc.principal_id  
JOIN sys.server_principals AS sRole  
    ON sRo.role_principal_id = sRole.principal_id;  

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

SELECT dRole.name AS [Database Role Name], dPrinc.name AS [Members]  
FROM sys.database_role_members AS dRo  
JOIN sys.database_principals AS dPrinc  
    ON dRo.member_principal_id = dPrinc.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)
データベース エンジンの有効なアクセス許可の決定