記事編集

日付

ジャンルのカテゴリ―名

タイトル

内容の要旨

検索Word:<a data-fr-linked="true" href="//asp.net" data-pasted="true">asp.net</a> mvc ：認証アプリでユーザー毎が使用するデータベースを構築するには？<p style="border: 0px; border-collapse: collapse; border-spacing: 0px; list-style-position: initial; list-style-image: initial; list-style-type: revert; margin: revert; padding: revert; -webkit-line-clamp: revert; color: rgb(17, 17, 17); font-family: Roboto, Helvetica, sans-serif; font-size: 16px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; white-space: normal; background-color: rgb(249, 249, 249); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;" data-pasted="true">ASP.NET MVC（または ASP.NET Core MVC）で「ユーザーごとに異なるデータベースを使う」構成を作る場合、設計パターンはいくつかあります。 これは&nbsp;マルチテナント（Multi-Tenant）アーキテクチャ の一種で、特に「DB分離型（Database-per-User / Database-per-Tenant）」に該当します。<h2 style="border: 0px; border-collapse: collapse; border-spacing: 0px; list-style-position: initial; list-style-image: initial; list-style-type: revert; margin: revert; padding: revert; font: 18px / 24px Roboto, sans-serif; color: rgb(17, 17, 17); letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; white-space: normal; background-color: rgb(249, 249, 249); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;">1. 基本的な考え方</h2><ul style="border: 0px; border-collapse: collapse; border-spacing: 0px; list-style-position: initial; list-style-image: initial; list-style-type: revert; margin: revert; padding: revert; color: rgb(17, 17, 17); font-family: Roboto, Helvetica, sans-serif; font-size: 16px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; white-space: normal; background-color: rgb(249, 249, 249); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;"><li style="border: 0px; border-collapse: collapse; border-spacing: 0px; list-style-position: initial; list-style-image: initial; list-style-type: revert; margin-top: revert; margin-right: revert; margin-bottom: 8px; margin-left: revert; padding: revert;">認証（ASP.NET Identity など）でユーザーを特定</li><li style="border: 0px; border-collapse: collapse; border-spacing: 0px; list-style-position: initial; list-style-image: initial; list-style-type: revert; margin-top: revert; margin-right: revert; margin-bottom: 8px; margin-left: revert; padding: revert;">ログイン後、そのユーザーに紐づく&nbsp;接続文字列（Connection String） を取得</li><li style="border: 0px; border-collapse: collapse; border-spacing: 0px; list-style-position: initial; list-style-image: initial; list-style-type: revert; margin-top: revert; margin-right: revert; margin-bottom: 8px; margin-left: revert; padding: revert;"><code style="border: 1px solid rgb(221, 221, 221); border-radius: 4px; padding: 1px 4px; background: rgb(255, 255, 255); font: 14px / 20px monospace;">DbContext</code> を動的に構築して、そのユーザー専用のDBに接続</li></ul><h2 style="border: 0px; border-collapse: collapse; border-spacing: 0px; list-style-position: initial; list-style-image: initial; list-style-type: revert; margin: revert; padding: revert; font: 18px / 24px Roboto, sans-serif; color: rgb(17, 17, 17); letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; white-space: normal; background-color: rgb(249, 249, 249); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;">2. 実装パターン</h2><h3 style="border: 0px; border-collapse: collapse; border-spacing: 0px; list-style-position: initial; list-style-image: initial; list-style-type: revert; margin: revert; padding: revert; display: inline-block; font: 18px / 22px Roboto, sans-serif; color: rgb(17, 17, 17); letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; white-space: normal; background-color: rgb(249, 249, 249); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;">パターンA：ユーザーごとに接続文字列を持つ</h3><ol style="border: 0px; border-collapse: collapse; border-spacing: 0px; list-style-position: initial; list-style-image: initial; list-style-type: revert; margin: revert; padding: revert; color: rgb(17, 17, 17); font-family: Roboto, Helvetica, sans-serif; font-size: 16px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; white-space: normal; background-color: rgb(249, 249, 249); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;"><li style="border: 0px; border-collapse: collapse; border-spacing: 0px; list-style-position: initial; list-style-image: initial; list-style-type: revert; margin-top: revert; margin-right: revert; margin-bottom: 8px; margin-left: revert; padding: revert;">共通の認証DB（例：<code style="border: 1px solid rgb(221, 221, 221); border-radius: 4px; padding: 1px 4px; background: rgb(255, 255, 255); font: 14px / 20px monospace;">AuthDb</code>）を用意し、<code style="border: 1px solid rgb(221, 221, 221); border-radius: 4px; padding: 1px 4px; background: rgb(255, 255, 255); font: 14px / 20px monospace;">AspNetUsers</code> テーブルに&nbsp;<code style="border: 1px solid rgb(221, 221, 221); border-radius: 4px; padding: 1px 4px; background: rgb(255, 255, 255); font: 14px / 20px monospace;">ConnectionString</code> カラムを追加</li><li style="border: 0px; border-collapse: collapse; border-spacing: 0px; list-style-position: initial; list-style-image: initial; list-style-type: revert; margin-top: revert; margin-right: revert; margin-bottom: 8px; margin-left: revert; padding: revert;">ログイン後、<code style="border: 1px solid rgb(221, 221, 221); border-radius: 4px; padding: 1px 4px; background: rgb(255, 255, 255); font: 14px / 20px monospace;">User.Identity.Name</code> または&nbsp;<code style="border: 1px solid rgb(221, 221, 221); border-radius: 4px; padding: 1px 4px; background: rgb(255, 255, 255); font: 14px / 20px monospace;">UserManager</code> からユーザー情報を取得</li><li style="border: 0px; border-collapse: collapse; border-spacing: 0px; list-style-position: initial; list-style-image: initial; list-style-type: revert; margin-top: revert; margin-right: revert; margin-bottom: 8px; margin-left: revert; padding: revert;">そのユーザーの接続文字列を使って&nbsp;<code style="border: 1px solid rgb(221, 221, 221); border-radius: 4px; padding: 1px 4px; background: rgb(255, 255, 255); font: 14px / 20px monospace;">DbContext</code> を生成</li></ol><h4 style="border: 0px; border-collapse: collapse; border-spacing: 0px; list-style-position: initial; list-style-image: initial; list-style-type: revert; margin: revert; padding: revert; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-variant-numeric: inherit; font-variant-east-asian: inherit; font-variant-alternates: inherit; font-variant-position: inherit; font-variant-emoji: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Roboto, Helvetica, sans-serif; font-optical-sizing: inherit; font-size-adjust: inherit; font-kerning: inherit; font-feature-settings: inherit; font-variation-settings: inherit; font-size: 16px; color: rgb(17, 17, 17); letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; white-space: normal; background-color: rgb(249, 249, 249); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;">サンプルコード（ASP.NET Core MVC + EF Core）</h4><pre data-priority="2" style="color: rgb(17, 17, 17); font-size: 16px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(249, 249, 249); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;"><code style="border: 1px solid rgb(221, 221, 221); border-radius: 8px; padding: 12px 20px; background: rgb(255, 255, 255); font: 14px / 20px monospace; display: block; white-space: pre-wrap; margin-right: 20px;">// ユーザー情報に接続文字列を持たせるモデル拡張
public class ApplicationUser : IdentityUser
{
 public string ConnectionString { get; set; }
}

