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

[アーティクル]
05/30/2023

適用対象:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

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

注意

この記事の情報は、SQL Server 2005 (9.x) 以降のバージョンに適用されます。 SQL Server のバージョンによっては、利用できない種類のアクセス許可があります。

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

両方のアクセス許可システムの集合体が有効なアクセス許可となります。
アクセス許可の拒否はアクセス許可の承諾をオーバーライドします。
あるユーザーが sysadmin という固定のサーバーロールに属している場合、アクセス許可はそれ以上確認されません。拒否は強制されません。
古いシステムと新しいシステムには類似点があります。たとえば、sysadmin 固定サーバーロールのメンバーシップには、CONTROL SERVER と同様のアクセス許可が与えられます。ただし、システムは同じではありません。たとえば、あるログインに CONTROL SERVER アクセス許可のみが与えられ、ストアドプロシージャが sysadmin 固定サーバーロールのメンバーシップを確認する場合、アクセス許可確認は失敗します。この逆も当てはまります。

概要

サーバーレベルのアクセス許可は、固定サーバーロールかユーザー定義サーバーロールのメンバーシップから与えられます。全員が 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 のみに関する情報を表示できます。

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

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

注意

サーバーレベルのアクセス許可が利用できない SQL Database または Azure Synapse Analytics には適用しないでください。 sys.server_principals の is_fixed_role 列が SQL Server 2012 (11.x) に追加されました。古いバージョンの SQL Server には必要ありません。

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;

注意

すべてのログインは public ロールに属し、削除できません。このクエリは master データベースのテーブルを確認しますが、オンプレミス製品のデータベースでは実行できません。

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

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 形式でダウンロードできます。

A screenshot from the Database Engine permissions 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 では使用できません)。各クラスに関する完全な情報を提供するには、異なるクエリが必要です。

プリンシパル

アクセス許可はプリンシパルに与えられます。プリンシパルには、サーバーロール、ログイン、データベースロール、ユーザーがあります。ログインは、さまざまな 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 データベースで実行してください。

注意

サーバーレベルのアクセス許可を SQL Database または Azure Synapse Analytics で承諾または拒否することはできません。

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

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

概要

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

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

セキュリティクラス

プリンシパル

便利なクエリ

サーバーのアクセス許可

データベース権限

次のステップ

フィードバック

フィードバック

その他のリソース

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

概要

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

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

セキュリティ クラス

プリンシパル

便利なクエリ

サーバーのアクセス許可

データベース権限

次のステップ

フィードバック

フィードバック

その他のリソース

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

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

セキュリティクラス