Multi-tenant data isolation, row level security, best-practices


i read through the web but can’t seem to find a real answer, so here is my question:

In a multi-tenant setup where all tenant’s would have their data in a single database (separated by a tenant-id column spread through all tables in the schema) - what are my options to isolate/enforce security so that one tenant will never be able to read/write data from other tenants?

One viable option in other/some RDBMS is to use the row-level-security feature and make it evaluate a session variable set to the tenant id, etc. but reading through the docs it seems CockroachDB does not (yet?) support this feature. Is this planned or already on the roadmap?

Reading through the first paragraph states:
“In CockroachDB, privileges are granted to users at the database and table levels. They are not yet supported for other granularities such as columns or rows.”

Is my assumption correct, there is currently no way to realize such behaviour on the database security level using grants/privileges (e.g.: having a user per tenant but use grants/privileges on row level)?

What would be the recommended “best-practice” do implement such a scenario? Would i have to put a layer on top of CockroachDB not using any of the database security model at all?

Can someone please shed some light on this topic or point me to some documentation?

Thanks & Cheers,

1 Like

Do they have to share the same table (unique constraints over the data, or some have query all rights)? Would one option be to make the tenant part of the table name or database name and have multiple tables instead of using the tenant id as a column?

Hi Daniel,

As you’ve found in the docs, CockroachDB does not yet support row-level privileges. As far as I’m aware, we haven’t yet planned when we’ll get to it, so it won’t be in 1.1 and probably won’t be in 1.2 unless we encounter a lot of demand for it over the next few months.

In the meantime, though, have you considered using views? I haven’t tried it myself, but I think you should be able to lock off the underlying table but provide a view that exposes the relevant rows. Something like:

CREATE VIEW t123 AS SELECT col1, col2, ..., colN FROM t WHERE tenantid = 123;
GRANT SELECT ON t123 TO tenant123;

Thanks for your feedback, but i they should share the same table. Not having a tenant id inside the table would mean an increased effort for schema maintenance/updates/migrations when the amount of tenants (and tables in that scenario) grow. Haven’t done a calculation yet but even with a decent set of tools and an automated process the time it would take to migrate let’s say 10000+ tables for a new/modified column in a production environment does not give me much of a confidence… :slight_smile:

Thanks for your feedback, i will consider the view approach having a user per tenant. This raises another question: If the user “tenant123” has the privilege to select rows from the view “t123” i assume “tenant123” needs to have full access in terms of select privileges to the entire table “t” correct? We probably can’t prevent the “tenant123” user from making a select without the view on the table “t” from a database security perspective - am i right?

@dddaniel, Not sure how well cockroach handles large table alterations, but at least with mysql it is a much bigger problem when migrating huge tables. Table alterations tend to be slower the larger the table, and can even take over a day when it’s in the multiple TB. Only reasonable way to migrate large tables without major locking is to have it broken up into manageable pieces and have the application be able to operate with either schema and do rolling table updates. Cockroach might handle it better (I am fairly sure Oracle can), but I would be more concerned about migrating one huge table with 10000+ tenants in a single transaction that can’t be split all having to lock at once than rolling 10000 smaller updates for a new/modified column… As mentioned, haven’t tried table alterations on large datasets with Cockroach, but you may want to do so before assuming what will work best…

@jlauro: Thanks, that is definately another aspect to consider, i will do some testing. Regards, Daniel

@dddaniel: Nope, you can grant access to a view without granting access to all the data in the underlying table. It’s one of the common use cases for views:

1 Like

@a-robinson: Oh ok, this gives us some interesting options to consider going further. Will give it a try, thanks!

I to am in the same situation where I want to use CockroachDB for my applications but Im reluctant due to there not being any support for row based security. I tried the views approach. There is a lot of overhead in applying the work around for no gain. Views do not support updates so you still need to write directly to the primary table. Which means you need to code or apply your own security model for writes which is not very nice. I would love for row level security to be available asap as I really like working with CockroachDB.

Where can we see the roadmap for: is the best open issue for you to track for SQL table partitioning. I believe we’re targeting the 1.2 release (early March 2018) for that RFC, but it won’t necessarily include row-level security.