Access Management
Access management in Dolt is handled similarly to how it is handled in MySQL. When Dolt is running in server mode (via sql-server), it relies upon the grant tables (mysql.user, mysql.db, etc.) to control user access. Access is determined by the privileges that a user has. For more information on the basics of how users and privileges work and how to use them, please read our blog post from when we announced their inclusion into Dolt. This document will assume some familiarity with users and privileges.

Enabling Users

Server Mode Required

Privilege access checks are only enabled when running in sql-server mode. All other commands, including all permutations of sql, operate with privilege access checks disabled. Currently, the only way to remotely interact with a dolt database is when a database is running under server mode, therefore it is the only context where it is sensible for users to exist. As all other commands require file-level access to a database's files, any possible protection offered by other commands enabling users could easily be circumvented by directly modifying the data within the database files.
In addition to privilege checks only being enabled when running in server mode, any changes to the grant tables will only persist in server mode. If any commands that modify the grant tables are used outside the server mode, those commands will fail, as the grant tables will not exist (they're only created when in server mode).

CLI Argument

To enable full user support when using sql-server mode, you may pass in the --privilege-file="PATH" argument. "PATH" represents the path to the privileges file, generally named privs.json.

YAML Configuration Option

To enable full user support when using sql-server mode, you may add the privilege_file: PATH line to your YAML config. PATH represents the path to the privileges file, generally named privs.json.

User and Password Arguments

Before the introduction of users and privileges, Dolt supported only a single user with an accompanying password. This was done using the --user and --password arguments (see the docs for their defaults, also available using YAML configuration), whereby a server would only allow connections that supplied that singular user and password combination. Although Dolt now supports users in a similar fashion to MySQL, we still retain the user and password arguments. In MySQL, the default super account (generally called the root user) is created during installation and configuration. Rather than creating a parallel with init, we handle the super account creation when starting a server via the arguments. Rather than creating this super account during init, we instead handle the super account creation when starting a server via the arguments.
This leads to an interaction with the privilege file that should be noted. When the privilege file is specified, it will be created as an empty file if it does not exist. As soon as any statement that modifies the grant tables (CREATE USER, GRANT, REVOKE, etc.) executes, the users and all privileges will save to the privilege file. This includes the super account as defined by the user and password arguments, therefore it is recommended that the super account is deleted after all users are set up, or it is given a strong password. On subsequent server starts, if the privilege file contains any data, the user and password arguments are fully ignored. This behavior was chosen so that server should always have at least one user that a client may log into, otherwise the server would be completely inaccessible.

Editing Users

Editing users and their privileges must be done from sql-server mode. You may also directly edit the privilege file, which is detailed below. Dolt comes with a MySQL client built-in, which is the sql-client command. In addition to allowing access to a running server, the client command also has the --dual flag which runs a server in the background, while the foreground automatically connects to the background server using the given port, etc.
Importantly, as described in the previous section, if a non-empty privilege file is provided, then the --user and --password arguments (also available via a YAML configuration file) only function as login credentials. Otherwise, the arguments handle both the creation of a super account and login credentials.

Privilege File

The privilege file contains a JSON object, designed to be editable by anyone inclined to do so. The structure is straightforward, although it is guaranteed to change in the future as more features are added (thus increasing the data that needs to be persisted). Below is an example file representing the following properties (formatted for easier viewing):
  • The default super account is root
    • May only be connected to from localhost
  • There is a role named test_role
    • Has the privileges SELECT, INSERT, UPDATE, and DELETE on all databases and tables
    • Roles are the same as users except for a special boolean being set, and are locked to prevent being used for logging in
  • There is a user named test_user
    • Can be connected to from any IP
    • Does not contain a password
    • Has the DROP privilege on the table some_tbl in the database some_db
    • Has both the CREATE and DROP privileges on the other_db database
  • The role test_role has been granted to test_user
  • The "Columns" field is present, but is currently unused
1
{
2
"Users": [
3
{
4
"User": "root",
5
"Host": "localhost",
6
"PrivilegeSet": {
7
"GlobalStatic": [
8
"SELECT",
9
"INSERT",
10
"UPDATE",
11
"DELETE",
12
"CREATE",
13
"DROP",
14
"RELOAD",
15
"SHUTDOWN",
16
"PROCESS",
17
"FILE",
18
"GRANT",
19
"REFERENCES",
20
"INDEX",
21
"ALTER",
22
"SHOW DATABASES",
23
"SUPER",
24
"CREATE TEMPORARY TABLES",
25
"LOCK TABLES",
26
"EXECUTE",
27
"REPLICATION SLAVE",
28
"REPLICATION CLIENT",
29
"CREATE VIEW",
30
"SHOW VIEW",
31
"CREATE ROUTINE",
32
"ALTER ROUTINE",
33
"CREATE USER",
34
"EVENT",
35
"TRIGGER",
36
"CREATE TABLESPACE",
37
"CREATE ROLE",
38
"DROP ROLE"
39
],
40
"Databases": []
41
},
42
"Plugin": "mysql_native_password",
43
"Password": "*A0F874BC7F54EE086FCE60A37CE7887D8B31086B",
44
"PasswordLastChanged": "1970-01-01T00:00:01Z",
45
"Locked": false,
46
"Attributes": null,
47
"IsRole": false
48
},
49
{
50
"User": "test_role",
51
"Host": "%",
52
"PrivilegeSet": {
53
"GlobalStatic": [
54
"SELECT",
55
"INSERT",
56
"UPDATE",
57
"DELETE"
58
],
59
"Databases": []
60
},
61
"Plugin": "mysql_native_password",
62
"Password": "",
63
"PasswordLastChanged": "2022-02-15T00:00:00.0000000Z",
64
"Locked": true,
65
"Attributes": null,
66
"IsRole": true
67
},
68
{
69
"User": "test_user",
70
"Host": "localhost",
71
"PrivilegeSet": {
72
"GlobalStatic": [],
73
"Databases": [
74
{
75
"Name": "some_db",
76
"Privileges": [],
77
"Tables": [
78
{
79
"Name": "some_tbl",
80
"Privileges": [
81
"DROP"
82
],
83
"Columns": []
84
}
85
]
86
},
87
{
88
"Name": "other_db",
89
"Privileges": [
90
"CREATE",
91
"DROP"
92
],
93
"Tables": []
94
}
95
]
96
},
97
"Plugin": "mysql_native_password",
98
"Password": "",
99
"PasswordLastChanged": "2022-02-15T00:00:00.0000000Z",
100
"Locked": false,
101
"Attributes": null,
102
"IsRole": false
103
}
104
],
105
"Roles": [
106
{
107
"FromHost": "%",
108
"FromUser": "test_role",
109
"ToHost": "localhost",
110
"ToUser": "test_user",
111
"WithAdminOption": false
112
}
113
]
114
}
Copied!

Updates and Persistence

Unlike in MySQL, Dolt immediately processes all updates to the grant tables. Any privilege changes will immediately take effect, any deleted users with current sessions will immediately lose access, etc. Whether these are benefits or drawbacks depend on those running the database. For Dolt, the decision to have all updates take immediate effect allows for emergency updates to not require a server restart in some cases, which we believe offers some security and convenience advantages. The benefit of delayed updates do not seem as likely or often, although we may still change this behavior in the future if it is proven otherwise.
Persistence to the privilege file is immediate only when the grant tables are modified through their typical statements (CREATE USER, GRANT, REVOKE, etc.). Directly modifying the grant tables using INSERT, UPDATE, etc. will cause an immediate update to all current users, however it will not be immediately persisted to the privilege file. The privilege file is only updated when the aforementioned statements are executed. This may change in the future.
Lastly, any manual changes to the privilege file should be done when the server is not running. Not only is the privilege file only read when the server is starting, but the privilege file's contents will be overwritten if any of the aforementioned commands are run.

Statements

For now, only some of the core statements are supported for users and privileges. Of those core statements, some are fully supported, while others only offer partial support.

Fully Supported

  • CREATE ROLE
  • DROP ROLE
  • DROP USER
  • SHOW PRIVILEGES
The following grant tables are fully implemented:
  • mysql.user
    • Contains the user definition, global static privileges, login details, password limits, account maximums, and attributes
    • Although this table is fully implemented, we do not support all of the features that this table provides, even though they may be set
      • For those features that are not yet implemented, their column values may not survive a server restart
  • mysql.db
    • Contains the database-level privileges
  • mysql.tables_priv
    • Contains the table-level privileges
  • mysql.role_edges
    • Contains the connections between all roles and users

Partially Supported

  • CREATE USER
    • For now, we only support setting the username and host name, global static privileges, locked status, and a basic mysql_native_password for authentication
    • All other fields, such as the DEFAULT ROLE and multiple auth options, are either ignored or will throw an error
    • Even though a comment and attributes may be set (and are persisted), they are ignored, and this includes partial revokes
  • GRANT
    • The form GRANT <privileges> ON <privilege_level> TO <users...> does not yet support columns, an object type (tables only), or assuming a different user
    • The form GRANT <roles...> TO <users...> [WITH ADMIN OPTION] is fully supported
    • The form GRANT PROXY ... is not yet supported
  • REVOKE
    • The form REVOKE <privileges...> ON <privilege_level> FROM <users...> does not yet support columns or an object type (tables only)
    • The form REVOKE <roles...> FROM <users...> is fully supported
    • The form REVOKE PROXY ... is not yet supported
    • The form REVOKE ALL PRIVILEGES ... is not yet supported, which differs from REVOKE ALL ON ... in functionality
  • SHOW GRANTS
    • Displays global static grants and granted roles
    • Does not yet display a user's database or table-level privileges
    • The optional [USING <roles...>] portion is not yet supported

Not Yet Supported

  • ALTER USER
  • RENAME USER
  • SET DEFAULT ROLE
  • SET PASSWORD
  • SET ROLE
The following grant tables (and their associated functionality) are not yet supported:
  • mysql.global_grants
    • Contains all global dynamic grants
  • mysql.columns_priv
    • Contains column privileges
  • mysql.procs_priv
    • Contains stored procedures
  • mysql.proxies_priv
    • Contains proxy accounts
  • mysql.default_roles
    • Stores each user's default roles
  • mysql.password_history
    • Contains password changes
The following system variables are not yet supported:
  • mandatory_roles
    • All roles (and users) named here are granted to all users automatically, and cannot be revoked or dropped
  • activate_all_roles_on_login
    • Sets all roles to active upon logging into a user
    • As SET ROLE is also not yet implemented, any granted roles are automatically active when granted and logging in
      • This will be changed as soon as SET ROLE is implemented

Future Plans

We plan to incorporate all currently missing statements and functionality that MySQL contains regarding users and privileges. In addition, we also plan to allow for all of our versioning features to have their access privilege-checked. This includes the Dolt SQL functions (DOLT_COMMIT(), DOLT_CHECKOUT(), etc.) as well as only allowing specific users to manage specific branches, just to name a few of the planned features. This page will be updated as features are added!