アクセス許可とセキュリティで保護されたアクセスを管理する
オブジェクト レベルのアクセス許可は、テーブル、ビュー、ストアド プロシージャ、関数などのデータベース オブジェクトに対してユーザーが実行できるアクションを制御します。 Row-Level Security はオブジェクト内のデータをフィルター処理しますが、オブジェクト レベルのアクセス許可によって、ユーザーがオブジェクトにまったくアクセスできるかどうか、および実行できる操作が決まります。
適切に設計されたアクセス許可モデルは、セキュリティで保護された認証と連携します。 このユニットでは、適切なアクセス許可を付与する方法と、ユーザーが安全に認証されるようにする方法 (できればパスワードなしで) の両方について説明します。
アクセス許可の階層を理解する
SQL Server では階層 型アクセス許可モデル が使用され、上位レベルで付与されたアクセス許可は下位レベルに下がります。 これはカスケードと考えてください。サーバー→データベース→スキーマ→個々のオブジェクトです。
サーバー レベルでは、アクセス許可によってサインイン管理とサーバー構成が制御されます。 データベース レベルのアクセス許可は、特定のデータベース内のアクションを制御します。 スキーマ レベルのアクセス許可はスキーマ内のすべてのオブジェクトに適用され、オブジェクト レベルのアクセス許可は特定のテーブル、ビュー、またはプロシージャを対象とします。
強力なパターンを次に示します。スキーマに SELECT アクセス許可を付与すると、ユーザーはそのスキーマ内のすべてのテーブルとビュー (将来作成されるオブジェクトを含む) から選択できます。
-- Grant SELECT on all objects in the Sales schema
GRANT SELECT ON SCHEMA::Sales TO SalesAnalyst;
-- Grant EXECUTE on all procedures in the Reports schema
GRANT EXECUTE ON SCHEMA::Reports TO ReportingUsers;
アクセス許可の付与と取り消し
3 つのステートメントによってアクセス許可が制御されます。 GRANT は、ユーザー固有のアクセス許可を付与し、 REVOKE 以前に付与されたアクセス許可を削除し、 DENY 明示的にアクセス許可をブロックして、許可をオーバーライドします。
-- Allow reading data
GRANT SELECT ON dbo.Customers TO CustomerServiceRole;
-- Allow data modifications
GRANT INSERT, UPDATE ON dbo.Orders TO OrderProcessingRole;
-- Explicitly deny access to sensitive data (overrides any grants)
DENY SELECT ON dbo.EmployeeSalaries TO HRAssistants;
-- Remove a permission without explicitly denying it
REVOKE INSERT ON dbo.Customers FROM CustomerServiceRole;
REVOKEとDENYの違いは何ですか? これは、ユーザーが複数のアクセス許可ソースを持っている場合に重要です。 取り消しでは、1 つの許可が削除されますが、他の許可によるアクセスは禁止されません。 拒否すると、他の許可が存在する場合でもアクセスがブロックされます。
ロールベースのアクセス制御を実装する
権限をユーザーに直接付与する代わりに、ジョブ機能を表す データベース ロール を作成します。 ロールにアクセス許可を割り当て、適切なロールにユーザーを追加します。
-- Create roles for different job functions
CREATE ROLE DataReaders;
CREATE ROLE DataWriters;
-- Grant appropriate permissions to each role
GRANT SELECT ON SCHEMA::dbo TO DataReaders;
GRANT INSERT, UPDATE, DELETE ON SCHEMA::dbo TO DataWriters;
-- Add users to roles
ALTER ROLE DataReaders ADD MEMBER JohnSmith;
ALTER ROLE DataWriters ADD MEMBER JaneDoc;
ロールを入れ子にして階層型のアクセス許可構造を作成することもできます。親ロールにユーザーを追加すると、そのユーザーにはすべての子ロールのアクセス許可が自動的に付与されます。
ヒント
ロール階層を文書化し、各ロールの目的を明確にする名前付け規則 (部門や関数のプレフィックスなど) を使用します。
セキュリティのためにスキーマの分離を適用する
スキーマは、 オブジェクトを整理し、アクセス許可を管理する自然な方法を提供します。 関連するオブジェクトをグループ化し、スキーマ レベルでアクセス許可を付与します。
CREATE SCHEMA Sales AUTHORIZATION dbo;
CREATE SCHEMA HR AUTHORIZATION dbo;
-- Sales team can read and write sales data
GRANT SELECT, INSERT, UPDATE ON SCHEMA::Sales TO SalesTeam;
-- HR has full control of their schema
GRANT CONTROL ON SCHEMA::HR TO HRAdministrators;
Microsoft Entra 認証を構成する
最新のアプリケーションでは、可能な限りパスワードレス認証を使用する必要があります。 Microsoft Entra 認証 は、資格情報の盗難やシークレットのローテーションの運用上の負担など、パスワード管理に関連するリスクを排除します。
SQL データベースでは、複数の認証方法がサポートされています。 SQL 認証では、データベースに格納されているユーザー名とパスワードが使用されます。 これは簡単ですが、資格情報が侵害された場合は危険です。 Microsoft Entra 認証は、Azure SQL Database、Azure SQL Managed Instance、SQL Server 2022 以降、および Microsoft Fabric の SQL データベースを使用して、ID 統合をクラウド シナリオに拡張します。
Microsoft Entra ID からデータベース ユーザーを作成するのは簡単です。
-- Create users for Entra accounts, managed identities, or groups
CREATE USER [app-service-identity] FROM EXTERNAL PROVIDER;
CREATE USER [developer@contoso.com] FROM EXTERNAL PROVIDER;
CREATE USER [DataAnalystsGroup] FROM EXTERNAL PROVIDER;
-- Grant permissions just like SQL users
ALTER ROLE db_datareader ADD MEMBER [developer@contoso.com];
ALTER ROLE db_datawriter ADD MEMBER [app-service-identity];
マネージド ID 認証を実装する
マネージド ID は、Azure でホストされるアプリケーションにとって最も安全なオプションです。 Azure は ID ライフサイクルを自動的に処理し、漏洩や盗難に備えた資格情報はありません。
Azure SQL Database に接続する Azure App Service の場合は、システム割り当てマネージド ID を有効にしてから、データベース ユーザーを作成します。
CREATE USER [MyWebApp] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [MyWebApp];
ALTER ROLE db_datawriter ADD MEMBER [MyWebApp];
マネージド ID 認証を使用するようにアプリケーション接続文字列を更新します。
Server=tcp:myserver.database.windows.net,1433;Database=mydb;Authentication=Active Directory Managed Identity;Encrypt=True;
ヒント
複数のアプリケーションで同じデータベースアクセス許可が必要な場合は、ユーザー割り当てマネージド ID を使用します。 これにより、管理するデータベース ユーザーの数が減ります。
セキュリティで保護された接続文字列
使用する認証方法に関係なく、次のベスト プラクティスを使用して接続文字列を保護します。
- コードではなく、Azure Key Vault または環境変数に格納する
- マネージド ID を使用して接続文字列から資格情報を完全に削除する
- で暗号化された接続を有効にする
Encrypt=True;TrustServerCertificate=False - ファイアウォール規則とプライベート エンドポイントを使用してネットワーク アクセスを制限する
Important
サービス プリンシパルにクライアント シークレットを使用する場合は、それらを Azure Key Vault に格納し、定期的にローテーションします。 証明書ベースの認証は、共有シークレットよりも強力なセキュリティを提供します。
アクセス許可の表示と監査
システム ビューにクエリを実行して、現在のアクセス許可の割り当てを理解できます。
-- View all permissions for a specific principal
SELECT
prin.name AS PrincipalName,
perm.permission_name,
perm.state_desc AS PermissionState,
obj.name AS ObjectName
FROM sys.database_permissions perm
INNER JOIN sys.database_principals prin ON perm.grantee_principal_id = prin.principal_id
LEFT JOIN sys.objects obj ON perm.major_id = obj.object_id
WHERE prin.name = 'SalesAnalyst';
-- Check effective permissions for the current user
SELECT * FROM fn_my_permissions('Sales.Orders', 'OBJECT');
アクセス許可の割り当てを定期的に監査し、現在のジョブ機能に合わせて調整することを習慣にします。 また、オブジェクト レベルのアクセス許可は、包括的な保護のために Row-Level セキュリティや動的データ マスクなどの他のセキュリティ機能と組み合わせると最適に機能します。