CODEWITHSUNDEEP
sql-serversql-server-2008database-designmulti-tenant
Tekito
Tekito

Reputation: 860

SQL Server - robust protection of client data (multi-tenancy)

We are considering using a single SQL Server database to store data for multiple clients. We feel having all the data in one database could make things more manageable than a "separate db per client" setup.

The biggest concern we have is accidental access to the wrong client. It would be very, very bad if we were to ever accidentally show one client's data to another client. We perform lots of queries, and are afraid of a scenario where someone says "write me a query of this and this to go show the client for the meeting in 15 minutes." If someone is careless and omits the WHERE clause that filters for the correct client then we would be in serious trouble. Is there a robust setup or design pattern for SQL Server such that it makes it impossible (or at least very difficult) to accidently pull the wrong client's data from a single "global" database?

To be clear, this is NOT a database that the clients use directly or via apps (yet). We are talking about a database accessed by several of our programmers and we are afraid of screwing up ourselves.

Upvotes: 4

Views: 1374

Answers (4)

maximnl
maximnl

Reputation: 1

You can limit access to data only via storedprocedures with obligatory customerid parameter. If you allow you IT build views sooner or later someone forget this where clause as you said. But a schema per client with already prefiltered views will enable selfservice and extra Brings value i guess.

Upvotes: 0

Neil McGuigan
Neil McGuigan

Reputation: 48256

Multi-Tenant Data Architecture http://msdn.microsoft.com/en-us/library/aa479086.aspx

here's what we do (mysql unfortunately):

  1. "tenant" column in each table
  2. tables are in one schema [1]
  3. views are in another schema (for easier security and naming). view must not include tenant column. view does a WHERE on the tenant based on current user
  4. tenant value is set by trigger on insert, based on the user

Assuming that all your DDL is in .sql files under source control (which it should be), then having many databases or schemas is not so tough.

[1] a schema in mysql is called a 'database'

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269753

At the very minimum, you should put the client data in separate schemas. In SQL Server, schemas are the unit of authorization. Only people authorized for a given client should be able to see that client's data. In addition to other protections, you should be using the built-in authorization capabilities of the database.

Right now, it sounds like you are in a situation where a very small group of people are the ones accessing all the data for everyone. Well, if you are successful, then you will probably need more people in the future. In fact, you might be giving some clients direct access to the data. If it is their data, they will want apps running on it.

My best advice, if you are planning on growing, is to place each client's data in a separate database. I would architect the system so this database can be on a remote server. If it needs to synchronize with common data, then develop a replication strategy for moving that data around.

You may think it is bad to have one client see another client's data. From the business perspective, this is deadly -- like "company goes out of business, no job" deadly. Your clients are probably more concerned about such confidentiality than you are. And, an architecture that ensures protection will make them more comfortable.

Upvotes: 2

usr
usr

Reputation: 171178

You could set up one inline table valued function for each table that takes a required parameter @customerID and filters that particular table to the data of this customer. If the entire app were to use only these TVP's the app would be safe by construction.

There might be some performance implications. The exact numbers depend on the schema and queries. They can be zero, however, as inline TVP's are inlined and optimized together with the rest of the query.

Upvotes: 1

Related Questions