MySQL :: How to create multiple accounts for an app?

This is a 3 part blog series:

Part 1: The SYSTEM_USER Dynamic Privilege
Part 2 : Partial Revokes from Database Objects
Part 3 : How to create multiple accounts for an app?

You can now grant CREATE USER so that your web apps would be able to use multiple accounts without you risking the end user hijacking the database by changing your root credentials for example. Wouldn’t it be nice, if you could grant a user privileges to create or modify users except a few users? If you have such use cases, then this blog post will interest you.

First, let us understand how you can modify a user’s specifications. There are following two ways to do that.

Through a DDL statements if you have ‘CREATE USER’ privilege.

For instance:

mysql> CREATE USER foo;
mysql> GRANT CREATE USER ON *.* TO foo WITH GRANT OPTION;

1 2	mysql> CREATE USER foo; mysql> GRANT CREATE USER ON . TO foo WITH GRANT OPTION;

Now, foo has the ability to do the following:

mysql> CREATE USER bar;
mysql> ALTER USER root@localhost IDENTIFIED BY 'gibberish';
mysql> DROP USER root@localhost;

mysql> CREATE USER bar;

mysql> ALTER USER root@localhost IDENTIFIED BY 'gibberish';

mysql> DROP USER root@localhost;

Through a DML statements if you have DB-level privileges on the mysql database.

For instance:

mysql> GRANT SELECT, UPDATE, DELETE ON mysql.* TO foo WITH GRANT OPTION;

1	mysql> GRANT SELECT, UPDATE, DELETE ON mysql.* TO foo WITH GRANT OPTION;

Now, foo has the ability to do the following:

mysql> UPDATE mysql.user SET authentication_string='gibberish' WHERE  
     > user='root';
mysql> DELETE FROM mysql.user WHERE user='root';

mysql> UPDATE mysql.user SET authentication_string='gibberish' WHERE

> user='root';

mysql> DELETE FROM mysql.user WHERE user='root';

In both cases, foo was able to modify the root account. What if you want to protect accounts such as root from being modified by other users?

In MySQL 8.0.16, we have added the following two capabilities.

A SYSTEM_USER privilege. It prevents users who have that privilege from being modified by the users who have privileges to create or modify users, but do not have the SYSTEM_USER privilege. You may read more about SYSTEM_USER in this blog post.
Revoke privileges from database objects, even if the user is granted global privileges. You may read more about privilege restrictions in this blog post.

This is the final blog post in the series that shows how to protect users leveraging the preceding two capabilities. Let us understand doing that through the following example.

The discussion here assumes that ‘partial_revokes’ system variable is ON.

We create two users and grant the CREATE USER privilege to them.

mysql> CREATE USER foo_admin, bar_admin;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT CREATE USER ON *.* TO foo_admin, bar_admin;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE USER foo_admin, bar_admin;

Query OK, 0 rows affected (0.01 sec)

mysql> GRANT CREATE USER ON *.* TO foo_admin, bar_admin;

Query OK, 0 rows affected (0.01 sec)

Grant the SYSTEM_USER privilege to one user.

mysql> GRANT SYSTEM_USER ON *.* TO foo_admin;
Query OK, 0 rows affected (0.01 sec)

1 2	mysql> GRANT SYSTEM_USER ON . TO foo_admin; Query OK, 0 rows affected (0.01 sec)

Grant the global update privilege to other user, but revoke privileges on the mysql database.

mysql> GRANT UPDATE ON *.*  TO bar_admin;
Query OK, 0 rows affected (0.01 sec)

mysql> REVOKE UPDATE ON mysql.* FROM bar_admin;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT UPDATE ON *.* TO bar_admin;

Query OK, 0 rows affected (0.01 sec)

mysql> REVOKE UPDATE ON mysql.* FROM bar_admin;

Query OK, 0 rows affected (0.01 sec)

bar_admin cannot change foo_admin using DDL statements because foo_admin has SYSTEM_USER but bar_admin does not.

mysql> ALTER USER foo_admin IDENTIFIED BY 'gibberish';
ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

mysql> DROP USER foo_admin;
ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

mysql> ALTER USER foo_admin IDENTIFIED BY 'gibberish';

ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

mysql> DROP USER foo_admin;

ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

bar_admin cannot change foo_admin because privileges on the mysql database have been revoked.

mysql> UPDATE mysql.user SET authentication_string ='gibberish' WHERE
     > user='foo_admin';
ERROR 1142 (42000): UPDATE command denied to user 'bar_admin'@'localhost' for table 'user'

mysql> DELETE  FROM mysql.user WHERE user = 'foo_admin';
ERROR 1142 (42000): DELETE command denied to user 'bar_admin'@'localhost' for table 'user'

mysql> UPDATE mysql.user SET authentication_string ='gibberish' WHERE

> user='foo_admin';

ERROR 1142 (42000): UPDATE command denied to user 'bar_admin'@'localhost' for table 'user'

mysql> DELETE FROM mysql.user WHERE user = 'foo_admin';

ERROR 1142 (42000): DELETE command denied to user 'bar_admin'@'localhost' for table 'user'

