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 multitenant architecture, or multitenancy.
The alternative to a multitenant system is a shared (or single-tenant) architecture, where multiple users query and store data in the same, shared tables. In contrast, in a multitenant design, each customer is more isolated.
This makes security less of a headache, and can make it easier to divide and optimize computing resources.
Multitenancy 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 multitenant 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
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:
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.
You must be logged in to post a comment.