MySQL权限管理和安全机制
安全保护策略概述
要确保MySQL的安全，可以做如下工作。
- 为操作系统和所安装的软件打安全补丁
如今打开计算机时。都会弹出软件的安全警告。虽然这些警告有时会给我们带来困扰，但是打上安全补丁还是非常有必要的。即使某些恶意用户在攻击方面没有太多经验，他们也能毫无阻碍地攻击未打补丁的服务器。即使用户在使用托管服务器，也不要过分依赖服务提供商来完成必要的升级；相反，要坚持间隔手动式更新，以确保和补丁相关的事情都安排妥当。
- 禁用所有不使用的系统服务
始终要注意再将服务器放在网络之前，已经消除所有不必要的潜在服务器攻击途径。这些攻击往往是由不安全的系统服务带来的，通常运行在不为系统管理员所知的系统中，简言之，如果不打算使用一个服务，就禁用该服务。
- 关闭端口
虽然系统不使用的系统服务是减少攻击可能性的好方法，不过还可以通过关闭未使用的端口来添加第二层防护。对于专用的数据库服务器，可以考虑关闭除22（SSH协议专用）、3306（MySQL数据库使用）和一些“工具”专用的端口号在1024以下的端口。简言之，若不希望在指定端口有数据通信，就关闭这个端口。除了在专用防火墙或路由器上做这些调整，还可以使用操作系统的防火墙。
- 审计服务器的用户账户
当已有的服务器再作为公司的数据库主机时，要确保禁用所有非特权用户，或者最好是全部删除。虽然MySQL用户和操作系统完全无关，但他们都需要访问服务器环境，就有可能有意地破坏数据库服务器及其内容。为确保在审计中不会有遗漏，可以考虑重新格式化所有相关的驱动器，并重新安装操作系统。
- 设置MySQL的用户密码
对所有MySQL用户设置密码，客户端程序不需要知道运行它的人员的身份。对于服务器应用程序，用户可以指定客户端程序的用户名。这样可以避免其他用户冒充他人进行数据库连接。
用户权限管理
MySQL中的表和其他任何关系表没有区别，都可以通过典型的SQL语句修改其结构和数据。使用GRANT和REVOKE命令可以创建和删除用户，也可以在线授予和撤回用户访问权限。由于语法严谨，消除了由不好的SQL语句（如忘记在UPDATE语句中添加WHERE子句）带来潜在的错误。
## 使用CREATE USER命令添加用户
CREATE USER用于创建新用户。要使用CREATE USER语句必须具有MySQL数据库全局的CREATE USER权限，或拥有INSERT权限。对于每个账户，CREATE USER会在没有权限的mysql.user表中创建一个新纪录。若账户已经存在则会发生错误。使用自选的IDENTIFIED BY子句，可以为账户设置一个密码。user值的密码和GRANT语句一样，其命令的语法如下。
CREATE USER user_name [IDENTIFIED BY [[PASSWORD] 'password']]
[,user_name [IDENTIFIED BY [[PASSWORD] 'password']]] ...
如我们创建一个新用户，用户名叫user1，密码为123456。
create user user1 identified by '123456';
常用的几种创建特定要求的用户的语句：
- 若要创建一个新用户admin，只允许该用户从localhost主机并使用密码123456连接到MySQL数据库，则CREATE USER语句如下。
create user admin@localhost identified by '123456';
- 若用户admin还可以从IP为192.168.1.100的主机连接到MySQL服务器，则CREATE USER语句如下。
create user admin@192.168.1.100 identified by '123456';
- 若允许用户从任何主机连接，则使用百分号通配符。
create user admin@'%' identified by '123456';
使用DROP USER命令删除用户
若存在一个或多个闲置用户，应当考虑将其删除，确保其不会被用于可能的违法活动。使用DROP USER就可以删除用户。该命令会从权限表中删除用户的所有信息。DROP USER命令语法如下。
DROP USER user1[,user2];
DROP USER不能自动关闭任何已经打开的用户对话框。而且若用户有被打开的对话，此时删除用户，则命令不会立即生效，直至用户对话被关闭后删除用户才生效。一旦用户对话被关闭，用户被删除，此用户再次登录时就会失败，因为此用户已经不存在了。
如我们使用DROP USER命令删除上面创建的user1用户，语法如下。
drop user user1;
使用RENAME USER命令重命名用户
语法如下。
RENAME USER old_user TO new_user [,old_user TO new_user]...;
使用GRANT和REVOKE命令管理访问权限
GEANT和REVOKE命令用来管理权限，也可以用来创建和删除用户。但是使用CREATE USER和DROP USER更容易实现。
查看用户权限
使用SHOW GRANTS语句可以查看用户的权限，语法如下。
SHOW GRANTS FOR 用户名@主机名;
如我们创建一个新用户叫demo，然后查看此用户的权限。
show grants for demo@localhost;
上面结果中的*\*表示用户只能登录到数据库服务器，没有其他权限。
设置用户权限
只有拥有GRANT权限的用户才可以执行GRANT语句，其语法如下。
GRANT priv_type [(column_list)] ON database.table TO user [IDENTIFIED BY [PASSWORD] 'password'] [,user [IDENTIFIED BY [PASSWORD] 'password']] ... [WITH with_option [with_option]...];
参数说明如下表。
| 参数 | 含义 |
|---|---|
priv_type | 权限类型 |
column_list | 表示权限作用于哪些列上，省略该参数时，表示作用于整个表 |
database.table | 用于指定权限的级别 |
user | 代表用户账户，由用户名和主机名构成，如user@localhost |
IDENTIFIED BY | 为用户设置密码 |
password | 是用户的新密码 |
WITH | 该参数后面跟着一个或多个with_option参数，该参数有五个选项，详细介绍如下表 |
| 可选项 | 含义 |
|---|---|
GRANT_OPTION | 被授权的用户可以将这些权限授予其他用户 |
MAX_QUERIES_PER_HOUR count | 设置每小时可以执行count次查询 |
MAX_UPDATES_PER_HOUR count | 设置每小时可以执行count次更新 |
MAX_CONNECTIONS_PER_HOUR count | 设置每小时可以建立count个连接 |
MAX_USER_CONNECTIONS count | 设置单个用户可以同时具有count个连接 |
MySQL中可以授予的权限有以下几组。
- 列权限：和表的一个具体列相关。如可以使用UPDATE语句更新students表的name字段的值的权限。
- 表权限：和一个具体表的所有字段数据相关。如可以使用SELECT语句查询students表的所有数据的权限。
- 数据库权限：和一个具体的数据库的所有数据表相关。如可以在已有的数据库mytest中创建新表的权限。
- 用户权限：和MySQL中所有数据相关。如可以删除一个数据库的权限。
对应地，在GRANT语句中可用于指定权限级别的值有以下几类格式。
*：表示当前数据库的所有表。*.*：表示所有数据库的所有表。db.*：表示db数据库中的所有表。db.tb表示db数据库中的tb表。db.routine：表示db数据库中的某个存储过程或存储函数。
TO子句表示将权限授予一个用户，若此用户不存在则会创建此用户，但是必须为此用户设置密码。
权限列表如下。
| 权限 | 意义 |
|---|---|
ALL [PRIVILEGES] | 设置除GRANT OPTION外的所有简单权限 |
ALTER | 允许使用ALTER TABLE |
ALTER ROUTINE | 更改或取消已有的子程序 |
CREATE | 允许使用CREATE TABLE |
CREATE ROUTINE | 创建已存储的子程序 |
CREATE TEMPORARY TABLES | 允许使用CREATE TEMPORARY TABLE |
CREATE USER | 允许使用CREATE USER、DROP USER、RENAME USER 和REVOKE ALL PRIVILEGES |
CREATE VIEW | 允许使用CREATE VIEW |
DELETE | 允许使用DELETE |
DROP | 允许使用DROP TABLE |
EXECUTE | 允许用户运行已存储的子程序 |
FILE | 允许使用LOAD DATA INFILE和SELECT INTO OUTFILE |
INDEX | 允许使用CREATE INDEX和DROP INDEX |
INSERT | 允许使用INSERT |
LOCK TABLES | 允许对拥有SELECT权限的表使用LOCK TABLES |
PROCESS | 允许使用SHOW FULL PROCESSLIST |
REFERENCES | 未被实施 |
RELOAD | 允许使用FLUSH |
REPLICATION CLIENT | 允许用户询问从属服务器或主服务器的地址 |
REPLICATION SLAVE | 用于复制型从属服务器（从主服务器中读取二进制日志事件） |
SELECT | 允许使用SELECT |
SHOW DATABASES | 显示所有数据库 |
SHOW VIEWS | 允许使用SHOW CREATE VIEW |
SHUTDOWN | 允许使用mysqladmin shutdouwn |
SUPER | 允许使用CHANGE MASTER、KILL、PURGE MASTER LOGS和SET GLOBAL语句，myadmin debug命令；允许连接（一次），即使已达到max_connections |
UPDATE | 允许使用UPDATE |
USAGE | “无权限”的同义词 |
GRANT OPTION | 允许授予权限 |
下面会创建一个管理员，来演示如何使用GRANT命令。
- 以root身份登录，使用CREATE USER命令创建一个管理员user_demo，设置密码为1234，语法如下。
create user demo_user identified by '1234';
使用root用户为用户demo赋予在db数据库下执行INSERT、SELECT、UPDATE和DELETE权限，然后查看权限是否赋予成功，代码如下。
grant insert,select,update,delete on db.* to demo_user;
show grants for demo_user;
新建一个cmd窗口，使用demo用户登录MySQL，执行CREATE TABLE命令创建user数据表，代码如下。
create table user(
id int primary key auto_increment,
name varchar(50)
);
在上图中，数据库提示“CREATE command denied to user 'demo_user'@'locallhost' for table 'user'”，这是因为demo_user不具备创建数据表的权限。
- 使用root用户赋予demo_user用户在db数据库下执行CREATE的权限，并查看demo_user的权限，代码如下。
grant create on db.* to demo_user;
show grants for demo_user;
- 新建一个cmd窗口，用demo_user登录，并再次尝试创建表user。
create table user(
id int primary key auto_increment,
name varchar(50)
);
可以看到创建表成功了，因为root管理员已授予demo_user创建表的权利。
MySQL数据库安全常见问题
权限更改何时生效
MySQL服务器启动及使用GRANT和REVOKE语句时，服务器会自动读取GRANT表。但是，既然知道这些权限保存的位置以及它们是如何保存的，就可以手动修改它们。当手动更新权限时，MySQL服务器不会注意到权限已修改，必须向服务器指出已经对权限进行了修改，有3种方法可以实现这个任务。
可以在MySQL命令提示符下（必须已管理员身份运行）输入以下命令。
flush privileges;
这是更新权限最常用的方法。或者也可以在操作系统中运行。
mysqladmin flush-privileges
或者是
mysqladmin reload
设置账户密码
- 可以用mysqladmin命令在DOS命令窗口中指定密码。
mysqladmin -u user_name -p "oldpwd" -h host_name password "newpwd"
mysqladmin命令重设服务器为host_name、用户名为user_name的用户的密码、oldpwd为旧密码，newpwd为新密码。
- 通过SET PASSWORD命令设置用户的密码。
SET PASSWORD FOR 'user_name'@'host_name' = 'newpwd';
只有以root用户（可以更新MySQL数据库的用户）的身份登录，才可以修改其他用户的密码。若没有以匿名用户连接，省略for子句可以修改自己的密码。
SET PASSWORD = '123456';
- 在全局级别下使用1GRANT USAGE语句（在*.*）指定某个账户的密码，而不影响账户当前的权限。
GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY 'newpwd;'
使密码更安全
- 在管理级别，切记不能将mysql.user表的访问权限授予任何非管理账户。
- 采用下面的命令登录MySQL，以隐藏密码。
mysql -u user -p
Enter password:******
- 若要在非交互方式下运行一个管理脚本调用一个客户端，就没有从终端输入密码的机会。其最安全的方法是让客户端程序提示输入密码或在适当的选项文件中指定密码。
日志文件
MySQL提供了日志文件来帮助开发人员更好地定位MySQL的错误信息和检查运行性能。
错误日志
错误日志对MySQL的启动、运行、关闭过程都有记录。开发人员在遇到问题时应该首先查看该文件以便定位问题。该文件不仅记录了所有的错误信息，也记录了一些警告信息或正确的信息。可以通过以下的命令查看错误日志路径。
SHOW VARIABLES LIKE '%log_error%';
重启MySQL，就会在错误日志中找到MySQL重启的信息。
慢查询日志
MySQL提供了记录慢查询的文件，该文件会记录查询时长超过long_query_time的SQL语句，默认是关闭的。若SQL查询的时间操作了设置的阈值，就会被记录到慢查询日志中。
可以通过以下语句查看慢查询日志是否开启，以及日志文件位置。
SHOW VARIABLES LIKE '%slow_query_log%';
慢查询日志默认是关闭的，可通过下面的语句开启。
SET GLOBAL slow_query_log = ON;
若在设置时提示ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation，则需要以管理员身份运行cmd，然后在此cmd中登录MySQL，再尝试设置即可。
MySQL中慢查询的时长阈值是靠变量long_query_time控制的，单位是秒，默认是10，可以通过下面的语句查看。
SHOW VARIABLES LIKE '%long_query_time%';
可通过下面的语句修改为1秒。
SET GLOBAL long_query_time = 1;
查询日志
查询日志会记录发送给MySQL服务器的所有SQL，因为SQL量大，默认是不开启的。若一个问题反复出现（如事务不结束），就需要把查询日志打开，即使事务没有提交，一样会写到查询日志，这样就可以定位出现问题的SQL语句。
MySQL有三个参数可以设置general log。
- general_log：用于开启general log。ON表示开启，OFF表示关闭。
- log_output：日志输出的模式。FILE表示输出的文件，TABLE表示输出到MySQL库的general_log表，NONE表示不记录。
- general_log_file：日记输出文件的路径，设置log_output为FILE时才会输出到此文件。
可以使用如下命令查看general_log是否开启。
show variables like '%general%';
若general_log是关闭的，可以通过以下的命令开启。
set global general_log = ON;
大多数情况都是临时开启general log，需要记得关闭，并把日志文件输出模式恢复为FILE，命令如下。
set global general_log = OFF;
set global log_output = 'FILE';
二进制日志
二进制日志包含描述数据库更改的事件，如表创建操作或表数据更改。除非使用基于行的日志记录，否则它还可能包含已进行更改的语句的事件（如，不匹配任何行的DELETE）。另外，二进制日志还包含关于每个语句花费多长时间更新数据的信息。二进制日志有以下几个用途。
- 恢复：某些数据的恢复需要二进制日志。例如，在一个数据库全备文件恢复后，用户可以通过二进制日志进行point-in-time的恢复。
- 复制：其原理和恢复类似，通过复制和执行二进制日志，使一台远程的MySQL数据库（一般称为slave或standby）与一台数据库（一般称为master或primary）进行实时同步。
- 审计：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入的攻击。
使用以下命令查看二进制日志是否开启。
show variables like 'log_bin';
默认情况下二进制日志是开启的。
使用以下命令查看二进制日志文件名。
show binary logs;
运行结果如上图。以后每次操作相关的表时，File_size都会增大。
此外，还可以使用如下命令对日志进行回放。
SHOW BINLOG EVENTS [IN 'log_name'];