MySQL按数据库前缀最小授权

1. 查看当前用户的完整权限

-- 查看用户的所有授权
SHOW GRANTS FOR 'user'@'%';

-- 查看数据库级别的权限
SELECT * FROM mysql.db WHERE User = 'user' AND Host = '%';

-- 查看表级别权限
SELECT * FROM mysql.tables_priv WHERE User = 'user' AND Host = '%';

2. 撤销全局权限并重新授权
   如果前面用户授予了全局权限你需要撤销用户的全局权限，只保留特定权限：

-- 1. 撤销所有全局权限
REVOKE ALL PRIVILEGES ON *.* FROM 'user'@'%';
REVOKE GRANT OPTION ON *.* FROM 'user'@'%';

-- 2. 授予创建数据库的权限（只能全局授予）
GRANT CREATE ON *.* TO 'user'@'%';

-- 3. 授予对prefix_开头的数据库的所有权限
GRANT ALL PRIVILEGES ON `prefix\_%`.* TO 'user'@'%';

-- 4. 刷新权限
FLUSH PRIVILEGES;

-- 5. 验证权限
SHOW GRANTS FOR 'user'@'%';

3. 如果还需要其他全局权限

-- 撤销所有权限
REVOKE ALL PRIVILEGES ON *.* FROM 'user'@'%';
REVOKE GRANT OPTION ON *.* FROM 'user'@'%';

-- 授予最小必要全局权限
GRANT CREATE, PROCESS, REPLICATION CLIENT, REPLICATION SLAVE 
ON *.* TO 'user'@'%';

-- 授予对prefix_开头的数据库的所有权限
GRANT ALL PRIVILEGES ON `prefix\_%`.* TO 'user'@'%';

FLUSH PRIVILEGES;

4. 新建用户并授权

-- 1. 创建新用户
CREATE USER 'lfl_user'@'%' IDENTIFIED BY 'new_password';

-- 2. 授予创建数据库权限
GRANT CREATE ON *.* TO 'lfl_user'@'%';

-- 3. 授予对lfl_开头的数据库的所有权限
GRANT ALL PRIVILEGES ON `lfl\_%`.* TO 'lfl_user'@'%';

-- 4. 可选：授予一些必要的全局权限
GRANT PROCESS, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'lfl_user'@'%';

-- 5. 刷新权限
FLUSH PRIVILEGES;

-- 6. 验证
SHOW GRANTS FOR 'lfl_user'@'%';

5. 用户只能查看自己权限的数据库

# my.cnf 或 my.ini
[mysqld]
skip-show-database

6. 检查权限

-- 查看全局权限
SELECT User, Host, Select_priv, Insert_priv, Create_priv, Show_db_priv 
FROM mysql.user 
WHERE User = 'user';

-- 查看数据库级别权限
SELECT * FROM mysql.db WHERE User = 'user';

-- 查看权限摘要
SHOW GRANTS FOR 'user'@'%';

输出

+---------------------------------------------------+
| Grants for user@%                           |
+---------------------------------------------------+
| GRANT CREATE, PROCESS ON *.* TO 'user'@'%'  |
| GRANT ALL PRIVILEGES ON `lfl\_%`.* TO 'user'@'%' |
+---------------------------------------------------+