MySQL详解（二）：权限与安全

MySQL权限与安全

MySQL的权限系统主要用来监测数据库用户是否属于合法的用户，如果是合法的用户，则赋予相应的数据库使用权限。

数据库的安全性在很大的程度上跟数据库的权限系统有很大的关系，不当的权限设置可能会导致各种各样的安全隐患。

1.权限表

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库中，由mysql_install_db脚本初始化。存储账户权限信息表主要有：user、db、host、tables_priv、columns_priv和procs_priv。

1.user表

user表示MySQL中最重要的一个权限表，记录允许连接到服务器的账号信息，里面的权限是全局级的。

user表中的字段可以分为4类，用户列、权限列、安全列、资源控制列

【用户列】

Host：主机名

User：用户名

Password：密码

其中User和Host为User表的联合主键。当用户与服务器之间建立连接的时候，输入的账户信息中的用户名称、主机名和密码必须匹配User表中对应的字段，只有3个值都匹配的时候，才允许连接的建立。

【权限列】

权限列的字段决定了用户的权限，描述了在全局范围内允许对数据和数据库进行的操作。包括查询权限、修改权限等普通权限，还包括了关闭服务器、超级权限和加载用户等高级权限。

普通权限用户操作数据库；

高级权限用户数据库的管理；

如果需要修改权限可以使用GRANT语句或者UPDATE语句更改user表的这些字段来修改用户对应的权限；

【安全列】

安全列只有6个字段，其中两个是ssl相关的，2个是x509相关的，另外2个是授权插件相关的。ssl用于加密；X509标准可用于标识用户；Plugin字段标识可以用于验证用户身份的插件，如果该字段为空，服务器使用内建授权验证机制验证用户身份。读者可以通过SHOW VARIABLES LIKE ‘have_openssl’语句来查询服务器是否支持ssl功能；

【资源控制列】

资源控制列的字段用来限制用户使用的资源，包含4个字段，分别为：

max_questions：用户每小时允许执行的查询操作次数

max_updates：用户每小时允许执行的更新操作次数

max_connections：用户每小时允许执行的连接操作次数

max_user_connections：用户允许同时建立的连接次数

一个小时内用户查询或者连接数量超过资源控制限制，用户将被锁定，直到下一个小时，才可以在此执行对应的操作。可以使用GRANT语句更新这些字段的值；

2.db表和host表

db表存储了用户对某个数据库的操作权限，决定用户能从哪个主机存取哪个数据库。host表中存储了某个主机对数据库的操作权限，配合db权限表对给定主机上数据库级操作做更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。db表比较常用，host表一般很少使用。db表和host表结构相似，字段大致可以分为两类：用户列和权限列。

【用户列】

db表标识从某个主机连接某个用户对某个数据库的操作权限

host表不存储用户名称，用户列只有2个字段，标识从某个主机连接的用户第某个数据库的操作权限，其主键包括Host和Db两个字段。host很少用到，一般情况下db表就可以满足权限控制的需求了；

【权限列】

db表和host表的权限列大致相同，表中create_routine_priv和alter_routine_priv这两个字段表名用户是否有创建和修改存储过程的权限；

user表中的权限是针对所有数据库的，如果希望用户只针对某个数据库有操作权限，那么需要将user表中对应的权限设置为N，然后在db表中设置对应数据库的操作权限。

3.tables_priv表和columns_priv表

tables_priv表用来对表设置操作权限，columns_priv表用来对表的某一列设置权限。

4.procs_priv表

procs_priv表可以对存储过程和存储函数设置操作权限。

提示：由于权限信息数据量比较小，访问比较频繁，MySQL在启动之后会将权限表的信息缓存起来，所以手工修改权限信息之后，都需要执行“FLASH  PRIVILEGES”指令来重新刷新缓存中存储的权限信息。值的注意的是：用户使用grant/revoke/drop user/create user命令修改用户权限的时候，也会更新缓存中的权限信息；

2.账户管理

