9.8 创建 Mysql 用户及赋予用户权限
9.8.1 通过 help 查看 grant 命令帮助
1. 通过在 mysql 中输入 "help grant" 得到如下帮助信息.
mysql> help grant;
...... 省略部分.......
CREATE USER 'jeffrey'@'localhost'IDENTIFIED BY 'mypass';
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' IDENTIFIED BY 'mypass';
3. 上述 grant 命令帮助里还提供了一个先用 create 命令创建用户, 然后再用 Grant 授权的方法, 即创建用户和授权权限分开进行, 列如:
CREATE USER 'jeffrey'@'localhost'IDENTIFIED BY 'mypass';
GRANT ALL ON db1. * TO 'jeffrey'@'localhost';
以上两条命令相当于下面一条命令
GRANT ALL ON db1.* TO 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
9.8.2 通过 grant 命令创建用户并授权
1.Grant 命令简单语法如下
Grant all privileges on dbname.* to username@localhost identified by 'passwd';
2. 列表说明
3. 案例: 创建用户 zhangsan, 对 test 库具备所有权限, 允许从 localhost 主机登录管理数据库, 密码是 zhangsan123.
实现上述操作的具体命令为
mysql> grant all privileges on test.* to 'zhangsan'@'localhost' identified by 'zhangsan123';
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' IDENTIFIED BY PASSWORD '*7E72D61D7B957897AA8ECED9A9397B649BE3B546' |
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' IDENTIFIED BY 'passwd';
mysql> create user 'lisi'@'localhost' identified by 'kisi123';
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for 'lisi'@'localhost';
Grants for lisi@localhost GRANT USAGE ON *.* TO 'lisi'@'localhost' IDENTIFIED BY PASSWORD '*686008E0BFD16925072B84AA099EB5BC8375C35B'
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'IDENTIFIED BY PASSWORD '*686008E0BFD16925072B84AA099EB5BC8375C35B'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.%'identified by 'zbf123';
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.%'IDENTIFIED BY PASSWORD '*E2190B1F46FD9E171DD25B61138EA7F4F4D82B8C'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' identified by 'wwn520';
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 服务器的代码写法为
<?php
//$link_id=mysql_connect('主机名','用户','密码');
$link_id=mysql_connect('192.168.1.108','wwn','wwn123');
if($link_id){
echo "mysql successful by wwn";
}else{
echo mysql_error();
}
?>
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' IDENTIFIED BY PASSWORD '*C9CE90EB588AA17159BB7C612DC7B34259AC0816' |
| 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' IDENTIFIED BY PASSWORD '*C9CE90EB588AA17159BB7C612DC7B34259AC0816'
GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test`.* TO 'wwn'@'192.168.1.0/255.255.255.0
提示: 此时 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, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test`.* TO 'wwn'@'192.168.1.0/255.255.255.0'
(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 所具有的所有权限.
ot@localhost ~]# cat all1.txt -n
SELECT
UPDATE
INSERT
DELETE
CREATE
DROP
REFERENCES
INDEX
ALTER
CREATE TEMPORARY TABLES
LOCK TABLES
EXECUTE
CREATE VIEW
SHOW VIEW
CREATE ROUTINE
ALTER ROUTINE
EVENT
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.%' identified by '1b23456';
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.%'IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' * **************************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) 2018 / 1 / 27 0 : 54 : 16
来源: http://www.bubuko.com/infodetail-2476667.html