Thus, you created a user ‘foo_admin’ who cannot be modified by another user even though the latter has privileges to modify some users. For bar_admin to modify foo_admin, bar_admin must also have the SYSTEM_USER privilege.

Let us create another user who has ‘CREATE USER’ and ‘SYSTEM_USER’ privileges. This user can modify the properties of user ‘foo_admin’.

mysql> CREATE USER baz_admin;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT CREATE USER, SYSTEM_USER ON *.* TO baz_admin;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER baz_admin;

Query OK, 0 rows affected (0.01 sec)

mysql> GRANT CREATE USER, SYSTEM_USER ON *.* TO baz_admin;

Query OK, 0 rows affected (0.00 sec)

baz_admin can change the password of foo_admin.

mysql> ALTER USER foo_admin IDENTIFIED BY 'gibberish';
Query OK, 0 rows affected (0.01 sec)

1 2	mysql> ALTER USER foo_admin IDENTIFIED BY 'gibberish'; Query OK, 0 rows affected (0.01 sec)

As we saw, in order to modify the properties of a user who is granted the SYSTEM_USER privilege, you need to have CREATE USER as well as SYSTEM_USER privilege.

Based on the preceding observations, we may visualize users with respect to the SYSTEM_USER and CREATE USER privileges as following.

System Users:

Users who are granted at least ‘SYSTEM_USER’ privilege, but not the CREATE USER privilege. These users themselves have no capability to modify other users. These users can only be modified by power users.

Privileged Users

The users who are granted at least ‘CREATE USER’ privilege, but not the ‘SYSTEM_USER’ privilege. These users can modify the all users except system users.

Non-privileged Users

The users who have neither ‘SYSTEM_USER’ nor ‘CREATE USER’ privilege, but may be granted other privileges. These users cannot modify any other users.

Power Users:

The users who are granted at least the ‘SYSTEM_USER’ and ‘CREATE USER’ privileges. These users can modify any user available in the database. These users are most powerful users hence named as power users.

The strategy to create the immutable users

Evaluate carefully which users really need to be granted the SYSTEM_USER privilege. There should not be many users who will need the SYSTEM_USER privilege.
To protect users against being modified through DDL statements, grant them the SYSTEM_USER privilege. This prevents them from being modified by users who do not have SYSTEM_USER. Your root account will definitely be one of them. Not surprisingly, it is granted the SYSTEM_USER privilege by default.
To protect against being modified through DML statements, impose a partial revoke on the mysql database on administrative users. First, you may create an administrative user who has DB-related privileges, granted globally but revoked for mysql database. You may use this administrative user to grant privileges to other users. You could achieve the same effect through roles and making them default as well. Whatever suits you best?

Let us see how to do that through a role.

mysql> CREATE ROLE administrator;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT SELECT, CREATE, ALTER, DROP, DELETE, INSERT, UPDATE ON *.*    
     > TO administrator;
Query OK, 0 rows affected (0.01 sec)

mysql> REVOKE SELECT, CREATE, ALTER, DROP, DELETE, INSERT, UPDATE ON 
     > mysql.* FROM administrator;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE ROLE administrator;

Query OK, 0 rows affected (0.01 sec)

mysql> GRANT SELECT, CREATE, ALTER, DROP, DELETE, INSERT, UPDATE ON *.*

> TO administrator;

Query OK, 0 rows affected (0.01 sec)

mysql> REVOKE SELECT, CREATE, ALTER, DROP, DELETE, INSERT, UPDATE ON

> mysql.* FROM administrator;

Query OK, 0 rows affected (0.01 sec)

Create an administrator user who needs the global access of DB-level privileges. We can achieve this by granting the previously created role to the user.

mysql> CREATE USER foo_admin;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT administrator TO foo_admin;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE USER foo_admin;

Query OK, 0 rows affected (0.01 sec)

mysql> GRANT administrator TO foo_admin;

Query OK, 0 rows affected (0.01 sec)

Connect as the administrator and activate the role to get the required privileges.

mysql> SET ROLE administrator;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS\G
*************************** 1. row ***************************
Grants for foo_admin@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON *.* TO `foo_admin`@`%`
*************************** 2. row ***************************
Grants for foo_admin@%: REVOKE SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON `mysql`.* FROM `foo_admin`@`%`
*************************** 3. row ***************************
Grants for foo_admin@%: GRANT `administrator`@`%` TO `foo_admin`@`%`
3 rows in set (0.00 sec)

mysql> SET ROLE administrator;

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS\G

*************************** 1. row ***************************

Grants for foo_admin@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON *.* TO `foo_admin`@`%`

*************************** 2. row ***************************

Grants for foo_admin@%: REVOKE SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON `mysql`.* FROM `foo_admin`@`%`

*************************** 3. row ***************************

Grants for foo_admin@%: GRANT `administrator`@`%` TO `foo_admin`@`%`

3 rows in set (0.00 sec)

Conclusion

In this blog post we learned to create users who are can be protected from getting modified by the users who usually create and modify users.

We hope you found this blog post series informative and interesting. Please give the techniques described here a try and let us know your feedback.

Thank you for using MySQL