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!

RageZ · Answer 1 · 2009-11-12T01:58:30.603

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';

edited Nov 12 '09 at 01:58

answered Nov 12 '09 at 01:51

RageZ

26,800
12
67
76

Tried that already but with "Error code 1410, SQL state 42000: You are not allowed to create a user with GRANT" error. – ajr Nov 12 '09 at 15:29
do you have root access to the server ? – RageZ Nov 13 '09 at 01:58

score 0 · Answer 2 · edited Apr 02 '13 at 16:19

0

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;

edited Apr 02 '13 at 16:19

kleopatra

51,061
28
99
211

answered Apr 02 '13 at 15:32

Petrovitch

139
3
11

Sure, you can do it with script. This is just raw code to give you the necessary information. I have copies a mysql.user table from one server to another when root information is lost. There is more than one way to skin a cat. – Petrovitch Apr 02 '13 at 16:45

MySQL Users and All Privileges

2 Answers2