如何在 MySQL 中限制其他用户删除 root 用户? [英] How to restrict other user drop root user in MySQL?

问题描述

我创建了一个用户并授予了创建用户"权限.我发现用户可以删除任何其他用户，包括 root.我怎样才能防止这种情况发生?

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?

实际上，我们正在实施 Mysql 即服务，就像 AWS 的 RDS 一样.我们将创建一个将由系统管理使用的超级用户.我们的客户将拥有另一个拥有大部分权限的用户，包括创建用户"，以便他们可以自己管理帐户.我想找出除 Mysql 正常权限以外的方法来获得该权限

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

如果您有 AWS RDS，您会发现 RDS 有一个rdsadmin"用户帐户.如果你运行 DROP USER 'rdsadmin'@'localhost' 你会得到一个错误:ERROR 1396 (HY000): Operation DROP USER failed for 'rdsadmin'@'localhost'.我很好奇如何实现它.

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.

我尝试在 mysq.user 表上添加触发器以在用户从表中删除 'rdsadmin' 时引发错误.但触发器仅适用于 DELETE FROM USER ... sql 不适用于 DROP USER.你知道原因吗，或者有什么办法可以解决?

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?

推荐答案

您可以通过创建数据库 API 来解决此问题.SQL 代码应该对您有所帮助.

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

创建mysql用户的过程.

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 ;

删除并检查 created_users 表以确保用户存在并删除正确检查的过程.

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 ;

并授予执行这些权利

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

并且您可能希望在 mysql.proc 上为用户提供 select priv，以便他们可以查看程序背后的源代码并知道参数

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

您可以像这样使用数据库 API.

You can use the database API like this.

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

请注意，root 帐户只能删除具有所有者 root@localhost 的 mysql.dropUser 用户

这篇关于如何在 MySQL 中限制其他用户删除 root 用户?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！