データベース エンジンの有効なアクセス許可の決定

対象者:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analyticsアナリティクスプラットフォームシステム(PDW)Microsoft FabricにおけるSQLデータベース

SQL Server データベース エンジンでは、さまざまなオブジェクトのアクセス許可が与えられます。この記事では、アクセス許可が与えられているユーザーを判断する方法について説明します。 SQL Server では、データベース エンジンのために 2 つのアクセス許可システムが実装されます。 固定ロールの古いほうのシステムでは、アクセス許可が事前構成されました。 SQL Server 2005 (9.x) より、より柔軟で正確なシステムが利用できます。

次の点に常に留意してください。

• 両方のアクセス許可システムの集合体が有効なアクセス許可となります。
• アクセス許可の拒否は、アクセス許可の付与より優先されます。
• あるユーザーが sysadmin という固定のサーバー ロールに属している場合、アクセス許可はそれ以上確認されません。拒否は強制されません。
• 古いシステムと新しいシステムには類似点があります。 たとえば、sysadmin 固定サーバー ロールのメンバーシップには、CONTROL SERVER と同様のアクセス許可が与えられます。 ただし、システムは同じではありません。 たとえば、あるログインに CONTROL SERVER アクセス許可のみが与えられ、ストアド プロシージャが sysadmin 固定サーバー ロールのメンバーシップを確認する場合、アクセス許可確認は失敗します。 逆も同じです。
• Fabric SQL データベースでは、データベース ユーザーの Microsoft Entra ID のみがサポートされている認証方法です。 サーバー レベルのロールとアクセス許可は使用できません。データベース レベルのみです。 詳細については、「 Microsoft Fabric の SQL データベースでの認証」を参照してください。

Summary

• サーバー レベルのアクセス許可は、固定サーバー ロールかユーザー定義サーバー ロールのメンバーシップから与えられます。 全員が public 固定サーバー ロールに属し、そこで割り当てられたアクセス許可を受け取ります。
• サーバー レベルのアクセス許可は、ログインに与えられるアクセス許可かユーザー定義サーバー ロールから与えられます。
• データベース レベルのアクセス許可は、固定データベース ロールのメンバーシップか各ロールベースのユーザー定義データベース ロールから与えられます。 全員が public 固定データベース ロールに属し、そこで割り当てられたアクセス許可を受け取ります。
• データベース レベルのアクセス許可は、ユーザーに与えられるアクセス許可か各データベースのユーザー定義データベース ロールから与えられます。
• アクセス許可は、guest ログインまたは guest データベース ユーザーが有効になっている場合、それらから付与されます。 guest のログインとユーザーは既定では無効になっています。
• Windows ユーザーは、ログインが与えられた Windows グループに所属できます。 Windows ユーザーが Windows トークンを Windows グループのセキュリティ ID と結び付けて提示すると、SQL Server は Windows グループのメンバーシップを認識します。 SQL Server は Windows グループ メンバーシップに関する自動更新を管理することも受信することもしないため、Windows ユーザーのアクセス許可が Windows グループ メンバーシップから与えられることを SQL Server が報告しても、その報告には信頼性がありません。
• アクセス許可は、あるアプリケーション ロールに切り替え、パスワードを指定する方法で取得できます。
• アクセス許可は、EXECUTE AS 句が含まれるストアド プロシージャを実行する方法で取得できます。
• アクセス許可は、IMPERSONATE アクセス許可が与えられているログインまたはユーザーにより取得できます。
• ローカル コンピューター管理者グループに属する場合、常に自分の特権を sysadmin に昇格できます。 (SQL データベースには適用されません。)
• securityadmin 固定サーバー ロールに属する場合、自分のさまざまな特権を昇格できます。特権を sysadmin に昇格できる場合もあります。 (SQL データベースには適用されません。)
• SQL Server 管理者は、すべてのログインとユーザーに関する情報を表示できます。 特権レベルの低いユーザーは、通常、自分の ID のみに関する情報を表示できます。

古い固定ロール アクセス許可システム

固定サーバー ロールと固定データベース ロールでは、アクセス許可が事前構成されており、このアクセス許可は変更できません。 固定サーバー ロールのメンバーを判断するには、次のクエリを実行します。

SELECT SP1.name AS ServerRoleName,
    ISNULL(SP2.name, 'No members') AS LoginName
FROM sys.server_role_members AS SRM
RIGHT JOIN sys.server_principals AS SP1
    ON SRM.role_principal_id = SP1.principal_id
LEFT JOIN sys.server_principals AS SP2
    ON SRM.member_principal_id = SP2.principal_id
WHERE SP1.is_fixed_role = 1 -- Remove for SQL Server 2008
ORDER BY SP1.name;

固定データベース ロールのメンバーを判断するには、各データベースで次のクエリを実行します。

SELECT DP1.name AS DatabaseRoleName,
    ISNULL(DP2.name, 'No members') AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT JOIN sys.database_principals AS DP1
    ON DRM.role_principal_id = DP1.principal_id
LEFT JOIN sys.database_principals AS DP2
    ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.is_fixed_role = 1
ORDER BY DP1.name;

各ロールに付与されるアクセス許可を理解するには、サーバー レベルのロールとデータベースレベルのロールの役割の説明を参照してください。

新しく詳細なアクセス許可システム

このシステムは柔軟であり、厳密に設定すると複雑になることがあります。 簡単にするには、ロールを作成し、ロールにアクセス許可を割り当て、ロールにグループを追加します。 データベース開発チームが作業をスキーマごとに分け、個々のテーブルやプロシージャではなくスキーマ全体に対してロール権限を付与するほうが簡単です。 実際のシナリオは複雑であり、ビジネス ニーズによって予期しないセキュリティ要件が生じる可能性があります。

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

