Mysql DBA 高级运维学习之路-创建Mysql用户及授权的多种方法实战

gznz12345

9.8创建Mysql用户及赋予用户权限

9.8.1通过help查看grant命令帮助
　　1.通过在mysql中输入“help grant”得到如下帮助信息。
　　

mysql> help grant;　　
……省略部分…….
　　
CREATE USER 'jeffrey'@'localhost'>　　
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
　　
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
　　
GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;
　　
……省略部分…….
　　

　　2.运维人员比较常用的创建用户的方法是，使用grant命令在创建用户的同时进行权限授权具体授权例子为：
　　

GRANT ALL ON db1.* TO 'jeffrey'@'localhost'>　　

　　3.上述grant命令帮助里还提供了一个先用create命令创建用户，然后再用Grant授权的方法，即创建用户和授权权限分开进行，列如：
　　

CREATE USER 'jeffrey'@'localhost'>
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
　　

　　以上两条命令相当于下面一条命令
　　

GRANT ALL ON db1.* TO 'jeffrey'@'localhost'>　　

9.8.2 通过grant命令创建用户并授权
　　1.Grant命令简单语法如下
　　

Grant all privileges on dbname.* to username@localhost>　　

　　2.列表说明

　　3.案例：创建用户zhangsan,对test库具备所有权限，允许从localhost主机登录管理数据库，密码是zhangsan123。
　　实现上述操作的具体命令为
　　

mysql> grant all privileges on test.* to 'zhangsan'@'localhost'>
Query OK, 0 rows affected (0.00 sec)　　
mysql> flush privileges;
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　检查授权用户zhangsan的具体权限
　　

mysql> show grants for 'zhangsan'@'localhost';　　Grants for zhangsan@localhost

　　
GRANT USAGE ON *.* TO 'zhangsan'@'localhost'>　　GRANT ALL PRIVILEGES ON `test`.* TO 'zhangsan'@'localhost'
　　
2 rows in set (0.00 sec)
　　

9.8.3    Create和grant配合法
　　1.首先创建用户username及密码passwd,授权主机localhost。
　　

CREATE USER 'username'@'localhost'>
mysql> create user 'lisi'@'localhost'>　　
Query OK, 0 rows affected (0.01 sec)
　　
mysql> show grants for 'lisi'@'localhost';

　　
Grants for lisi@localhost   GRANT USAGE ON *.* TO 'lisi'@'localhost'>　　
1 row in set (0.00 sec)
　　

　　默认权限是USAGE，及连接的权限，因为此时还没有权限。
　　2.然后授权localhost主机上通过用户username管理test数据库的所有权限，无需密码。
　　

mysql> grant all on test.* to 'lisi'@'localhost';　　
Query OK, 0 rows affected (0.00 sec)
　　
mysql> show grants for 'lisi'@'localhost';
　　
Grants for lisi@localhost
　　
GRANT USAGE ON *.* TO 'lisi'@'localhost'>　　
GRANT ALL PRIVILEGES ON `test`.* TO 'lisi'@'localhost'
　　

　　提示：可以看到默认权限是usage即连接的权限，后面又增加了ALL权限。

9.8.4 授权局域网内主机远程连接数据库
　　根据grant命令的语法我们知道，test@localhost位置为授权访问数据库的主机，localhost可以用域名，IP地址或者IP段来替代，因此要授权局域网内主机可以通过如下方法来实现。
　　a.百分号匹配法
　　

system@ceshi 01:5945->grant all privileges on test.* to 'zbf'@'192.168.1.%'>
Query OK, 0 rows affected (0.01 sec)
　　
system@ceshi 01:5950->show grants for 'zbf'@'192.168.1.%';
　　
Grants for zbf@192.168.1.%
　　
GRANT USAGE ON *.* TO 'zbf'@'192.168.1.%'>　　
GRANT ALL PRIVILEGES ON `test`.* TO 'zbf'@'192.168.1.%'
　　
2 rows in set (0.00 sec)
　　
system@ceshi 02:0023->flush privileges;
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　b.子网掩码配置法
　　

system@ceshi 02:3013->grant all privileges on test.* to 'wwn'@'192.168.1.0/255.255.255.0'>
Query OK, 0 rows affected (0.01 sec)
　　
system@ceshi 02:3127->flush privileges;
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　通过mysql客户端连接异地数据库服务：
　　1.本地mysql –uroot –pzbf666连接数据库相当于mysql –uroot –pzbf666 –h localhost
　　2.要远程连接192.168.1.108的数据库，命令为 mysql -uwwn -pwwn520 -h 192.168.1.108
　　3.通过php服务器连接mysql服务器的代码写法为
　　


　　

9.8.5 MySQL用户可以授权的权限有哪些？
　　通过实验获得ALL PRIVILEGES包括哪些权限
　　1.先看看有哪些用户
　　

system@ceshi 03:3751->select user,host from mysql.user;　　
+--------+---------------------------+
　　
| user   | host  |
　　
+--------+---------------------------+
　　
| zbf| 192.168.1.%   |
　　
| wwn| 192.168.1.0/255.255.255.0 |
　　
| system | localhost |
　　
+--------+---------------------------+
　　

　　2.看看授权过的wwn的权限
　　

