MYSQL数据库权限

1. 查询root用户权限及host

SELECT user, host, Select_priv, Insert_priv, Update_priv, Grant_priv ,Drop_priv FROM mysql.user WHERE user = 'root';

2. 设置授权权限

GRANT ALL PRIVILEGES ON *.* TO 'root'@'host_ip' WITH GRANT OPTION;

一般管理员才授权所有权限，可以授权给个别host_ip

3. 给别人开远程访问权限

创建新用户并限制 Host

--1.创建用户，仅允许从特定 IP 连接
CREATE USER 'app_user'@'192.168.1.100' IDENTIFIED BY 'StrongPassword123!';

-- 或者，如果 IP 不固定，可以使用网段（如 192.168.1.%）或任意主机（%）
-- CREATE USER 'app_user'@'%' IDENTIFIED BY 'StrongPassword123!';
--2. 仅授予增删改查权限，不包含 DROP 和 GRANT，myapp_db是数据库名

GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_db.* TO 'app_user'@'192.168.1.100';-- 刷新权限使其生效

FLUSH PRIVILEGES;

3.验证权限

SELECT user, host, Select_priv, Insert_priv, Update_priv, Grant_priv, Drop_priv
FROM mysql.user
WHERE user = 'app_user' AND host = '192.168.1.100';