SQL Server における Public Role

データベース ロールは Windows のグループに似ています。各ユーザーに対して個別にアクセスを取り消したり付与したりするのではなく、管理者はロールから権限を付与または取り消し、さらにロールのメンバーシップを変更することでアクセスを管理します。ロールを使うと、データベース ユーザーに対して権限を正確に付与・取り消ししやすくなります。また、複数のユーザーが SQL のデータベース ロールのメンバーになれるため、ユーザー グループ全体の権限を一度に簡単に管理できます。

この投稿では、Microsoft SQL Server の public ロールと、それに関連するいくつかのベストプラクティスを説明します。（Oracle の世界とは異なり、SQL Server の世界では public ロールはこのように小文字で表記されるため、ここでは意図的に小文字を使用しています。）

SQL Server のロールの種類

Microsoft SQL Server には、あらかじめ用意されたいくつかのロールの種類があります:

• 固定サーバー ロール — public ロールを除き、変更したり削除（drop）したりできないため、「固定」サーバー ロールと呼ばれます
• 固定データベース ロール
• アプリケーション ロール — アプリケーションが、自身の権限セットで実行するために使用できるデータベース プリンシパル（既定では無効）
• ユーザー定義ロール（SQL Server 2012 から）— ユーザー定義にはサーバーレベルの権限のみを追加できます

Public ロールがどこに当たるか

すべてのデータベース プラットフォームには、あらかじめ定義されたロール public が用意されていますが、このロールの実装はプラットフォームによって異なります。SQL Server では、public ロールは固定サーバーロールの一部であり、SQL Server の public ロール権限に対して、権限の付与／拒否／取り消し（取り消す）が可能です。

SQL Server のログインが作成されると、そのログインに public ロールが割り当てられ、取り消すことはできません。サーバー プリンシパルに対して、保護可能なオブジェクト上で特定の権限が付与または拒否されていない場合、そのログインは public ロールに付与されている、そのオブジェクトの権限を自動的に継承します。

Public ロールに割り当てられる権限

SQL Server のセキュリティを維持するために、SQL Server security さらに PCI DSS や HIPAA を含む多くの規制に準拠するには、各ユーザーに割り当てられているすべてのサーバーレベルおよびデータベースレベルのロールを把握する必要があります。Management Studio の transact-SQL クエリを使用して、public ロールに割り当てられているサーバーレベルの権限を確認してみましょう：

      SELECT sp.state_desc as "Permission State", sp.permission_name as "Permission",
sl.name "Principal Name",sp.class_desc AS "Object Class", ep.name "End Point"
FROM sys.server_permissions AS sp
  JOIN sys.server_principals AS sl
    ON sp.grantee_principal_id = sl.principal_id
  LEFT JOIN sys.endpoints AS ep
    ON sp.major_id = ep.endpoint_id
WHERE sl.name = 'public';
      

上の表が示すとおり、public ロールに割り当てられるサーバーレベルの権限は 5 つだけです。なお、VIEW ANY DATABASE の権限はユーザーに対してデータベース オブジェクトへのアクセスを与えるものではなく、SQL Server のインスタンス内にあるすべてのデータベースを一覧表示できるようにするだけです。したがって、新しいログインを作成して他のロールや権限を一切割り当てない場合、ユーザーはインスタンスにログインできるだけで、それ以外の操作はできません。

SQL Server ログインに既定のデータベースが割り当てられている場合の権限

では、ユーザーに既定のデータベースを割り当てて、SQL 認証を使用する SQL Server ログインを作成しましょう。

      USE [master]
GO
CREATE LOGIN [SQTest] WITH PASSWORD=N'nhggLboBn6SHolSWfipjzO/7GYw8M2RMbCt1LsCTK5M=', DEFAULT_DATABASE=[SBITS], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
      

