There are two main means of achieving the design goals of a multitenant database system – in Part one, we outlined the issues when using a physical database separation design. Here we will look at logical separation design and the suggested implementation techniques.
Logical Separation: Less secure, but provides easier data aggregation
If our applications design goals and business rules specify that different tenants will need access to either common data infrastructure (say, an inventory parts list, for example), then our database design choices lean towards sharing, at least in part, some physical database structures.
In general, if the business rules or aggregation requirements are paramount, or the data storage requirements are smaller, then it is both simpler and easier to share physical database structures, where practical. This can include designing “wider” data tables than needed for a single tenant so that additional fields needed by another tenant can share the same core table structures without incurring the overhead of additional data tables (and their indexes and relational integrity declarations et. al.).
We’ll consider two possible approaches. First approach works well when we are using a user table; managing logins within the application and using a shared SQL Login along with TempVars. Second approach assumes that each user has their own SQL Login; allowing us to use Views to enforce filtering.
Technique 1: Use one SQL Server login for the entire app
At our firm we specialize in multi-tenant apps, (give us a call if you rather we take care of this for you!), and we almost always use a user table to authenticate users and just one login for all users. The login is used in VBA code to initiate the connection to SQL Server and to link tables. Users are managed via a tblUsers table and when they login they don’t do so to SQL Server but rather through code that validates their credentials via the table. All SQL Server sees is the same user name for all users.
TempVars makes it all happen
To enable data separation in your app you’re going to need to do so at all levels: Forms, Reports, Queries and code, and the easiest and most reliable way to do so is with TempVars. (Click here to learn more about them).
Step 1: Use a login form to identify the user and limit their data exposure
Quite frequently we design apps where users can login to one identity, (office, franchise or division for example), by either picking from a list or just loading their segment if they are only authorized for just one. In doing so we set the controlling TempVar that will dictate the data they will see going forward in the app.
For example, in one application we had a client with “soft” data segregation requirements between two separately managed business locations. Employees were assigned to a particular office by default, but could do work for other offices or for customers of another office.
The primary means of segregating data in this case was an OfficeID (a primary key from an tblOffices table). So at login, the users’s default office is retrieved from the database and saved in a new TempVars variable using this syntax:
TempVars.Add “OfficeID”, lngUserOfficeID
Thereafter, the value can easily be referenced by using:
TempVars(“OfficeID”) or TempVars!OfficeID
Step 2: Design your queries, forms and reports to load with the TempVar
Design all of your primary objects to use a where clause with your limiting TempVar. If it’s a query then use the TempVar as a criteria, for example:
Select * From tblCustomers Where OfficeID = TempVars!OfficeID
Notice how ACE understands the use of TempVars in where clauses.
Design your main forms and reports to use a query source and use the TempVar as a where clause too in their data source property. Sub forms/reports don’t need them if you are binding the sub to it’s parent via a link on the Primary/Secondary keys of each underlying table. In other words, don’t go wild adding the where clause to every subform’s recordsource just so that you can use the TempVar when all you need to do is use linking.
Step 3: Use ADODB and TempVars to retrieve correct data from SQL Server
Quite often we need to tap into data behind the scenes with ADODB and in this case we do so using the following syntax:
Dim strSQL as String
strSQL = “SELECT COUNT(OrderID) AS Qty FROM tblOrders WHERE OfficeID = ” & TempVars!OfficeID
SQL Server does not understand TempVars, so you need to use the syntax above to pass along the correct where clause.
Avoid use of filtering at all costs
One method I would specifically NOT recommend is using or relying on Filter expressions within forms or reports – these are simply too fragile and insecure an enforcement mechanism to be considered at all reliable.
Technique #2: Using SQL Views & SQL Logins – best for separation
The absolute best way to avoid data corruption and data exposure to unauthorized users is by using SQL Server Logins, a user table and a third table. It moves all of the filtering logic to server side by using only views, never direct links to tables in your app. The views would then have a common filtering criteria such as:
CREATE VIEW dbo.vwCustomers AS
SELECT c.*
FROM dbo.tblCustomers AS c
INNER JOIN dbo.tblUsers AS u
ON c.FranchiseID = u.FranchiseID
WHERE u.UserID = CURRENT_USER AND u.InUse = 1;
We use the built-in function CURRENT_USER which will return the name of logged in user. In this example, all users work for a franchise so we link the customers to users via their mutual FranchiseID. This approach thus always ensure that user will be always viewing a filtered listing of the data table and there is no need to perform the filtering in Access at all. Further, if the database falls in the wrong hands and someone opens the linked view they get nothing, since they don’t have a SQL Server login and no data is returned back from the server. We also set InUse when a user has logged into the App, allowing the system to return data only if the user is logged into the app.
A word on table links: Don’t even think of leaving them unprotected
If your database includes links directly to the data tables, (something to avoid almost entirely with a multitenant application), and you don’t programmatically add or remove them when the app loads or terminates, then for all practical purposes there is no protection or isolation of the data from one tenant to another. Any expert Access programmer can bypass your security measures to get to the tables and compromise your data. Either use our DSN-less destroy link technique or Ben’s don’t store password methodology.
Building powerful solutions using multi-tenancy
We love multi-tenancy applications and do them all the time, and when done right it can be a boon to productivity since all users are using just one backend data storage solution, (either Access or SQL Server), with just one frontend design. I encourage you to master this technique and offering your clients the same advantages.
