ch02 - MySQL安全相关配置

设置MySQL管理员账号密码

在安装MySQL数据库后，MySQL管理员的账号root密码默认为空，极不安全

启动修改丢失的MySQL单实例root密码方法

停止MySQL

/etc/init.d/mysqld stop

使用 –skip-grant-tables启动mysql，忽略授权登陆验证

# 单实例
/app/mysql/bin/mysqld_safe --skip-grant-tables --user=mysql
# 多实例
/app/mysql/bin/mysqld_safe --defaults-file=/data/3306/my.cnf --user=mysql --skip-grant-tables &
# 登录时空密码

$ mysql -S /data/3306/mysql.sock
...
...
Welcome to the MySQL monitor.  Commands end with ; or \g.
# 在启动时加 --skip-grant-tables参数，表示忽略授权

修改root密码为新密码

mysql> set password=password('123');
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
mysql> update mysql.user set password=password('123') where user='root';      
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0
mysql> flush privileges;

重启服务再登陆

# 此时发现用原密码不能登陆mysql了
$ mysql -uroot -S /data/3306/mysql.sock
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

$ mysql -uroot -S /data/3306/mysql.sock -p111
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

$ mysql -uroot -S /data/3306/mysql.sock -p123
Welcome to the MySQL monitor.  Commands end with ; or \g.

提示：启动时加 --skip-grant-tables 参数启动登陆修改完密码后一定要重启再对外提供服务，skip一定要放到后面

清理无用的MySQL用户与库

清理无用的库

mysql> show databases;
+---------------------+
| Database            |
+---------------------+
| information_schema  |
| mysql               |  # 这个是mysql系统信息
| performance_schema  |
| test                |  # 这个相当于linux的/tmp，不用保留，直接删掉
+---------------------+

语法：dorp user ‘user’@’host/ip’ <= 注意引号，可以使单或双引号

mysql> DROP USER 'root'@'::1';
mysql> FLUSH PRIVILEGES;

注意：如果drop删除不了（一般为特殊字符或大写），可以用下面方式删除（以root用户，oldboy主机为例）：

DELETE FROM mysql.user WHERE user= 'root' AND host='oldboy';
flush privileges;

处理完用户必须执行 flush privileges

创建MySQL用户及赋予用户权限

通过help查看grant命令帮助

mysql> help grant
Name: 'GRANT'
Description:
Syntax:
GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user [auth_option] [, user [auth_option]] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH {GRANT OPTION | resource_option} ...]

GRANT PROXY ON user
    TO user [, user] ...
    [WITH GRANT OPTION]
....
....

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;

通过查看grant的命令帮助，可以很容易的找到创建用户并授权的例子。

比较常见的创建用户的方法是，使用grant命令在创建用户的通同时进行授权。具体例子：

GRANT ALL ON db1.* TO 'lc'@'localhost' IDENTIFIED BY '111';

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

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';	 # 创建用户

useradd Jeffrey|passwd --stdin jeffrey

GRANT ALL ON db1.* TO 'jeffrey'@'localhost'; 	# 对用户授权

通过grant命令创建用户并授权

grant all privileges on dbname.* to username@'localhost' identified by 'passwd'

列表说明如下

grant	all privileges	on dbname.*	to username@localhost	identified by ‘passwd’
授权命令	对应权限	目标：表和库	用户名和客户端主机	用户密码

说明：上述命令是授权localhost主机上通过用户username管理dbname数据库的所有权限，密码为passwd。其中username，dbname，passwd可根据业务的情况修改。

create和grant配合法

创建用户名username及密码passwd，授权主机localhost。

create user 'username'@'localhost' identified by 'passwd';

然后授权localhost主机上通过用户名username管理dbname数据库的所有权限，无需密码。

grant all on dbname.* to 'username'@'localhost';

操作示例

案例1：创建oldboy用户，对zhangsan库具备所有权限，允许localhost主机登陆管理数据库，密码是oldboy123

实现具体命令：

GRANT ALL PRIVILEGES ON oldboy.* TO zhangsan@'localhost' IDENTIFIED BY '123';

演示：

# 查看当前数据库情况，然后执行对应命令授权如下：
select user,host from mysql.user;

# 查看zhangsan具体权限
show grants for zhangsan@'localhost';

案例2：创建oldgirl用户，对test库具备所有权限，允许localhost主机登陆管理数据库的所有权限，无需密码

查看当前数据库用户情况，然后执行命令创建用户

mysql> select user,host from mysql.user;
+----------+--------------+
| user     	| host        |
+----------+--------------+
| root   	| 127.0.0.1   |
| zhangsan	| 172.168.1.% |
| root     	| localhost   |
| zhangsan	| localhost   |
+----------+--------------+

创建用户，指定密码，提示：仅仅是创建用户并未授权

create user 'oldgirl'@'localhost';
grant all on test.* to 'oldgirl'@'localhost';

查看授权后的MySQL用户列表情况

mysql> show grants for 'oldgirl'@'localhost';
+-----------------------------------------------------------+
| Grants for oldgirl@localhost                              |
+-----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'oldgirl'@'localhost'			    |
| GRANT ALL PRIVILEGES ON `test`.* TO 'oldgirl'@'localhost' |
+-----------------------------------------------------------+
# 默认权限是usage，即连接的权限，因为此时还没有权限。
# 第二个表示对test库有所有权限

授权局域网内主机远程链接数据库

