Mysql – Create a user with privileges to create users

loginsMySQLpermissionsusers

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.

Best Answer

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.

Related Question