適用対象:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Microsoft Fabric の SQL データベース
この記事では、いくつかの基本的なセキュリティの概念を確認してから、アクセス許可の一般的な実装について説明します。 データベース エンジンの権限は、ログインとサーバー ロールを通じて サーバー レベル で管理され、 データベース レベルではデータベース ユーザーとデータベース ロールによって管理されます。
Microsoft Fabric の SQL Database と SQL データベースでは、各データベース内で同じオプションが提供されますが、サーバー レベルのアクセス許可は使用できません。
SQL Database では、「 チュートリアル: Azure SQL Database でデータベースをセキュリティで保護する」を参照してください。 Microsoft Entra ID 認証をお勧めします。 詳細については、「チュートリアル: Microsoft Entra アプリケーションを使用して Microsoft Entra ユーザーを作成する」を参照してください。
Microsoft Fabric の SQL データベースでは、データベース ユーザーに対してサポートされている認証方法は Microsoft Entra ID のみです。 サーバーレベルのロールと権限は利用できません。 詳細については、Microsoft Fabric の SQL Database における認可に関する記事を参照してください。
Note
Microsoft Entra ID は、旧称、Azure Active Directory (Azure AD) の製品です。
セキュリティ プリンシパル
セキュリティ プリンシパルは、SQL Server が使用する ID であり、アクションを実行するためのアクセス許可を割り当てることができます。 セキュリティ プリンシパルは通常、ユーザーまたはユーザーのグループですが、ユーザーのふりをする他のエンティティである可能性があります。 セキュリティ プリンシパルは、この記事に示す Transact-SQL の例を使用するか、SQL Server Management Studio を使用して作成および管理できます。
Login
ログイン は、SQL Server データベース エンジンにサインインするための個々のユーザー アカウントです。 SQL Server と SQL Database では、Windows 認証に基づくログインと、SQL Server 認証に基づくログインがサポートされます。 2 種類のログインの詳細については、「 認証モードの選択」を参照してください。
固定サーバー ロール
SQL Server では、 固定サーバー ロール は、サーバー レベルのアクセス許可の便利なグループを提供する構成済みロールのセットです。 ロールには ALTER SERVER ROLE ... ADD MEMBER ステートメントを使用してログインを追加できます。 詳細については、「 ALTER SERVER ROLE」を参照してください。 SQL Database は固定サーバー ロールをサポートしていませんが、 master データベース (dbmanager と loginmanager) には、サーバー ロールと同様に動作する 2 つのロールがあります。
ユーザー定義サーバー ロール
SQL Server では、独自の サーバー ロール を作成し、サーバー レベルのアクセス許可を割り当てることができます。 サーバー ロールには ALTER SERVER ROLE ... ADD MEMBER ステートメントを使用してログインを追加できます。 詳細については、「 ALTER SERVER ROLE」を参照してください。 SQL Database はユーザー定義サーバー ロールをサポートしていません。
データベース ユーザー
データベースへのログインのアクセス権を付与するには、そのデータベースに データベース ユーザー を作成し、データベース ユーザーをログインにマップします。 通常、データベース ユーザー名は慣例によりログイン名と同じですが、同じである必要はありません。 各データベース ユーザーは、単一のログインにマッピングされます。 ログインはデータベース内の 1 人のユーザーにのみマップできますが、複数の異なるデータベースのデータベース ユーザーとしてマップできます。
対応するログインがないデータベース ユーザーも作成できます。 これらのユーザーは、包含データベース ユーザーと呼ばれます。 Microsoft では、包含データベース ユーザーの使用を推奨しています。これは、データベースを別のサーバーに簡単に移動できるためです。 ログインと同様に、包含データベース ユーザーは Windows 認証または SQL Server 認証のいずれかを使用できます。 詳細については、コンテインメント データベースの移植性の確保に関する記事を参照してください。
12 種類のユーザーはそれぞれ認証方法がわずかに異なり、それぞれ何を代表するかも異なります。 ユーザーの一覧を表示するには、「 CREATE USER」を参照してください。
固定データベース ロール
固定データベース ロール は、データベース レベルのアクセス許可の便利なグループを提供する構成済みロールのセットです。 固定データベース ロールには、ALTER ROLE ... ADD MEMBER ステートメントを使用してデータベース ユーザーとユーザー定義データベース ロールを追加できます。 詳細については、 ALTER ROLE を参照してください。
ユーザー定義データベース ロール
CREATE ROLE権限を持つユーザーは、共通のアクセス許可を持つユーザーのグループを表す新しいユーザー定義データベース ロールを作成できます。 通常、権限の管理と監視を簡略化するために、権限はロール全体に対して付与または拒否されます。 データベース ロールには、 ALTER ROLE ... ADD MEMBER ステートメントを使用してデータベース ユーザーを追加できます。 詳細については、 ALTER ROLE を参照してください。
その他のプリンシパル
ここで説明しないその他のセキュリティ プリンシパルには、 アプリケーション ロール、および証明書または非対称キーに基づくログインとユーザーが含まれます。
Windows ユーザー、Windows グループ、ログイン、およびデータベース ユーザー間の関係を示す図については、「 データベース ユーザーの作成」を参照してください。
典型的なシナリオ
次の例は、権限を構成する一般的な方法および推奨される方法を示します。
Windows Active Directory または Microsoft Entra ID の場合
- 各ユーザーに 1 人のユーザーを作成します。
- 作業単位と職務を表す 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 と Azure SQL Database によって異なります。 権限の詳細については、「 権限 (データベース エンジン)」を参照し、この記事で後述するグラフを参照してください。ON <securable>::<name>は、セキュリティ保護可能な型 (サーバー、サーバー オブジェクト、データベース、データベース オブジェクト) とその名前です。 一部のアクセス許可は、コンテキストで明確または不適切であるため、<securable>::<name>を必要としません。 たとえば、CREATE TABLEアクセス許可には<securable>::<name>句 (GRANT CREATE TABLE TO Mary;が Mary にテーブルの作成を許可する) は必要ありません。<principal>は権限を受け取るまたは失うセキュリティ プリンシパル (ログイン、ユーザー、ロール) です。 ロールに権限を付与できるタイミングで付与します。
次のステートメント例では、Production スキーマに含まれるParts テーブルまたはビューに対するUPDATEアクセス許可を、PartsTeamという名前のロールに付与します。
GRANT UPDATE ON OBJECT::Production.Parts TO PartsTeam;
次のステートメント例では、Production スキーマに対するUPDATE権限と、このスキーマに含まれる任意のテーブルまたはビューに対する拡張機能によって、ProductionTeam という名前のロールを付与します。これは、個々のオブジェクト レベルよりもアクセス許可を割り当てる方が効果的でわかりやすい方法です。
GRANT UPDATE ON SCHEMA::Production TO ProductionTeam;
権限をセキュリティ プリンシパル (ログイン、ユーザー、ロール) に付与するには、 GRANT ステートメント使用します。 権限を明示的に拒否するには、DENY コマンドを使用します。 以前に付与または拒否された権限を削除するには、 REVOKE ステートメントを使用します。 権限は累積的であるため、ユーザーはユーザー、ログイン、すべてのグループ メンバーシップに付与された権限をすべて受け取ります。ただし、権限の拒否はすべての付与をオーバーライドします。
注意事項
よくある間違いは、 GRANT の代わりに DENY を使用して REVOKEの削除を試行することです。 これにより、ユーザーが複数のソースからアクセス許可を受け取ったときに問題が発生する可能性があります。これは一般的なシナリオです。 次の例は、この原則を示しています。
Sales グループは、ステートメント GRANT SELECT ON OBJECT::OrderStatus TO Sales;を使用して、OrderStatus テーブルに対するSELECTアクセス許可を受け取ります。 ユーザー Jae は、 Sales ロールのメンバーです。 Jae には、GRANT SELECT ON OBJECT::OrderStatus TO Jae; ステートメント を通じて、OrderStatus テーブルに対する SELECT アクセス許可が独自のユーザー名で付与されています。 管理者がSalesロールへのGRANTを削除すると仮定します。
管理者が
REVOKE SELECT ON OBJECT::OrderStatus TO Sales;を正しく実行した場合、Jae は個々のGRANTステートメントを通じてOrderStatusテーブルへのSELECTアクセスを保持します。管理者が
DENY SELECT ON OBJECT::OrderStatus TO Sales;を誤って実行した場合、SalesロールのメンバーであるJaeの個々のGRANTがDENYによってSalesにオーバーライドされるため、SELECTのアクセス許可が拒否されます。
Note
権限は Management Studio を使用して構成できます。 オブジェクト エクスプローラーでセキュリティ保護可能なリソースを探し、セキュリティ保護可能なリソース名を右クリックして、 [プロパティ] を選択します。 [権限] ページを選択します。 権限ページの使用に関するヘルプについては、「 Permissions or Securables Page」を参照してください。
権限の階層
権限には、親子階層があります。 つまり、データベースに SELECT 権限を付与すると、その権限にデータベース内のすべての (子) スキーマの SELECT 権限が含まれます。 スキーマに SELECT 権限を付与すると、その権限にスキーマ内のすべての (子) テーブルとビューの SELECT 権限が含まれます。 権限は 推移的です。データベースに SELECT 権限を付与すると、すべての (子) スキーマ、およびすべての (孫) テーブルとビューに対する SELECT 権限が含まれます。
権限には、包含権限もあります。 通常、オブジェクトに対する CONTROL 権限は、オブジェクトに対する他のすべての権限を付与します。
同じ権限に親子階層と包含階層の両方が存在することがあるため、権限のシステムは複雑になります。 たとえば、データベース (SalesDB) 内のテーブル (Region) をスキーマ (Customers) で取得します。
CONTROLテーブルRegionに対するアクセス許可には、ALTER、SELECT、INSERT、UPDATE、DELETE、その他のアクセス許可など、テーブルRegionに対する他のすべてのアクセス許可が含まれます。SELECTが所有するCustomersスキーマのRegionテーブルには、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を何度も付与するのではなく、スキーマ レベルで 1 回SELECT付与します。 データベースの設計は、この戦略の成功に大きく影響する可能性があります。 この方法は、同じ権限を必要とするオブジェクトが 1 つのスキーマに含まれるようにデータベースを設計する場合に最適です。
ヒント
データベースとそのオブジェクトを設計するときは、アプリケーションとユーザーがそれらのオブジェクトにアクセスする方法を最初から計画します。 この情報を使用して、スキーマを使用してテーブル、ビュー、関数、およびストアド プロシージャへのアクセスを制御します。 スキーマを使用すると、アクセスの種類をより簡単にグループ化できます。
権限の図
次の図は、アクセス許可とそれらの関連性を示します。 一部の高いレベルの許可 ( CONTROL SERVERなど) は複数回列挙されています。 この記事のポスターは、読み取るには小さすぎます。 Database Engine のアクセス許可ポスターは PDF 形式のフルサイズ版としてダウンロードできます。
Database Engine プリンシパルとサーバーおよびデータベース オブジェクト間の関係を示す図については、「権限の階層 (データベース エンジン)」をご覧ください。
権限対固定サーバーと固定データベース ロール
固定サーバー ロールと固定データベース ロールのアクセス許可は、詳細なアクセス許可とまったく同じですが、まったく同じではありません。 たとえば、 sysadmin 固定サーバー ロールのメンバーには、 CONTROL SERVER アクセス許可を持つログインと同様に、SQL Server のインスタンスに対するすべてのアクセス許可があります。
ただし、 CONTROL SERVER アクセス許可を付与しても、ログインは sysadmin 固定サーバー ロールのメンバーにはなりません。また、 sysadmin 固定サーバー ロールにログインを追加しても、ログインに CONTROL SERVER アクセス許可は明示的に付与されません。 ストアド プロシージャは、固定ロールをチェックし、詳細なアクセス許可をチェックしないことによってアクセス許可をチェックすることがあります。
たとえば、データベースをデタッチするには、固定データベース ロール db_owner メンバーシップが必要です。 同じ CONTROL DATABASE 権限では不十分です。 これらの 2 つのシステムは並行して運用されますが、互いに干渉することはほとんどありません。 Microsoft では、可能な限り固定ロールではなく、新しい詳細なアクセス許可システムを使用することをお勧めします。
アクセス許可の監視
次のビューは、セキュリティ情報を返します。 セキュリティ関連のすべてのビューについては、「 セキュリティ カタログ ビュー (Transact-SQL)」を参照してください。
| ビュー | 説明 |
|---|---|
sys.server_principals1 |
サーバー上のログインおよびユーザー定義のサーバーロール |
sys.database_principals |
データベース内のユーザーとユーザー定義ロール |
sys.server_permissions1 |
ログインとユーザー定義の固定サーバー ロールに付与される権限 |
sys.database_permissions |
ユーザーとユーザー定義の固定データベース ロールに付与されるアクセス許可 |
sys.database_role_members |
データベース ロール メンバーシップ |
sys.server_role_members1 |
Server ロールのメンバーシップ |
1 このビューは SQL Database では使用できません。
例
次のステートメントでは、権限に関する有用な情報を返します。
A. 各ユーザーのデータベース権限の一覧
データベース (SQL Server および SQL Database) で許可または拒否された明示的なアクセス許可を返すには、データベースで次の Transact-SQL ステートメントを実行します。
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
INNER JOIN sys.database_principals AS dp
ON perms.grantee_principal_id = dp.principal_id
INNER 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 OUTER 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
INNER JOIN sys.database_principals AS dp
ON dRo.member_principal_id = dp.principal_id
INNER 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)
- 有効なデータベース エンジンのアクセス許可を決定する
- チュートリアル: データベース エンジンの概要
- レッスン 1: データベース オブジェクトの作成とクエリ
- チュートリアル: SQL Server Management Studio
- チュートリアル: Transact-SQL ステートメントを作成する