MySQL第22天 MySQL的权限与安全

MySQL 的权限系统主要用来对连接到数据库的用户进行权限的验证,以此来判断此用户是
否属于合法的用户,如果是合法用户则赋予相应的数据库权限。
数据库的权限和数据库的安全是息息相关的,不当的权限设置可能会导致各种各样的安全隐

患,操作系统的某些设置也会对 MySQL 的安全造成影响。

MySQL 权限管理

权限系统的工作原理

MySQL 权限系统通过下面两个阶段进行认证:
(1)对连接的用户进行身份认证,合法的用户通过认证,不合法的用户拒绝连接;
(2)对通过认证的合法用户赋予相应的权限,用户可以在这些权限范围内对数据库做
相应的操作。

对于身份的认证,MySQL 是通过 IP 地址和用户名联合进行确认的,也就是说,同样的一个用户名,如
果来自不同的 IP 地址,则 MySQL 将其视为不同的用户

MySQL 的权限表在数据库启动的时候就载入内存,当用户通过身份认证后,就在内存中进
行相应权限的存取

权限表的存取

在权限存取的两个过程中,系统会用到“mysql”数据库(安装 MySQL 时被创建,数据库名
称叫“mysql”)中 user、host 和 db 这 3 个最重要的权限表

其中,通常用得最多的是用户列和权限列,其中权限列有分为普通权限和管理权限。普通权
限主要用于数据库的操作,比如 select_priv、create_priv 等;而管理权限主要用来对数据库
进行管理的操作,比如 process_priv、super_priv 等。

当用户进行连接的时候,权限表的存取过程有以下两个阶段。
 先从 user 表中的 host、user 和 password 这 3 个字段中判断连接的 IP、用户名和密码是
否存在于表中,如果存在,则通过身份验证,否则拒绝连接。

如果通过身份验证,则按照以下权限表的顺序得到数据库权限:
userdbtables_privcolumns_priv。

当只授予部分数据库某些权限时,user 表中的相应权限列保持“N”,而将
具体的数据库权限写入 db 表。
table 和 column 的权限机制和 db 类似,这里就不再赘述

当用户通过权限认证,进行权限分配时,将按照
userdbtables_privcolumns_priv 的顺序进行权限分配,即先检查全局权限表 user,如
果 user 中对应权限为“Y”,则此用户对所有数据库的权限都为“Y”,将不再检查 db、 tables_priv
和 columns_priv;如果为“N”,则到 db 表中检查此用户对应的具体数据库,并得到 db 中为
“Y”的权限;如果 db 中相应权限为“N”,则检查 tables_priv 中此数据库对应的具体表,
取得表中为“Y”的权限;如果 tables_priv 中相应权限为“N”
,则检查 columns_priv 中此表
对应的具体列,取得列中为“Y”的权限

账号管理

本节开始介绍账号的管理，主要包括账号的创建、权限更改和账号的删除

1 创建账号

有两种方法可以用来创建账号:使用 GRANT 语法创建或者直接操作授权表,但更推荐使用

第一种方法,因为操作简单,出错几率更少

GRANT 的常用语法如下:

例1：

创建用户 z1,权限为可以在所有数据库上执行所有权限,只能从本地进行连接。
mysql> grant all privileges on *.* to z1@localhost;

例2：

在例 1 基础上,增加对 z1 的 grant 权限。
mysql> grant all privileges on *.* to z1@localhost with grant option;

例 3:

在例 2 基础上,设置密码为“123”。

mysql> grant all privileges on *.* to z1@localhost identified by '123' with grant option;

例 4:

创建新用户 z2,可以从任何 IP 进行连接,权限为对 test1 数据库里的所有表进行 SELECT、
UPDATE、INSERT 和 DELETE 操作,初始密码为“123”

mysql> grant select,insert,update,delete on test1.* to 'z2'@'%' identified by '123';

本例中的 IP 限制为所有 IP 都可以连接,因此设置为“*”,mysql 数据库中是通过 user 表的

host 字段来进行控制,host 可以是以下类型的赋值。

host

1Host 值可以是主机名或 IP 号,或“localhost”指出本地主机。
2可以在 Host 列值使用通配符字符“%”和“_”。
3Host 值“%”匹配任何主机名,空 Host 值等价于“%”。它们的含义与 LIKE 操作符的模式匹配操作相同。例如,
“%”的 Host 值与所有主机名匹配,而“%.mysql.com”匹配 mysql.com 域的所有主机。

