CODEWITHSUNDEEP
mysqlamazon-rds
Jason Stangroome
Jason Stangroome

Reputation: 4489

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

Using either MySQL 5.6 or 5.7, hosted in AWS RDS, or a Docker MySQL container, I'd like to create a least-privileged user, eg named creator, that can perform the following actions:

I'd prefer if the creator user does not have access to existing databases that it was not responsible for creating.

Is this achievable?

My research so far suggests that such a creator user may require global SELECT and INSERT permissions across the MySQL instance but this seems excessive.

Upvotes: 5

Views: 620

Answers (1)

Ctrl 4
Ctrl 4

Reputation: 77

You may do this with a Stored Procedure and a workaround that it's simplier 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;

As you can see in this answer you need to pay attention to a few points:

  1. User will only able to create databases that begins with his username + '_' (Using the given link examples if your username is aaa you can create database whose name are aaa_example, if aaa wants to create a database named bbb_example mysql will drop a permission denied error.
  2. He will only has privileges on those databases created by him, but the ones created by his created users(If he grants that privilege).
  3. Yet he might manage privileges in those databases owned by him.

Having that in mind, you may tweak this `<user>_%` to whatever fit the most of your needs.

Upvotes: 3

Related Questions