给业务搭建数据库时由于采用的时分库策略，导致每个服务器上都有上百个数据库，新用户需要只对这些库有权限读写，由于服务器多，数据库多，如果采用逐个赋权限会很麻烦
在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 -pmypasswd
mysql> 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_0007
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> 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_0082
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> 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 -pmypasswd
mysql> 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和密码用的是单引号'
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
