![Mysql 用户可以看到所有数据库,但只能看到自己的数据库](https://linux22.com/image/559540/Mysql%20%E7%94%A8%E6%88%B7%E5%8F%AF%E4%BB%A5%E7%9C%8B%E5%88%B0%E6%89%80%E6%9C%89%E6%95%B0%E6%8D%AE%E5%BA%93%EF%BC%8C%E4%BD%86%E5%8F%AA%E8%83%BD%E7%9C%8B%E5%88%B0%E8%87%AA%E5%B7%B1%E7%9A%84%E6%95%B0%E6%8D%AE%E5%BA%93.png)
我们使用 Virtualmin 进行大部分数据库管理,但显然它有一个错误,在某些情况下会将用户的权限设置得太宽泛。此外,MySQL 手册没有提到使用“show databases”命令限制用户查看其他数据库的能力。
当我运行“show grants for 'user'@'localhost';”时,我得到了以下信息:
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for user@localhost |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, CREATE TEMPORARY TABLES ON *.* TO 'user'@'localhost' IDENTIFIED BY PASSWORD 'ENCRYPTED' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON `database`.* TO 'user'@'localhost' WITH GRANT OPTION |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
即使在我运行“GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, CREATE TEMPORARY TABLES ON database.* to 'user'@'localhost';”之后也是如此。
答案1
不确定您的实际问题是什么,但如果是为什么用户仍然可以看到所有内容,那是因为这些授权是附加的。
您需要删除您不想要的授权(第一个)并包含第二个授权。
即删除
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, CREATE TEMPORARY TABLES ON *.* TO 'user'@'localhost' IDENTIFIED BY PASSWORD 'ENCRYPTED'
并保持,
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON `database`.* TO 'user'@'localhost' WITH GRANT OPTION
尝试,
REVOKE SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, CREATE TEMPORARY TABLES ON *.* FROM 'user'@'localhost'