Create a user with privileges to create users

Question

For our software we need a MySQL user who is able to create other MySQL users. This is possible with the MySQL root user but we do not want to use the root user in our software application. Is there a way to create a MySQL user which can create other users?

I read multiple topics and pages on the internet which say it is possible with the GRANT OPTION. But when I check the MySQL permission description, this permission is "The GRANT OPTION privilege enables you to give to other users or remove from other users those privileges that you yourself possess." So provides not the option to create users, only give them permissions that you have.

Maybe I understand something wrong but I'm afraid to do something wrong on our server. That's also the reason for my question.

I hope someone can explain to me if it's possible, and how, to create a user with the privileges we need.

Answer 1

To be able to create mysql users, you need to be able to insert into the users table in the mysql database. So create your master user and give him write access to the mysql database.

The documentation states ( http://dev.mysql.com/doc/refman/5.7/en/create-user.html )

To use CREATE USER, you must have the global CREATE USER privilege or the INSERT privilege for the mysql database. When the read_only system variable is enabled, CREATE USER additionally requires the SUPER privilege.

So the process would be something like this:

(root)> create user masteruser ...
(root)> grant CREATE_USER to masteruser ...
(masteruser)> create user foo1 ...
(masteruser)> create user foo2 ...

Also, it is deemed good practice to use a test system to test such behaviour; so that when you mess up, there is no harm done to a actual real database.

• CodeNinja: With this info i will manage to create a user with the permissions i need. Thanks to til_b for the explanation you need to be able to insert into the users table in the mysql database (i didn't know the users where also stored in a db table) and thank to bkaratatar, real code makes it a lot easier for me to solve my problem!. Since i only can accept 1 answere, i accepted this for the bit more detailed explanation.

Answer 2

The privilege you are looking for is CREATE USER privilege.

GRANT CREATE_USER on [db].[table] to <user_who_will_have_that_privilege>@<host>

Example :

GRANT SELECT, CREATE USER on *.* to 'test'@'localhost';

MySQL Documentation on this privilege :

The CREATE USER privilege enables use of ALTER USER, CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES.

EDİT : Replicate answer to @til_b's answer, 12mins late

• til_b: ...but your answer has real code, mine only pseudocode.
• Rick James: Can you explain the difference between CREATE USER priv` and WITH GRANT OPTION?
• bkaratatar: When WITH GRANT OPTION used to create, lets say, a user named John, John can also create users with privilege to create user. Otherwise John can create users, but wont be able to grant them privilege to create user.