SQL User Management

July 4, 2020

Managing users in SQL servers is impossible to do well with manually applied GRANT and REVOKE statements:

If you manage SQL user privileges by hand:

Since user management is so tedious, it's likely to be ignored, where you'll have a single root user shared by everybody, or users according to role-levels (read, write, root), but then you lose access to logging to see who specifically performed each action.

These issues can have catastrophic security consequences, and there's no excuse why these problems weren't solved by the databases themselves 20 years ago.

sum(1) solves these problems and makes user management easy and automatic. Currently it supports just MySQL v5.7 but Postgres, MariaDB, and MySQL v8 are planned.

How it works

sum(1) connects to your database and retrieves its schema. It parses a configuration file sum.conf(5) which defines allow and deny rules and is highly inspired by OpenBSD's pf(4) firewall configuration syntax.

Given a SQL schema like the following:

> SHOW TABLES
admins
admin_recovery_codes
messages

> DESCRIBE admins
id            INTEGER
email         TEXT
password_hash TEXT

And a sum.conf input similar to the following:

read  = { bob jim }
write = { alice@localhost _dashboard }

# default deny
deny all

# allow root to do anything
allow root

# grant access to specific statements across any table
allow $write db dashboard table any statement { select insert update delete }

# allow all select statements, except for admins.password and everything in
# admin recovery tables
allow $read db dashboard table any statement select
deny  $read db dashboard table admins statement select column password_hash
deny  $read db dashboard table admin_recovery_codes statement select

sum(1) will update the _priv tables in your SQL database to restrict access to only the specified data. In the above example, the users bob and jim will be able to run select queries across all tables except admin_recovery_codes and the password_hash field in the admins table. Our application itself, _dashboard, is given access to select, insert, update, and delete across every table in the dashboard database. SQL injection through the app user, while still terrible, at least no longer results in a full SQL server takeover and is restricted to just a single database and those four statements.

Since your tables and columns will change after each migration, you'll want to re-run sum(1) following migrations every time.

Getting started

To install sum(1):

$ git clone https://git.sr.ht/~egtann/sum && cd sum
$ make
$ sudo make install

For usage instructions, see sum(1) and sum.conf(5) in the man pages.