shadowdb

Overview ¶

Command shadowdb creates a new MySQL database that contains VIEWs pointing to original database tables with content of sensitive columns masked.

Operation mode ¶

The use case for shadowdb is to have a read-only MySQL database similar to the original one but with some sensitive text information replaced by placeholders. Naturally one can use MySQL permissions system to only grant SELECT privilege to subset of table columns, but this approach breaks programs which expect to be able to read values of sensitive columns as well.

Such scenario can be handled by creating a separate database with SQL VIEWs referencing original database tables but returning placeholder values for columns with sensitive data.

Consider the following table:

create table original_db.users (
	id integer auto_increment primary key,
	name varchar(100),
	email varchar(100)
)

Here's an example of query against such table:

> select * from original_db.users;
+----+----------+------------------+
| id | name     | email            |
+----+----------+------------------+
|  1 | John Doe | john@example.com |
+----+----------+------------------+

The view limiting visibility of the "name" column for such table can be created like this:

create view masked_db.users (id, name, email) as
	select id, '*****', email from original_db.users

Now query against masked_db.users view returns the same number of columns as original_db.users, but has asterisks in place of the "name" column values:

> select * from masked_db.users;
+----+-------+------------------+
| id | name  | email            |
+----+-------+------------------+
|  1 | ***** | john@example.com |
+----+-------+------------------+

Command shadowdb automates such process by discovering fields of each table in the original database and creating (or updating) corresponding views for them, masking provided sensitive fields. Additionally this command creates/updates privileges for the given MySQL user so that such user can only select non-masked fields from the original database and all fields of the created views.

Notes ¶

If original database schema changes, shadowdb command should be run again — it will update views to reflect original database tables. Note that it does not remove views for dropped tables, you have to manually do this.

shadowdb automatically creates destination (masked) database as necessary, as well as the user to grant select privileges to. It does not configure authorization for such user.

List of sensitive fields is expected to be in CSV format of table_name,column_name pairs (case insensitive) without header, see the -mask flag. It is implied that masked columns are of some text type (varchar, etc.), if they're not, clients working with created views may be surprised by seeing text placeholder value instead of non-text type of the original table.

MySQL credentials (user and password) are read from the "client" section of the .my.cnf file which is expected to have the following format:

[client]
user = username
password = password

The user is expected to have SUPER privileges (or their subset sufficient to create databases, views and grant privileges).

If -tls flag is used, program connects to the server over TLS and expects server certificate to be signed with certificate authority from the system CA pool. On UNIX systems the environment variables SSL_CERT_FILE and SSL_CERT_DIR can be used to override the system default locations for the SSL certificate file and SSL certificate files directory, respectively.