MySQL提供许多的语句用来管理用户账户，这些语句可以用来管理包括登陆和退出MySQL服务器、创建用户、删除用户、密码管理和权限管理等内容。MySQL数据库的安全性，需要通过账户管理来保证。

1.登陆和退出MySQL服务器

通过mysql-help命令可以查看mysql命令帮助信息。MySQL命令的常用参数如下：

-h：主机名；

-u：用户名

-p：密码（注意：该参数后面的字符串和-p之前不能有空格）

数据库名：可以在命令的最后指定数据库名

-e：执行SQL语句，如果指定了该参数，将在登陆之后执行-e后面的命令或SQL语句并退出；

2.新建普通用户

创建新用户，必须有相应的权限来执行创建操作。在MySQL数据库中，有两种方式创建新用户：一种是使用CREATE USER或者GRANT语句；

另一种是直接操作MySQL授权表。

最好的方式是使用GRANT语句，因为这样更加的精确，错误少。

【使用CREATE USER语句】

执行CREATE USER或者GRANT语句的时候，服务器会修改相应的用户授权表，添加或者修改用户及其权限。CREATE USER语句的基本语法格式如下：

脚本：CREATE USER 'username'@'host' [IDENTIFIED BY 'PASSWORD'] 其中密码是可选项；

例子：

CREATE USER 'john'@'192.168.189.71' IDENTIFIED BY "123";

CREATE USER 'john'@'192.168.189.%' IDENTIFIED BY "123";

CREATE USER 'john'@' %' ;   表示对所有的主机开放权限，并且登录不需要密码

CREATE USER 'john'@' localhost' IDENTIFIED BY “123” ;

说明：该方法创建出来的用户只有连接数据库的权限，需要后续继续授权；

CREATE USER语句会添加一个新的MySQL账户。使用CREATE USER语句的用户，必须有全局的CREATE USER权限或者MySQL数据库的INSERT权限。

CREATE USER语句会在mysql.user表中添加一条新的记录，但是新创建的账户没有任何的权限。如果添加的账户已经存在，CREATE USER语句会返回一个错误；

或者可以使用插件的方式验证用户登录；

IDENTIFIED WITH auth_plugin [AS ‘auth_string’]

IDENTIFIED WITH语句为用户指定一个身份验证的插件；auth_plugin是插件的名称，插件的名称可以是一个带单引号的字符串，或者带引号的字符串；

auth_stirng是一个可选的字符串参数，该参数将传递给身份验证插件，有该插件解释该参数的意义；

CREATE USER 'john'@' localhost' IDENTIFIED WITH  my_auth_plugin;

注意：IDENTIFIED BY 和IDENTIFIED WITH是互斥的，所以对于一个账户来讲，只能使用一个验证方法。

CREATE USER语句的操作会被记录到服务器日志文件或者操作历史文件中，如~/.MySQL_history.

这意味着对这些文件有读取权限的人，都可以读取新添加用户的明文密码；

【使用GRANT 语句创建新用户】

使用GRANT语句不仅可以创建新用户，还可以在创建的同时对用户授权。GRANT还可以指定账户的其他特点，如果使用安全连接、限制使用服务器资源等。使用GRANT语句创建新用户的时候必须有GRANT权限。

语法格式：

 GRANT privileges ON db.table TO user@host [IDENTIFIED BY ‘password’] [,user IDENTIFIED BY ‘password’] [WITH GRANT OPTION];

参数说明：

privileges表示赋予用户的权限类型；

db.table：表示用户的权限操作所作用的数据库中的表；

IDENTIFIED BY关键字用来设置密码，‘password’表示用户密码；

WITH GRANT OPTION为可选参数，表示对新建立的用户赋予GRANT权限，即该用户可以对其他的用户赋予权限；

举例：

使用GRANT语句创建一个新的用户testUser，密码为testpwd，并授予用户对所有数据表的SELECT 和UPDATE权限。GRANT语句及其执行结果如下；

