Here at DoltHub we love fixing customer problems. We have a goal to fix every bug our customers report within 24hrs. It's one of the reasons we release new builds all the time, for instance, three this week!

It would be irresponsible for us to always be so reactive. Projecting what our customers needs will be as they grow is just as important. One area often neglected by businesses early in their history is security. They start as small teams and everyone has access to everything, and securing their database is pretty low on their list of priorities. As they grow, their requirements for securing and auditing across their business grows along with them. This is what success looks like, and we are only successful if our customers are successful.

MySQL, one of Dolt's genetic ancestors has a long history of enterprise usage. As a result, MySQL has many ways to control access to data. One area where Dolt closed a gap recently was the ability to have fine-grained control of stored procedures, and it was released on Halloween 🎃!

TL;DR;

If you want to create a SQL Server user and give them enough access to use Dolt's core versioning operations, but not a bunch of other permissions they don't need, do the following:

> CREATE USER 'sqluser'@'%' IDENTIFIED BY 'sqlusers_password';
> GRANT SELECT,EXECUTE,INSERT,UPDATE on mydb.* to sqluser@'%';

Read more to know more!

Dolt is SQL

One of our main design goals for Dolt is that users have access to version controlled features within the SQL context. For example, if you need to create a branch foo, the following SQL statement will do it:

call dolt_branch('foo');

which is a valid SQL statement. This allows Dolt to interoperate with virtually all SQL clients which significantly lowers the barrier to adoption. As a result, Dolt uses System Tables, Functions, and Stored Procedures to allow our users to have access to all the version control features of Dolt.

Of particular interest here is that Dolt's procedures perform the write operations of your common version control operations. A sample of such operations are:

• dolt_branch()
• dolt_merge()
• dolt_commit() These operations, and others like them, are critical to the "version control" aspect of Dolt's mission. These are operations which we want our users to execute easily and freely so that they can make use of Dolt with little hassle.

Stop Using Root

The first time you try Dolt, you are probably going to follow our getting started guide and you'll notice that there is no mention of users or permissions or security at all really. That is as it should be. The security stuff comes later after you've kicked the tires.

Now you are at the point where you've kicked the tires, and you want to tighten things up a bit. I'll assume you have a server running, or you have connected to your Hosted instance using your SQL client of choice.

TIP! If you are in the directory where a dolt sql-server is running and your system user can read the lock file of the server, you can execute dolt sql, and you will be connected to the running server as user with super user privileges.

The first step in locking down your system is to create an unprivileged user. Dolt, being a MySQL clone, creates users in the familiar way:

> CREATE USER 'sqluser'@'%' IDENTIFIED BY 'sqlusers_password';

Using the SHOW GRANTS command we can see that sqluser only has USAGE permissions, which means they can login but not do much else. Can't list databases or anything.

Note: The commands below are a back and forth of commands run by two different users: root and sqluser. To indicate which is which, we are prefixing the prompts: root> and sqluser>. What you see on your screen will depend on the client your are using.

root> SHOW GRANTS FOR sqluser@'%';
+-------------------------------------+
| Grants for sqluser@%                |
+-------------------------------------+
| GRANT USAGE ON *.* TO `sqluser`@`%` |
+-------------------------------------+

In order for sqluser to start using Dolt and it's magical version control features, sqluser needs to be granted access to SELECT, INSERT and UPDATE the database, but also to be able to EXECUTE procedures. Let's create a database, and apply each permission one at a time so it's clear what each does.

root> CREATE DATABASE mydb;
root> GRANT SELECT on mydb.* to sqluser@'%';
root> SHOW GRANTS FOR sqluser@'%';
+-------------------------------------------+
| Grants for sqluser@%                      |
+-------------------------------------------+
| GRANT USAGE ON *.* TO `sqluser`@`%`       |
| GRANT SELECT ON `mydb`.* TO `sqluser`@`%` |
+-------------------------------------------+

sqluser logs in, and tries to list what's in the database, which they have permission to do thanks to the SELECT grant:

sqluser> USE mydb; -- Not always required
sqluser> SHOW TABLES;
Empty set (0.00 sec)

sqluser> CREATE TABLE tbl (pk int auto_increment primary key, val int);
Error 1105 (HY000): command denied to user 'sqluser'@'%'

In order for sqluser to create tables, they need the CREATE grant. As root:

root> GRANT CREATE on mydb.* to sqluser@'%';

Now sqluser can create the table, select on it, but can't insert new data:

sqluser> INSERT INTO tbl VALUES (0,42);
Error 1105 (HY000): command denied to user 'sqluser'@'%'

In order to add rows to any table in the database, the user must be granted INSERT rights. We'll add UPDATE rights at the same time, which allow the user to change any row.

root> GRANT INSERT ON mydb.* TO sqluser@'%';
root> GRANT UPDATE ON mydb.* TO sqluser@'%';
root> SHOW GRANTS FOR sqluser@'%';
+-----------------------------------------------------------+
| Grants for sqluser@%                                      |
+-----------------------------------------------------------+
| GRANT USAGE ON *.* TO `sqluser`@`%`                       |
| GRANT SELECT, INSERT, CREATE ON `mydb`.* TO `sqluser`@`%` |
+-----------------------------------------------------------+

Can sqluser add this new table and create a dolt commit yet?

sqluser> INSERT INTO tbl VALUES (0,42);
sqluser> INSERT INTO tbl VALUES (0,23);
sqluser> SELECT * FROM tbl;
+----+-----+
| pk | val |
+----+-----+
| 1  | 42  |
| 2  | 23  |
+----+-----+
sqluser> select * from dolt_status;
+------------+--------+-----------+
| table_name | staged | status    |
+------------+--------+-----------+
| tbl        | 0      | new table |
+------------+--------+-----------+

