0

My task is simple i want to create a user in MySQL server that has all the privileges but excluding the ability to create other users.

I tried to create a user through the root account. then i gave him all the privileges, then i revoked the 'create user' privilege from this new user, then i 'flushed privileges'. i thought this is would be enough but when i logged in with the new user, i was able to create another user.

these are the codes that i used in my root account to create a new user and give him privileges except for the 'create user' privilege:

     create user 'user91'@'localhost' identified by 'google';
     grant all privileges on *.* to 'user91'@'localhost';
     revoke create user on *.* from 'user91'@'localhost';
     flush privileges;

After this i logged out of the root account and signed in with the 'user91' account and tried to create another user with this code:

     create user 'slim2000'@'localhost' identified by 'stealth';

and this code worked, what should i add in the first block of code in order to prevent 'user91' from creating other users.

  • Was the user created WITH GRANT OPTION? – Michael - sqlbot Oct 26 '15 at 3:35
  • Hi @mohamed - do a SHOW GRANTS FOR 'slim2000'@'localhost'; you should have only USAGE - which means that the user can only login and not connect to any schemas. It's puzzling - I'm afraid I don't have time to check it out tonight - I'll look tomorrow. Motto of the story - only give the grants that you know they'll need - no need to REVOKE then. – Vérace Oct 26 '15 at 21:58
1

This works:

As root:

mysql> CREATE USER 'myuser'@'%' IDENTIFIED BY 'billy';
Query OK, 0 rows affected (0.04 sec)

Then, on the sport schema, grant ALL to myuser

mysql> GRANT ALL ON sport.* TO 'myuser'@'%';
Query OK, 0 rows affected (0.02 sec)

Exit and then log on as the new user myuser

mysql> exit;
Bye
[pol@localhost inst]$ ./bin/mysql -S ./mysql.sock -u myuser -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.22-log Source distribution

Show all the schemas myuser can see

mysql> show schemas;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| sport              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

There are many more schemas on this server - it's just that myuser can't see them.

mysql> use sport;
Database changed

Then, try to create a user as myuser.

mysql> create user 'fred'@'%' IDENTIFIED BY 'mary';
ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation

Fails.

But, I can do this.

mysql> create table test (a int, b varchar(10));
Query OK, 0 rows affected (0.53 sec)

mysql> insert into test values(3, 'testvalue');
Query OK, 1 row affected (0.04 sec)

Take a look at the three answers here, here and here for a better idea of what's going on. Always test your users after creation - it's very easy to give users too much power.

You can also do it this way (a more elegant solution perhaps)

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, 
    INDEX, ALTER, CREATE TEMPORARY TABLES 
ON sport.* TO 'username'@'localhost' IDENTIFIED BY 'password';

Finally, you could just remove the CREATE USER privilege (and, of course, the WITH GRANT OPTION) from this answer to have a "super user" who can do everything except create new users.

I think @sqlbot may have figured out the root of your problem.

  • thank you for this great answer, i would to share with you my code so that you can tell me what i did wrong: – mohamed Oct 26 '15 at 19:35
  • Why not share your code here? The more people who see it, the more likely you are to get good answers. You can either edit your question or you could ask a new question with a link back to this question? If you think my answer could help others, you can give me an upvote or mark it correct. Oh, and I forgot in my last post, welcome to the forum! :-) – Vérace Oct 26 '15 at 19:41
  • thank you for this great answer, i would to share with you my code so that you can tell me what i did wrong: first i created a user just as you did, then i granted his privileges by saying (grant all privileges on . to 'user91'@'localhost' identified by 'google';) then i revoked the create user privilege by (revoke create user on . from 'user91'@'localhost';) and then (flush privileges;). with this i was still able to create a new user with 'user91' but unable to grant any privileges. did i miss anything? – mohamed Oct 26 '15 at 19:42
  • i dont know why the codes aren't showing properly in this comment but when i said 'grant all privileges on...' then there are two stars and a dot in the middle. and the same goes for the rest of the codes – mohamed Oct 26 '15 at 19:47
  • Sometimes Markdown can change/swallow characters like asterisks, greater than/less than and others. Better maybe to edit your question and let me know by leaving a comment with @Vérace - note the accent over the e! – Vérace Oct 26 '15 at 20:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.