MySQL>GRANT SELECT ,UPDATE ON *.* TO ‘testUser’ @ ‘localhost’ IDENTIFIED BY ‘testpwd’;

【直接操作MySQL用户表】

可以使用INSERT 语句向user表中直接插入一条记录来创建一个新的用户。使用INSERT 语句，必须拥有对mysql.user表的INSERT 权限。使用INSERT 语句，必须拥有对mysql.user表的INSERT权限。使用INSERT语句创建新用户的语法：

INSERT INTO mysql.user(Host,User,Password,[privilegelist]) VALUES(‘host’,’username’,PASSWORD(‘password’),privilegevaluelist);

Host,User,Password分别为user表中的主机，用户名，密码

privilegeslist表示用户的权限，可以有多个权限；

PASSWORD()函数为密码加密函数；

privilegevaluelist为对应的权限的值，只能取‘Y’或者“N”

3.删除普通用户

【使用drop user语句删除用户】

DROP USER user[,user]

DROP USER 语句用于删除一个或者多个MySQL账户。要使用DROP USER ，必须拥有MySQL数据库的全局CREATE USER权限或者DELETE权限。

使用与GRANT 或者REVOKE相同的格式为每个账户命名；

DROP USER不能自动关闭任何打开的用户对话。而且，如果用户有打开的对话，此时取消用户，命令则不会生效，直到用户对话被关闭才能生效。一旦对话被关闭，用户也被取消，此用户再次试图登录的时候将会失败；

【使用DELETE语句删除用户】

DELETE FROM MySQL.user WHERE host=’hostname’ and user=’username’

4.root用户修改自己的密码

【使用mysqladmin命令在命令行指定新密码】

mysqladmin -u username -h hostname -p password ‘newpwd’

【修改mysql数据库的user表】

UPDATE mysql.user set Password=PASSWORD(‘rootpwd’) WHERE User=’root’ and Host=’localhost’;

执行完UPDATE语句之后，需要使用FLUSH PRIVILEGES语句重新加载用户权限；

【使用set语句修改root用户的密码】

SET PASSWORD 语句可以用来重新设置其他用户的登录密码或者自己使用的账户的密码。使用SET语句修改自身密码的语法结构如下：

SET PASSWORD=PASSWORDA(‘rootpwd’);

为了使更改生效，需要重新启动MySQL或者使用FLUSH PRIVILEGES语句刷新权限，重新加载权限表；

5.root用户修改普通用户密码

【使用SET语句修改】

SET PASSWORD FOR  ‘user’@’host’=PASSWORD(‘somepassword’)

【使用UPDATE语句修改普通用户的密码】

UPDATE MySQL.user SET Password=PASSWORD(“pwd”)

WHERE User =”username” AND Host=”hostname”;

【使用UPDATE语句修改普通用户的密码】

UPDATE mysql.user set Password=PASSWORD(‘rootpwd’) WHERE User=’username’ and Host=’hostname’;

【使用GRANT语句修改普通用户密码】

MySQL>GRANT USAGE ON *。* TO ‘someuser’@‘%’ INDENTIFIED BY ‘somepassword’

6.普通用户修改密码

普通用户登录MySQL服务器之后，通过SET语句设置自己的密码；

SET PASSWORD = PASSWORD(‘newpassword’);

7.root用户密码丢失的解决办法

【1.使用--skip-grant-tables选项启动MySQL服务】

以skip-grant-tables选项启动的时候，MySQL服务器将不加载权限判断，任何用户都可以访问数据库。

在windows中可以使用mysqld或者mysqld-nt来启动MySQL服务进程。

mysqld命令如下：

mysqld --skip-grant-tables

mysqld-nt --skip-grant-tables

在Linux系统下，使用mysqld_safe来启动MySQL服务。也可以使用/etc/init.d/mysql命令来启动MySQL服务；

mysqld-safe命令如下：

mysqld_safe --skip-grant-tables user=mysql

/etc/init.d/mysql start-mysqld --skip-grant-tables

启动MySQL服务之后，就可以使用root用户登录了；