根据grant命令语法，知道test@’localhsot’位置为授权方位数据库的主机，localhost可以用域名，IP或IP段来代替，因此，要授权局域网内主机可以通过如下方法实现

一条命令，“百分号” 匹配法

grant all on *.* to test@'10.0.0.%' identified by '111';

一条命令，“子网掩码” 配置法

grant all on *.* to test@'10.0.0.0/255.255.255.0' identified by '111'
# 子网掩码部分不要用24

两条命令实现

create user test@'10.0.0.%' identified by '111'
grant all on *.* to test@'10.0.0.%'

最后记得上述每条grant命令都要刷新权限

flush privileges

---

提示：如果是web链接数据库的用户，尽量不要授权all，而是select,insert,update,delete….

---

通过MySQL客户端链接异地数据库服务

本地 mysql -uroot -p111 连接相当于 mysql -uroot -p111 -hlocalhost

要远程链接10.0.0.7的数据库，命令为 mysql -utest -p111 -h10.0.0.7，如果要能链接成功，还需要在10.0.0.7 的数据库服务器上通过如下命令授权：

grant all on *.*  to test@'10.0.0.%'identified by '111'

用户可以授权的权限都有那些?

通过实验获得all privileges包括那些权限，

先看看前面授权过的oldgirl的权限

mysql> show grants for oldgirl@'localhost';
+-----------------------------------------------------------+
| Grants for oldgirl@localhost                              |
+-----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'oldgirl'@'localhost'			 	|
| GRANT ALL PRIVILEGES ON `test`.* TO 'oldgirl'@'localhost' |
+-----------------------------------------------------------+
# 此时查看，还是all privileges权限，但并未细分

取消 oldgirl 的只读权限（SELECT）看看结果

mysql> help revoke
Name: 'REVOKE'
Description:
Syntax:
REVOKE
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user [, user] ...

REVOKE ALL PRIVILEGES, GRANT OPTION
    FROM user [, user] ...

REVOKE PROXY ON user
    FROM user [, user] ...
....
....
REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';
....
....
# 通过help revoke可以看出删除一个权限的方法

使用revoke移除授权

mysql> revoke SELECT on test.* from oldgirl@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for oldgirl@'localhost';
| Grants for oldgirl@localhost                                                         ---------------| GRANT USAGE ON *.* TO 'oldgirl'@'localhost'--------------------------                         
| GRANT INSERT, 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 'oldgirl'@'localhost' |
--------------------------------2 rows in set (0.01 sec)------------------------------

此时我们再查看oldgirl用户权限，ALL PRIVILEGES权限已经被细分了，但是没有SELECT权限了。

因此我们就可以得出结论：ALL PRIVILEGES包括

 SELECT
 INSERT
 UPDATE
 DELETE
 CREATE
 DROP
 REFERENCES
 INDEX
 ALTER
 CREATE TEMPORARY TABLES
 LOCK TABLES
 EXECUTE
 CREATE VIEW
 SHOW VIEW
 CREATE ROUTINE
 ALTER ROUTINE
 EVENT
 TRIGGER

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

生产环境如何授权用户权限

博客 CMS等产品的数据库授权：

对于web链接用户授权尽量采用最小化原则，很多开源软件都是web界面安装，因此，在安装期间处理select, insert, update, delete4个权限外，还需要 create drop 等比较危险的权限。

常规情况下授权 select, insert, update, delete4个权限即可，有的开源软件，如 discuz cms还需要create drop等比较危险的权限。

生成数据库表后，要收回create、drop权限

生产环境针对主库（写为主读为辅）用户授权

普通的环境：

本机：lnmp，lamp环境数据库授权

grant all privileges on `blog`.* to blog@'localhost' identified by '111';

应用服务器和数据库服务器不在一个主机上的授权：

grant all privileges on `blog`.* to blog@'10.0.0.1' identified by '111';

严格的授权：重视安全，忽略了方便

grant SELECT,INSERT,UPDATE,DELETE privileges on `blog`.* to blog@'10.0.0.1' identified by '111';

生产环境从库（只读）用户的授权：

grant SELECT privileges on `blog`.* to blog@'10.0.0.1' identified by '111';

说明：这里表示给10.0.0.0/24的用户blog管理blog数据库的所有表（ * 表示所有表）只读权限（select），密码为111。

生产场景授权具体命令为

主库授权的命令

grant select,insert,update,delete on `blog`.* to blog@'10.0.0.%' identified by '111';

从库授权用户命令

grant SELECT on `blog`.* to blog@'localhost' identified by '111'

当然从库除了做SELECT授权外，还可以加read-only等只读参数，严格控制web用户写从库。

重要问题：就是主从库的MySQL库和表是同步的，无法针对同一个用户授权不同的权限。应为，主库授权后会自动同步到从库上，导致从库的授权只读失败。

解决方法：

1. 取消mysql库的同步。

2. 授权主库权限后，从库执行收回增删改权限。

3. 不在授权上控制增删改，而是用read-only参数，控制普通用户更新从库，注意，read-only参数对超级用户无效。

查看MySQL数据库中的用户和主机信息

查询授权用户oldboy的具体的授权权限

mysql> show grants for root@'localhost';    
+--------------------------------------------------------------------------+
| Grants for root@localhost                                                |           
+--------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' WITH GRANT OPTION 			  |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION   		   |           
+--------------------------------------------------------------------------+