如果有多个匹配,服务器必须选择使用哪个条目。按照下述原则来解决:
 服务器在启动时读入 user 表后进行排序;
 然后当用户试图连接时,以排序的顺序浏览条目;
 服务器使用与客户端和用户名匹配的第一行。

管理权限不能授予某个指定的用户

更新权限表的用法

mysql> insert into db (host,db,user,select_priv,insert_priv,update_priv,delete_priv)
values('%','test1','z2','Y','Y','Y','Y');

mysql> flush privileges;

2.查看和更改账号权限

创建完账号后,时间长了可能就会忘记分配的权限而需要查看账号权限

 查看权限。
账号创建好后,可以通过如下命令进行查看权限:
show grants for user@host;

host 可以不写,默认是“%”

对于 MySQL 5.0 以后的版本,也可以利用新增的 information_schema 数据库进行权限的查看:

mysql> select * from SCHEMA_PRIVILEGES where grantee="'z1'@'localhost'";

 更改权限

权限变更也有两种方法:使用 GRANT(新
增)和 REVOKE(回收)语句,或者更改权限表。

REVOKE 语句可以回收已经赋予的权限,语法如下:
REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON [object_type] {tbl_name | * | *.* | db_name.*}
FROM user [, user] ...
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

usage 权限不能被回收,也就是说,REVOKE 用户并不能删除用户

 修改密码

方法 1:可以用 mysqladmin 命令在命令行指定密码。
shell> mysqladmin -u user_name -h host_name password "newpwd"

方法 2:执行 SET PASSWORD 语句。下例中将账号'jeffrey'@'%'的密码改为'biscuit'。
mysql> SET PASSWORD FOR 'jeffrey'@'%' = PASSWORD('biscuit');
如果是更改自己的密码,可以省略 for 语句:
mysql> SET PASSWORD = PASSWORD('biscuit');

方法 3:还可以在全局级别使用 GRANT USAGE 语句(在*.*)来指定某个账户的密码而
不影响账户当前的权限

mysql> GRANT USAGE ON *.* TO 'jeffrey'@'%' IDENTIFIED BY 'biscuit';

方法 4:直接更改数据库的 user 表。

shell> mysql -u root mysql
mysql> INSERT INTO user (Host,User,Password)
-> VALUES('%','jeffrey',PASSWORD('biscuit'));
mysql> FLUSH PRIVILEGES;
shell> mysql -u root mysql
mysql> UPDATE user SET Password = PASSWORD('bagel')
-> WHERE Host = '%' AND User = 'francis';
mysql> FLUSH PRIVILEGES;

注意:更改密码时候一定要使用 PASSWORD 函数(mysqladmin 和 GRANT 两种方式不用写,会自动加上)。

3.删除账号

要彻底删除账号,同样也有两种方法:DROP USER 命令和修改权限表。
DROP USER 语法非常简单,具体如下

DROP USER user [, user] ...

MySQL 安全问题

操作系统相关的安全问题

1.严格控制操作系统账号和权限

在数据库服务器上要严格控制操作系统的账号和权限,比如:
 锁定 mysql 用户;
 其他任何用户都采取独立的账号登录,管理员通过 mysql 专有用户管理 MySQL,或者
通过 root su 到 mysql 用户下进行管理;
 mysql 用户目录下,除了数据文件目录,其他文件和目录属主都改为 root。

2.尽量避免以 root 权限运行 MySQL

MySQL 安装完毕后,一般会将数据目录属主设置为 mysql 用户,而将 MySQL 软件目录的属
主设置为 root,这样做的目的是当使用 mysql 用户启动数据库时,可以防止任何具有 FILE
权限的用户能够用 root 创建文件。而如果使用 root 用户启动数据库,则任何具有 FILE 权限
的用户都可以读写 root 用户的文件,这样会给系统造成严重的安全隐患。来看下面一个例
子。

3.防止 DNS 欺骗

数据库相关的安全问题

本节介绍一些常见的数据库安全问题,这些问题大多数是由于账号的管理不当造成的。希望
读者读完本节后能够认识到账号管理的重要性,同时加强对账号管理的安全意识

1.删除匿名账号

2.给 root 账号设置口令

mysql> set password=password('newpassword');

