mysql grant 通配符_mysql上利用通配符模糊匹配数据库进行grant | 学步园

给业务搭建数据库时由于采用的时分库策略，导致每个服务器上都有上百个数据库，新用户需要只对这些库有权限读写，由于服务器多，数据库多，如果采用逐个赋权限会很麻烦在mysql中，当我们想对某个用户赋予权限时，对于数据库可以利用通配符(_和%)指定一类数据库进行操作，这样就可以避免逐个操作啦。举例如下，假设我们有数据库，root@(none) 09:41:16>show databases;+--------------------+| Database           |+--------------------+| information_schema || bp_crm             || dp_0007            || dp_0019            || dp_normandie_0028  || dp_p4p_0082        || dp_p4p_0169        || home               || mysql              || test               |+--------------------+10 rows in set (0.00 sec)###大家可以看到除了系统db(mysql,information_schema,test)之外，我们有一批报表库是以“dp”开头的，如果我们想创建一个用户，只对这些db可以进行操作，那么可以利用通配符%root@(none) 09:52:13>select host,user,password from mysql.user;+------------------------+--------+-------------------------------------------+| host                   | user   | password                                  |+------------------------+--------+-------------------------------------------+| localhost              | root   |                                           || linezing128042.sqa.cm4 | root   |                                           || 127.0.0.1              | root   |                                           || localhost              |        |                                           || linezing128042.sqa.cm4 |        |                                           || %                      | lzstat | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 || %                      | admin  | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 || %                      | crm    | *46E75F13B7337A95AAEB7680B6C52280D9CDF5D2 |+------------------------+--------+-------------------------------------------+8 rows in set (0.01 sec)root@(none) 09:52:17>grant all privileges on `dp%`.* to dp_admin identified by 'mypasswd';Query OK, 0 rows affected (0.00 sec)###注意这里不是单引号'，而是反单引号`root@(none) 09:53:56>flush privileges;Query OK, 0 rows affected (0.01 sec)root@(none) 09:54:38>select host,user,password from mysql.user;+------------------------+----------+-------------------------------------------+| host                   | user     | password                                  |+------------------------+----------+-------------------------------------------+| localhost              | root     |                                           || linezing128042.sqa.cm4 | root     |                                           || 127.0.0.1              | root     |                                           || localhost              |          |                                           || linezing128042.sqa.cm4 |          |                                           || %                      | lzstat   | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 || %                      | admin    | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 || %                      | crm      | *46E75F13B7337A95AAEB7680B6C52280D9CDF5D2 || %                      | dp_admin | *85E26B8AB29FEE8453201A3511DAE24A24059109 |+------------------------+----------+-------------------------------------------+9 rows in set (0.00 sec)###我们测试一下远程登录，是否可以访问：[mysql@testdb2 ~]$ mysql -udp_admin -h10.232.128.42 -pmypasswdmysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || dp_0007            || dp_0019            || dp_normandie_0028  || dp_p4p_0082        || dp_p4p_0169        || test               |+--------------------+7 rows in set (0.00 sec)###可以看到mysql db是无法看到的，这正符合我们的初衷。mysql> use dp_0007Reading 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_dp_0007                    |+--------------------------------------+| dpunit_p4p_campaign_d__201006        || dpunit_p4p_effect_contrast_d__201006 || dpunit_p4p_effect_contrast_d__201007 || mytest2                              || mytesttab                            |+--------------------------------------+5 rows in set (0.00 sec)mysql> select count(*) from dpunit_p4p_campaign_d__201006;+----------+| count(*) |+----------+|    16622 |+----------+1 row in set (0.00 sec)mysql> use dp_p4p_0082Reading 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_dp_p4p_0082                       |+---------------------------------------------+| dim_m_star                                  |。。。。。| dpunit_p4p_platform_d__201006               || dpunit_p4p_platform_d__201007               || dpunit_p4p_platform_d__201008               || lz_dim_category_level1                      || mytest2                                     || mytesttab                                   |+---------------------------------------------+74 rows in set (0.00 sec)mysql> select count(*) from dpunit_p4p_platform_d__201008;+----------+| count(*) |+----------+|    38640 |+----------+1 row in set (0.00 sec)###看到可以访问操作“dp”开头的数据库。###注意：_也是通配符，在你grant "dp_p4p"开头的数据库权限时需要用"\"做一下转义。root@(none) 10:18:15>grant all privileges on `dp\_p4p%`.* to dp_admin2 identified by 'mypasswd';Query OK, 0 rows affected (0.00 sec)root@(none) 10:22:41>flush privileges;Query OK, 0 rows affected (0.01 sec)root@(none) 10:22:46>select host,user,password from mysql.user;+------------------------+-----------+-------------------------------------------+| host                   | user      | password                                  |+------------------------+-----------+-------------------------------------------+| localhost              | root      |                                           || linezing128042.sqa.cm4 | root      |                                           || 127.0.0.1              | root      |                                           || localhost              |           |                                           || linezing128042.sqa.cm4 |           |                                           || %                      | lzstat    | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 || %                      | admin     | *4ACFE3202A5FF5CF467898FC58AAB1D615029441 || %                      | crm       | *46E75F13B7337A95AAEB7680B6C52280D9CDF5D2 || %                      | dp_admin  | *85E26B8AB29FEE8453201A3511DAE24A24059109 || %                      | dp_admin2 | *85E26B8AB29FEE8453201A3511DAE24A24059109 |+------------------------+-----------+-------------------------------------------+10 rows in set (0.00 sec)###我们测试一下远程登录，是否可以访问：[mysql@testdb2 ~]$ mysql -udp_admin2 -h10.232.128.42 -pmypasswdmysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || dp_p4p_0082        || dp_p4p_0169        || test               |+--------------------+4 rows in set (0.00 sec)###注意，dp_admin2只有权限看到"dp_p4p"开头的数据库，dp_p4p_0082和dp_p4p_0169###同样你也可以在hostname中指定通配符，但不可以在user中指定：root@(none) 10:36:53>grant all privileges on `dp\_p4p%`.* to dp_admin3@'10.254.3.%' identified by 'mypasswd';Query OK, 0 rows affected (0.00 sec)root@(none) 10:37:25>flush privileges;###表示10.254.3子网段的服务器都可以访问"dp_p4p"这类数据库，注意这里是单引号###另外，使用反勾号(`)为数据库、表、列和子程序名称加引号。使用单引号(')为hostnames、usernames和password加引号。root@(none) 10:58:26>grant select on dp_p4p_0082.`dpunit_p4p_effect_adgroup_bidword_d__201006` to dp_admin4@'10.254.3.%'

identified by 'mypasswd';Query OK, 0 rows affected (0.00 sec)root@(none) 10:58:22>flush privileges;Query OK, 0 rows affected (0.00 sec)###例子中，表用的是反引号`，而给hostname和密码用的是单引号'