MySQL8 基于角色的权限管理

MySQL8新增了角色(role)的概念，使账号权限的管理，更加灵活方便。所谓角色，就是一些权限的集合。然后再把该集合授权给某个账户（往往是某一批账户，因为账号会绑定IP，不同的IP，虽然账号名相同被视为不同账号），这样当我们需要对这些账号减少或增加权限时，只需要修改权限集合（role）即可，不用单个账号多次修改。这确实使DBA的运维轻松了不少。

下面我们看下role是如何使用的。

创建角色

比如开发环境账户需要某库的所有权限，生产环境账号往往需要增删改查这些权限，我们可以单独为这些权限建一个role.如果有读写分离，还可以建两个读和写的role。

1 2 3 4 5 6 7 8

create role 'app_dev','app_read','app_write'; mysql8[(none)]>show grants for 'app_dev'; +-------------------------------------+ | Grants for app_dev@% | +-------------------------------------+ | GRANT USAGE ON *.* TO `app_dev`@`%` | +-------------------------------------+

创建角色时同样可以绑定Host（默认%）, 即角色名分为name + host两部分，这和账号没有什么区别。
同样创建的角色也和账号一样保存在mysql.user表中。通过查询此表可以看到角色的信息：

1 2 3 4 5 6 7 8

mysql8[(none)]>select * from mysql.user; +-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+ | Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked | Create_role_priv | Drop_role_priv | Password_reuse_history | Password_reuse_time | +-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+ | % | app_dev | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | caching_sha2_password | | Y | 2018-06-14 11:27:35 | NULL | Y | N | N | NULL | NULL | | % | app_read | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | caching_sha2_password | | Y | 2018-06-14 11:27:35 | NULL | Y | N | N | NULL | NULL | | % | app_write | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N