How to restrict other user drop root user in MySQL?

Question

I created a user and granted "CREATE USER" privileges. I found that user can drop any other user include root. How can I prevent that happen?

Actually we are implementing Mysql as a Service just like AWS's RDS. We will create a super user which will be used by system management. Our customer will have another user which has most of privileges including "CREATE USER", so that they can manage account themselves. I want to find out a approach other than Mysql normal privileges to get that

If you have AWS RDS, you will find RDS has a 'rdsadmin' user account. If you run DROP USER 'rdsadmin'@'localhost' you will get a error: ERROR 1396 (HY000): Operation DROP USER failed for 'rdsadmin'@'localhost'. I'm curious how to implement that.

I tried add a trigger on mysq.user table to throw a error when user delete 'rdsadmin' from table. but the trigger only work for DELETE FROM USER ... sql not for DROP USER. Do you know the reason, or is there any way to fix it?

Answer 1

As Devart said, You cannot change the privileges of CREATE USER.

However, what it appears like you are doing is provisioning mysql instances out to users to control for themselves. To that end, how about a server management tool. There's many out there, to include froxlor Server Management Studio (which is free and open source): https://www.froxlor.org/

It's just a thought.

Answer 2

It is not possible to grant privileges to specified users, CREATE USER is a global privilege.

I'd suggest you to separate MySQL users, for example - there are can be some of them: for administrating (with root privileges), for developers (to access and change database objects and tables data), and so on...

Answer 3

You can use this work around by creating an database API. The SQL code should help you.

CREATE TABLE mysql.`created_users` (
  `user_name` varchar(255) DEFAULT NULL,
  `owner` varchar(255) DEFAULT NULL
)

The table hold the usernames and what user created them. Note create the Procedures with your root account

Procedure to create an mysql user.

DROP PROCEDURE IF EXISTS mysql.createUser;

DELIMITER //
CREATE PROCEDURE mysql.createUser(IN userName VARCHAR(255), IN userPassword VARCHAR(255))
 BEGIN

  SET @createUserQuery = CONCAT('
        CREATE USER "',userName,'"@"localhost" IDENTIFIED BY "',userPassword,'" '
        );
  PREPARE stmt FROM @createUserQuery;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;


  SET @createcreatedUserQuery = CONCAT('
        INSERT INTO mysql.created_users (user_name, owner) VALUE("',userName,'", "',USER(),'")'
        );
  PREPARE stmt FROM @createcreatedUserQuery;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

 END //
DELIMITER ;

Procedure to drop and with check on created_users table to make sure the user exists and delete right check.

DROP PROCEDURE IF EXISTS mysql.dropUser;

DELIMITER //
CREATE PROCEDURE mysql.dropUser(IN userName VARCHAR(255))
 BEGIN  

  SET @canDeleteUser = 0;   

  SET @createCountUserQuery = CONCAT('
        SELECT COUNT(*) FROM mysql.created_users WHERE user_name = "',userName,'" AND owner = "',USER(),'" INTO @canDeleteUser'
        );

  PREPARE stmt FROM @createCountUserQuery;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;  

  IF @canDeleteUser = 0 THEN

      SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'The user is not known on the server or you dont have rights to delete this user';
  END IF;

  IF @canDeleteUser = 1 THEN
      SET @createDropUserQuery = CONCAT('
        DROP USER "',userName,'"@"localhost"'
        );
      PREPARE stmt FROM @createDropUserQuery;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;

      SET @createDeleteUserQuery = CONCAT('
        DELETE FROM created_users WHERE user_name = "',userName,'" AND owner = "',USER(),'"'
        );
      PREPARE stmt FROM @createDeleteUserQuery;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;    
  END IF;

 END //
DELIMITER ;

And to give rights to execute these

GRANT EXECUTE ON PROCEDURE mysql.createUser TO '[user]'@'localhost';
GRANT EXECUTE ON PROCEDURE mysql.dropUser TO '[user]'@'localhost';

And you may want to give the user select priv on mysql.proc so they can see the source code behind the procedures and know the parameters

You can use the database API like this.

CALL mysql.createUser('user', 'password');
CALL mysql.dropUser('user');

Note that root account can only remove users with mysql.dropUser that have the owner root@localhost