Dolt is a SQL database with Git-style versioning. Today, we're proud to announce that we've implemented users and privileges! As we're aiming to be a drop-in replacement for MySQL, it is very important that we get our security model correct, and we're taking our first steps towards that goal.

Users and Privileges?

When running a shared database, a database owner generally wants to ensure that everyone that can see the database server can neither see nor modify all of its data at will. In MySQL, this is enforced through a system of read and write privileges. Every connecting user (clients) has to supply a username and authentication (such as a password). This is then matched against an internal table, combining the username, connection address (such as IP address), and password to find the correct user, which is then associated with that client's session. From there, any statements that are run from that session are checked against the privileges that the session user contains. If the appropriate privileges are present, the statement executes as expected. If the appropriate privileges are absent, the statement is rejected.

In addition to outright rejection, some informational commands will have a limited view depending on the session user's privileges. For example, the statement SHOW DATABASES will show all databases that the session user may interact with in some way, while hiding the others.

Roles vs Direct Privileges

Privileges may be granted directly to each user, however this has the potential to be both tedious and error-prone. For many cases, users can be grouped by their access needs, and roles are the way to handle those cases. Privileges may be granted directly to a role rather than a user, and that user may then be granted a role. When the role is updated, all users who have been granted that role will have their privileges updated. A user may have multiple roles granted to them, allowing for complex combinations to easily be managed by the database's maintainers.

Getting Started with Users

Let's set up a Dolt database and try using privileges! First we'll install Dolt. Next, we'll create our repository directory, initialize Dolt, and create some data:

$ mkdir db_name

$ cd db_name

$ dolt init
Successfully initialized dolt data repository.

$ dolt sql <<SQL
> CREATE TABLE example (pk BIGINT PRIMARY KEY);
> INSERT INTO example VALUES (1), (2), (3);
> CREATE TABLE example2 (pk BIGINT PRIMARY KEY, v1 BIGINT);
> INSERT INTO example2 VALUES (0, 1), (2, 3);
> SQL
Query OK, 3 rows affected
Query OK, 2 rows affected

dolt sql is a command that allows one to pass SQL commands directly to the database. As we must have direct access to the database in order to run CLI commands, dolt sql disables all privilege checking, so that every SQL command will succeed. In order to use privileges, let's run our database in server mode.

$ dolt sql-server --privilege-file=privs.json --user="root" --password=""
Starting server with Config HP="localhost:3306"|U="root"|P=""|T="28800000"|R="false"|L="info"

The --privilege-file argument (also available through a YAML configuration file) specifies the location of the file that will contain our user, role, and privilege data. If the file does not exist, it will be created. If the file contains any users, then the given user argument is ignored, and all users are loaded from the file. In our case, since the file was just created and is therefore empty, we create a user named root who, by default, has an empty password (the user argument has a default value of root if it is not supplied, it is just explicitly here for the example). Our root user also has every privilege, allowing them to execute any statement. This is intended for setting up the remaining users, as a database without any users would be a database that nobody could connect to. Be careful though, as our root user will be saved to our privileges file, so it may be wise to drop the user after all other users have been set up (or ensure the initial user has a strong password).

Let's connect to our server, and let's have our session use the root user. Dolt has a built-in SQL client, which we will use to connect to the server.

$ dolt sql-client --user="root"
# Welcome to the Dolt MySQL client.
# Statements must be terminated with ';'.
# "exit" or "quit" (or Ctrl-D) to exit.
mysql> CREATE USER testuser@localhost IDENTIFIED BY 'password123';
mysql> GRANT SELECT ON db_name.example TO testuser@localhost;
mysql> CREATE ROLE testrole;
mysql> GRANT SELECT, INSERT, UPDATE, DELETE on *.* TO testrole;
mysql> exit;

Here we've created a new user testuser with the password password123 (please don't use this as a real password, it's a very bad idea). This new user has the SELECT privilege only on our example table (db_name is the name of our directory, which Dolt uses as the name of the default database). We also created a new role testrole with the privileges SELECT, INSERT, UPDATE, and DELETE on *all possible tables. The *.* portion is a wildcard for both the database name and table, meaning it will apply to everything (both currently existing and future tables). We'll come back to roles in a bit. For now, let's connect to the server again, using our new user this time.

