Mysql用户权限教程与实用案例

前言

本文介绍下Mysql如何控制用户的访问权限，包括创建用户、给用户授权、生产环境限制网段写权限控制、生产环境只读账号创建等实用操作。本文用通俗易懂的表述，帮你从0开始掌握Mysql用户授权。

操作环境：Mysql v5.7版本

一.查询Mysql用户

查询当前数据库连接下，存在的用户：

SELECT  User,Host FROM mysql.user order by user;

可用于查询用户是否创建成功。

二.创建Mysql用户

创建一个名为java，密码为Java_123456的用户：

CREATE USER 'java'@'%' IDENTIFIED BY 'Java_123456';

参数解释：

①.'CREATE USER ：创建用户关键字
②.java为用户名，可自定义。标准写法可由''包裹。
③.@为连接符（不可更改）
④.%为Host占位符，代表所有IP（所有主机）都能访问。（非常重要，后文会用到）
⑤.IDENTIFIED BY 'Java_12345' 设置用户密码为Java_123456

三.Mysql授权案例讲解

1.给用户java授予所有数据库的全部操作权限

grant all privileges on *.* to 'java'@'%' identified by 'Java_123456' WITH GRANT OPTION;  

参数解释：

①.grant all privileges on：授权关键字
②. *.* ：第一个*代表一个数据连接下的所有数据库都有权限查看。第二个*代表数据库下所有表都能查看。如果只想让该用户看到其中一个数据库testDB，则语法为：

GRANT ALL PRIVILEGES ON `testDB`.* TO 'java'@'%'

③. 'java'@'%' identified by 'Java_123456' ：参考上文《二.创建Mysql用户》
④.WITH GRANT OPTION ：授予用户可以给用户授权的权限，即此用户可以执行grant all privileges on此类SQL。如果当前用户仅需要增删改查数据库，就不需要加此命令，如果该账户作为DBA需要给别的账户授权，那就加上次命令。

2.给用户Java授权某一个数据的全部操作权限

-- 用户名java，数据库testDB，密码TestDB123
CREATE USER 'java'@'%' IDENTIFIED BY 'TestDB123';
-- 查询用户是否创建成功
SELECT  User,Host FROM mysql.user order by user;
-- 授权
GRANT ALL PRIVILEGES ON `testDB`.* TO 'java'@'%'
-- 查看用户授权成功否
show grants for 'java'@'%';
-- 移除权限
revoke all on `testDB`.*  from 'java'@'%';
-- 删除用户
drop user 'java'@'%';

3.给用户java授权所有数据库只读权限

-- 用户名java，数据库testDB，密码TestDB123
CREATE USER 'java'@'%' IDENTIFIED BY 'TestDB123';
-- 授权只读
GRANT SELECT ON testDB.*  TO 'java'@'%'
-- 查看用户授权成功否
show grants for 'java'@'%';

4.给用户java授权可以在固定IP操作数据库

例：我们想让用户Java仅在10.2.96.179这个IP下能够操作数据库，并为此用户设置一个独立密码TestDB456

-- 用户名java，数据库testDB，密码TestDB123，此时你可以用TestDB123在此IP地址登陆，否则无法登陆
CREATE USER  'java'@'10.2.96.179'  IDENTIFIED BY 'TestDB123';

-- 单个账号，单个IP授权，并将此ip对应的密码改为TestDB456。此时你只能用TestDB456这个密码在10.2.96.179这个IP下登陆，否则无法登陆
grant all privileges on *.* to 'java'@'10.2.96.179' identified by 'TestDB456' ;

-- 查看用户授权成功否
show grants for 'java'@'10.2.96.179';

5.给用户java授权，可以在某个IP网段下操作数据库

此种授权与上方类似，只不过用%代替IP网段。我们让所有IP以10.19开头的，都能操作数据：

-- 网段：'10.19.%'
CREATE USER 'java'@'10.19.%' IDENTIFIED BY 'TestDB123';
-- 查询用户是否创建成功
SELECT  User,Host FROM mysql.user order by user;
-- 给网段授权
grant all privileges on *.* to 'java'@'10.19.%';  
-- 查看用户授权成功否
show grants for  'java'@'10.19.%';  

总结

本文讲解了Mysql用户创建和用户授权，并增加了很多实用案例，包括授权管理员用户、授权只读账户、授权摸个IP网段、授权某个IP、授权单个数据库等实用案例，只需将案例中的用户名、密码、数据库替换为你的信息，就可以使用，授权SQL写法都经过验证，放心使用吧。建议收藏本文，防止错误授权，造成业务系统数据无法写入或丢失。喜欢本文请点赞收藏。