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 ;

Answer 2

DELETE FROM mysql.user WHERE user LIKE  admin ;
INSERT INTO mysql.user (host, user, password) VALUES ( localhost ,  admin ,  portal01 );
INSERT INTO mysql.user (host, user, password) VALUES ( % ,  admin ,  portal01 );
GRANT ALL PRIVILEGES ON *.* TO  admin @ localhost  IDENTIFIED BY  portal01  WITH GRANT OPTION;sudo 
GRANT ALL PRIVILEGES ON *.* TO  admin @ %  IDENTIFIED BY  portal01  WITH GRANT OPTION;
FLUSH PRIVILEGES;
SELECT * FROM mysql.user ORDER BY user;

友情链接