There are a few scenario where folks want to do things in CockroachDB that aren't officially supported but can be approximated using views:
- using a multi-tenant style data model but enforcing that different tenants can't see each other's data -- and enforcing that at the DB level
- letting some users see all the data in a record but restricting other users to only be able to see limited or masked versions of the data.
In this blog post, I'm going to give an example of solving the second example (but hopefully you see how it can be applied to solve the first example).
Consider a database that has some PII (personally-identifiable information) such as SSN or phone number. You might want to let some users see this info but restrict other users (like a reporting user) to only see. For this example, we'll create a DB called "pii" with a table called "pii".
CREATE DATABASE pii;
USE pii;
CREATE TABLE pii_data ( id INT PRIMARY KEY, first_name TEXT, last_name TEXT, ssn TEXT, phone TEXT ) ;
INSERT INTO pii_data ( id, first_name, last_name, ssn, phone ) VALUES ( 1, 'jim', 'hatcher', '111-222-3333', '555-1212' );
Now, we can create a view in the database which masks the SSN number and phone:
CREATE VIEW pii_data_limited AS SELECT id, first_name, last_name, 'hidden' AS ssn, RIGHT(phone, 4) AS phone FROM pii_data;
Since we're logged in as the root user, we can see both the base table's data and we can also SELECT from the view.
root@:26257/pii> SELECT * FROM pii_data;
id | first_name | last_name | ssn | phone
-----+------------+-----------+--------------+-----------
1 | jim | hatcher | 111-222-3333 | 555-1212
(1 row)
Time: 1ms total (execution 1ms / network 0ms)
root@:26257/pii> SELECT * FROM pii_data_limited;
id | first_name | last_name | ssn | phone
-----+------------+-----------+--------+--------
1 | jim | hatcher | hidden | 1212
(1 row)
Time: 1ms total (execution 0ms / network 0ms)
Now, let's create two users to which we will grant different levels of access:
CREATE user fullaccess WITH PASSWORD 'full';
GRANT ALL ON DATABASE pii TO fullaccess;
GRANT SELECT ON pii.pii_data TO fullaccess;
CREATE user limitedaccess WITH PASSWORD 'limited';
REVOKE ALL ON DATABASE pii FROM limitedaccess;
GRANT SELECT ON pii.pii_data_limited to limitedaccess;
The limited user can only see the data through the view:
the limited user can only see the data through the view:
$ cockroach sql --certs-dir /path/to/certs/ --user limitedaccess
#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
Enter password:
# Server version: CockroachDB CCL v20.2.8 (x86_64-apple-darwin14, built 2021/04/23 13:59:30, go1.13.14) (same version as client)
# Cluster ID: c5ab1d2e-1a06-490e-a02c-b3544861f035
# Organization: hatcher
#
# Enter \? for a brief introduction.
#
limitedaccess@:26257/defaultdb> SELECT * FROM pii.pii_data;
ERROR: user limitedaccess does not have SELECT privilege on relation pii_data
SQLSTATE: 42501
limitedaccess@:26257/defaultdb> SELECT * FROM pii.pii_data_limited;
id | first_name | last_name | ssn | phone
----------+------------+-----------+--------+--------
1 | jim | hatcher | hidden | 1212
(1 row)
Time: 1ms total (execution 1ms / network 0ms)
But, the full user can see both:
$ cockroach sql --certs-dir /path/to/certs/ --user fullaccess
#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
Enter password:
# Server version: CockroachDB CCL v20.2.8 (x86_64-apple-darwin14, built 2021/04/23 13:59:30, go1.13.14) (same version as client)
# Cluster ID: c5ab1d2e-1a06-490e-a02c-b3544861f035
# Organization: hatcher
#
# Enter \? for a brief introduction.
#
fullaccess@:26257/defaultdb> SELECT * FROM pii.pii_data;
id | first_name | last_name | ssn | phone
----------+------------+-----------+--------------+-----------
1 | jim | hatcher | 111-222-3333 | 555-1212
(1 row)
Time: 1ms total (execution 1ms / network 0ms)
Top comments (0)