chill_notes/Linux/MySQL/MySQL用户权限设置.md

MySQL 用户权限设置

MySQL 用户创建、授权、管理

---

连接 MySQL

mysql -u root -p

---

1. 创建新用户

-- 创建用户（本地访问）
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';

-- 创建用户（远程访问）
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' IDENTIFIED BY 'password';

-- 刷新权限
FLUSH PRIVILEGES;

---

2. 设置数据库访问权限

-- 只能访问指定数据库
GRANT ALL PRIVILEGES ON mydb.* TO 'username'@'localhost' IDENTIFIED BY 'password';

-- 访问所有数据库
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';

-- 只能访问指定表的某列
GRANT SELECT ON mydb.users TO 'username'@'localhost';

---

3. 设置操作权限

权限	说明
ALL PRIVILEGES	所有权限
SELECT	查询
INSERT	插入
UPDATE	更新
DELETE	删除
CREATE	创建
DROP	删除
WITH GRANT OPTION	可授权

-- 授予所有权限（管理员）
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;

-- 只授予查询权限
GRANT SELECT ON *.* TO 'username'@'localhost';

-- 授予查询和插入权限
GRANT SELECT, INSERT ON *.* TO 'username'@'localhost';

-- 授予增删改查权限
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'username'@'localhost';

---

4. 撤销权限

REVOKE SELECT, INSERT ON *.* FROM 'username'@'localhost';
FLUSH PRIVILEGES;

---

5. 设置远程访问权限

-- 允许指定 IP 访问
GRANT ALL PRIVILEGES ON *.* TO 'username'@'192.168.1.100' IDENTIFIED BY 'password';

-- 允许所有 IP 访问
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' IDENTIFIED BY 'password';

配置文件修改

sudo vim /etc/mysql/my.cnf

注释掉绑定地址：

# bind-address = 127.0.0.1

重启 MySQL：

sudo systemctl restart mysql

---

6. Root 用户管理

-- 查看所有用户
SELECT user, host FROM mysql.user;

-- 允许 root 远程访问
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

-- 禁止 root 远程访问
DELETE FROM mysql.user WHERE user='root' AND host='%';
FLUSH PRIVILEGES;

---

7. 删除用户

DROP USER 'username'@'localhost';

---

8. 查看用户权限

SHOW GRANTS FOR 'username'@'localhost';

---

常用示例

-- 创建开发用户
CREATE USER 'dev'@'localhost' IDENTIFIED BY 'dev_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'dev'@'localhost';
FLUSH PRIVILEGES;

-- 创建只读用户
CREATE USER 'reader'@'%' IDENTIFIED BY 'read_password';
GRANT SELECT ON myapp.* TO 'reader'@'%';
FLUSH PRIVILEGES;

-- 修改用户密码
SET PASSWORD FOR 'username'@'localhost' = PASSWORD('new_password');
FLUSH PRIVILEGES;

---

参考：MySQL 用户权限管理