Community

What is a multi-tenant database architecture? How is it done in dashDB and DB2?

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 IBM dashDB and DB2

Multi-tenancy is easy in DB2 and dashDB. 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, dashDB only allows multiple schemas, and not multiple databases.

DB2 and dashDB also provide row-level access control, and even column-level access control, to further refine access in both a shared or multi-tenant environment. See Row and Column Access Control in dashDB for more details.

Note for MySQL users: In MySQL, a “database” and a “schema” refer to the same thing. In IBM dashDB 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.
  • Consider the trade-offs between dividing based on table, schema or database. Each has it’s own advantage. For dashDB for Transactions, 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 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.

Conclusion

With dashDB and DB2, 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.

More Community stories

Watson Data Kit New Beta Planned, Current Beta is Ending

Announced at IBM Think in March, Watson Data Kits will provide licensed, machine-readable data to train Watson in the nuances of specific industries and use cases. The  kits are being designed to  reduce AI training time from months to as little as minutes, accelerating AI development to support faster, more-informed decision making for business leaders. Learn more here.

Continue reading

Common IBM Cloud ID and Billing Questions: Part 2

You expect and deserve answers to questions as quickly as possible so that you can move forward with your business. In Bluemix Support, we receive a number of similar questions involving account changes, billing, and login issues. As we see patterns, we update our externally published FAQs to help you address questions without needing to open a ticket. However, if you need to open a ticket, we will address it as quickly as possible based on the documented severity levels in our Getting customer support information. In conjunction with my April 2016 Common Bluemix ID and billing questions article, here are some questions and answers:

Continue reading

App Development for iOS is just plain easier on IBM Cloud

Digging through layers of cloud-integration documentation before even opening Xcode is precious time that you could spend building apps. In March 2018, Apple and IBM launched the IBM Cloud Developer Console for Apple to provide the fastest route to coding. It offers everything that you need to create a full-stack, production-ready cloud native application, no digging required.

Continue reading