MySQL Blog Archive
For the latest blogs go to blogs.oracle.com/mysql
How to create multiple accounts for an app?

This is a 3 part blog series:

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.

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

For instance:

Now, foo has the ability to do the following:

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

For instance:

Now, foo has the ability to do the following:

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.

Grant the SYSTEM_USER privilege to one user.

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

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

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

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’.

baz_admin can change the password of foo_admin.

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.

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.

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

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