3.设置安全密码

密码的安全体现在以下两个方面:
设置安全的密码,建议使用 6 位以上字母、数字、下画线和一些特殊字符组合而
成的字符串;
使用上的安全,使用密码期间尽量保证使用过程安全,不会被别人窃取。

1)直接将密码写在命令行中

(2)交互式方式输入密码

3)将用户名和密码写在配置文件里面,连接的时候自动读取。比如应用连接数据库或
者执行一些批处理脚本。对于这种方式,MySQL 供了一种方法,在 my.cnf 里面写入连接信

息。

[client]
user=username
password=password
然后对配置文件进行严格的权限限制,例如:
chmod +600 my.cnf

第 1 种最不安全，第2 种比较安全， 第三种使用渐变，但是任何只要可以登录操作系统的用户都可以自动登录,存在一定
的安全隐患

4.只授予账号必须的权限

比如：

Grant select,insert,update,delete on tablename to ‘username’@’hostname’;

赋予用户权限的时候越具体,
则对数据库越安全

5.除 root 外,任何用户不应有 mysql 库 user 表的存取权限

由于 MySQL 中可以通过更改 mysql 数据库的 user 表进行权限的增加、删除、变更等操作,
因此,除了 root 以外,
任何用户都不应该拥有对 user 表的存取权限(SELECT、 UPDATE、 INSERT、
DELETE 等),造成系统的安全隐患。

6.不要把 FILE、PROCESS 或 SUPER 权限授予管理员以外的

账号

FILE 权限主要以下作用:
 将数据库的信息通过 SELECT ...INTO OUTFILE...写到服务器上有写权限的目录下,作
为文本格式存放。具有权限的目录也就是启动 MySQL 时的用户权限目录。
 可以将有读权限的文本文件通过 LOAD DATA INFILE...命令写入数据库表,如果这些
表中存放了很重要的信息,将对系统造成很大的安全隐患。

PROCESS 权限能被用来执行“show processlist”命令,查看当前所有用户执行的查询的明文
文本,包括设定或改变密码的查询。在默认情况下,每个用户都可以执行“show processlist”

命令,但是只能查询本用户的进程。因此,对 PROCESS 权限管理不当,有可能会使得普通
用户能够看到管理员执行的命令

SUPER 权限能执行 kill 命令,终止其他用户进程

7.LOAD DATA LOCAL 带来的安全问题

LOAD DATA 默认读的是服务器上的文件,但是加上 LOCAL 参数后,就可以将本地具有访问权
限的文件加载到数据库中。这在带来方便的同时,也带来了以下安全问题。

 可以任意加载本地文件到数据库。
 在 Web 环境中,客户从 Web 服务器连接,用户可以使用 LOAD DATA LOCAL 语句来
读取 Web 服务器进程有读访问权限的任何文件(假定用户可以运行 SQL 服务器的
任何命令)。在这种环境中,MySQL 服务器的客户实际上是 Web 服务器,而不是连
接 Web 服务器的用户运行的程序。
解决方法是,可以用--local-infile=0 选项启动 mysqld 从服务器端禁用所有 LOAD DATA LOCAL
命令。

对于 mysql 命令行客户端,可以通过指定--local-infile[=1]选项启用 LOAD DATA LOCAL,或通过
--local-infile=0 选项禁用

8.使用 MERGE 存储引擎潜藏的安全漏洞

MERGE 存储引擎的表在某些版本中可能存在以下安全漏洞:
 用户 A 赋予表 T 的权限给用户 B;
 用户 B 创建一个包含 T 的 MERGE 表,做各种操作;
 用户 A 收回对 T 的权限。
存在的安全隐患是用户 B 通过 merge 表仍然可以访问表 A 中的数据。下面例子描述了这个
过程。

9.DROP TABLE 命令并不收回以前的相关访问授权

注意:对表做删除后,其他用户对此表的权限不会自动收回,一定记住要手工收回。

10.使用 SSL

SSL(Secure Socket Layer,安全套接字层)是一种安全传输协议,最初由 Netscape 公司所开
发,用以保障在 Internet 上数据传输之安全,利用数据加密(Encryption)技术,可确保数
据在网络上之传输过程中不会被截取及窃听。

在 MySQL 中,要想使用 SSL 进行安全传输,需要在命令行中或选项文件中设置“--ssl”选项