system@ceshi 03:3920->show grants for 'wwn'@'192.168.1.0/255.255.255.0';　　
| Grants for wwn@192.168.1.0/255.255.255.0

　　GRANT USAGE ON *.* TO 'wwn'@'192.168.1.0/255.255.255.0'>　　
| GRANT ALL PRIVILEGES ON `test`.* TO 'wwn'@'192.168.1.0/255.255.255.0'
　　

　　注意这个地方的test.，我们后面取消只读权限的时候也这样写成test.
　　这时候查看还是ALL PRIVILEGES权限，没有细分。
　　3.取消wwn的只读权限（SELECT）。
　　(1) 先看一下帮助，帮助里面提供了语法，revoke在sql语言介绍那节已经提到过了，意思是取消授权。
　　

system@ceshi 03:4001->help revoke　　

　　
……省略……….
　　
The REVOKE statement enables system administrators to revoke privileges
　　
from MySQL accounts. Each account name uses the format described in
　　
http://dev.mysql.com/doc/refman/5.1/en/account-names.html.For example:
　　
REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';
　　
If you specify only the user name part of the account name, a host name
　　
part of '%' is used.
　　
……省略……
　　

　　(2) 取消授权，将ALL PRIVILEGES权限细分。
　　

system@ceshi 03:4909->REVOKE INSERT ON test.* FROM 'wwn'@'192.168.1.0/255.255.255.0';　　
Query OK, 0 rows affected (0.00 sec)
　　
system@ceshi 03:5216->flush privileges;
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　(3)再查看一下用户wwn的权限就已经被细分了。
　　

system@ceshi 03:5224->show grants for 'wwn'@'192.168.1.0/255.255.255.0';
　　
GRANT USAGE ON *.* TO 'wwn'@'192.168.1.0/255.255.255.0'>
　　
GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX,>　　

　　提示：此时wwn用户的权限，ALL PRIVILEGES权限已经被细分了。按照下面的步骤我们可以更加清楚的知道ALL PRIVILEGES的权限包括哪些内容。
　　(1) 我们用-e 不登录mysql数据库直接查看用户wwn有哪些权限
　　

[root@localhost ~]# mysql -usystem -pzbf666 -e "show grants for 'wwn'@'192.168.1.0/255.255.255.0';"|grep -i grant|tail -1
　　
GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX,>　　

　　(2) 查看用户wwn有哪些权限之后，我们要把有用的筛选出来。grep的-i参数是忽略大小写的意思。
　　

[root@localhost ~]# mysql -usystem -pzbf666 -e "show grants for 'wwn'@'192.168.1.0/255.255.255.0';"|grep -i grant|tail -1|tr ',' '\n'>all1.txt　　

　　(3) 好我们查看一下过滤的内容，下面内容就是用户wwn所具有的所有权限。
　　

[root@localhost ~]# cat all1.txt -n　　1   SELECT
　　2   UPDATE
　　3   INSERT
　　4   DELETE
　　5   CREATE
　　6   DROP
　　7   REFERENCES
　　8   INDEX

　　9  >　　
10   CREATE TEMPORARY TABLES
　　
11   LOCK TABLES
　　
12   EXECUTE
　　
13   CREATE VIEW
　　
14   SHOW VIEW
　　
15   CREATE ROUTINE
　　
16  >　　
17   EVENT
　　
18   TRIGGER ON
　　

　　注意：在授权时可以授权用户最小的满足业务的权限，而不是一味的授权“ALL PRIVILEGES”

9.8.6 企业环境授权用户权限
　　1.博客，CMS等产品的数据库授权
　　对于web连接用户授权尽量采用最小化规则，很多开源软件都是web界面安装，因此，在安装期间除了select,insert,update,delete4个权限外，还需要create,drop等比较危险的权限。
　　

system@ceshi 04:5606->grant select,insert,update,delete,create,drop on blog.* to 'blog'@'192.168.1.%'>
Query OK, 0 rows affected (0.00 sec)
　　
system@ceshi 04:5907->flush privileges;
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　常规情况下授权select,insert,update,delete4个权限即可，有的开源软件，列如discuzbbs，还需要create,drop等比较危险的权限。
　　2.生成数据库表之后，要收回create、drop授权
　　

system@ceshi 04:5925->help revoke　　
REVOKE ALL PRIVILEGES, GRANT OPTION
　　
FROM user [, user] ...
　　
REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';
　　
2   rows in set (0.01 sec)
　　
system@ceshi 05:1327->REVOKE CREATE,DROP ON blog.* FROM 'blog'@'192.168.1.%';
　　
Query OK, 0 rows affected (0.00 sec)
　　

　　
system@ceshi 05:1452->flush privileges;
　　
Query OK, 0 rows affected (0.00 sec)
　　
system@ceshi 05:1543->show grants for 'blog'@'192.168.1.%'\G;
　　
*************************** 1. row ***************************

　　
Grants for blog@192.168.1.%: GRANT USAGE ON *.* TO 'blog'@'192.168.1.%'>　　
*************************** 2. row ***************************
　　
Grants for blog@192.168.1.%: GRANT SELECT, INSERT, UPDATE, DELETE ON `blog`.* TO 'blog'@'192.168.1.%'
　　
2 rows in set (0.00 sec)