【2.使用root用户登录，重新设置密码】

例如XP系统：

1、使用net stop mysql命令停止MySQL服务进程

2、在命令行输入mysqld--skip-grant-tables选项启动MySQL服务

3、打开另一个命令行窗口，输入不加密码的登录命令。

mysql -u root

4、使用UPDATE 或者mysqladmin命令重新设置root密码，设置密码的语句：

mysql>UPDATE mysql.user SET Password=PASSWORD(‘newpwd’) WHERE User =’root’ and Host=’localhost’;

【3.加载权限表】

修改密码完成之后，必须使用FLUSH PRIVILEGES语句加载权限表。加载权限表之后，新的密码才会生效，同时MySQL服务器开始权限验证。

修改完密码完成之后，将输入mysqld --skip-grant-tables命令的命令行窗口关闭，接下来就可以使用新设置的密码登录MySQL了；

3.权限管理

权限管理主要是对登录到MySQL的用户进行权限验证。所有的用户的权限都存储在MySQL的权限表中。

MySQL权限系统的主要功能是证实连接到一台给定主机的用户，并且赋予该用户在数据库上的SELECT、INSERT、UPDATE和DELETE权限。

1.MySQL的各种权限

账户权限信息被存储在MySQL数据库的user、db、host、tables_priv、columns_priv和procs_priv表中。在MySQL启动的时候，服务器将这些数据库中权限信息的内容读入内存；

（1）CREATE 和DROP权限：

（2）SELECT 、INSERT、UPDATE和DELETE权限允许在一个数据库现有的表上实施操作；

（3）SELECT权限只有在它们真正的从一个表中检索的时候才被用到；

（4）INDEX权限允许创建或者删除索引，INDEX适用于已有的表。如果具有某个表的CREATE权限，可以CREATE TABLE语句中包括索引定义；

（5）ALTER权限，可以使用ALTER TABLE来更改表的结构和重新命名表；

（6）CREATE ROUTINE权限：创建保存的程序（函数和程序），ALTER ROUTINE权限用来更改和删除保存的程序，EXECUTE权限用来执行保存的程序；

（7）GRANT权限允许授权给其他的用户。可用于数据库、表和保存的程序；

（8）FILE权限给予用户使用LOAD DATA INFILE和SELECT…INTO OUTFILE语句读或者写服务器上的文件。

其余的权限用于管理性的操作，它使用mysqladmin程序或者SQL语句实施。

权限	权限拥有者允许执行命令
RELOAD	flush-host，flush-logs，flush-privileges，flush-status，flush-tables，flush-threads，reload
SHUTDOWN	shutdown
PROCESS	processlist
SUPER	kill

（1）reload

告诉服务器将授权表重新读入内存；

（2）shutdown

关掉服务器，只能从mysqladmin发出命令；

（3）processlist

命令显示在服务器内执行的线程的信息（即其他的郑虎相关的客户端执行的语句）。kill命令杀死服务器线程。用户总是可以显示或者杀死自己的线程，但是需要PROCESS权限来显示或者杀死其他用户和SUPER权限启动的线程；

（4）kill

kill命令能用来终止其他用户或者更改服务器的操作方式；

2.授权

为某个用户授予权限。

授予权限分为多个层级：

【1全局层级】

全局层级适用于一个给定服务器中的所有数据库。这些权限存储在mysql.user表中；

GRANT ALL ON *.*和REVOKE ALL ON *.*只授予和撤销全局权限；

【2.数据库层级】

该权限存储在mysql.db和mysql.host表中

GRANT ALL ON db_name和REVOKE ALL ON db_name.*

【3.表层级】

这些权限存储于mysql.tables_priv表中；

GRANT ALL ON tbl_name和REVOKE ALL ON db_name.tbl_name

【4.列层级】

列权限适用于一个给定表中的单一列。这些权限存储在mysql.columns_priv表中。当使用REVOLE的时候，必须指定与被授权相同的列；

【5.子程序层级】

