🔍
📢

MySQL用户权限管理介绍

设置用户与权限

一个MySQL系统可能有许多用户。为了安全起见,root用户通常只用管理目的。对于每个需要使用该系统的用户,应该为他们创建一个账号和密码。这些用户名和密码不必与MySQL之外的用户名称和密码(例如,Linux或NT用户名和密码)相同。同样的原则也适合于root用户。对于系统用户和MySQL用户最好使用不同的密码,这一点对root用户尤其应该这样。
为用户设置密码不是必须的,但是强烈建议为所有创建的用户设定密码。要建立一个Web数据库,最好为每个网站应用程序建立一个用户。你可能会问,“为什么要这么做呢?”----答案在于权限。

用户管理

我们的MySQL的用户,都被记录在了mysql数据库的user表中。

在MySQL数据库中,一个完整的用户账号应该包含用户名登录主机,也就是说 用户名@主机名才是一个完整的用户账号。

创建用户基本语法:

CREATE USER 用户名@主机名 IDENTIFIED BY '密码';

删除用户基本语法:

DROP USER 用户名@主机名;

修改用户密码:

- 修改自己的密码
set password = password('新密码');
- 修改别人的密码(需要有该权限)
set password for 用户名@主机名 = password('新密码');

MySQL权限系统介绍

MySQL的最好特性之一是支持复杂的权限系统权限是对特定对象执行特定操作的权力,它与特定用户相关。其概念非常类似于文件的权限。挡在MySQL中创建一个用户时,就赋予了该用户一定的权限,这些权限指定了用户在本系统中可以做什么和不可以做什么。

最少权限原则

最少权限原则可以用来提高任何计算机系统的安全性。它是一个基本的、但又是非常重要的而且容易为我们忽略的原则。该原则包含如下内容:

一个用户(或一个进程)应该拥有能够执行分配给他的任务的最低级别的权限。

该原则同样适用于MySQL,就像它应用于其他地方一样。例如,要在网站上运行查询,用户并不需要root用户所拥有的所有权限。因此,我们应该创建另一个用户,这个用户只有访问我们刚刚建立的数据库的必要权限。

创建用户:GRANT命令

GRANT和REVOKE命令分别用来授予取消MySQL用户的权限,这些权限分4个级别。它们分别是:

  • 全局
  • 数据库

GRANT命令用来创建用户并赋予他们权限。GRANT命令的常见形式是:

GRANT privileges [columns]
ON item
TO user_name [IDENTIFIED BY 'password']
[REQUIRE ssl_options]
[WITH [GRANT OPTION | limit_options]]

普通写法可以简略如下:

GRANT 权限列表 ON 数据库.表 TO 用户名@主机名 [IDENTIFIED BY '密码'];

identified by可以省略,也可以写出
(1)如果写了,用户存在,就是修改用户的密码
(2)如果写了,该用户不存在,就是创建用户,同时指定密码

方括号内的子句是可选的。在本语法中,出现了许多占位符。第一个占位符是 privileges,应该是由逗号分开的一组权限。MySQL已经有一组已定义的权限。它们在下一节详细介绍。
占位符 columns是可选的。可以用它对每一个列指定权限。也可以使用单列的名称或者逗号分开的一组列的名称。

占位符 item是新权限的所应用于的数据库或表。可以将项目指定为“.”,而将权限应用于所有数据库。这叫做赋予全局权限。如果没有使用在特定的数据库,也可以通过只指定*完成赋予全局权限。更常见的是,以dbname.*的形式指定数据库中所有的表,以dbname.tablename的形式指定单个表,或者通过指定tablename来指定特定的列。这些分别表示其他3个可以利用的权限:数据库、表、列。如果在输入命令的时候正在使用一个数据库,tablename本身将被解释成当前数据库中的一个表。

user_name应该是用户登录MySQL所使用的用户名。请注意,它不必与登录系统时所使用的用户名相同。MySQL中的user_name也可以包含一个主机名。可以用它来区分如itbsl(解释成itbsl@localhost)和itbsl@somewhere.com。这是非常有用的一项能力,因为来自不同域的用户经常可能使用同一个名字。这也提高了安全性能,因为可以指定用户从什么地方连接到本机,甚至可以指定它们在特定的地方可以访问那些表和数据库。

password应该是用户登录时使用的密码。常见的密码选择规则在这里都适用。我们后面将更详细地讲述安全问题,但是密码应该不容易被猜出来。这意味着,密码不应该是一个字段单词或与用户名相同。理想的密码应该是大、小写字母和非字母的组合。

REQUIRE子句允许指定用户是否必须通过加密套接字连接,或者指定其它的SSL选项,关于SSL到MySQL连接的更多信息,请参阅MySQL手册。

WITH GRANT OPTION选项,如果指定,表示允许指定的用户向别人授予自己所拥有的权限。
我们也可以指定如下所示的WITH子句:
MAX_QUERIES_PER_HOUR n
或者
MAX_UPDATES_PER_HOUR n
或者
MAX_CONNECTIONS_PER_HOUR n
这些子句可以指定每个用户每小时执行的查询、更新和链接的数量。在共享的系统上限制单个用户的负载时,这些子句是非常有用的。
权限存储在名为mysql的数据库中的5个系统中。这些表分别是mysql.user、mysql.db、mysql.host、mysql.tables_priv和mysql.columns_priv。作为GRANT命令的替代,可以直接修改这些表。

