This set of articles was first published on my Sybase blog in 2009. Since that time, this 5 part series has been one of the most requested set of articles. As part of Sybase's integration with SAP, I am republishing them on the SAP SCN, and taking the opportunity to update them.
In the previous posts (Part 1, Part 2, and Part 3), I outlined the factors to consider, and 3 possible architectures for implementing a multi-tenant database system. The 3 models used independent database files for each tenant. In this fourth post, I will outline a model where the isolation provided by separate database files is removed, and multiple tenants share a single database. Data isolation is still preserved by storing each tenant’s data in a separate schema. By this, I mean that each tenant has their own set of tables. Most major DBMS systems provide some mechanism for storing multiple schemas, however my examples will use SQL Anywhere.
Shared Database, Separate Schema
In this architecture, each tenant has their own set of tables stored in a separate schema. Each database server will only serve up one database. The login credentials used to connect to the database by each user will determine which tenant’s data that user will be allowed to access. It is important that the DBMS have a permission structure in place to ensure users only have access to the data to which they are entitled.
An evaluation of the decision factors shows that this architecture has many of the benefits of the separate database models, while minimizing some of the negative aspects:
- Development time – There is minimal additional development time. Depending on the specific DBMS support for different schemas, there may a few additional instructions after connection to ensure that the user is accessing their correct tables and data.
- Hardware cost – This architecture allows for improved utilization of machine memory resources.
- Application and database performance – The performance of one tenant may be impacted by the activities of other tenants sharing the server machine.
- Security – The DBMS must ensure that its permission structure is such that each tenants data is only available to authorized users. As well, the application must make use of any required commands to select or restrict the schema that be accessed by a user.
- Customization requirements – Each tenant has their own schema, so it is easy to customize it for the differing needs of each tenant.
- The number of tenants – This model is able to handle more tenants than the separate database models, but does still require a certain amount of administration . Migration of tenants to a separate database may be challenging depending on the utilities that are offered by the DBMS.
SQL Anywhere uses the concept of Groups to implement separate schemas. A SQL Anywhere GROUP can be used for two reasons:
- To establish a hierarchy of database object permissions.
- To enable access to group owned objects without specifying the qualifying owner name.
To create a new group called Tenant1 that will own the schema for a tenant, you could use these commands:
GRANT CONNECT TO Tenant1;
GRANT RESOURCE TO Tenant1;
GRANT GROUP TO Tenant1;
Now that the group is created, a user with permissions to create tables for other users could create a table in the new schema by specifying the owner on the CREATE TABLE command:
CREATE TABLE Tenant1.Employees
(
EmployeeID INTEGER NOT NULL,
ManagerID INTEGER NULL,
Surname CHAR(20) NOT NULL,
GivenName CHAR(20) NOT NULL,
DepartmentID INTEGER NOT NULL,
Street CHAR(30) NOT NULL,
City CHAR(20) NOT NULL,
State CHAR(16) NULL,
Country CHAR(16) NULL,
CONSTRAINT EmployeesKey PRIMARY KEY (EmployeeID)
);
Just a few more commands to allow a given user to access the new table:
GRANT SELECT, INSERT, DELETE, UPDATE ON Tenant1.Employees TO Tenant1;
GRANT CONNECT TO T1User IDENTIFIED BY T1pass;
GRANT MEMBERSHIP IN GROUP Tenant1 TO T1User;
CONNECT AS T1User IDENTIFIED BY T1pass;
SELECT * FROM Employees;
This model can be easily demonstrated using SQL Anywhere.
- SAP Managed Tags:
