Here at DoltHub, our centerpiece is Dolt, which fuses a MySQL-compatible database with Git-style versioning capabilities. After you install Dolt, all it takes are a few commands to have a running server:

mkdir demo
cd demo
dolt init
dolt sql-server

Your MySQL-backed applications will require no change, as we aim to be completely compatible with MySQL (and we're getting pretty close). However, you now have access to the full power that versioning provides, and as Git is the most popular versioning software ever, you can apply that same knowledge to Dolt; there is practically zero learning curve.

But with Dolt, you're versioning your data rather than your source code, and it requires a few different security considerations. We've just added a new security model, which handles write access to branches. Read on to learn how to incorporate them into your workflow!

The 3 Security Models

I'm defining a security model as a function that may limit or restrict access or modification to something.

The first security model is based on MySQL's user and privilege system. MySQL is generally run as a server, exposed through some interface (ports, sockets, etc.), that allows users to connect and assume the identity of a predefined user. This user then has a set of privileges that controls which databases they may see, whether they may create tables, columns that they may modify, etc. There are also roles that may be granted to users, making it very easy to quickly add or remove privileges to many users. Dolt has the same security model when run as a server (using dolt sql-server).

The second security model revolves around Git. Git does not have a security model built-in. Due to its distributed nature, a repository's owner does not have to worry about a foreign actor changing or corrupting their local copy. When most think of securing their repository, it's generally due to their use of a central repository that stands as a single source-of-truth. This repository is hosted on a server, either within their control (primarily within organizations), or on a popular host like GitHub. The repository's host then employs a security model to control access and modifications. DoltHub (which is to Dolt as GitHub is to Git) follows GitHub's model.

You may have noticed that there's a gap between the two models that have been mentioned. Dolt, when run as a server, gives its users the ability to access the full suite of versioning commands, however neither model protects against this scenario.

That is where our new feature comes in: branch permissions.

What are Branch Permissions?

Branch permissions control who can write and modify a particular branch when the SQL server is running. Anyone user can read from any branch, and the CLI has unrestricted access to modify any branch. This security model exists on top of MySQL's users and privileges system, but operates in a Dolt-specific context.

Let's go over some examples on how this works, as word dumps aren't quite as fun to read.

Using Branch Permissions

Let's start from an empty repository. Feel free to download and install Dolt and follow along!

Let's create a new directory and start a server.

$ mkdir example

$ cd example

$ dolt init
Successfully initialized dolt data repository.

$ dolt sql-server
Starting server with Config HP="localhost:3306"|T="28800000"|R="false"|L="info"

We now have a server running on localhost on port 3306, which defaults to the same values as MySQL. Feel free to change these using --host and --port if you already have a running MySQL instance on the default port. Upon initialization, we also create a user named root that has all global privileges, so that it's easy to get your database set up quickly. Definitely remember to either add a password or remove the root user later though, so that your database isn't quite so open.

Let's connect to our database and check out the first system table! As we're MySQL-compatible, ANY MySQL client or connector should work (and if you find one that doesn't, definitely submit an issue). To make life easier, Dolt ships with a MySQL client built right in via dolt sql-client (this is a generic MySQL client, so it will work with a standard MySQL instance as well). Feel free to use your own, but I'll use the built-in one for the examples.

$ 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> USE example;
mysql> SELECT * FROM dolt_branch_control;
+----------+--------+------+------+-------------+
| database | branch | user | host | permissions |
+----------+--------+------+------+-------------+
| %        | %      | %    | %    | write       |
+----------+--------+------+------+-------------+
1 row in set (0.00 sec)

In Dolt, databases are named after the directory that they're in. Since we're in the example folder, our database is named example, hence why we selected the example database. The second query gets into the meat of branch permissions. There are two tables, but we'll focus on the first one for now. dolt_branch_control controls which users have permissions on a branch through the use of pattern matching. Each column uses the same format as LIKE expressions in MySQL. For a fresh database, we enable all users to have the write permission on all branches. You'll notice that we also have a database column, as each database corresponds to an entirely different Dolt repository. For these examples we'll focus on our current database only, but know that this extends to multi-database servers.

Let's add another user, remove our default global branch allowance, and create a table.

mysql> CREATE USER testuser@localhost;
mysql> GRANT ALL ON *.* TO testuser@localhost;
mysql> DELETE FROM dolt_branch_control;
mysql> SELECT COUNT(*) FROM dolt_branch_control;
+----------+
| COUNT(*) |
+----------+
| 0        |
+----------+
1 row in set (0.00 sec)

mysql> CREATE TABLE example_table (pk BIGINT PRIMARY KEY);
Error 1105: `root`@`%` does not have the correct permissions on branch `main`

We granted all privileges to testuser@localhost to make the examples a bit simpler, but it's worth noting that GRANT ALL does not give the GRANT OPTION privilege. GRANT OPTION is a special privilege that allows a user to grant their privileges to others, and its absence for testuser@localhost will become relevant later.

Anyway, there's a big bright error after we tried creating a table. The error is relatively straightforward—root doesn't have the correct permissions to create a table. We need to add a rule back into dolt_branch_control that allows root to create tables on our default main branch.

mysql> INSERT INTO dolt_branch_control VALUES ('example', 'main', 'root', '%', 'write');
mysql> SELECT * FROM dolt_branch_control;
+----------+--------+------+------+-------------+
| database | branch | user | host | permissions |
+----------+--------+------+------+-------------+
| example  | main   | root | %    | write       |
+----------+--------+------+------+-------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE example_table (pk BIGINT PRIMARY KEY);
mysql> INSERT INTO example_table VALUES (1), (2), (3);
mysql> SELECT * FROM example_table;
+----+
| pk |
+----+
| 1  |
| 2  |
| 3  |
+----+
3 rows in set (0.00 sec)

Much better. For fun, let's commit our new table to the main branch.

mysql> CALL DOLT_ADD('-A');
+--------+
| status |
+--------+
| 0      |
+--------+
1 row in set (0.00 sec)

mysql> CALL DOLT_COMMIT('-m', 'Committed example_table');
+----------------------------------+
| hash                             |
+----------------------------------+
| ap1acbkiqtvs7s67joccc8jb1une7d4o |
+----------------------------------+
1 row in set (0.00 sec)

The same commands that you'd use on the command line are now stored procedures, and their parameters are the same as well. This makes it super easy to translate CLI usage to the SQL context. Enough with the root user, let's check out testuser. I'm reusing the same terminal window, hence the exit command.

mysql> exit;

$ dolt sql-client --user=testuser
# Welcome to the Dolt MySQL client.
# Statements must be terminated with ';'.
# "exit" or "quit" (or Ctrl-D) to exit.
mysql> USE example;
mysql> SELECT * FROM dolt_branch_control;
+----------+--------+------+------+-------------+
| database | branch | user | host | permissions |
+----------+--------+------+------+-------------+
| example  | main   | root | %    | write       |
+----------+--------+------+------+-------------+
1 row in set (0.01 sec)

mysql> SELECT * FROM example_table;
+----+
| pk |
+----+
| 1  |
| 2  |
| 3  |
+----+
3 rows in set (0.00 sec)

testuser can see everything, as we granted them all privileges except for GRANT OPTION. If we try to insert a new row into example_table, we'll get an error since we don't have any permissions on the main branch.

mysql> INSERT INTO example_table VALUES (4);
Error 1105: `testuser`@`localhost` does not have the correct permissions on branch `main`

No problem, we just need to add an entry to dolt_branch_control.

mysql> INSERT INTO dolt_branch_control VALUES ('example', 'main', 'testuser', 'localhost', 'write');
Error 1105: `testuser`@`localhost` cannot add the row ["example", "main", "testuser", "localhost", "write"]

testuser cannot add a row to the dolt_branch_control table? Well, it's due to that GRANT OPTION privilege missing. Any users that have both the SUPER AND GRANT OPTION privileges in the global space are viewed as global admins, and are allowed to freely edit the dolt_branch_control table. testuser has SUPER, but does not have GRANT OPTION.

Notice from the initial error encountered by root that, although a user is considered a global admin, they still require an entry in the dolt_branch_control table. This makes the table the single source-of-truth regarding branch permissions, which is much easier for an admin to manage.

Although testuser may not edit the main branch, they may still create their own branch off of main.

mysql> CALL DOLT_BRANCH('newbranch');
+--------+
| status |
+--------+
| 0      |
+--------+
1 row in set (0.00 sec)

mysql> CALL DOLT_CHECKOUT('newbranch');
+--------+
| status |
+--------+
| 0      |
+--------+
1 row in set (0.00 sec)

mysql> INSERT INTO example_table VALUES (4);
mysql> SELECT * FROM example_table;
+----+
| pk |
+----+
| 1  |
| 2  |
| 3  |
| 4  |
+----+
4 rows in set (0.00 sec)

testuser created the branch newbranch, and they may freely modify the branch that they created. This is similar to forking a repository on GitHub: you may not touch the parent, but you can do whatever you want with your copy.

So how does this work?

mysql> SELECT * FROM dolt_branch_control;
+----------+-----------+----------+-----------+-------------+
| database | branch    | user     | host      | permissions |
+----------+-----------+----------+-----------+-------------+
| example  | main      | root     | %         | write       |
| example  | newbranch | testuser | localhost | admin       |
+----------+-----------+----------+-----------+-------------+
2 rows in set (0.00 sec)

Creating a new branch also creates an entry for that user and branch. The new thing to note here is that testuser has the admin permission. While write just gives a user the ability to modify branches, admin gives the user all permissions (there are only two at the time of writing), in addition to being able to modify any entries that match the database and branch. This means that testuser may freely give other users permissions on their branch.

mysql> INSERT INTO dolt_branch_control VALUES ('example', 'newbranch', 'root', '%', 'write');
mysql> SELECT * FROM dolt_branch_control;
+----------+-----------+----------+-----------+-------------+
| database | branch    | user     | host      | permissions |
+----------+-----------+----------+-----------+-------------+
| example  | main      | root     | %         | write       |
| example  | newbranch | testuser | localhost | admin       |
| example  | newbranch | root     | %         | write       |
+----------+-----------+----------+-----------+-------------+
3 rows in set (0.00 sec)

Controlling Branch Names

At this point, you should have a fairly decent understanding of how the dolt_branch_control table works, so let's take a quick peek at how the other table, dolt_branch_namespace_control, works. The purpose of dolt_branch_namespace_control flows from how dolt_branch_control works, as it matches against expressions. This means that, in order to fully control which users may modify which branches, you also need to control which users may create branches within a namespace. While dolt_branch_control operates as a whitelist (requiring entries to be present to allow operations), dolt_branch_namespace_control operates as a hybrid blacklist (the absence of an entry allows operation). Let's explain what I mean by hybrid.

When a user creates a branch, the name of that branch is checked against dolt_branch_namespace_control. If no matching entries are found, the name is allowed and the operation succeeds. The table's initial state is empty, meaning all branch names are allowed. When an entry is added, it blocks all users from using that branch name except for the users that are matched by the entry. If multiple entries match a specific branch name, then the longest matching entry is used. For example, with two entries ab% and abcd%, the branch abcdef will only match abcd%, as it is the longest match. If the "longest match" is multiple entries (such as multiple entries using abcd%), then a user has to match only one of the entries.

Let's look at this in action. We'll switch back to the root account to set up the dolt_branch_namespace_control table. Editing that table follows the same rules as dolt_branch_control regarding global admins and the admin permission.

mysql> exit;

$ 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> USE example;
mysql> INSERT INTO dolt_branch_namespace_control VALUES ('example', 'pre%', '%', '%'), ('example', 'prefix%', 'root', '%');
mysql> SELECT * FROM dolt_branch_namespace_control;
+----------+---------+------+------+
| database | branch  | user | host |
+----------+---------+------+------+
| example  | pre%    | %    | %    |
| example  | prefix% | root | %    |
+----------+---------+------+------+
2 rows in set (0.00 sec)

Now let's swap back to testuser and create some branches!

mysql> exit;

$ dolt sql-client --user=testuser
# Welcome to the Dolt MySQL client.
# Statements must be terminated with ';'.
# "exit" or "quit" (or Ctrl-D) to exit.
mysql> USE example;
mysql> CALL DOLT_BRANCH('pre');
+--------+
| status |
+--------+
| 0      |
+--------+
1 row in set (0.00 sec)

mysql> CALL DOLT_BRANCH('pretend');
+--------+
| status |
+--------+
| 0      |
+--------+
1 row in set (0.00 sec)

mysql> CALL DOLT_BRANCH('prefix');
Error 1105: `testuser`@`localhost` cannot create a branch named `prefix`
mysql> CALL DOLT_BRANCH('prefixes');
Error 1105: `testuser`@`localhost` cannot create a branch named `prefixes`
mysql> CALL DOLT_BRANCH('other');
+--------+
| status |
+--------+
| 0      |
+--------+
1 row in set (0.00 sec)

testuser attempted to create 5 different branches. Both pre and pretend only match the pre% entry, and are therefore allowed. prefix and prefixes match both the pre% and prefix% entries, but as prefix% is the longer match, the pre% entry is ignored. On the prefix% entry, only root is allowed to create such tables, meaning testuser is denied. Lastly, other does not match any entries in the table, and is therefore allowed.

The explanation may have seemed a bit complex, but in practice it's quite straightforward. Due to our use of pattern matching, you can do creative things like turning the dolt_branch_namespace_table into a whitelist as well. The power is in your hands!

Conclusion

Branch permissions are a powerful tool, and opens up a ton of new use cases that Dolt excels in! It has also been very fun to implement, and we still have many more features in the pipeline. Companies are already using Dolt in production, so no need to wait, download and try us out today! You can keep up to date with us through Twitter, or you can chat directly with us through Discord.