MySQL用户创建和权限管理完整教程

前言

做网站开发或者运维的同学，肯定会遇到这种情况：需要给开发或者运营同学一个数据库账号，但是又不能给root权限，怕他们误操作删库。这时候就需要用到MySQL的用户和权限管理功能了。

很多新手同学只会用root账号登录数据库，不知道怎么创建新用户、怎么分配权限。这篇文章就把MySQL用户创建和权限管理的完整教程整理出来，从创建用户、授权、改密码到删除用户，一步一步讲清楚。

一、创建用户

首先我们来创建一个新用户。MySQL创建用户有两种方式：CREATE USER 和 GRANT。

方式1：用CREATE USER创建用户

CREATE USER 'testuser'@'localhost' IDENTIFIED BY '123456';

这个命令创建了一个叫testuser的用户，密码是123456，只能从本机（localhost）连接MySQL。

如果你想让用户可以从任何地方连接，可以把localhost改成%：

CREATE USER 'testuser'@'%' IDENTIFIED BY '123456';

注意：生产环境不建议用%，最好指定具体的IP地址，更安全。

方式2：用GRANT创建用户并直接授权

其实更常用的是创建用户的同时直接授权，一步到位：

GRANT ALL PRIVILEGES ON testdb.* TO 'testuser'@'localhost' IDENTIFIED BY '123456';

这个命令会自动创建testuser用户，并且把testdb数据库的所有权限都给它。

二、给用户授权

创建完用户之后，我们来给用户分配权限。MySQL的权限粒度很细，可以精确到某张表、某个字段。

常用权限说明

• ALL PRIVILEGES：所有权限
• SELECT：查询权限
• INSERT：插入权限
• UPDATE：更新权限
• DELETE：删除权限
• CREATE：创建表/数据库权限
• DROP：删除表/数据库权限
• ALTER：修改表结构权限
• INDEX：创建索引权限
• CREATE VIEW：创建视图权限
• PROCESS：查看进程权限
• RELOAD：执行flush权限
• SHOW DATABASES：查看所有数据库权限

给用户所有权限

GRANT ALL PRIVILEGES ON testdb.* TO 'testuser'@'localhost';

这个命令的意思是：把testdb数据库里所有表的所有权限都给testuser用户。

只给用户查询和修改权限

GRANT SELECT, INSERT, UPDATE, DELETE ON testdb.* TO 'testuser'@'localhost';

这样用户就只能查询、插入、修改、删除数据，不能修改表结构，也不能删表。

只给用户某张表的权限

GRANT SELECT ON testdb.users TO 'testuser'@'localhost';

这样用户只能查询testdb库里的users表，其他表都访问不了。

给用户所有数据库的权限

GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'localhost';

.的意思是所有数据库的所有表。这个权限基本相当于root了，要谨慎使用。

授权之后一定要刷新权限

每次给用户授权之后，一定要执行下面这个命令刷新权限，不然授权可能不生效：

FLUSH PRIVILEGES;

三、查看用户权限

想知道某个用户有什么权限，可以用下面这个命令：

SHOW GRANTS FOR 'testuser'@'localhost';

执行完之后会显示这个用户的所有权限。

查看所有用户

如果想看看MySQL里有哪些用户，可以查询mysql.user表：

SELECT user, host FROM mysql.user;

四、修改用户密码

如果要修改某个用户的密码，可以用下面的命令：

ALTER USER 'testuser'@'localhost' IDENTIFIED BY '新密码';

修改完之后也要刷新权限：

FLUSH PRIVILEGES;

五、回收用户权限

如果用户权限给多了，想收回某些权限，可以用REVOKE命令：

收回所有权限

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'testuser'@'localhost';

收回某个具体权限

REVOKE DELETE ON testdb.* FROM 'testuser'@'localhost';

这样用户就没有删除数据的权限了。

回收完权限之后也要刷新：

FLUSH PRIVILEGES;

六、删除用户

如果某个用户不需要了，可以删除：

DROP USER 'testuser'@'localhost';

注意：删除用户的时候，用户已有的权限也会一起被删掉。

七、创建用户的安全建议

1. 不要随便给用户ALL PRIVILEGES：按需授权，只给需要的权限就行
2. 不要用%允许所有IP连接：生产环境最好指定具体的IP
3. 不要用root账号给应用用：单独创建一个应用账号，只给需要的权限
4. 密码要复杂一点：不要用123456这种简单密码
5. 定期清理不用的用户：不用的用户及时删掉，减少安全隐患

常见坑

坑1：授权之后忘了FLUSH PRIVILEGES

很多同学授权完之后直接就用了，结果发现权限没生效。这就是因为忘了执行FLUSH PRIVILEGES。每次授权或者改密码之后，一定要执行一下这个命令刷新权限。

坑2：用户创建了但是连不上

很多同学创建完用户之后，用这个用户去连接数据库，结果连接失败。这一般是因为：

1. 你创建用户的时候指定的是localhost，但是你从其他IP连接
2. 防火墙没放行MySQL端口
3. 用户密码输错了

坑3：%和localhost搞混了

很多同学不知道'localhost'@'%'和'%'@'localhost'的区别。记住：用户名是前面的，后面的是允许连接的IP。

坑4：给了权限但是还是操作不了

有时候给了用户权限，但是用户还是操作不了。这一般是因为：

1. 忘了FLUSH PRIVILEGES
2. 表或者字段的权限没给
3. 用户根本就没有这个数据库的权限

总结

MySQL用户创建和权限管理的核心内容：

1. 创建用户：CREATE USER 或者 GRANT 直接创建并授权
2. 给用户授权：GRANT 权限 ON 数据库.表 TO 用户@IP
3. 查看权限：SHOW GRANTS FOR 用户
4. 改密码：ALTER USER 用户 IDENTIFIED BY 新密码
5. 回收权限：REVOKE 权限 ON 数据库.表 FROM 用户
6. 删除用户：DROP USER 用户
7. 所有操作完之后都要 FLUSH PRIVILEGES 刷新权限

记住这些命令，以后给应用或者同事分配数据库账号的时候就不用慌了。安全第一，不要随便给用户root权限。

遇到问题加QQ23979811 协助处理