MySQL account creation requires too many privs?

[steveg]

I have a user (user1) for a certain database (db1). I need this user to be able to create and remove other users for the SAME database (db1).

I have this working, but I had to grant GLOBAL privs using PHPMyAdmin as root, which I am sure I should not have to do, because that grants this user access to ALL other databases, which I would prefer not to do. I am sure something must be wrong.

I would expect to ONLY have to grant this user privs on his own database (db1) and on the mysql database (where the user tables are), with GRANT privs. However it fails unless I ALSO grant ALL global privs as well.

I am using a perl script to do the account creation with an SQL statement like this...
GRANT SELECT ON db1.* TO 'user2'@'localhost' IDENTIFIED BY 'password'
and for removal again
DROP USER 'user2'@'localhost'

MySQL Version 4.1.21
PHPMyAdmin version 2.8.2.4

Anyone suggest what I might be missing?

 

[cPanelKenneth]

It's because the user does not have privileges necessary to update the mysql.* tables. That is why you needed to grant in the global context. You essentially have two root users now.

When granting privileges, the user only receives privileges in the context given. Thus:

GRANT ALL PRIVILEGES ON db.* TO user1@localhost WITH GRANT OPTION;

only allows user1 to alter the db context. When a new user is added to MySQL, it happens in the global context because new records are added to the various tables in the mysql database. As long as those records already exist, a normal user with sufficient privileges can update them, but not add to them, unless granted explicitly.

 

[steveg]

Hmmmmm, thanks for the reply.

User1 (the one that needs to create and remove other users) was created using phpmyadmin under the cpanel account in which this user and database lives, and added to the database db1.

After that I used phpmyadmin under the WHM account for root to grant ALL privs on mysql.* to this user, WITH Grant.

This should mean (if I have understood it correctly) that the user can use the database db1, and has all the necessary privs to create new user. However that fails, and it is ONLY if I use phpmyadmin as root again to grant ALL GLOBAL privs that it works. - which doesn't seem right.

 

[cPanelKenneth]

No it doesn't. I just tested the following:

grant all privileges on mysql.* to user2@localhost with grant option;
grant all privileges on bob.* to user2 with grant option;

$ mysql -u user2
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11 to server version: 5.0.24a-Debian_9ubuntu0.1-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use bob;
Database changed
mysql> create table one (id int);
Query OK, 0 rows affected (0.02 sec)

mysql> grant INSERT on bob.one to normal1;
Query OK, 0 rows affected (0.04 sec)

I snipped some of the output. The gist is I had to use both the shown GRANT statements for user2 to have the ability to create a user for the bob database. Different MySQL versions, but the privilege model is the same.

 

[steveg]

Solved....PHPMyadmin gets it wrong...!

Thanks for the reply again, your example code used the command line for creating the initial user that will need to create other users where I had been using PHPMyAdmin, so I decided to use the command line myself..... and it worked.

Create a user in PHPMyAdmin, tick all the boxes, including grant to add that user to the mysql database, AND do the same for the database in question, and that user cannot create new users for that database.

Do the same thing on the command line, and that user CAN create new users.

I have a solution then. Thankyou.