$ dolt sql-client --user="testuser" --password="password123"
# Welcome to the Dolt MySQL client.
# Statements must be terminated with ';'.
# "exit" or "quit" (or Ctrl-D) to exit.
mysql> USE db_name;
mysql> SELECT * FROM example;
+----+
| pk |
+----+
| 1  |
| 2  |
| 3  |
+----+
mysql> SELECT * FROM example2;
Error 1105: Access denied for user 'testuser'@'localhost' to table 'example2'
mysql> SELECT * FROM table_does_not_exist;
Error 1105: Access denied for user 'testuser'@'localhost' to table 'table_does_not_exist'
mysql> INSERT INTO example VALUES (4);
Error 1105: command denied to user 'testuser'@'localhost'
mysql> exit;

As can be seen from the above example, we can execute a SELECT statement on our example table, as we have the SELECT privilege for that table. However, when we attempt to execute SELECT on example2, it throws an "access denied" error, as we do not have the correct privileges on that table. In addition, the error message does not even give away that the table exists at all, as attempting to execute SELECT on a table that we know does not exist produces the same error message. This is an additional layer of security, as even knowing potential database or table names could reveal some critical information. As a last example, attempting to execute INSERT on the example table fails as we would expect.

Now, let's see what happens when we grant our role testrole to our user testuser.

$ dolt sql-client --user="root"
# Welcome to the Dolt MySQL client.
# Statements must be terminated with ';'.
# "exit" or "quit" (or Ctrl-D) to exit.
mysql> GRANT testrole TO testuser@localhost;
mysql> exit;

$ dolt sql-client --user="testuser" --password="password123"
# Welcome to the Dolt MySQL client.
# Statements must be terminated with ';'.
# "exit" or "quit" (or Ctrl-D) to exit.
mysql> USE db_name;
mysql> SELECT * FROM example;
+----+
| pk |
+----+
| 1  |
| 2  |
| 3  |
+----+
mysql> SELECT * FROM example2;
+----+----+
| pk | v1 |
+----+----+
| 0  | 1  |
| 2  | 3  |
+----+----+
mysql> SELECT * FROM table_does_not_exist;
Error 1146: table not found: table_does_not_exist
mysql> INSERT INTO example VALUES (4);
mysql> SELECT * FROM example;
+----+
| pk |
+----+
| 1  |
| 2  |
| 3  |
| 4  |
+----+
mysql> exit;

As we would expect, we can still SELECT from the example table. In addition, as the role testrole has the SELECT privilege for all tables, testuser may also SELECT from example2. Even attempting to SELECT from the non-existent table gives a different error message, as if the table did exist we would be able to SELECT from it. Lastly, testrole has the INSERT privilege, meaning our previous INSERT attempt now succeeds when we try it again.

Let's remove our role testrole from our user testuser.

$ dolt sql-client --user="root"
# Welcome to the Dolt MySQL client.
# Statements must be terminated with ';'.
# "exit" or "quit" (or Ctrl-D) to exit.
mysql> REVOKE testrole FROM testuser@localhost;
mysql> exit;

$ dolt sql-client --user="testuser" --password="password123"
# Welcome to the Dolt MySQL client.
# Statements must be terminated with ';'.
# "exit" or "quit" (or Ctrl-D) to exit.
mysql> USE db_name;
mysql> SELECT * FROM example;
+----+
| pk |
+----+
| 1  |
| 2  |
| 3  |
| 4  |
+----+
mysql> SELECT * FROM example2;
Error 1105: Access denied for user 'testuser'@'localhost' to table 'example2'
mysql> exit;

We're back to our original state, where we may SELECT from example but not from example2. This shows that we removed the role only, and the privileges that were granted directly to testuser are completely unaffected. This is always the case, as role privileges are not given directly to the user when said role is granted. Rather, a "connection" is created, and Dolt searches the set of privileges that the user has directly, in addition to any privilege sets that are "connected" to that user.

Closing

Implementing the privilege system is a major milestone for the Dolt project, and we have many more privilege-related features planned. One of the more exciting ones is allowing for branch access to be controlled through privileges, allowing even the Git concepts such as branches, merging, etc. to be controlled through users and roles.

We have an exciting time ahead of us, and we hope you'll join us for the ride! You can find us on Twitter, and we're always up for a chat on Discord. You can also browse our ever-growing list of open-source repositories over at DoltHub. Thank you for reading!