データベース エンジンのアクセス許可の概要
適用対象: 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 形式でダウンロードできます。
データベース エンジン プリンシパルとサーバーおよびデータベース オブジェクト間の関係を示す図については、「アクセス許可の階層 (データベース エンジン)」を参照してください。
アクセス許可対固定サーバーと固定データベース ロール
固定サーバー ロールおよび固定データベース ロールの権限は似ていますが、詳細な権限がまったく同じということではありません。 たとえば、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)
- データベース エンジンの有効なアクセス許可の決定