关注我《程序猿集锦》获取更多分享。
MySQL在5.7版本中开始支持角色的功能，但这并不是像MySQL8.0版本中的role的概念一样。在8.0版本中，有create role的语法，但是在5.7版本中并没有这样的语法。在5.7版本所谓的角色是使用create user语法来创建的，它更像是一种代理的概念。
下面是实验中使用到的SQL语句
/* 创建数据库 mydb1 */drop database if exists mydb1;create database mydb1 default charset utf8mb4;/* 创建表 mytab1 */use mydb1;drop table if exists mytab1;create table mytab1(id smallint not null primary key auto_increment, col1 varchar(255), col2 varchar(255), col3 varchar(255));/* 初始化数据 mytab1 */insert into mytab1(col1,col2,col3) values('a1','a2','a3'),('b1','b2','b3'),('c1','c2','c3');/* 创建角色myrole1 并为角色授权 */drop user if exists 'myrole1'@'%';create user 'myrole1'@'%';grant select on mydb1.* to 'myrole1'@'%';/* 创建用户myuser1 并把角色myrole1赋予该用户 */drop user if exists 'myuser1'@'%';create user 'myuser1'@'%' identified by 'myuser1';grant proxy on 'myrole1'@'%' to 'myuser1'@'%';/* 创建用户myuser2 并把角色myrole1赋予该用户 */drop user if exists 'myuser2'@'%';create user 'myuser2'@'%' identified by 'myuser2';grant proxy on 'myrole1'@'%' to 'myuser2'@'%';/* 创建数据库 mydb2 */drop database if exists mydb2;create database mydb2 default charset utf8mb4;/* 创建表 mytab2 */use mydb2;drop table if exists mytab2;create table mytab2(id smallint not null primary key auto_increment, col1 varchar(255), col2 varchar(255), col3 varchar(255));/* 初始化数据 mytab2 */insert into mytab2(col1,col2,col3) values('a1','a2','a3'),('b1','b2','b3'),('c1','c2','c3');在创建角色的时候可能有如下提示，是因为MySQL服务器设置的密码策略不允许创建没有密码的用户，但是我们的角色确实不需要使用密码的，因为它不需要登录。
mysql> create user 'myrole1'@'%';ERROR 1819 (HY000): Your password does not satisfy the current policy requirementsmysql>那么我们可以这样创建用户，但是又遇到了插件没有安装的错误提示：
mysql> create user 'myrole1'@'%' identified with mysql_no_login;ERROR 1524 (HY000): Plugin 'mysql_no_login' is not loadedmysql>因为角色不需要登录，我们可以有以下三种方式不让创建的角色登录，这里我们选在第一种方式。
那么就安装这个缺失的插件，在安装之前，先检查一下插件的情况：
mysql> show plugins;+----------------------------+----------+--------------------+----------------------+---------+| Name | Status | Type | Library | License |+----------------------------+----------+--------------------+----------------------+---------+| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL || mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL || sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL || CSV | ACTIVE | STORAGE ENGINE | NULL | GPL || MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL || InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL || INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL || MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL || MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL || PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL || ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL || BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL || FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL || partition | ACTIVE | STORAGE ENGINE | NULL | GPL || ngram | ACTIVE | FTPARSER | NULL | GPL || validate_password | ACTIVE | VALIDATE PASSWORD | validate_password.so | GPL |+----------------------------+----------+--------------------+----------------------+---------+45 rows in set (0.01 sec)mysql>发现确实没有安装前面错误信息中提示的mysql_no_login插件，执行下面的命令安装该插件
mysql> install plugin mysql_no_login soname 'mysql_no_login.so';Query OK, 0 rows affected (0.01 sec)mysql>再次查看安装的plugin是否有mysql_no_login插件。
mysql> show plugins;+----------------------------+----------+--------------------+----------------------+---------+| Name | Status | Type | Library | License |+----------------------------+----------+--------------------+----------------------+---------+| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL || mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL || sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL || CSV | ACTIVE | STORAGE ENGINE | NULL | GPL || MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL || InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL || INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMP_PER_INDEX | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_CMP_PER_INDEX_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_TEMP_TABLE_INFO | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_DEFAULT_STOPWORD | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_BEING_DELETED | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_CONFIG | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_INDEX_CACHE | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_FT_INDEX_TABLE | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL || INNODB_SYS_VIRTUAL | ACTIVE | INFORMATION SCHEMA | NULL | GPL || MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL || MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL || PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL || ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL || BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL || FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL || partition | ACTIVE | STORAGE ENGINE | NULL | GPL || ngram | ACTIVE | FTPARSER | NULL | GPL || validate_password | ACTIVE | VALIDATE PASSWORD | validate_password.so | GPL || mysql_no_login | ACTIVE | AUTHENTICATION | mysql_no_login.so | GPL |+----------------------------+----------+--------------------+----------------------+---------+46 rows in set (0.00 sec)mysql>关于更多关于MySQL中插件的安装与卸载的问题，请参考MySQL官方文档：Installing and Uninstalling Plugins
接着我们创建我们的角色
mysql> create user 'myrole1'@'%' identified with mysql_no_login;Query OK, 0 rows affected (0.02 sec)mysql> 创建完成后，我们去mysql.user表中查询，发现已经存在我们创建的角色了，并且我们创建的角色它的plugin选项显示不能登录，密码也是空的。
mysql> select user, host, plugin, authentication_string from mysql.user;+---------------+-----------+-----------------------+-------------------------------------------+| user | host | plugin | authentication_string |+---------------+-----------+-----------------------+-------------------------------------------+| root | % | mysql_native_password | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B || mysql.session | localhost | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE || mysql.sys | localhost | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE || myrole1 | % | mysql_no_login | |+---------------+-----------+-----------------------+-------------------------------------------+4 rows in set (0.00 sec)mysql>下面我们创建用户，用户名和密码我们设置为相同的字符串myuser1。
mysql> create user 'myuser1'@'%' identified by 'myuser1';ERROR 1819 (HY000): Your password does not satisfy the current policy requirementsmysql> SHOW VARIABLES LIKE 'validate_password%';+--------------------------------------+--------+| Variable_name | Value |+--------------------------------------+--------+| validate_password_check_user_name | OFF || validate_password_dictionary_file | || validate_password_length | 8 || validate_password_mixed_case_count | 1 || validate_password_number_count | 1 || validate_password_policy | MEDIUM || validate_password_special_char_count | 1 |+--------------------------------------+--------+7 rows in set (0.03 sec)mysql>发现创建用户失败，因为我们给用户指定的密码太简单了，需要设置一个复杂的密码。这是因为我们的这个MySQL服务安装了密码插件，就是前面我们看到的validate_password插件。它要求用户的密码必须满足一定的条件才可以创建成功。为了方便，我们暂时把这个密码的验证的等级修改为low低等级的，密码的长度验证修改为1。如果你的MySQL没有安装或没有启用validate_password插件，那么你的MySQL就可以不用做下面的设置，直接创建对应的用户就可以了。
mysql> set global validate_password_policy=low;Query OK, 0 rows affected (0.01 sec)mysql> set global validate_password_length=1;Query OK, 0 rows affected (0.01 sec)mysql> SHOW VARIABLES LIKE 'validate_password%';+--------------------------------------+-------+| Variable_name | Value |+--------------------------------------+-------+| validate_password_check_user_name | OFF || validate_password_dictionary_file | || validate_password_length | 4 || validate_password_mixed_case_count | 1 || validate_password_number_count | 1 || validate_password_policy | LOW || validate_password_special_char_count | 1 |+--------------------------------------+-------+7 rows in set (0.00 sec)mysql>然后在创建用户，并检查创建后的用户是否成功。可以看到我们创建的新的用户myuser1是可以登录，并且有密码的。
mysql> create user 'myuser1'@'%' identified by 'myuser1';Query OK, 0 rows affected (0.01 sec)mysql> select user, host, plugin, authentication_string from mysql.user;+---------------+-----------+-----------------------+-------------------------------------------+| user | host | plugin | authentication_string |+---------------+-----------+-----------------------+-------------------------------------------+| root | % | mysql_native_password | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B || mysql.session | localhost | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE || mysql.sys | localhost | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE || myrole1 | % | mysql_no_login | || myuser1 | % | mysql_native_password | *A8866F07643159AF3EDB07E8D0F951C740E7014F |+---------------+-----------+-----------------------+-------------------------------------------+5 rows in set (0.00 sec)mysql>上面我们把角色和用户都准备好了，下面我们把角色赋予创建好的用户，让这个用户具有这个角色。
mysql> grant proxy on 'myrole1'@'%' to 'myuser1'@'%';Query OK, 0 rows affected (0.02 sec)mysql> show grants for myuser1;+-----------------------------------------------+| Grants for myuser1@% |+-----------------------------------------------+| GRANT USAGE ON *.* TO 'myuser1'@'%' || GRANT PROXY ON 'myrole1'@'%' TO 'myuser1'@'%' |+-----------------------------------------------+2 rows in set (0.00 sec)mysql> show grants for myrole1;+--------------------------------------------+| Grants for myrole1@% |+--------------------------------------------+| GRANT USAGE ON *.* TO 'myrole1'@'%' || GRANT SELECT ON `mydb1`.* TO 'myrole1'@'%' |+--------------------------------------------+2 rows in set (0.00 sec)mysql> select host, user, proxied_host, proxied_user, with_grant, grantor from mysql.proxies_priv;+------+---------+--------------+--------------+------------+----------------------+| host | user | proxied_host | proxied_user | with_grant | grantor |+------+---------+--------------+--------------+------------+----------------------+| % | root | | | 1 | boot@connecting host || % | myuser1 | % | myrole1 | 0 | root@172.18.0.1 |+------+---------+--------------+--------------+------------+----------------------+2 rows in set (0.00 sec)mysql>此时我们已经把角色赋予对应的用户了，但是我们发现我们使用用户myuser1登录后，并不能看到我们希望它看到的数据库。
(base) ➜ ~ mysql -umyuser1 -pmyuser1 -h127.0.0.1 -P33061mysql> show databases;+--------------------+| Database |+--------------------+| information_schema |+--------------------+1 row in set (0.05 sec)mysql> use mydb1;ERROR 1044 (42000): Access denied for user 'myuser1'@'%' to database 'mydb1'mysql> exit;这是因为要启用角色代理功能，还需要配置两个参数，需要设置如下两个参数的值为ON才可以。
mysql> show global variables like 'check_proxy_users'; /*此时是OFF*/+-------------------+-------+| Variable_name | Value |+-------------------+-------+| check_proxy_users | OFF |+-------------------+-------+1 row in set (0.01 sec)mysql> show global variables like 'mysql_native_password_proxy_users'; /*此时是OFF*/+-----------------------------------+-------+| Variable_name | Value |+-----------------------------------+-------+| mysql_native_password_proxy_users | OFF |+-----------------------------------+-------+1 row in set (0.01 sec)mysql> set global check_proxy_users=ON; /*设置为ON*/Query OK, 0 rows affected (0.01 sec)mysql> set global mysql_native_password_proxy_users=ON; /*设置为ON*/Query OK, 0 rows affected (0.01 sec)mysql> show global variables like 'check_proxy_users'; /*检查结果是否为ON*/+-------------------+-------+| Variable_name | Value |+-------------------+-------+| check_proxy_users | ON |+-------------------+-------+1 row in set (0.01 sec)mysql> show global variables like 'mysql_native_password_proxy_users'; /*检查结果是否为ON*/+-----------------------------------+-------+| Variable_name | Value |+-----------------------------------+-------+| mysql_native_password_proxy_users | ON |+-----------------------------------+-------+1 row in set (0.01 sec)mysql>上面的设置只是暂时生效，如果要永久生效，请把上面的参数配置在MySQL的参数配置文件中my.cnf中，这样MySQL在重启后，仍然会启用角色代理的功能。配置内容如下：
[mysqld]check_proxy_users=ONmysql_native_password_proxy_users=ON此时再次验证myuser1是否可以看到mydb1这个数据库。通过下面的示例可以看到，已经可以查询mydb1下面的表了。
(base) ➜ ~ mysql -umyuser1 -pmyuser1 -h127.0.0.1 -P33061mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mydb1 |+--------------------+2 rows in set (0.03 sec)mysql> use mydb1;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;+-----------------+| Tables_in_mydb1 |+-----------------+| mytab1 |+-----------------+1 row in set (0.01 sec)mysql> select * from mytab1;+----+------+------+------+| id | col1 | col2 | col3 |+----+------+------+------+| 1 | a1 | a2 | a3 || 2 | b1 | b2 | b3 || 3 | c1 | c2 | c3 |+----+------+------+------+3 rows in set (0.01 sec)mysql>当使用代理用户访问MySQL的时候，可以看到如下的区别，函数CURRENT_USER()返回值并不是当前登录的myuser1，而是角色myrole1。
mysql> SELECT USER(), CURRENT_USER();+--------------------+----------------+| USER() | CURRENT_USER() |+--------------------+----------------+| myuser1@172.18.0.1 | myrole1@% |+--------------------+----------------+1 row in set (0.00 sec)mysql>查看当前登录的用户是否使用代理角色登录，可以使用下面的这个SQL语句查看。
mysql> SELECT @@proxy_user;+---------------+| @@proxy_user |+---------------+| 'myuser1'@'%' |+---------------+1 row in set (0.00 sec)mysql> show variables like 'proxy_user';+---------------+---------------+| Variable_name | Value |+---------------+---------------+| proxy_user | 'myuser1'@'%' |+---------------+---------------+1 row in set (0.01 sec)mysql>如果是一个非代理用户登录的数据库，参数proxy_user的查看的结果为空NULL，如下所示：
mysql> SELECT @@proxy_user;+--------------+| @@proxy_user |+--------------+| NULL |+--------------+1 row in set (0.00 sec)mysql> show variables like 'proxy_user';+---------------+-------+| Variable_name | Value |+---------------+-------+| proxy_user | |+---------------+-------+1 row in set (0.09 sec)mysql>上面的myuser1已经具有了myrole1这个角色，当我们使用myuser1登录数据库后，可以拥有myrole1这个角色所拥有的权限。但是如果此时，我们单独给myuser1授予一个新的权限，这个权限是和myrole1角色所拥有的权限是相互独立的，也就是说myuser1这个用户，除了具有myrole1这个角色之外，它还有属于自己的一些权限，那么此时用myuser1登录到数据库之后，是看不到它自己特有的那部分权限的。
另外，如果授予一个用户两个角色，则使用该用户登录的时候，只会使用第一个授予的角色登录。如下所示：myuser1出来具有myrole1这个角色之外，还有yourrole1这个角色。而myrole1对mydb开头的数据库有读权限，yourrole1对yourdb开头的数据库有读权限。而此时我们使用myuser1登录数据库之后，发现只能看到myrole1角色所能看到的内容，看不到它的第另外一个角色yourrole1所能看到数据库。
/* 下面命令都使用root用进行查看 */mysql> select host, user, proxied_host, proxied_user, with_grant, grantor from mysql.proxies_priv;+------+---------+--------------+--------------+------------+----------------------+| host | user | proxied_host | proxied_user | with_grant | grantor |+------+---------+--------------+--------------+------------+----------------------+| % | root | | | 1 | boot@connecting host || % | myuser1 | % | myrole1 | 0 | root@172.18.0.1 || % | myuser1 | % | yourrole1 | 0 | root@172.18.0.1 |+------+---------+--------------+--------------+------------+----------------------+4 rows in set (0.01 sec)mysql> show grants for 'myrole1'@'%';+--------------------------------------------+| Grants for myrole1@% |+--------------------------------------------+| GRANT USAGE ON *.* TO 'myrole1'@'%' || GRANT SELECT ON `mydb%`.* TO 'myrole1'@'%' |+--------------------------------------------+2 rows in set (0.00 sec)mysql> show grants for 'yourrole1'@'%';+------------------------------------------------+| Grants for yourrole1@% |+------------------------------------------------+| GRANT USAGE ON *.* TO 'yourrole1'@'%' || GRANT SELECT ON `yourdb%`.* TO 'yourrole1'@'%' |+------------------------------------------------+2 rows in set (0.00 sec)mysql> show grants for 'myuser1'@'%';+-------------------------------------------------+| Grants for myuser1@% |+-------------------------------------------------+| GRANT USAGE ON *.* TO 'myuser1'@'%' || GRANT SELECT ON `herdb`.* TO 'myuser1'@'%' || GRANT PROXY ON 'myrole1'@'%' TO 'myuser1'@'%' || GRANT PROXY ON 'yourrole1'@'%' TO 'myuser1'@'%' |+-------------------------------------------------+4 rows in set (0.00 sec)mysql> show databases;+-------------------------+| Database |+-------------------------+| information_schema || docker_compose_instance || herdb || mydb1 || mydb2 || mydb3 || mysql || performance_schema || sys || yourdb1 || yourdb2 |+-------------------------+11 rows in set (0.00 sec)mysql>myuser1等到数据库进行验证，看它能看到哪个角色锁能看到的数据库。通过验证发现，只能看到myrole1这个角色能看到的数据库，它自己单独被授权的herdb和它的另外一个角色yourrole1能看到的yourdb开头的数据库都看不到。这是因为myuser1在登录的时候，就被判定为是使用myrole1角色登录的，它只能拥有myrole1这个角色所具有的权限，其他的权限myuser1则没有。
(base) ➜ ~ mysql -umyuser1 -pmyuser1 -h127.0.0.1 -P33061mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mydb1 || mydb2 || mydb3 |+--------------------+4 rows in set (0.00 sec)mysql> select user(), current_user();+--------------------+----------------+| user() | current_user() |+--------------------+----------------+| myuser1@172.18.0.1 | myrole1@% |+--------------------+----------------+1 row in set (0.00 sec)mysql>总结为：如果一个用户被赋予了某一个角色，则它只能使用这个角色的身份进行登录，登录后也只能使用这个角色所有的权限，自己独有的或者额外赋予的其他权限，登录后不能使用。同时，一个用户不建议赋予2个及2个以上的角色。因为MySQL不会取两个角色对应权限的并集作为用户最后的权限，只会取第一个角色的权限。
我们再次创建一个用户myuser2，然后把myrole1这个角色也赋予myuser2，看下myuser2是不是和myuser1拥有同样的权限。
mysql> drop user if exists 'myuser2'@'%';Query OK, 0 rows affected, 1 warning (0.01 sec)mysql> create user 'myuser2'@'%' identified by 'myuser2';Query OK, 0 rows affected (0.01 sec)mysql> grant proxy on 'myrole1'@'%' to 'myuser2'@'%';Query OK, 0 rows affected (0.02 sec)mysql> show grants for 'myuser2'@'%';+-----------------------------------------------+| Grants for 'myuser2'@'%' |+-----------------------------------------------+| GRANT USAGE ON *.* TO 'myuser2'@'%' || GRANT PROXY ON 'myrole1'@'%' TO 'myuser2'@'%' |+-----------------------------------------------+2 rows in set (0.00 sec)mysql> select user, host, plugin, authentication_string from mysql.user;+---------------+-----------+-----------------------+-------------------------------------------+| user | host | plugin | authentication_string |+---------------+-----------+-----------------------+-------------------------------------------+| root | % | mysql_native_password | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B || mysql.session | localhost | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE || mysql.sys | localhost | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE || myrole1 | % | mysql_no_login | || myuser1 | % | mysql_native_password | *A8866F07643159AF3EDB07E8D0F951C740E7014F || myuser2 | % | mysql_native_password | *05BB3827F42B15D8C38C720FBBDACE970076AF83 |+---------------+-----------+-----------------------+-------------------------------------------+6 rows in set (0.00 sec)mysql> select host, user, proxied_host, proxied_user, with_grant, grantor from mysql.proxies_priv;+------+---------+--------------+--------------+------------+----------------------+| host | user | proxied_host | proxied_user | with_grant | grantor |+------+---------+--------------+--------------+------------+----------------------+| % | root | | | 1 | boot@connecting host || % | myuser1 | % | myrole1 | 0 | root@172.18.0.1 || % | myuser2 | % | myrole1 | 0 | root@172.18.0.1 |+------+---------+--------------+--------------+------------+----------------------+3 rows in set (0.01 sec)mysql>使用myuser2登录进行验证，通过下面的登录验证，我们发现myuser2也拥有了和myuser1相同的权限。
(base) ➜ ~ mysql -umyuser2 -pmyuser2 -h127.0.0.1 -P33061mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mydb1 |+--------------------+2 rows in set (0.01 sec)mysql> use mydb1;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;+-----------------+| Tables_in_mydb1 |+-----------------+| mytab1 |+-----------------+1 row in set (0.00 sec)mysql> select * from mytab1;+----+------+------+------+| id | col1 | col2 | col3 |+----+------+------+------+| 1 | a1 | a2 | a3 || 2 | b1 | b2 | b3 || 3 | c1 | c2 | c3 |+----+------+------+------+3 rows in set (0.00 sec)mysql> select user(), current_user();+--------------------+----------------+| user() | current_user() |+--------------------+----------------+| myuser2@172.18.0.1 | myrole1@% |+--------------------+----------------+1 row in set (0.01 sec)mysql> show variables like 'proxy_user';+---------------+---------------+| Variable_name | Value |+---------------+---------------+| proxy_user | 'myuser2'@'%' |+---------------+---------------+1 row in set (0.24 sec)mysql>在多租户的场景下面，一个租户一个schema，这些schema共同存在于一个MySQL数据库实例中，但是schema的名称有某规律：比如是以指定的字符串开头，后面跟上不同的租户的名称。
例如下面这样的数据库，都已mydb开头，用1、2、3来表示不同的租户，当然这里的1、2、3可以使用有意义的字符来标识。这里我们只是拿1、2、3来举例说明。
mysql> show databases like 'mydb%';+------------------+| Database (mydb%) |+------------------+| mydb1 || mydb2 || mydb3 |+------------------+3 rows in set (0.00 sec)mysql>此时如果需要让测试组中所有的测试人员，对mydb开头的数据库都有查询的权限，该如何实现？我们可以针对不同测试人员分别给他们授予读的权限，例如下面这样：
grant select on mydb1.* to 'myuser1'@'%';grant select on mydb2.* to 'myuser1'@'%';grant select on mydb3.* to 'myuser1'@'%';/*或者使用下面这一句授权语句来替代上面的三句*/grant select on `mydb%`.* to 'myuser1'@'%';除了上面的方式之外，结合前面我介绍的角色的概念，我们很容易想到使用角色来解决。
我们可以把所有对mydb开头的数据库的查询权限都赋予一个角色myrole1，然后把这个角色赋予所有测试人员，让所有的测试人员的账号都使用这个角色代理登录到MySQL数据库，这样就可以了。示例如下：
grant select on `mydb%`.* to 'myrole1'@'%'; /*此时，角色myrole1对所有mydb开头的数据库都有查询的权限了*//*以后人只要新增加一个账号，就把这个角色赋予对应新增加的账号即可*/create user 'myuser2'@'%' identified by 'myuser2'; /*新增加myuser2用户*/grant proxy on 'myrole1'@'%' to 'myuser2'@'%'; /*此时新增的用户myuser2对所有mydb开头的数据库都有查询权限了*/create user 'myuser3'@'%' identified by 'myuser3'; /*新增加myuser3用户*/grant proxy on 'myrole1'@'%' to 'myuser3'@'%'; /*此时新增的用户myuser3对所有mydb开头的数据库都有查询权限了*/要求使用MySQL5.7中的角色功能，我们需要如下几步：
关注我《程序猿集锦》获取更多分享。
| 留言与评论（共有 0 条评论） “” |
