Getting Started with Dolt Permissions
Dolt is a MySQL compatible version controlled database. Most databases require account management and permissions. You don't want just anyone mucking around with your data. Released in February 2022, Dolt supports the standard SQL permissions model in a MySQL compatible way. This blog is an introduction on how to use permissions with Dolt.
What are permissions?
The original SQL standard published in 1986 defines users, grants, privileges, and roles as the permissions object model for SQL databases. It also defines the syntax for creating, updating, and deleting these objects. The MySQL implementation of permissions sticks pretty close to the SQL standard in terms of syntax. Permissions are stored in a separate database named mysql along with other database system information.
In a simple example workflow, users are created. Users are then granted specific predefined privileges on {database, table} pairs. For instance, you could create a user timsehn and grant it SELECT permissions on database test1, table test2 with the following SQL statements, provided the user you are operating as had been granted CREATE USER and GRANT OPTION permissions.
create user timsehn;
grant SELECT on test1.test2 to timsehn;Why write about Permissions now?
Dolt is starting to be used more and more like a traditional Online Transaction Processing (OLTP) database, a replacement for MySQL or Postgres that backs an application. OLTP use cases generally require permissions.
We launched Hosted Dolt to better support OLTP applications using Dolt. Hosted Dolt exposes a port on the internet for any client to connect to. You will definitely want to secure a hosted Dolt database behind permissions.
Dolt Permissions Example
Start a SQL Server
Install Dolt if you haven't yet. Navigate to the directory you want your data stored in. For me, I chose ~/dolt.
$ cd ~
$ mkdir dolt
$ cd doltNow start a Dolt SQL server.
dolt sql-server
Starting server with Config HP="localhost:3306"|T="28800000"|R="false"|L="info"If not defined using the -u flag, the default user is root.
Connect a Client
Let's use the default user to connect to our running server. On my Mac laptop, I run the MariaDB client which I'll use to connect here. But you can connect with any MySQL compatible client. Dolt even ships with a client, dolt sql-client. To connect I need to specify the user root using -u and I need to force the client to use the TCP interface by defining the host using -h.
Dolt supports the default socket interface but you need to start the server with the --socket option to make it work. I didn't do that so I'll define the host as 127.0.0.1 instead and stick with TCP.
$ mysql -h 127.0.0.1 -u root
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.9-Vitess
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]>I'm connected.
Inspect Permissions
Now, let's see what users are defined. You can see what user you are by using select user(). There are mysql system tables containing all users and permissions. MySQL also supports the SHOW GRANTS statement to see the active user's grants.
MySQL [(none)]> select user();
+--------+
| user() |
+--------+
| root@% |
+--------+
1 row in set (0.000 sec)
MySQL [(none)]> select user, host from mysql.user;
+------+------+
| User | Host |
+------+------+
| root | % |
+------+------+
1 row in set (0.001 sec)The % in the host is a wildcard meaning "any host". The user root can connect from any host.
MySQL [(none)]> show grants;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@% |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`%` WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
Make a new super user
We want to make a new user called admin that has an additional password safeguard. We'll use the root account to create this with the intent of deleting the root account later, after we make sure our admin account works. To do this we create a user identified by a password and grant all privileges to that user.
MySQL [(none)]> create user admin identified by '12345678';
Query OK, 0 rows affected (0.001 sec)
MySQL [(none)]> select user, host from mysql.user;
+-------+------+
| User | Host |
+-------+------+
| root | % |
| admin | % |
+-------+------+
2 rows in set (0.001 sec)The admin user is created. Now, I want to give it all privileges on all databases and all tables in those databases. This is what the *.* syntax means. * instead of % is the wildcard now. The first field is databases amd the second field is tables, separated by a ..
MySQL [(none)]> grant all on *.* to admin;
Query OK, 0 rows affected (0.002 sec)
MySQL [(none)]> show grants for admin;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for admin@% |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `admin`@`%` |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)Delete the default user
Now we're going to log out as root and log in as admin. Then, we'll test our admin permissions by deleting the root user.
First, log out.
MySQL [(none)]> exit
ByeNow, log in as admin.
$ mysql -h 127.0.0.1 -u admin -p12345678
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.9-Vitess
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]>It worked! Now let's test our power by deleting the default root user.
MySQL [(none)]> drop user root;
Query OK, 0 rows affected (0.001 sec)
MySQL [(none)]> select user, host from mysql.user;
+-------+------+
| User | Host |
+-------+------+
| admin | % |
+-------+------+
1 row in set (0.001 sec)
Gone. Let's make sure by trying to log in.
$ mysql -h 127.0.0.1 -u root
ERROR 1045 (28000): User not found 'root'No more root user. We're all set with a more secure admin account and no insecure ones.
Make other users
Now, it's time to make other users. I'm going to assume you want a database named example. You want a reader user who can run select queries on any table in example. You'll have a writer user who can run insert, and update queries on any table in example. Finally, you want a destroyer user who can only run delete queries on any table in example. These users will all have passwords that match their username. Data description language (DDL) changes like creating or altering a table will be restricted to the admin user.
Let's create the database and a test table first using the admin user.
$ mysql -h 127.0.0.1 -u admin -p12345678
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.9-Vitess
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]>create database example;
Query OK, 1 row affected (0.069 sec)
MySQL [(none)]> use example;
Database changed
MySQL [example]> create table test (pk int primary key, c1 int);
Query OK, 0 rows affected (0.033 sec)
Now let's make our three users: reader, writer, and destroyer.
MySQL [example]> create user reader identified by 'reader';
Query OK, 0 rows affected (0.009 sec)
MySQL [example]> create user writer identified by 'writer';
Query OK, 0 rows affected (0.002 sec)
MySQL [example]> create user destroyer identified by 'destroyer';
Query OK, 0 rows affected (0.001 sec)
Now we have to grant them their desired privileges. I picked the privileges for this example because they are named after the queries they give you privileges to run, but Dolt supports the same privileges as MySQL.
MySQL [example]> grant select on example.* to reader;
Query OK, 0 rows affected (0.005 sec)
MySQL [example]> grant insert, update on example.* to writer;
Query OK, 0 rows affected (0.001 sec)
MySQL [example]> grant delete on example.* to destroyer;
Query OK, 0 rows affected (0.002 sec)
Notice the use of * to grant privileges on all tables. The use of * can also be used on the database name. However, this is not recommended as giving update or insert permissions on the mysql database is the equivalent of giving that user administrative permissions.
Let's test it out. First writer so we have something in the table:
$ mysql -h 127.0.0.1 -u writer -pwriter
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.9-Vitess
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> insert into example.test values (0,0), (1,1), (2,2);
Query OK, 3 rows affected (0.027 sec)
MySQL [(none)]> select * from example.test;
ERROR 1105 (HY000): command denied to user 'writer'@'%'
Seems to be working. writer can write but not read. Let's make sure reader works.
$ mysql -h 127.0.0.1 -u reader -preader
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.9-Vitess
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> select * from example.test;
+------+------+
| pk | c1 |
+------+------+
| 0 | 0 |
| 1 | 1 |
| 2 | 2 |
+------+------+
3 rows in set (0.004 sec)
MySQL [(none)]> insert into example.test values (3,3);
ERROR 1105 (HY000): command denied to user 'reader'@'%'
All good. Now, let's try destroyer.
$ mysql -h 127.0.0.1 -u destroyer -pdestroyer
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.9-Vitess
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> select * from example.test;
ERROR 1105 (HY000): command denied to user 'destroyer'@'%'
MySQL [(none)]> delete from example.test where c1=2;
Query OK, 1 row affected (0.008 sec)
MySQL [(none)]> drop table example.test;
ERROR 1105 (HY000): command denied to user 'destroyer'@'%'
MySQL [(none)]> truncate table example.test;
ERROR 1105 (HY000): command denied to user 'destroyer'@'%'
Notice, the destroyer can delete but not drop or truncate. Everything seems to be set up correctly.
As you can see, you can get very descriptive with your users and permissions models using users and grants. That concludes the practical example of users and privileges but we've only scratched the surface of what's possible. You can define roles. You can define permissions based on the hosts the users originate from. The MySQL, and thus Dolt, permissions model is vast.
Locked yourself out?
What if you lock yourself out? For instance, you delete all the users that have the ability to create other users and you need to make a new one.
There are two solves here. The first is to start the server with a default user defined. This will create a new user with that name with all privileges granted to it.
Here's an example. In one shell, start a server with the -u recovery option,
dolt_new $ dolt sql-server -u recovery
Starting server with Config HP="localhost:3306"|T="28800000"|R="false"|L="info"
2022-08-18T11:39:37-07:00 INFO [conn 1] NewConnection {DisableClientMultiStatements=false}Now, in another shell, log in as the recovery user:
$ mysql -h 127.0.0.1 -u recovery
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.7.9-Vitess
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> show grants;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for recovery@% |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `recovery`@`%` WITH GRANT OPTION |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
Now, you can set up new users to your heart's content.
The other option is to just start over. The mysql database is stored in the .doltcfg/privileges.db file where you are running your Dolt SQL server. If you delete this file, you will be able to log in as the default root user. Note, all your other users will be gone so this is the reset --hard option.
$ rm -rf .doltcfg/privileges.db
$ dolt sql-server
Starting server with Config HP="localhost:3306"|T="28800000"|R="false"|L="info"
2022-08-18T11:44:18-07:00 INFO [conn 1] NewConnection {DisableClientMultiStatements=false}Now, in the client shell:
$ mysql -h 127.0.0.1 -u root
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.7.9-Vitess
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> select user, host from mysql.user;
+------+------+
| User | Host |
+------+------+
| root | % |
+------+------+
1 row in set (0.001 sec)
Back to where we started.
Some Dolt Idiosyncrasies
Most of the things outlined above (excluding .doltcfg) are the same as MySQL. This article would not be complete without cataloguing some of the idiosyncrasies of Dolt's permissions system.
The first thing to mention is versioning. Dolt is a version controlled database. But Dolt permissions are not versioned. Permissions operate like a normal SQL database. Updates destroy the previous entry in the database. Also, when you dolt push only the database is pushed. So, for instance, you need to make a separate backup mechanism for your permissions database. We built permissions this way to maintain MySQL compatibility.
If you want the same permissions on multiple databases, you can make copies or share a version of .doltcfg/privileges.db. You can specify which version of the privileges.db file to use using the --privileges-file option when running dolt sql-server.
Lastly, privileges are only respected when SQL is being issued via a client to the Dolt SQL server. Privileges are not respected by dolt sql. This is on our list of things to fix but for now, dolt sql always runs with super user permissions. Beware who has ssh access to your host if you are worried about this.
Try Dolt Permissions Today
Dolt is rapidly becoming a fully featured OLTP database. Permissions are a critical piece of the OLTP feature set. As you can see from this introductory blog, Dolt supports a fully featured permissions model. Try it out today. Questions? Our engineering team hangs out on our Discord and is always ready and willing to field questions from interested users.