对于服务器,“--ssl”选项规定该服务器允许 SSL 连接。对于客户端程序,它允许客户使用
SSL 连接服务器。单单该选项不足以使用 SSL 连接。还必须指定--ssl-ca、--ssl-cert 和--ssl-key
选项。如果不想启用 SSL,可以将选项指定为--skip-ssl 或--ssl=0

确保使用 SSL 连接的安全方式是,使用含 REQUIRE SSL 子句的 GRANT 语句在服务器上创建一
个账户,然后使用该账户来连接服务器,服务器和客户端均应启用 SSL 支持。

mysql> grant select on *.* to z4 identified by '123' REQUIRE ssl;

 --ssl-ca=file_name 含可信 SSL CA 的清单的文件的路径。
 --ssl-cert=file_name SSL 证书文件名,用于建立安全连接。
 --ssl-key=file_name SSL 密钥文件名,用于建立安全连接。

11.如果可能,给所有用户加上访问 IP 限制

对数据库来说,我们希望客户端过来的连接都是安全的,因此,就很有必要在创建用户
的时候指定可以进行连接的服务器 IP 或者 HOSTNAME,只有符合授权的 IP 或者 HOSTNAME
才可以进行数据库访问

12.REVOKE 命令的漏洞

当用户对多次赋予权限后,由于各种原因,需要将此用户的权限全部取消,此时,REVOKE
命令可能并不会按照我们的意愿执行

在一个数据库上多次赋予权限,权限会自动合并;但

是在多个数据库上多次赋予权限,每个数据库上都会认为是单独的一组权限,必须在此数据
库上用 REVOKE 命令来单独进行权限收回,而 REVOKE ALL PRIVILEGES ON *.* 并不会替

用户自动完成这个。

其他安全设置选项

除了上面介绍的那些需要注意的安全隐患外,MySQL 本身还带着一些选项,适当地使用这

些选项将会使数据库更加安全。

old-passwords

新版本对于老版本密码的不支持

(1)在服务器端用 OLD_PASSWORD 函数更改密码为旧密码格式,客户端先可以进行正
常连接:
mysql> SET PASSWORD FOR 'some_user'@'some_host' = OLD_PASSWORD('mypass');
(2)在 my.cnf 的[mysqld]中增加 old-passwords 参数并重启服务器,这样新的数据库连接
成功之后做的 set password、grant、password()操作后,生成的新密码全部变成旧的密
码格式。

safe-user-create

此参数如果启用,用户将不能用 GRANT 语句创建新用户,除非用户有 mysql 数据库中
user 表的 INSERT 权限。如果想让用户具有授权权限来创建新用户,应给用户授予下面的权
限:

mysql> GRANT INSERT(user) ON mysql.user TO 'user_name'@'host_name';

grant insert on mysql.user to z1@localhost;

secure-auth

secure-auth 参数的作用是让 MySQL 4.1 以前客户端无法进行用户认证。即使使用了
old-passwords 参数也行。此参数的作用就是为了防止低版本的客户端使用旧的密码认证方
式连接高版本的服务器,从而引起安全隐患

skip-grant-tables

skip-grant-tables 这个选项导致服务器根本不使用权限系统,从而给每个人以完全访问所有数
据库的权力。

通过执行 mysqladmin flush-privileges 或 mysqladmin reload 命令,或执行 flush
privileges 语句,都可以让一个正在运行的服务器再次开始使用授权表。

skip-network

在网络上不允许 TCP/IP 连接,所有到数据库的连接必须经由命名管道(Named Pipes)
或共享内存(Shared Memory)或 UNIX 套接字(SOCKET)文件进行。这个选项适合应用
和数据库共用一台服务器的情况,其他客户端将无法通过网络远程访问数据库,大大增强
了数据库的安全性,但同时也带来了管理维护上的不方便

skip-show-database

使用 skip-show-database 选项,只允许有 show databases 权限的用户执行 show databases
语句,该语句显示所有数据库名。不使用该选项,允许所有用户执行 show databases,但

只显示用户有 show databases 权限或部分数据库权限的数据库名

小结

权限和安全问题在任何数据库中都是非常重要的。本章重点介绍了 MySQL 中的权限管理以
及可能存在的一些安全问题,并给出了很多例子加以详细说明。最后还讨论了 MySQL 提供
的一些安全方面的参数,用户可以根据实际情况选择使用。