セキュリティ クラス

アクセス許可はサーバー レベル、データベース レベル、スキーマ レベル、オブジェクト レベルなどで与えることができます。26 のレベル (クラスと呼ばれます) があります。 クラスをアルファベット順に並べると、APPLICATION ROLE、ASSEMBLY、ASYMMETRIC KEY、AVAILABILITY GROUP、CERTIFICATE、CONTRACT、DATABASE、DATABASESCOPED CREDENTIAL、ENDPOINT、FULLTEXT CATALOG、FULLTEXT STOPLIST、LOGIN、MESSAGE TYPE、OBJECT、REMOTE SERVICE BINDING、ROLE、ROUTE、SCHEMA、SEARCH PROPERTY LIST、SERVER、SERVER ROLE、SERVICE、SYMMETRIC KEY、TYPE、USER、XML SCHEMA COLLECTION になります。 (一部のクラスは、一部の種類の SQL Server では使用できません)。各クラスに関する完全な情報を提供するには、異なるクエリが必要です。

Principals

アクセス許可はプリンシパルに与えられます。 プリンシパルには、サーバー ロール、ログイン、データベース ロール、ユーザーがあります。 ログインは、さまざまな Windows ユーザーを含む Windows グループを表すことができます。 Windows グループは SQL Server によって管理されていないため、SQL Server が Windows グループのメンバーを常に認識することはありません。 Windows ユーザーが SQL Server に接続すると、そのユーザーの Windows グループ メンバーシップ トークンがログイン パケットに含まれます。

Windows ユーザーがWindows グループに基づくログインを使用して接続する場合、一部のアクティビティでは、個々のWindows ユーザーを表すログインまたはユーザーの作成にSQL Serverが必要になる場合があります。 たとえば、ある Windows グループ (エンジニア) にユーザー (Mary、Todd、Pat) が含まれ、このエンジニア グループにデータベース ユーザー アカウントが与えられています。 Mary にアクセス許可が与えられ、テーブルを作成すると、ユーザー (Mary) がテーブルの所有者として作成されることがあります。 あるいは、エンジニア グループの他のメンバーに与えられているアクセス許可が Todd に与えられない場合、アクセス許可を追跡記録するためにユーザー (Todd) を作成する必要があります。

Windows ユーザーは複数の Windows グループに属することがあります (エンジニア グループとマネージャー グループの両方など)。 エンジニア ログイン、マネージャー ログインへのアクセス許可、ユーザーに対する個別の付与または拒否、およびユーザーがメンバーであるロールに対する許可または拒否のアクセス許可はすべて集計および評価され、有効なアクセス許可が決定されます。 HAS_PERMS_BY_NAME 関数は、ユーザーまたはログインに特定のアクセス許可が与えられているかどうかを判断できます。 ただし、アクセス許可の承諾または拒否のソースを決定するこれといった方法はありません。 試行錯誤を重ねることも含め、アクセス許可の一覧を研究してください。

便利なクエリ

サーバーのアクセス許可

次のクエリは、サーバー レベルで承諾または拒否されているアクセス許可の一覧を返します。 master データベースでこのクエリを実行します。

SELECT pr.type_desc,
    pr.name,
    ISNULL(pe.state_desc, 'No permission statements') AS state_desc,
    ISNULL(pe.permission_name, 'No permission statements') AS permission_name
FROM sys.server_principals AS pr
LEFT JOIN sys.server_permissions AS pe
    ON pr.principal_id = pe.grantee_principal_id
WHERE is_fixed_role = 0 -- Remove for SQL Server 2008
ORDER BY pr.name,
    type_desc;

データベース権限

次のクエリは、データベース レベルで承諾または拒否されているアクセス許可の一覧を返します。 各データベースでこのクエリを実行します。

SELECT pr.type_desc,
    pr.name,
    ISNULL(pe.state_desc, 'No permission statements') AS state_desc,
    ISNULL(pe.permission_name, 'No permission statements') AS permission_name
FROM sys.database_principals AS pr
LEFT JOIN sys.database_permissions AS pe
    ON pr.principal_id = pe.grantee_principal_id
WHERE pr.is_fixed_role = 0
ORDER BY pr.name,
    type_desc;

アクセス許可テーブル内の権限の各クラスは、セキュリティ保護可能なそのクラスに関する関連情報を提供する他のシステム ビューに結合できます。 たとえば、次のクエリでは、アクセス許可の影響を受けるデータベース オブジェクトの名前が与えられます。

SELECT pr.type_desc,
    pr.name,
    pe.state_desc,
    pe.permission_name,
    s.name + '.' + oj.name AS OBJECT,
    major_id
FROM sys.database_principals AS pr
INNER JOIN sys.database_permissions AS pe
    ON pr.principal_id = pe.grantee_principal_id
INNER JOIN sys.objects AS oj
    ON oj.object_id = pe.major_id
INNER JOIN sys.schemas AS s
    ON oj.schema_id = s.schema_id
WHERE class_desc = 'OBJECT_OR_COLUMN';

HAS_PERMS_BY_NAME 関数を使用すると、特定のユーザー (この場合、TestUser) にアクセス許可が与えられているかどうかが判断されます。 例えば次が挙げられます。

EXECUTE AS USER = 'TestUser';
SELECT HAS_PERMS_BY_NAME ('dbo.T1', 'OBJECT', 'SELECT');
REVERT;

構文の詳細については、「HAS_PERMS_BY_NAME」を参照してください。

関連するコンテンツ

• データベース エンジンの権限の概要
• チュートリアル : データベース エンジンの概要