Mysql – To give a user all privileges except the ‘Create user’ privilege

command lineMySQL

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.

Best Answer

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.

Related Question