I have been evaluating Open Access for a MS SQL Server based multi-tenant project and have run into a problem described below.
My question is what is the recommended approach for using Open Access in a multi-tenant environment?
The details …
Our preference is for 1) tenants to store data in shared tables with a tenantId column to identify which rows belong to which tenants 2) To use triggers to set the TenantId as rows are inserted 3) to use views to make sure tenants only see their data
I have experimented with a combination of using the connection string and sql connection context to pass the tenant id and user name into SQL Server to use in the triggers and views. My preference would be to use SQL context to pass data in – to avoid passing everything on the SQL connection string and so end up having many SQL connections.
I have extended my entity model as shown below to set the SQL context by calling a stored proc call SetContextInfo in an overridden OnDatabaseOpen method
public partial class db
{
public string tenantId;
public string tenantUserId;
public db(string connection, string TenantId, string TenantUserId)
: base(connection, backend, metadataSource)
{
this.tenantId = TenantId;
this.tenantUserId = TenantUserId;
}
protected override void OnDatabaseOpen(Telerik.OpenAccess.BackendConfiguration backendConfiguration, Telerik.OpenAccess.Metadata.MetadataContainer metadataContainer)
{
string contextCmdText = String.Format(@"[Core].[SetContextInfo] '{0}', '{1}'", tenantId, tenantUserId);
backendConfiguration.ConnectionPool.InitSQL.Add(contextCmdText);
base.OnDatabaseOpen(backendConfiguration, metadataContainer);
}
}
I have a test rig repeatedly calling the test code below passing in different tenant Ids each time
string connectionString = "TestConnection";
using (db dbContext = new db(connectionString, TenantId, TenantUserName))
{
var person = new Person();
person.FirstName = FirstName;
person.LastName = LastName;
dbContext.Add(person);
dbContext.SaveChanges();
}
I am finding that the entity model constructor gets called correctly every time my overridden OnDatabaseOpen method to set the connect context does not get called every time - hence my inserted row is not allocated to the correct tenant.
So is there a better approach than this one?
Sorry for the long post – thanks for your help
Mike