What are the minimum privileges required to create MySQL databases, users, and permissions?

Question

Using MySQL 5.6 or 5.7, hosted in AWS RDS or the Docker MySQL container , I would like to create the user with the least privileges, for example, with the name creator , which can do the following:

• Create a new database.
• Create a new user.
• Grant the new user SELECT and INSERT permission for all tables in the new database.

I would prefer that the creator user does not have access to existing databases, for which he was not responsible for the creation.

Is this achievable?

My research so far shows that for such a creator user, global SELECT and INSERT permissions for a MySQL instance may be required, but this seems excessive.

Answer 1

You can do this with a stored procedure and a workaround that is simpler than SP, and may be what you are looking for: GRANT CREATE USER ON *.* TO '<user>'@'<host'; GRANT ALL PRIVILEGES ON `<user>_%`.* TO '<user>'@'<host>' WITH GRANT OPTION; GRANT CREATE USER ON *.* TO '<user>'@'<host'; GRANT ALL PRIVILEGES ON `<user>_%`.* TO '<user>'@'<host>' WITH GRANT OPTION; As you can see in this answer , you need to pay attention to several points:

• The user will be able to create databases starting with their username + '_' (using the given examples of links, if your username is aaa , you can create a database named aaa_example if aaa wants to create a database named bbb_example mysql will remove the error allowed by permission .
• He will have only privileges for the databases he creates, but created by his created users (if he grants this privilege).
• However, he could manage privileges in the databases that belonged to him.

With that in mind, you can customize this `<user>_%` to whatever suits your needs.