public static string GetTenantConnectionString(int tenantId) { try { return(_csMap.GetOrAdd(tenantId, (id) => { EFDAL.Entity.TenantMaster item = null; using (var context = new MultiTenantEntities(DatabaseHelper.MasterConnectionString)) { item = context.TenantMaster.FirstOrDefault(x => x.TenantId == id); if (item == null) { throw new Exception("Tenant not found"); } } var builder = new SqlConnectionStringBuilder(MasterConnectionString); builder.UserID = item.DbUserName; builder.Password = SecurityDomain.Decrypt(item.DbPassword); builder.IntegratedSecurity = false; return builder.ToString(); })); } catch (Exception ex) { Logger.LogError(ex); return(null); } }
public static bool CreateTenant(string tenantName) { //Create a new tenant if the name does not exist using (var context = new MultiTenantEntities(DatabaseHelper.MasterConnectionString)) { //Check for tenant existence if (context.TenantMaster.Any(x => x.TenantName == tenantName)) { throw new Exception("The tenant already exists."); } //Create a SQL database user and store the creds for this tenant var newUser = Utilities.GetRandomString(); var password = Utilities.GetRandomString(); var passwordCipher = SecurityDomain.Crypt(password); var newItem = new TenantMaster { TenantName = tenantName, DbUserName = newUser, DbPassword = passwordCipher, }; context.Add(newItem); context.SaveChanges(); //Create database permissions //Note: cannot use parameters as these are not data queries. They are DDL var sb = new StringBuilder(); sb.AppendLine($"CREATE LOGIN [{newItem.DbUserName}] WITH PASSWORD=N'{password}'"); sb.AppendLine($"if not exists(select * from sys.sysusers where name = '{newItem.DbUserName}')"); sb.AppendLine($"CREATE USER [{newItem.DbUserName}] FOR LOGIN [{newItem.DbUserName}];"); //The "TenantRole" was created in a custom script in the SQL installer in the "6_UserDefinedFinalize\Always" folder sb.AppendLine("GRANT DELETE TO [TenantRole];"); sb.AppendLine("GRANT INSERT TO [TenantRole];"); sb.AppendLine("GRANT SELECT TO [TenantRole];"); sb.AppendLine("GRANT UPDATE TO [TenantRole];"); sb.AppendLine($"ALTER ROLE [TenantRole] ADD MEMBER [{newItem.DbUserName}];"); DatabaseHelper.ExecuteSql(sb.ToString()); } return(true); }
static void Main(string[] args) { //**************************************** //The EFDAL project is generated //The Installer project is generated //Teh API project is hand written containing a few utilities to create tenants //The Console app //For now this will store the tenant ID in a variable. //In production you would use a session variable or some other login management technique. var tenant1Id = 0; var tenant2Id = 0; //Connect to the database with the master connection string //There is no tenant information in this connection string using (var context = new MultiTenantEntities(DatabaseHelper.MasterConnectionString)) { var tenant1Name = "Tenant1"; var tenant2Name = "Tenant2"; #region Create Tenants IF EMPTY if (!context.TenantMaster.Any()) { //Create Tenant 1 TenantDomain.CreateTenant(tenant1Name); Console.WriteLine($"Created {tenant1Name}"); //Create Tenant 2 TenantDomain.CreateTenant(tenant2Name); Console.WriteLine($"Created {tenant2Name}"); } #endregion //Pull each Tenant ID from the database for testing tenant1Id = context.TenantMaster.FirstOrDefault(x => x.TenantName == tenant1Name).TenantId; tenant2Id = context.TenantMaster.FirstOrDefault(x => x.TenantName == tenant2Name).TenantId; } //Now connect with the connection string for Tenant1 using (var context = new MultiTenantEntities(DatabaseHelper.GetTenantConnectionString(tenant1Id))) { //Add "Project 1" through "Project 10" for (var ii = 1; ii <= 10; ii++) { var newProject = new Project { Name = $"Project {ii}", ProjectTypeValue = ProjectTypeConstants.CSharp }; context.AddItem(newProject); } context.SaveChanges(); } //Now connect with the connection string for Tenant2 using (var context = new MultiTenantEntities(DatabaseHelper.GetTenantConnectionString(tenant2Id))) { //Add "Project 11" through "Project 20" for (var ii = 11; ii <= 20; ii++) { var newProject = new Project { Name = $"Project {ii}", ProjectTypeValue = ProjectTypeConstants.Java }; context.AddItem(newProject); } context.SaveChanges(); } //Now select ALL Projects using the Tenant1 connection string using (var context = new MultiTenantEntities(DatabaseHelper.GetTenantConnectionString(tenant1Id))) { var list = context.Project.ToList(); Console.WriteLine($"Tenant 1 Project Count={list.Count}"); foreach (var item in list) { Console.WriteLine($"Tenant 1, Project ={item.Name}"); } //Notice that there are only 10 items even with no Where clause //The framework limits the selection to only items for Tenant 1. //There is never any data leak between tenants } //Now select ALL Projects using the Tenant2 connection string using (var context = new MultiTenantEntities(DatabaseHelper.GetTenantConnectionString(tenant2Id))) { var list = context.Project.ToList(); Console.WriteLine($"Tenant 2 Project Count={list.Count}"); foreach (var item in list) { Console.WriteLine($"Tenant 2, Project ={item.Name}"); } //Notice that there are only 10 items even with no Where clause //The framework limits the selection to only items for Tenant 2. //There is never any data leak between tenants } }