MYSQL用户、角色和权限你真的懂吗?

一、用户角色权限表描述

1.MySQL用户角色权限相关表

表名	描述
user	用户表
db	数据库权限表
tables_priv	表权限表
columns_priv	字段权限表
proxies_priv	代理角色表

描述：

上述表，是我们装完MySQL数据库后，系统库“mysql”中的表。

当我们创建一个用户后，相应在user表中就会有一行记录。
db表是数据库权限的表，表明用户是否有操作某个数据库的使用权限。
tables_priv表是表权限表，表明用户是否有操作某张表的权限。
columns_priv表是字段权限表，表明用户是否有操作某个字段的权限。
proxies_priv代理角色表，表明用户和某个角色之间的关系。在MySQL中用户就是角色。

二、实际案例操作

1.创建一个MySQL用户

create user 'test01'@'%' identified by 'test01';

描述：

创建一个用户名为test01的用户，密码是test01，'%'代表该用户客户端IP不限。
示例：'test01'@'192.168.0.%' ，代表test01用户客户端IP只能是 [ 192.168.0.0 - 192.168.0.255 ] 。

执行结果：

删除用户（这个不必删除，看看就行。）：

drop user 'test01'@'%';

2.授予用户权限

授予这个用户 mall.* 也就是“mall”这个数据库下的全部的权限。

GRANT SELECT ON mall.* TO 'test01'@'%' IDENTIFIED BY 'test01' WITH GRANT OPTION;

授予这个用户全部数据库的全部权限。

GRANT SELECT ON *.* TO 'test01'@'%' IDENTIFIED BY 'test01' WITH GRANT OPTION;

3.查看用户权限

查看用户权限

SHOW GRANTS FOR 'test01'@'%';

4.删除用户权限

删除用户的权限

REVOKE SELECT ON *.*  FROM 'test01'@'%';

删除用户的权限

REVOKE SELECT ON mall.*  FROM 'test01'@'%';

5.设置用户只能访问表有限个字段的权限

5.1 创建表并新增数据

CREATE TABLE `account`(
    `id` int(11) NOT NULL,
    `name` varchar(50) DEFAULT NULL,
    `balance` int(255) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_balance` (`balance`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `account` values ('1','lilei','900');
insert into `account` values ('2','wangwang','600');
insert into `account` values ('3','xiaohua','200');
insert into `account` values ('4','hebing','300');

5.2 查看并清理掉该用户之前设置的权限，单单只设置用户表字段权限，去控制台查看结果。

GRANT SELECT(id,name) ON mall.account TO 'test01'@'%';

结果显示我们只能查 id和name 的字段。

mysql> select * from mall.account ;
ERROR 1142 (42000): SELECT command denied to user 'test01'@'localhost' for table 'account'
mysql>
mysql> select id,name from mall.account ;
+----+----------+
| id | name |
+----+----------+
| 1 | lilei |
| 2 | wangwang |
| 3 | xiaohua |
| 4 | hebing |
+----+----------+
4 rows in set (0.00 sec)

6.用户角色配置

6.1 查看角色代理是否开启

检查用户角色开启

set GLOBAL check_proxy_users = 1;

native 密码加密

set GLOBAL mysql_native_password_proxy_users = 1;

执行完设置再次查看就开启了。

6.2 创建角色和测试用户

创建一个开发角色

create USER 'dev_role';

搞两个用户

create USER 'muzi';
create USER 'fuyi';

5.7后不需要执行这个

GRANT PROXY ON "@" TO 'root'@'%' WITH GRANT OPTION;

授权角色给用户

grant proxy on 'dev_role' to 'muzi';
grant proxy on 'dev_role' to 'fuyi';

6.3 授权给角色,拥有该角色的用户就都会有这个权限

grant select(id,name) on mall.account to 'dev_role';

6.4 使用muzi用户登陆并查看

“muzi”这个用户没有密码，提示填写密码直接回车。

mysql -umuzi -p

mysql> select * from mall.account;
ERROR 1142 (42000): SELECT command denied to user 'dev_role'@'localhost' for table 'account'
mysql> select id,name f from mall.account;
+----+----------+
| id | name |
+----+----------+
| 1 | lilei |
| 2 | wangwang |
| 3 | xiaohua |
| 4 | hebing |
+----+----------+
4 rows in set (0.00 sec)

****可以看到muzi也有了该角色的权限，只能访问id和name字段。****

三、总结

我们可以通过上述的方式去管理我们的数据库，避免程序员从删库到跑路的心酸历程。
可能上述的方式并非是最最专业的DBA管理MySQL的用法，不过身为开发的我们是需要掌握一些这类的知识，即便是测试库也要有一些规范，一个root打天下显得很不专业。