October 11, 2016 | Written by: Simon Lightstone
Categorized: Community | Data Analytics
Share this post:
When you have an SQL database that deals with multiple users, there’s a tough choice to make over how you set up and access your tables to provide security. One model that’s growing in adoption is to give each user their own, separate schema, database or set of tables. This is called a multi-tenant architecture, or multi-tenancy.
The alternative to a multi-tenant system is a shared (or single-tenant) architecture, where multiple users query and store data in the same, shared tables. In contrast, in a multi-tenant design, each customer is more isolated.
This makes security less of a headache, and can make it easier to divide and optimize computing resources.
Multi-tenancy Support in Db2
Multi-tenancy is easy in Db2 and Db2 on Cloud. In fact, they are among the few databases that provide enough security functionality to deeply address the issues, and let programmers build a totally contained app.
Keep in mind that, at this time, Db2 on Cloud and Db2 Warehouse on Cloud only allows multiple schemas, and not multiple databases.
Db2 and Db2 on Cloud also provide row-level access control, and even column-level access control, to further refine access in both a shared or multi-tenant environment.
Note for MySQL users: In MySQL, a “database” and a “schema” refer to the same thing. In IBM Db2 on Cloud and Db2, one database supports multiple schemas inside of it. This helps all schemas benefit from shared configurations and optimizations.
Security levels on a schema have separate permissions for create (CREATEIN), modify existing (ALTERIN) and delete (DROPIN). A basic example would be:
GRANT CREATEIN ON SCHEMA MY_SCHEMA TO JOHN_DOE;
You can also set up a user that has permissions on a schema and can also grant permissions to other users:
GRANT ALTERIN ON SCHEMA CUSTOMER_COMPANY TO CUSTOMER_ADMIN WITH GRANT OPTION;
For official documentation on schema-level security, see SQL Statements: GRANT (schema privileges).
For greater speed, but less isolation, you can alternatively divide up your users by using separate tables with access controls inside the same schema. For example:
GRANT SELECT, INSERT ON MY_CUSTOMER_TABLE TO USER SARAH, USER JOHN
A few tips for building multi-tenant architectures
- Remember: Usernames and company names change: A common mistake is to name each database, schema or table according to a company name or username. Factor in that usernames and company names often change. For example, either use a lookup table or have an extra value called “official_name” to use as a company name for login or visual display. Also consider exposure of this data in logs when considering privacy laws.
- Consider the trade-offs between dividing based on table, schema or database. Each has it’s own advantage. For Db2 on Cloud, the current recommendation is to leverage multiple schemas, which provides a balance between isolation and costs. However, dividing based on tables can provide performance advantages.
Understanding the choice between multi-tenancy and shared tenancy
When the web was younger, shared tenancy ruled the day: Databases had weaker security models built in and it was extremely common to create a single database user who could access anything in the database. The code, written in early web languages such as Perl or PHP, would use this single database user to read and write all data for all users to your database tables. It was up to the programmer to think of every corner case to prevent one user from seeing something he or she shouldn’t. Security became a huge problem, especially with SQL injections.
The following on some of the pros/cons of shared tenancy:
- Harder to isolate heavy users: Shared tenancy also makes it hard to scale resources based on customer usage. For example, suppose you built an app that does invoicing. Inevitably, some customers would be large organizations with millions of invoices, and require tons of computing power. But scaling for this customer, when all their data is jumbled into the same tables as everyone else, gets very difficult.
- Lower development costs: There are so many corner cases with a shared database model and security, that to deal with each and every one would begin to take a toll on development. Initially, progress is faster, but the release to production can be plagued with issues, especially for sensitive business apps.
- Is shared tenancy ever desirable? Shared tenancy is sometimes a desired model. The main benefit tends to be performance and a perceived simplification. For heavily consumer-focused apps, where most data is shared, it can be a better choice.
With Db2 and Db2 on Cloud, because your database permissions can perfectly match your actual intention, it can make development, APIs and integration go much, much faster.
In the long term, it’s possible that shared tenancy technologies like ACLs and row-based permissions once again become a better option. But today, a multitenant architecture for SQL is typically a safer bet.