// 動的にDbContextを生成するファクトリ
public class TenantDbContextFactory
{
 public TenantDbContext Create(string connectionString)
 {
 var options = new DbContextOptionsBuilder&lt;TenantDbContext&gt;()
 .UseSqlServer(connectionString)
 .Options;

 return new TenantDbContext(options);
 }
}

// ユーザー専用のDbContext
public class TenantDbContext : DbContext
{
 public TenantDbContext(DbContextOptions&lt;TenantDbContext&gt; options)
 : base(options) { }

 public DbSet&lt;Product&gt; Products { get; set; }
}

// コントローラ例
public class ProductsController : Controller
{
 private readonly UserManager&lt;ApplicationUser&gt; _userManager;
 private readonly TenantDbContextFactory _dbFactory;

 public ProductsController(UserManager&lt;ApplicationUser&gt; userManager, TenantDbContextFactory dbFactory)
 {
 _userManager = userManager;
 _dbFactory = dbFactory;
 }

 public async Task&lt;IActionResult&gt; Index()
 {
 var user = await _userManager.GetUserAsync(User);
 if (user == null || string.IsNullOrEmpty(user.ConnectionString))
 {
 return Unauthorized();
 }

 using var db = _dbFactory.Create(user.ConnectionString);
 var products = await db.Products.ToListAsync();
 return View(products);
 }
}
</code></pre><h3 style="border: 0px; border-collapse: collapse; border-spacing: 0px; list-style-position: initial; list-style-image: initial; list-style-type: revert; margin: revert; padding: revert; display: inline-block; font: 18px / 22px Roboto, sans-serif; color: rgb(17, 17, 17); letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; white-space: normal; background-color: rgb(249, 249, 249); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;">パターンB：DB名だけを切り替える</h3><ul style="border: 0px; border-collapse: collapse; border-spacing: 0px; list-style-position: initial; list-style-image: initial; list-style-type: revert; margin: revert; padding: revert; color: rgb(17, 17, 17); font-family: Roboto, Helvetica, sans-serif; font-size: 16px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; white-space: normal; background-color: rgb(249, 249, 249); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;"><li style="border: 0px; border-collapse: collapse; border-spacing: 0px; list-style-position: initial; list-style-image: initial; list-style-type: revert; margin-top: revert; margin-right: revert; margin-bottom: 8px; margin-left: revert; padding: revert;">接続文字列のテンプレートを&nbsp;<code style="border: 1px solid rgb(221, 221, 221); border-radius: 4px; padding: 1px 4px; background: rgb(255, 255, 255); font: 14px / 20px monospace;">appsettings.json</code> に置き、ユーザーごとにDB名を差し替える</li></ul><pre data-priority="2" style="color: rgb(17, 17, 17); font-size: 16px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(249, 249, 249); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;"><code style="border: 1px solid rgb(221, 221, 221); border-radius: 8px; padding: 12px 20px; background: rgb(255, 255, 255); font: 14px / 20px monospace; display: block; white-space: pre-wrap; margin-right: 20px;">{
 &quot;ConnectionStrings&quot;: {
 &quot;TenantTemplate&quot;: &quot;Server=.;Database={DBNAME};Trusted_Connection=True;&quot;
 }
}
</code></pre><ul style="border: 0px; border-collapse: collapse; border-spacing: 0px; list-style-position: initial; list-style-image: initial; list-style-type: revert; margin: revert; padding: revert; color: rgb(17, 17, 17); font-family: Roboto, Helvetica, sans-serif; font-size: 16px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; white-space: normal; background-color: rgb(249, 249, 249); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;"><li style="border: 0px; border-collapse: collapse; border-spacing: 0px; list-style-position: initial; list-style-image: initial; list-style-type: revert; margin-top: revert; margin-right: revert; margin-bottom: 8px; margin-left: revert; padding: revert;">ログイン後に&nbsp;<code style="border: 1px solid rgb(221, 221, 221); border-radius: 4px; padding: 1px 4px; background: rgb(255, 255, 255); font: 14px / 20px monospace;">{DBNAME}</code> をユーザー固有の値に置換して&nbsp;<code style="border: 1px solid rgb(221, 221, 221); border-radius: 4px; padding: 1px 4px; background: rgb(255, 255, 255); font: 14px / 20px monospace;">DbContext</code> を生成</li></ul><h3 style="border: 0px; border-collapse: collapse; border-spacing: 0px; list-style-position: initial; list-style-image: initial; list-style-type: revert; margin: revert; padding: revert; display: inline-block; font: 18px / 22px Roboto, sans-serif; color: rgb(17, 17, 17); letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; white-space: normal; background-color: rgb(249, 249, 249); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;">パターンC：スキーマ分離（1DB内でユーザーごとにスキーマを分ける）</h3><ul style="border: 0px; border-collapse: collapse; border-spacing: 0px; list-style-position: initial; list-style-image: initial; list-style-type: revert; margin: revert; padding: revert; color: rgb(17, 17, 17); font-family: Roboto, Helvetica, sans-serif; font-size: 16px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; white-space: normal; background-color: rgb(249, 249, 249); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;"><li style="border: 0px; border-collapse: collapse; border-spacing: 0px; list-style-position: initial; list-style-image: initial; list-style-type: revert; margin-top: revert; margin-right: revert; margin-bottom: 8px; margin-left: revert; padding: revert;">DBは1つだが、ユーザーごとに異なるスキーマを使う</li><li style="border: 0px; border-collapse: collapse; border-spacing: 0px; list-style-position: initial; list-style-image: initial; list-style-type: revert; margin-top: revert; margin-right: revert; margin-bottom: 8px; margin-left: revert; padding: revert;">接続文字列は共通だが、<code style="border: 1px solid rgb(221, 221, 221); border-radius: 4px; padding: 1px 4px; background: rgb(255, 255, 255); font: 14px / 20px monospace;">OnModelCreating</code> でスキーマを切り替える</li></ul><h2 style="border: 0px; border-collapse: collapse; border-spacing: 0px; list-style-position: initial; list-style-image: initial; list-style-type: revert; margin: revert; padding: revert; font: 18px / 24px Roboto, sans-serif; color: rgb(17, 17, 17); letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; white-space: normal; background-color: rgb(249, 249, 249); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;">3. 注意点</h2><ul style="border: 0px; border-collapse: collapse; border-spacing: 0px; list-style-position: initial; list-style-image: initial; list-style-type: revert; margin: revert; padding: revert; color: rgb(17, 17, 17); font-family: Roboto, Helvetica, sans-serif; font-size: 16px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; white-space: normal; background-color: rgb(249, 249, 249); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;"><li style="border: 0px; border-collapse: collapse; border-spacing: 0px; list-style-position: initial; list-style-image: initial; list-style-type: revert; margin-top: revert; margin-right: revert; margin-bottom: 8px; margin-left: revert; padding: revert;">接続プール：ユーザーごとに異なる接続文字列を使うと、接続プールが分かれるためメモリ消費が増える</li><li style="border: 0px; border-collapse: collapse; border-spacing: 0px; list-style-position: initial; list-style-image: initial; list-style-type: revert; margin-top: revert; margin-right: revert; margin-bottom: 8px; margin-left: revert; padding: revert;">マイグレーション管理：ユーザーごとにDBを作る場合、マイグレーションを自動適用する仕組みが必要</li><li style="border: 0px; border-collapse: collapse; border-spacing: 0px; list-style-position: initial; list-style-image: initial; list-style-type: revert; margin-top: revert; margin-right: revert; margin-bottom: 8px; margin-left: revert; padding: revert;">セキュリティ：接続文字列は暗号化して保存（例：Azure Key Vault、User Secrets）</li><li style="border: 0px; border-collapse: collapse; border-spacing: 0px; list-style-position: initial; list-style-image: initial; list-style-type: revert; margin-top: revert; margin-right: revert; margin-bottom: 8px; margin-left: revert; padding: revert;">スケーラビリティ：ユーザー数が多い場合、DB数が増えすぎると管理が困難になる</li></ul><p style="border: 0px; border-collapse: collapse; border-spacing: 0px; list-style-position: initial; list-style-image: initial; list-style-type: revert; margin: revert; padding: revert; -webkit-line-clamp: revert; color: rgb(17, 17, 17); font-family: Roboto, Helvetica, sans-serif; font-size: 16px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; white-space: normal; background-color: rgb(249, 249, 249); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;">✅&nbsp;おすすめ構成<ul style="border: 0px; border-collapse: collapse; border-spacing: 0px; list-style-position: initial; list-style-image: initial; list-style-type: revert; margin: revert; padding: revert; color: rgb(17, 17, 17); font-family: Roboto, Helvetica, sans-serif; font-size: 16px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; white-space: normal; background-color: rgb(249, 249, 249); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;"><li style="border: 0px; border-collapse: collapse; border-spacing: 0px; list-style-position: initial; list-style-image: initial; list-style-type: revert; margin-top: revert; margin-right: revert; margin-bottom: 8px; margin-left: revert; padding: revert;">ユーザー数が少ない &rarr;&nbsp;DB分離型（パターンA）</li><li style="border: 0px; border-collapse: collapse; border-spacing: 0px; list-style-position: initial; list-style-image: initial; list-style-type: revert; margin-top: revert; margin-right: revert; margin-bottom: 8px; margin-left: revert; padding: revert;">ユーザー数が多い &rarr;&nbsp;スキーマ分離型（パターンC） または&nbsp;テーブル分離型</li></ul><p style="border: 0px; border-collapse: collapse; border-spacing: 0px; list-style-position: initial; list-style-image: initial; list-style-type: revert; margin: revert; padding: revert; -webkit-line-clamp: revert; color: rgb(17, 17, 17); font-family: Roboto, Helvetica, sans-serif; font-size: 16px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; white-space: normal; background-color: rgb(249, 249, 249); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;">もし希望があれば、この仕組みを&nbsp;ASP.NET Core Identity + EF Core で「ログイン後に自動でユーザー専用DBを作成する」コードも作れます。 これを実装すると、初回ログイン時にDBが自動生成されるようになります。<p style="border: 0px; border-collapse: collapse; border-spacing: 0px; list-style-position: initial; list-style-image: initial; list-style-type: revert; margin: revert; padding: revert; -webkit-line-clamp: revert; color: rgb(17, 17, 17); font-family: Roboto, Helvetica, sans-serif; font-size: 16px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; white-space: normal; background-color: rgb(249, 249, 249); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;">作りますか？Powered by <a href="https://www.froala.com/wysiwyg-editor?pb=1" title="Froala Editor">Froala Editor</a>

添付画像

画像を変更

Back to List

//検索文：Editor内でHTMLコードをコピーさせる