权限的类型和级别

MySQL中存在3个基本类型的权限:适用于赋予一般用户的权限、适用于赋予管理员的权限和几个特定的权限。任何用户都可以被赋予这3类权限,但是根据最少权限原则,最好严格限定只将管理员类型的权限赋予管理员。

我们应该只赋予用户访问他们必须使用的数据库和表的权限。而不应该将访问mysql的权限赋予不是管理员的人。mysql数据库是所有用户名、密码等信息存储的地方。

常规用户的权限直接与特定的SQL命令类型以及用户是否被允许运行它们相关。下表所示的是基本用户权限。“应用于”列下面的对象给出了该类型权限可以授予的对象。

用户的权限

权限 应用于 描述
SELECT 表、列 允许用户从表中查询行(记录)
INSERT 表、列 允许用户在表中插入新行
UPDATE 表、列 允许用户修改现存表里行中的值
DELETE 允许用户删除现存表的行
INDEX 允许用户创建和拖动特定表索引
ALTER 允许用户改变现存表的结构,例如,可添加列、重命名列或表、修改列的数据类型
CREATE 数据库、表 允许用户创建新数据库或表。如果在GRANT中指定了一个特定的数据库或表,它们只能够创建该数据库或表,即它们必须首先删除(drop)它
DROP 数据库、表 允许用户拖动(删除)数据库或表

从系统的安全性方面考虑,适于常规用户的权限大多数是相对无害的。ALTER权限通过重命名表可能会影响权限系统,但是大多数用户需要它。安全性常常是可用性与保险性的折中。遇到ALTER的时候,应当做出自己的选择,但是通常还是会将这个权限授予用户。

下面的表给出了适用于管理员用户使用的权限。
可以将这些权限授予非管理员用户,这样做的时候要非常小心。
FILE权限有些不同,它对普通用户非常有用,因为它可以将数据从文件载入数据库,从而可以节省许多时间,否则,每次将数据输入数据库都需要重新输入,这很浪费时间。
然而,文件载入可以用来载入MySQL可识别的任何文件,包括属于其他用户的数据库和潜在的密码文件。授予该权限的时候需要小心,或者自己为用户载入数据。

管理员权限

权限 描述
CREATE TEMPORARY TABLES 允许管理员在CREATE TABLE语句中使用TEMPORARY关键字
FILE 允许将数据从文件读入表,或从表读入文件
LOCK TABLES 允许使用LOCK TABLES语句
PROCESS 允许管理员查看属于所有用户的服务器进程
RELOAD 允许管理员重新载入授权表、清空授权、主机、日志和表
REPLICATION CLIENT 允许管理员重新载入授权表、和从机(Slave)上使用SHOW STATUS
REPLICATION SLAVE 允许复制从服务器连接到主服务器
SHOW DATABASES 允许使用SHOW DATABASES语句查看所有数据库列表。没有这个权限,用户只能看到他们能够看到的数据库
SHUTDOWN 允许管理员关闭MySQL服务器
SUPER 允许管理员关闭属于任何用户的的线程

特别的权限

权限 描述
ALL 授予上面两个表列表的所有权限。也可以将ALL写成ALL PRIVILEGES
USAGE 不授予权限。这创建一个用户并允许他登录,但是不允许进行任何操作。通常会在以后授予该用户更多的权限

REVOKE命令

与GRANT相反的命令是REVOKE。它用来从一个用户收回权限。在语法上与GRANT非常相似:

REVOKE privileges [(columns)]
ON item
FROM user_name

中文翻译:

REVOKE 权限列表 ON 数据库.对象 FROM 用户名@主机名;

如果已经给出了WITH GRANT OPTION子句,可以按如下方式撤销它(以及所有其他权限):

REVOKE ALL PRIVILEGES, GRANT
FROM user_name

权限立即生效

当我们修改用户权限之后,如果想不重启立即生效,需要执行以下flush privileges,这样能快速刷新权限

FULSH PRIVILEGES;

使用GRANT和REVOKE的例子

要创建一个管理员,可以输入如下所示的命令:

grant all on * to fred identified by 'mnb123' with grant option;

以上命令授予了用户名为fred、密码为mnb123的用户使用所有数据库的所有权限,并允许他向其他人授予这些权限。

如果不希望用户在系统中存在,可以按如下方式撤销:

revoke all privileges, grant from red;

现在,我们可以按如下方式创建一个没有任何权限的常规用户:

grant usage on books.* to sally identified by 'magic123';

在与sally交谈后,我们对她需要进行的操作有了一定的了解,因此按如下方式可以授予她适当的权限:

grant select, insert, update, delete, index, alter, create, drop on books.* to sally;

请注意,要完成这些,并不需要指定sally密码。

如果我们认为sally权限过高,可能会决定按如下方式减少一些权限:

revoke alter, create, drop on books.* from sally;

后来,当她不再需要使用数据库时,可以按如下方式撤销所有的权限:

revoke all on books.* from sally;