Orchard CMS includes a very simple but powerful module that allows you to host any number of independent tenant websites using a single Orchard installation and IIS site. The sites are independent in that they all require their own set of orchard database tables, albeit they share an appdomain. Instructions for setting up multi-tenancy are provided by Orchard here.

Two of the options required for setting up the tenant site are a database connection-string and a table prefix. A prefix isn't needed if each tenant is going to have their own database. If tenant sites are going to share a database then one option (we'll come to another option later) is to use a prefix - this will prepend all table names with the supplied prefix. Even modules installed later on that create their own tables will also have prefixed tables - as long as they follow the Orchard/NHibernate approach for building tables. Note that there isn't an option to use a single set of tables to manage all tenants - as there is with Kentico for example, where all tables are keyed on a SiteID column.

Separate Databases

The benefits of having a separate database for each tenant include the flexibility around being able to manage security, performance and backup/restore operations etc in splendid isolation - you can move the database from one storage location to another, or even different servers without impacting any other tenant site for example. However, such flexibility and isolation comes at a price - and that is manageability: now you might have separate scripts and jobs to manage backups/alerts/monitoring etc. You might also not have the luxury of using multiple databases with your hosting provider.

Shared Database

However, given that you've decided on a multi-tenancy option to begin with and sites will already share a web server and appdomain, you're probably OK with sites sharing a database too. Seemingly then you're stuck with table prefixes.

This option definitely works, and I haven't come across any technical issues per se that would preclude using prefixes - other than the potential usage of a non-Orchard third-party module/application that doesn't allow for prefixed table names etc.

However, this model does compromise security somewhat. In a single-database single-schema scenario, managing different database users so that they have different object-level permissions and can only view/manage tables that they created, all in the same schema, is probably nigh-on impossible/unworkable. And whilst you might consider the risk low, there would be nothing preventing a malicious/badly-written/badly-designed piece of code from stealing/trashing data for all the tenants in your instance.

Having prefixed tables also means that you can't simply run the same sql script for each tenant; you have to copy/paste and replace prefixes - or write some clever SP to take care of it for you.

Shared Database - Multiple schemas

The easiest solution we've found to managing the single-database scenario for Orchard multi-tenancy is to use a separate database schema for each site, with a separate login and user to match and then grant a role to each user to govern permissions. This only applies to Sql Server - Sql Compact doesn't support schemas.

We initially created a role with these basic permissions and execute it once on each new database we create:

/* Create the Role */
USE [Orchard_DevDB]
CREATE ROLE [Orchard_Site_Role] AUTHORIZATION [dbo]
GO
/* Add the permissions */
GRANT CREATE TABLE TO [Orchard_Site_Role]
GRANT CREATE FUNCTION TO [Orchard_Site_Role]
GRANT CREATE PROCEDURE TO [Orchard_Site_Role]
GRANT CREATE TYPE TO [Orchard_Site_Role]
GRANT CREATE VIEW TO [Orchard_Site_Role]
GO

And then for each new tenant we are about to create, we run a version of this script to create a new schema, login and user specifically for that tenant:

USE [master]
CREATE LOGIN [Orchard_DevDB_TestUserA] WITH PASSWORD=N'#####', DEFAULT_DATABASE=[Orchard_DevDB], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
USE [Orchard_DevDB]
CREATE USER [TestUserA] FOR LOGIN [Orchard_DevDB_TestUserA] WITH DEFAULT_SCHEMA=[TestUserA]
ALTER ROLE [Orchard_Site_Role] ADD MEMBER [TestUserA]
CREATE SCHEMA [TestUserA] AUTHORIZATION TestUserA]
GO

Notice that we use the same name for the schema and user as they will be unique in each database, but prefix the login with the database name to make them unique on each server.

Once this tenant-specific script has been executed, go ahead and create the tenant from the host admin site, without the need for a table prefix and specifying the connection-string i.e.

Data Source=<DbServer>;Initial Catalog=Orchard_DevDB;Persist Security Info=True;User ID=TestUserA;Password=#####

Now you can reuse sql scripts across tenants if necessary and although this hasn't helped with backup/restore isolation directly, at least we now have a 'contained' set of database objects that can be managed with a single login. This login won't have permission to alter database objects for any other tenants, helping to safeguard your tenants' data.

As always, the decisions on how to split up sites and their databases will depend on the time/money/resources at your disposal to set things up and maintain them. Hopefully this article provides a useful compromise between full isolation and full shared access.

No Comments