CREATE ROUTINE、ALTER ROUTINE、EXECUTE和GRANT权限适用于已存储的子程序。

这些权限可以被授予全局层级和数据库层级。而且，处理CREATE ROUTINE外，这些权限可以被授予为全局层级和数据库层级，并存储在mysql.procs_priv表中；

3.收回权限

MySQL使用REVOKE语句取消用户的某些权限。使用REVOKE收回权限之后，用户账户的记录将从db、host、tables_priv和columns_priv表中删除，但是用户账号记录仍然在user表中保存；

REVOKE ALL PRIVILEGES,GRANT OPTION FROM ‘user’@‘host’

REVOKE priv_type [(columns)] [,priv_type[(columns)]]… ON table1,table2,…,tablen FROM ‘user’@’host’ [,’user’@’host’…]

4.查看权限

SHOW GRANTS 语句可以显示指定用户的权限信息，使用SHOW GRANTS查看账户信息语法：

SHOW GRANTS FOR ‘user’@’host’;

 

4.访问控制

MySQL访问控制分为两个阶段：连接核实阶段和请求核实阶段

1.连接核实阶段

当连接MySQL服务器的时候，服务器基于用户的身份以及用户是否能通过正确的密码身份验证来接受或者拒绝连接；（通过主机、用户名、密码进行验证）

2.请求核实阶段

建立了连接之后，需要对用于的相关权限进行检验；

MySQL通过向下层级的顺序检查权限表（从user表到columns_priv表），但是并不是所有的权限都需要执行该过程，例如一个用户登录到MySQL服务期之后只执行对MySQL的管理操作，此时，只是涉及管理权限，因此MySQL只检查user表。另外，如果请求的权限操作不被允许，MySQL也不会继续检查下一层级的表；

注意：MySQL的身份验证是通过身份和IP地址联合进行认证的。对于同一个用户如果来自不同的IP地址，MySQL则视为不同的用户；

5.MySQL的安全问题

1.操作系统相关的安全问题

【1、尽量避免以root用户运行MySQL服务器】

在Linux操作系统下，MySQL数据库目录的属主为root，数据目录的属主为mysql用户；

mysql用户专门负责MySQL数据库的启动和关闭，这样做可以防止任何具有FIFE权限的用户能够访问root用户创建的文件。

通过修改/etc/my.cnf文件或者数据库目录的my.cnf文件，MySQL数据库可以在my.cnf中配置用户名；

[mysqld] user=mysql

此时服务器启动的时候会根据my.cnf配置文件中指定的用户来启动

Linux用户启动mysqld的时候，为了确保mysqld运行，只使用对数据目录具有读或者写权限的Linux用户来启动。绝对不嗯给你使用Linux系统的root用户运行MySQL服务器；

Linux下MySQL的启动方式

http://blog.chinaunix.net/uid-13642598-id-3153537.html

【2、尽量关闭不需要的服务】

端口扫描工具推荐：

Nmap工具：

Unicornscan工具：

Zenmap工具；

Nast工具：

Knocker工具：

2.数据库相关的安全问题

【1、修改root用户口令和删除匿名账号】

drop user ‘’#@localhsot;

【2、设置安全密码】

【3、禁止远程连接数据库】

MySQL启动默认的端口3306是打开的，此时打开了mysqld的网络监听，允许用户远程通过账号密码连接本地服务器。

提供skip-networking，将my.cnf的skip-networking注释去掉即可；

 

如果确实需要提供远程用户访问数据库，此时应该考虑修改默认的监听端口，同时添加防火墙规则，只允许信任网络的mysql监听端口的数据通过；

mysqladmin -u root -p shutdown

mysqld_safe -user=mysql &

【4、不要将FILE、PROCESS或者SUPER权限授予管理员以外的账户】

FILE权限：主要用于将数据库的信息通过SELECT …INTO OUTFILE…写到服务器上有写权限的目录下，然后作为文本格式保存。

FILE权限可以通过LOAD DATA INFILE…命令写入到数据库表中。如果这些表中存放了很重要的信息，将对系统造成很大的安全隐患；