そのユーザーとしてログインし、ユーザーの既定のデータベース SBITS に対するすべてのデータベース レベルの権限を一覧表示すると、どのような権限を持っているか確認できます。下に示すとおり、ユーザー Stealth は既定のデータベースが SBITS であっても、SBITS データベースに対する権限を一切持っていません。つまり、ユーザー ログインに既定のユーザー データベースが割り当てられているからといって、ユーザーがデータベース オブジェクトやデータを閲覧できるとは限りません。

これを確認するには、次のスクリプトを使用できます。

      EXECUTE AS LOGIN= 'SQTest';
GO
USE SBITS
GO
SELECT dp.state_desc AS "Class Description", dp.permission_name AS "Permission Name",
SCHEMA_NAME(ao.schema_id) AS "Schema Name", ao.name AS "Object Name"
FROM sys.database_permissions dp
 LEFT JOIN sys.all_objects ao
  ON dp.major_id = ao.object_id
 JOIN sys.database_principals du
  ON dp.grantee_principal_id = du.principal_id
WHERE du.name = 'TestLoginPerms'
 AND ao.name IS NOT NULL
ORDER BY ao.name;
REVERT
      

マスター データベース上でパブリック ロールが継承する権限

ユーザー ログイン Stealth は既定で public ロールの一部であるため、public ロールが master データベースで継承する権限を見てみましょう。

      USE master;
GO
 SELECT sp.state_desc AS "Permission State", sp.permission_name AS "Permission",
 SCHEMA_NAME(ao.schema_id) AS 'Schema', ao.name AS "Object Name"
 FROM sys.database_permissions sp
  LEFT JOIN sys.all_objects ao
     ON sp.major_id = ao.object_id
   JOIN sys.database_principals dp
     ON sp.grantee_principal_id = dp.principal_id
 WHERE dp.name = 'public'
 AND ao.name IS NOT NULL
 ORDER BY ao.name
      

SQL Server 2016 では、公用ロールに付与されている master データベース上の権限が 2,089 個保存されています。大変に見えるかもしれませんが、すべて SELECT 権限であり、ユーザー Stealth が master データベースに変更を加えることはできません。ただし、組織のセキュリティ ポリシーに基づいて一部の権限を取り消すことは良い実践です。取り消す際には注意してください。状況によっては、ユーザーが通常の操作を行うために一部の権限が必要になる場合があります。

ロールのレビューと管理を簡素化

一度に1つのインスタンスずつ、publicロールに関連する問題を把握するために複雑なカスタムスクリプトに頼るのではなく、Netwrix Access Analyzer の使用を検討してください。このデータアクセスガバナンス基盤は、SQL Server の public ロールおよび SQL Server の public データベース ロールを含む、すべての SQL Server ロールと権限を列挙し、標準機能だけで詳細なエンタイトルメント レポートを作成できます。さらに、企業内のすべての SQL Server にわたる public ロールを単一の画面で可視化し、ボタンを1回クリックするだけで問題の是正を支援します。

SQL Server の public ロールに関するベストプラクティス

SQL Server の public ロールに関して、次のベストプラクティスを推奨します。

• いかなる場合でも、デフォルトの権限（default privileges）以外の追加権限を public ロールに付与しないでください。必要であれば、ユーザー定義ロール（user-defined role）を使用してください。
• public ロールに対するサーバー レベルの権限を変更しないでください。変更すると、ユーザーがデータベースに接続できなくなる可能性があります。
• SQL Server をアップグレードするたびに public 権限を確認してください。Microsoft は public ロールに変更を加えることがよくあります。

よくある質問

public データベース ロールとは何ですか？

public データベース ロールに付与された権限は、各データベース ユーザーに継承されます。

SQL Server の public ロールにはどのような権限がありますか？

すべての SQL Server ログインは public サーバー ロールに属します。サーバー プリンシパルが、保護可能なオブジェクト（securable object）に対して特定の権限を許可または拒否されていない場合、ユーザーは、そのオブジェクトに対して public ロールに付与されている権限を継承します。