MySQL Users and All Privileges

Question

Ok, I'm trying to add a user to a MySQL database. This user should be able to add other users to the database it has privileges for. So I did this:

GRANT ALL privileges ON thedbname.* TO 'topuser'@'%' IDENTIFIED BY 'pass';

However, I this user cannot add users. Is that because I only gave them 'all' for a single database? The permissions show up as 'N' when I view them whereas if I remove the database name they show as 'Y'.

I want 'topuser' to be able to run this:

GRANT SELECT ON thedbname.* TO 'seconduser'@'%' IDENTIFIED BY 'pass';

They do not need to add users to other databases, hence my attempts here.

This is for a web app where users provide a username and password to access a database so that a password is not stored in the code. Other solutions welcomed though!

Answer 1

You have to GRANT the "grant to other privilege". The manual of the GRANT is available here.

MySQL to include only basic privileges in the ALL, if you take a close look at the documentation it says all is :

Grant all privileges at specified access level except GRANT OPTION

so you should grant the "grant option" to your top user :

GRANT GRANT OPTION ON thedbname.* TO 'topuser'@'%' IDENTIFIED BY 'pass';