具有PROCESS权限的用户可以使用show processlist命令查看其他的用户执行的查询的文本，包括定制或者改变密码的查询；

SUPER权限可以用来执行kill命令，关闭掉其他的进程，普通用户如果具有SUPER权限，便可以关闭掉任何用户的进程。这是不安全的；

【5、限制单个用户的连接次数】

设置/etc/my.cnf文件中[mysqld]选项中的max_user_connections参数，可以用来限制单个用户允许连接的次数；

【6、REVOKE命令的漏洞】

使用REVOKE ALL PRIVILIEGES 的时候最好看下该用户是否还存在其他的操作权限，数据库回收权限应该使用REVOKE 命令单独进行权限回收；

6.使用SSH安全连接

SSL（Secure Socket Layer）利用Encryption技术可以确保数据在网络传输过程中不被截取以及窃听。

SSL技术目前广泛应用于浏览器和服务器之间的身份认证和加密数据传输；

SSL协议提供的服务主要包括：

认证用户和服务器，确保数据发送到正确的客户机和服务器；

加密数据以防止被窃取；

维护数据的完整性，确保数据在传输过程中不被改变；

步骤：

1、下载openssl工具，在Linux下安装；

tar zxvf openssl-0.9.6.tar.gz

cd openssl-0.9.6

./config

make && make install

2、查看MySQL服务器中是否支持SSL功能；

mysql -u root -p

show variables like ‘%ssl%’

mysqladmin -u root -p shutdown

3、修改MySQL服务器配置文件my.cnf，使其开启SSL功能：

vi /etc/my.cnf

4、编辑全局配置文件，使其[mysqld]后面加上“ssl”，然后重启MySQL服务器；

5、使用openssl命令生成密钥对；

mkdir -p /etc/mysql/nercerts

cd /etc/mysql/newcerts

openssl genrsa 2048 > ca-key.pem

openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem

6、使用openssl命令生成服务器端的密钥对；

openssl rep -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem>server-rep.pem

opensl x509 -rep -in server-req.pem -days 1000 -CA ca-cert.pem -Cakey ca-key.pem -set_serial 01 >server-cert.pem

7、使用openssl命令生成客户端的密钥对；

openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -Cakey ca-key.pem -set_serial 01>server-cert.pem

openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem >client-req.pem

8、使用ls命令查看下生成的CA密钥文件、服务器密钥文件和客户端密钥文件；

 

9、把生成的CA密钥文件和客户端的密钥文件传递给客户端，这里使用Windows客户端。把clien-cert.pem、client-rep.pem、ca-cert.pem、ca-key.pem传递给客户端。客户端需要配置一下my.ini文件；设置如下；
ssl-ca=”C:\Progrem Files\MySQL\MySQL Server 5.6\SSL_key\ca-cert.pem”

ssl-cert=” C:\Progrem Files\MySQL\MySQL Server 5.6\SSL_key\client-cert.pem”

ssl-key=” C:\Progrem Files\MySQL\MySQL Server 5.6\SSL_key\client-key.pem”

10、服务器使用含REQUIRE SSL子句的GRANT语句在服务器上创建一个用户，然后使用该用户来连接服务器，服务器和客户端均应该支持SSL，如下：

grant all on *.* to test2@’192.168.0.23’ identified by ‘123’ require ssl;

 

11、客户端测试SSL连接

注意：如果MySQL数据库设置SSL安全连接，那么所有的用户默认都是用SSL安全连接。如果不想使用SSL连接，可以在连接命令的后面加上‘-ssl = 0’即可，例如：

mysql -u username -p -ssl=0

 

7.综合管理用户权限

使用GRANT语句创建新账户；

使用SHOW GRANTS语句查看用户的权限信息

SHOW GRANTS FOR ‘username’@’localhost’;

【收回权限】

REVOKE 权限 ON 数据库 FROM 用户名@主机名

【删除用户的账户信息】

DROP USER 用户名@主机名;