次の方法で共有


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

適用対象: SQL Server Azure SQL データベース Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

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

Note

この記事の情報は、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 のみに関する情報を表示できます。

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

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

Note

サーバー レベルのアクセス許可が利用できない SQL Database または Azure Synapse Analytics には適用しないでください。 sys.server_principalsis_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;

Note

すべてのログインは 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 形式でダウンロードできます。

データベース エンジン アクセス許可 PDF のスクリーンショット。

セキュリティ クラス

アクセス許可はサーバー レベル、データベース レベル、スキーマ レベル、オブジェクト レベルなどで与えることができます。26 のレベル (クラスと呼ばれます) があります。 クラスをアルファベット順に並べると、APPLICATION ROLEASSEMBLYASYMMETRIC KEYAVAILABILITY GROUPCERTIFICATECONTRACTDATABASEDATABASE SCOPED CREDENTIALENDPOINTFULLTEXT CATALOGFULLTEXT STOPLISTLOGINMESSAGE TYPEOBJECTREMOTE SERVICE BINDINGROLEROUTESCHEMASEARCH PROPERTY LISTSERVERSERVER ROLESERVICESYMMETRIC KEYTYPEUSERXML 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 データベースで実行してください。

Note

サーバー レベルのアクセス許可を 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」を参照してください。

次のステップ