sqluser> call dolt_add(".");
Error 1105 (HY000): command denied to user 'sqluser'@'%'

Curses! the call dolt_add() statement is the EXECUTION of a stored procedure. That requires it's own EXECUTE grant:

root> GRANT EXECUTE ON mydb.* TO sqluser@'%';

Now, sqluser will finally be able to create a commit!

sqluser> call dolt_add(".");
+--------+
| status |
+--------+
| 0      |
+--------+
1 row in set (0.01 sec)

sqluser> call dolt_commit("-m", "my first commit!");
+----------------------------------+
| hash                             |
+----------------------------------+
| s8k3u6j1a5gp682e4aom3kna7ima6mlj |
+----------------------------------+
1 row in set (0.01 sec)

Every organization is different. For example, in some organizations CREATE and DROP table are both of significant risk and should only be allowed for some users and not others. As a starting place, granting SELECT,EXECUTE,INSERT, and UPDATE is the bare minimum to start for a team using a shared Dolt server. You can grant all in one command as follows:

root> GRANT SELECT,EXECUTE,INSERT,UPDATE on mydb.* to sqluser@'%';

Also, in full disclosure, for some situations, granting everything is the shortest path to enabling users to do what they need to do.

root> GRANT ALL PRIVILEGES on mydb.* to sqluser@'%';

GRANT ALL PRIVILEGES provides a very large degree of access as it makes the user virtually a super user on the database in question. They can't create users, but they can grant access to other users, which is a lot of power.

So... What's New In Dolt?

Everything discussed up until now is how Dolt has worked for a long time. There is a subtle door we opened. By granting database level grants, we are saying that all tables and procedures are available to the user. That's a pretty big net. Procedures would include not only Dolt procedures but all procedures which could include user defined code which is sensitive. Furthermore, I stated above most of the dolt procedures are necessary as core operations for our primary product. There are other procedures which we consider administrative in nature which could create risk for our users. The list of Dolt procedures which we consider could cause risk if granted too widely are:

• dolt_backup()
• dolt_clone()
• dolt_fetch()
• dolt_undrop()
• dolt_purge_dropped_databases()
• dolt_gc()
• dolt_pull()
• dolt_push()
• dolt_remote()

In Release 1.22.0 we enabled fine grain access control of procedures. This means that if you don't want to grant blanket Execute permission on all things, you can target them directly. First, if you have already granted permission, you can REVOKE with the following:

root> REVOKE EXECUTE ON mydb.* FROM sqluser@'%';
root> SHOW GRANTS FOR sqluser@'%';
+-----------------------------------------------------------+
| Grants for sqluser@%                                      |
+-----------------------------------------------------------+
| GRANT USAGE ON *.* TO `sqluser`@`%`                       |
| GRANT SELECT, INSERT, UPDATE ON `mydb`.* TO `sqluser`@`%` |
+-----------------------------------------------------------+

Now if you want to enable the user to only add and commit, so the following:

root> GRANT EXECUTE ON PROCEDURE mydb.dolt_add to sqluser@'%';
root> GRANT EXECUTE ON PROCEDURE mydb.dolt_commit to sqluser@'%';
root> SHOW GRANTS FOR sqluser@'%';
+------------------------------------------------------------------+
| Grants for sqluser@%                                             |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `sqluser`@`%`                              |
| GRANT SELECT, INSERT, UPDATE ON `mydb`.* TO `sqluser`@`%`        |
| GRANT EXECUTE ON PROCEDURE `mydb`.`dolt_add` TO `sqluser`@`%`    |
| GRANT EXECUTE ON PROCEDURE `mydb`.`dolt_commit` TO `sqluser`@`%` |
+------------------------------------------------------------------+

sqluser> UPDATE tbl SET val = 108 WHERE pk = 2;
sqluser> SELECT * from dolt_status;
+------------+--------+----------+
| table_name | staged | status   |
+------------+--------+----------+
| tbl        | 0      | modified |
+------------+--------+----------+
mydb> call dolt_add(".");
+--------+
| status |
+--------+
| 0      |
+--------+
mydb> call dolt_commit("-m", "Another commit");
+----------------------------------+
| hash                             |
+----------------------------------+
| ove9uqtbdfd5jgn6u79nudndhu54kk90 |
+----------------------------------+
neil> call dolt_push();
Error 1105 (HY000): command denied to user 'sqluser'@'%'

Wait a second?!? Are we contradicting ourselves? We said twice above that we recommend doing the following:

root> GRANT SELECT,EXECUTE,INSERT,UPDATE on mydb.* to sqluser@'%';

Doesn't that mean that sqluser is going to be able to run those special dolt procedures listed above? Am I taking crazy pills? How is this more secure?

Dolt's got your back. We made it so that the special procedures require additional grants. Specifically, if a user has database level Execute permissions, they are not granted access to the dolt_push, dolt_backup, and the other procedures listed above. For users who need access to those operations, they need to be granted explicit access. For example, a user who needs to be able to purge dropped data from the server needs access:

root> GRANT EXECUTE ON PROCEDURE mydb.dolt_purge_dropped_databases TO sqluser@localhost;

Dolt Recommends

With the addition of fine grain controls and safety guards around our sensitive procedures in place, Dolt is ready for you to create your users with confidence. The following will set you up for success:

root> CREATE USER 'sqluser'@'%' IDENTIFIED BY 'sqlusers_password';
root> GRANT SELECT,EXECUTE,INSERT,UPDATE on mydb.* to sqluser@'%';

We'd love to hear how this plays in your enterprise. Come talk to us about your use case on discord!