Row-Level Security (RLS) 導入分析

// Middlewares/TenantMiddleware.cs
public async Task InvokeAsync(HttpContext context, ITenantService tenantService)
{
    var host = context.Request.Host.Host;
    var tenantName = ExtractTenantNameFromHost(host);
    tenantService.SetTenant(finalTenantName);
    await _next(context);
}

// Models/EcAuthDbContext.cs
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Organization>()
        .HasQueryFilter(o => o.TenantName == _tenantService.TenantName);

    modelBuilder.Entity<EcAuthUser>()
        .HasQueryFilter(u => u.Organization != null &&
                            u.Organization.TenantName == _tenantService.TenantName);

    modelBuilder.Entity<ExternalIdpMapping>()
        .HasQueryFilter(m => m.EcAuthUser != null &&
                            m.EcAuthUser.Organization != null &&
                            m.EcAuthUser.Organization.TenantName == _tenantService.TenantName);

    modelBuilder.Entity<AuthorizationCode>()
        .HasQueryFilter(ac => ac.EcAuthUser != null &&
                             ac.EcAuthUser.Organization != null &&
                             ac.EcAuthUser.Organization.TenantName == _tenantService.TenantName);

    modelBuilder.Entity<AccessToken>()
        .HasQueryFilter(at => at.EcAuthUser != null &&
                             at.EcAuthUser.Organization != null &&
                             at.EcAuthUser.Organization.TenantName == _tenantService.TenantName);

    modelBuilder.Entity<ExternalIdpToken>()
        .HasQueryFilter(eit => eit.EcAuthUser != null &&
                              eit.EcAuthUser.Organization != null &&
                              eit.EcAuthUser.Organization.TenantName == _tenantService.TenantName);
}

-- スキーマ作成
CREATE SCHEMA Security;
GO

-- フィルター述語関数
CREATE FUNCTION Security.fn_TenantFilter(@TenantName NVARCHAR(255))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
    SELECT 1 AS result
    WHERE @TenantName = CAST(SESSION_CONTEXT(N'TenantName') AS NVARCHAR(255));
GO

-- セキュリティポリシー（organizationテーブル）
CREATE SECURITY POLICY Security.OrganizationPolicy
    ADD FILTER PREDICATE Security.fn_TenantFilter(tenant_name) ON dbo.organization
    WITH (STATE = ON);
GO

-- セキュリティポリシー（ec_auth_userテーブル - organization_idを経由）
CREATE FUNCTION Security.fn_UserTenantFilter(@OrganizationId INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
    SELECT 1 AS result
    WHERE EXISTS (
        SELECT 1 FROM dbo.organization o
        WHERE o.id = @OrganizationId
        AND o.tenant_name = CAST(SESSION_CONTEXT(N'TenantName') AS NVARCHAR(255))
    );
GO

CREATE SECURITY POLICY Security.EcAuthUserPolicy
    ADD FILTER PREDICATE Security.fn_UserTenantFilter(organization_id) ON dbo.ec_auth_user
    WITH (STATE = ON);
GO

// DbConnectionInterceptorでSESSION_CONTEXTを設定
public class TenantSessionInterceptor : DbConnectionInterceptor
{
    private readonly ITenantService _tenantService;

    public TenantSessionInterceptor(ITenantService tenantService)
    {
        _tenantService = tenantService;
    }

    public override async ValueTask<InterceptionResult> ConnectionOpeningAsync(
        DbConnection connection,
        ConnectionEventData eventData,
        InterceptionResult result,
        CancellationToken cancellationToken = default)
    {
        return result;
    }

    public override async Task ConnectionOpenedAsync(
        DbConnection connection,
        ConnectionOpenedEventData eventData,
        CancellationToken cancellationToken = default)
    {
        if (!string.IsNullOrEmpty(_tenantService.TenantName))
        {
            using var command = connection.CreateCommand();
            command.CommandText = "EXEC sp_set_session_context @key=N'TenantName', @value=@tenant";
            var param = command.CreateParameter();
            param.ParameterName = "@tenant";
            param.Value = _tenantService.TenantName;
            command.Parameters.Add(param);
            await command.ExecuteNonQueryAsync(cancellationToken);
        }
    }
}

// Program.cs での設定
builder.Services.AddDbContext<EcAuthDbContext>((sp, options) =>
{
    var tenantService = sp.GetRequiredService<ITenantService>();
    options.UseSqlServer(connectionString)
           .AddInterceptors(new TenantSessionInterceptor(tenantService));
});