​MySQL中用户与权限的示例分析

小编给大家分享一下MySQL中用户与权限的示例分析，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

MySQL用户与权限

         在连接到 MySQL 服务器并执行查询时，会验证您的身份并为您的活动授权。

l  验证：验证用户的身份。这是访问控制的第一个阶段。每次连接时都必须成功验证身份。如果验证失败，则无法连接，您的客户机将断开连接。

l  授权：验证用户的权限。这是访问控制的第二个阶段，面向针对成功验证了身份的活动连接的每个请求。对于每个请求， MySQL 将确定您要执行的操作，然后检查您是否有执行此操作所需的恰当权限。

         通过查询mysql.user表可以查看用户权限等信息，例如：

mysql> SELECT * FROM mysql.user\G

mysql> SELECT user, host, password FROM mysql.user WHERE user='root';

         在使用本机口令验证插件（mysql_native_password，默认的验证机制）连接到 MySQL 服务器时，会将指定的用户名、发出连接的主机以及口令与 mysql.user 表中的行进行匹配，以此确定您能否连接和执行操作。

         要使用 mysql 客户机连接到本地服务器，请指定您要使用的帐户的用户名和口令：

shell> mysql -u<username> -p<password>

         请注意，与 mysql.user 表中您的用户关联的主机名是指发起连接的主机（而不是服务器主机）的名称。要连接到未在您客户机的本地主机上安装的服务器，请提供您要连接到的服务器的主机名：

shell> mysql -u<username> -p<password> -h<server_host>

1.1.        创建用户与设置口令

1)         创建用户

         提供每个用户帐户的用户和主机。例如，使用 CREATE USER...IDENTIFIED BY 语句

CREATE USER 'jim'@'localhost' IDENTIFIED BY 'Abc123';

         在创建帐户时避免可能的安全风险：不创建没有口令的帐户、不创建匿名帐户、在可能的情况下，避免在指定帐户主机名时使用通配符。

         帐户名称包括用户名和用户必须从其连接到服务器的客户机主机的名称。帐户名称的格式为 'user_name'@'host_name'。用户名长度最多可以有 16 个字符。如果用户名和主机名包含特殊字符（如短划线），则必须将它们放在单引号中。如果某个值在不带引号时也有效，则引号是可选的。但是，在任何情况下都可使用引号。允许的主机名格式示例：

• 主机名：localhost

• 合格的主机名：'hostname.example.com'

• IP 编号：192.168.9.78

• IP 地址：10.0.0.0/255.255.255.0

• 模式或通配符：% 或_

用户名和主机名示例：

• john@10.20.30.40

• john@'10.20.30.%'

• john@'%.ourdomain.com'

• john@'10.20.30.0/255.255.255.0'

         另外，GRANT 语句也可创建新帐户或者修改现有帐户。具体参考“授予权限”。

2)         设置口令

设置MySQL 用户口令的方法有多种：

– CREATE USER...IDENTIFIED BY

– GRANT...IDENTIFIED BY

– SET PASSWORD

– mysqladmin password

– UPDATE 授权表（不推荐）

为所有用户帐户分配唯一的强口令。

• 避免可以轻易猜测到的口令。

• 使用以下SELECT 语句可列出没有口令的所有帐户：

SELECT Host，User FROM mysql.user WHERE Password = '';

• 确定重复口令：

SELECT User FROM mysql.user GROUP BY password HAVING count(user)>1;

• 让口令失效：

ALTER USER jim@localhost PASSWORD EXPIRE;

                  MySQL 使用多种算法对用户表中存储的口令加密：

l  mysql_native_password 插件实施标准口令格式： 41 字节宽的散列。

l  mysql_old_password 插件实施较旧的格式，安全性较低， 16 字节宽。

l  sha256_password 插件实施在安全计算中广泛采用的 SHA-256 散列算法。

         old_passwords 系统变量的值指定 PASSWORD() 函数用于创建口令的算法，如下所示：

：标准算法，与 MySQL 4.1.1 及更高版本中所用的算法相同

1：旧算法，与 MySQL 4.1.1 之前版本中所用的算法相同

2： SHA-256 算法

       启动服务器时将 default-authentication-plugin 选项设置为 sha256_password，可将 SHA-256 口令用于所有新用户，也可使用 CREATE USER 和 IDENTIFIED WITH

         sha256_password 子句为特定用户指定 SHA-256 口令。有关 sha256_password 插件的进一步信息，请访问

http://dev.mysql.com/doc/refman/5.6/en/sha256-authentication-plugin.html

3)         管理用户

         使用 RENAME USER 语句可重命名用户帐户，即更改现有帐户的帐户名称，更改帐户名称的用户名或主机名部分，或同时更改两者；

mysql> RENAME USER 'jim'@'localhost' TO 'james'@'localhost';

         使用 DROP USER 语句可删除用户帐户；撤消现有帐户的所有权限，然后删除该帐户，同时从存在帐户的任何授权表中删除帐户的所有记录；

mysql> DROP USER 'jim'@'localhost';

4)         关于口令验证插件

A.        客户端明文验证插件

         有些验证方式（如 PAM（Pluggable Authentication Module，可插入验证模块）验证）要求客户机向服务器发送纯文本口令，以便服务器处理普通形式的口令。mysql_clear_password 插件支持此行为。

       MySQL 客户机库中有一个内置的明文验证插件mysql_clear_password。该插件可用于将纯文本口令发送给服务器，口令通常经过散列处理。

         通过以下方式启用：

方式一：LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN 环境变量，在运行 MySQL 客户机应用程序（如 mysql 和mysqladmin）时指定 --enable-cleartext-plugin；

方式二：mysql_options() C API 函数的MYSQL_ENABLE_CLEARTEXT_PLUGIN 选项；

B.        可载入验证插件

Ø  test_plugin_server：实施本机和旧口令验证，此插件使用 auth_test_plugin.so 文件。测试验证插件 (test_plugin_server) 使用本机或旧口令验证进行身份验证，适用于测试和开发。

Ø  auth_socket：仅允许通过 UNIX 套接字从具有相同名称的 UNIX 帐户登录的 MySQL 用户，此插件使用 auth_socket.so 文件。套接字对等凭证 (auth_socket) 插件允许用户仅在其 Linux sername 与其 MySQL帐户匹配时通过 UNIX 套接字文件连接。

Ø  authentication_pam：允许使用外部验证机制登录，此插件使用 authentication_pam.so 文件。PAM 验证插件 (authentication_pam) 是一个企业版插件，允许使用外部验证机制登录。 MySQL 不存储您的口令，但使用 UNIX PAM（Pluggable Authentication Module，可插入验证模块）机制传输客户机提供的用户名和口令供操作系统进行验证。

         要载入可载入验证插件，可在服务器启动时在命令行中或在 my.cnf 文件中使用plugin-load 选项，如以下示例中所示：

[mysqld]

plugin-load=authentication_pam.so

         另外，您也可以开发自己的验证插件。测试验证插件适用于开发者创建自己的插件；其源代码随MySQL 源代码一起分发。

C.        PAM 验证插件

         前面简单介绍了多种插件，下面重点介绍一下PAM验证插件，PAM 验证插件是一个企业版插件，可向操作系统验证MySQL 帐户。PAM 可定义配置验证的服务。这些服务存储在 /etc/pam.d 中，该插件也针对以下各项进行验证：操作系统用户和组、外部验证（如 LDAP）；

         要创建使用 PAM 验证的 MySQL 用户，请执行以下操作：

mysql>CREATE USER user@host IDENTIFIED WITH authentication_pam AS 'pam_service, os_group=mysql_user';

         PAM 在 /etc/pam.d 中查找其验证的服务。例如，要创建 PAM 服务 mysql-pam，可在创建文件 /etc/pam.d/mysql-pam 时使用以下内容：

#%PAM-1.0

auth include password-auth

account include password-auth

         除了 MySQL 验证以外， PAM 还会与其他验证方法（包括 LDAP 和 Active Directory）集成，因此可以使用 PAM 向网络中的单个存储验证许多服务（包括 MySQL）。要创建直接映射到某个操作系统用户的 MySQL 用户，可使用如下语句：

mysql>CREATE USER bob@localhost IDENTIFIED WITH authentication_pam AS 'mysql-pam';

         当 bob 登录时， MySQL 会将从客户机接收的用户名和口令传递到 PAM，后者向操作系统验证。客户机必须以明文形式发送口令。启用客户端明文验证插件以实现此目的：

shell> mysql --enable-cleartext-plugin -ubob -p

Enter password: bob’s_OS_password

         要使用 PAM 验证插件启用基于组的登录，可创建一个启用 PAM 的匿名代理帐户，该帐户不与任何用户匹配，但指定一组从操作系统组到 MySQL 用户的映射：

mysql>CREATE USER ''@'' IDENTIFIED WITH authentication_pam AS 'mysql-pam, sales=m_sales, finance=m_finance';

         上例假定您拥有 sales 和 finance 操作系统组以及 m_sales 和 m_finance MySQL用户。然后，必须向该匿名代理帐户授予 PROXY 权限，使其能以 m_sales 和 m_finance MySQL 用户身份登录：

GRANT PROXY ON m_sales@localhost TO ''@'';

GRANT PROXY ON m_finance@localhost TO ''@'';

         现在，作为 sales 和 finance 组成员的用户可以在 mysql 命令行提示符处提供其操作系统凭证，以 m_sales 或 m_finance MySQL 用户身份登录，从而拥有授予这些帐户的所有权限。例如，如果 peter 是 sales 组的成员，则可通过以下方式登录：

shell> mysql --enable-cleartext-plugin -upeter -p

Enter password: peter’s_OS_password

Welcome to the MySQL monitor. Commands end with ; or \g.

...

mysql> SELECT CURRENT_USER();

+-------------------+

| CURRENT_USER() |

+-------------------+

| m_sales@localhost |

+-------------------+

1 row in set (0.01 sec)

1.2.        权限列表

1.3.        授予权限

1)         GRANT 语句

GRANT 语句可创建新帐户或者修改现有帐户。GRANT 语法：

GRANT SELECT ON world_innodb.* TO 'kari'@'localhost' IDENTIFIED BY 'Abc123';

该语句的子句：

– 要授予的权限

– 权限级别：

—  全局：*.*

—  数据库：<db_name>.*

—  表：<db_name>.<table_name>

—  存储过程：<db_name>.<routine_name>

– 要授予其权限的帐户

– 可选口令

2)         允许的主机名格式示例

• 主机名：localhost

• 合格的主机名：'hostname.example.com'

• IP 编号：192.168.9.78

• IP 地址：10.0.0.0/255.255.255.0

• 模式或通配符：% 或_

用户名和主机名示例：

• john@10.20.30.40

• john@'10.20.30.%'

• john@'%.ourdomain.com'

• john@'10.20.30.0/255.255.255.0 '

3)         授予管理权限

         以下全局权限适用于管理用户：

l  FILE：允许用户指示 MySQL 服务器在服务器主机文件系统中读取和写入文件；

l  PROCESS：允许用户使用 SHOW PROCESSLIST 语句，查看客户机正在执行的所有语句；

l  SUPER：允许用户中止其他客户机连接，或者更改服务器的运行时配置；SUPER 管理权限允许用户执行额外任务，其中包括设置全局变量和终止客户机连接。

l  ALL：授予所有权限（但不能向其他用户授予权限），要尽可能少地授予管理权限，因为管理权限可能会被恶意用户或粗心用户滥用。

l  使用 ALL 和 ALL PRIVILEGES 授予所有权限（但不能向其他帐户授予权限）。使用GRANT ALL … WITH GRANT OPTION 授予所有权限（可以向其他帐户授予权限）。

l  使用 USAGE 允许连接到服务器。此权限将在 user 表中为帐户创建一个记录，但没有任何权限。然后，可以使用帐户访问服务器用于有限的目的，例如发出 SHOW VARIABLES 或 SHOW STATUS 语句。不能使用帐户访问表之类的数据库内容，但可在以后授予此类权限。

         其他管理权限包括 CREATE USER、 CREATE TEMPORARY TABLES、 SHOW DATABASES、LOCK TABLES、 RELOAD 和 SHUTDOWN。可以利用管理权限来破坏安全、访问权限数据或者对服务器执行DDoS攻击。确保仅将这些权限授予适当的帐户。有关授予 MySQL 权限的更多信息，请参阅《MySQL 参考手册》：

http://dev.mysql.com/doc/refman/5.6/en/privileges-provided.html

4)         查询用户权限

         使用 SHOW GRANTS 语句显示常规帐户权限，口令以加密形式存储和显示。

SHOW GRANTS;

SHOW GRANTS FOR CURRENT_USER();

mysql> SHOW GRANTS FOR 'kari'@'myhost.example.com';

+----------------------------------------------------------------+

| Grants for kari@myhost.example.com |

+----------------------------------------------------------------+

| GRANT FILE ON *.* TO 'kari'@'myhost.example.com' |

| GRANT SELECT ON `world_innodb`.* TO 'kari'@'myhost.example.com‘|

| IDENTIFIED BY PASSWORD |

|'*E74858DB86EBA20BC33D0AECAE8A8108C56B17FA' |

+----------------------------------------------------------------+

SHOW GRANTS 显示了为指定用户重新创建权限的语句。该语句仅显示在该语句中指定的帐户的权限。

如果帐户有口令，则 SHOW GRANTS 将在 GRANT 语句末尾显示一条 IDENTIFIED BY PASSWORD 子句，该子句可列出帐户的全局权限。 IDENTIFIED BY 之后的单词PASSWORD 指示显示的口令值是存储在用户表中的加密值，不是实际口令。由于口令是使用单向加密存储的，因此 MySQL 无法显示未加密的口令。

如果帐户可以将其部分或全部权限授予其他帐户，则输出将在其适用的每条 GRANT 语句末尾显示 WITH GRANT OPTION。

1.4.        权限表

1)         权限表

MySQL 安装过程会创建权限表，权限表使用 MyISAM 存储引擎。

user：      针对服务器已知的每个帐户包含一个记录

db：         特定于数据库的权限

tables_priv：  特定于表的权限

columns_priv：特定于列的权限

procs_priv：存储过程和函数权限

每个权限表有 host 列和 user 列，用于标识其记录适用的帐户。在连接尝试过程中，服务器会确定客户机是否能连接。服务器根据 user 表的 Host、 User 和 Password 列确定客户机是否可连接。要成功连接， MySQL 必须将用户表中的某个记录与客户机发起连接的主机、客户机提供的用户名以及匹配记录中列出的口令匹配。

在连接后，服务器会确定每条语句的访问权限。在客户机连接之后，MySQL 将检查每条语句的访问权限：将帐户的身份与权限表的 Host和 User 列匹配。

l  user 表的每行中的权限全局适用于其 Host 和 User 列标识的帐户。

l  db、 tables_priv、 columns_priv 和 procs_priv 表的匹配记录中的权限在由特定权限表的名称标识的级别上适用。

例如，一个 db 表记录中的权限适用于在该记录中指定的数据库，但不适用于其他数据库。

2)         影响权限的情况

服务器会在其启动过程中将授权表读取到内存中，并使用内存中副本来检查客户机访问权限。在下列情况下，服务器将刷新其授权表的内存中副本：

l  通过发出帐户管理语句（如 CREATE USER、 GRANT、 REVOKE 或 SET PASSWORD）修改了用户帐户。

l  通过发出 FLUSH PRIVILEGES 语句或者执行 mysqladmin flush-privileges或mysqladmin reload 命令显式重新载入了表。

由于以下原因，应避免直接更改授权表：

l  帐户管理语句的语法设计清晰、简单明了。

l  如果在某个帐户管理语句中犯错，该语句就会失败，不会更改任何设置。

l  如果在直接更改授权表时犯错，则可能会将所有用户锁在系统外面。

1.5.        撤消用户权限

• 使用REVOKE 语句可以撤消特定的SQL 语句权限：

REVOKE DELETE，INSERT，UPDATE ON world_innodb.*

FROM 'Amon'@'localhost';

• 撤消权限以便将权限授予其他用户：

REVOKE GRANT OPTION ON world_innodb.*

FROM 'Jan'@'localhost';

• 撤消所有权限（包括向他人授权）：

REVOKE ALL PRIVILEGES，GRANT OPTION

FROM 'Sasha'@'localhost';

• 在发出REVOKE 之前使用SHOW GRANTS 语句确定要

撤消的权限，随后再次确认结果。

1.6.        访问控制

l  要指示服务器不读取授权表并禁用访问控制，可使用--skip-grant-tables 选项。每个连接都成功：

a)       可以提供任何用户名及任何口令，并且可以从任何主机连接。

b)       该选项将禁用整个权限系统。

c)       连接的用户实际上拥有所有权限。

d)       此选项会禁用帐户管理语句，如 CREATE USER、 GRANT、 REVOKE 和 SET PASSWORD。

l  阻止客户机连接：

– 使用--skip-networking 选项可阻止网络访问，并且仅允许在本地套接字、命名管道或共享内存上访问。

– 使用--socket 选项可在非标准套接字上启动服务器以防止本地应用程序或用户随便访问。

如果您忘了 root 口令，需要将其重置，则禁用访问控制会很方便，因为任何用户都可使用完全权限连接，无需提供口令。显然，这是很危险的。要阻止远程客户机通过 TCP/IP进行连接，可使用 --skip-networking 选项。这样，客户机只能使用 UNIX 上的套接字文件或者 Windows 上的命名管道或共享内存从 localhost 连接。要避免来自本地主机的随意连接，可在命令提示符下使用非标准套接字名称。

帐户管理语句需要授权表的内存中副本；在禁用访问控制时，这些副本不可用。要更改权限或设置口令，可直接修改授权表。或者，在连接到服务器之后发出一条 FLUSH PRIVILEGES 语句，这将使服务器读取表，并且还会启用帐户管理语句。

1.7.        资源限制

通过将全局变量MAX_USER_CONNECTIONS 设置为非零值，限制使用服务器资源。这将限制任何一个帐户的同时连接数量，但不会限制客户机在连接后能执行的操作。

• 限制单个帐户的以下服务器资源：

– MAX_QUERIES_PER_HOUR：一个帐户每小时可发出的查询数量

– MAX_UPDATES_PER_HOUR：一个帐户每小时可发出的更新数量

– MAX_CONNECTIONS_PER_HOUR：一个帐户每小时可连接到服务器的次数

– MAX_USER_CONNECTIONS：允许的同时连接数量

       要设置帐户的资源限制，可使用 GRANT 语句以及指定要限制的每个资源的 WITH 子句。每个限制的默认值是零，表示没有限制。例如，要限制用户 francis 访问客户数据库，可发出以下语句：

mysql> GRANT ALL ON customer.* TO 'francis'@'localhost'

-> WITH MAX_QUERIES_PER_HOUR 20

-> MAX_UPDATES_PER_HOUR 10

-> MAX_CONNECTIONS_PER_HOUR 5

-> MAX_USER_CONNECTIONS 2;

按任意顺序在 WITH 子句中提供资源限制。将 MAX_USER_CONNECTIONS 限制设置为 0可将其设置为全局默认值，表示此帐户允许的最大同时连接数是max_user_connections 系统变量的全局值。

要将任何每小时资源的现有限制设置为默认的“无限制” ，可指定值 0，如以下示例中所示：

mysql> GRANT USAGE ON *.* TO 'quinn'@'localhost'

-> WITH MAX_CONNECTIONS_PER_HOUR 0;

1.8.        MySQL权限实战

a.查看当前用户的权限：

mysql> show grants;

b.查看某个用户的权限：

mysql> show grants for 'jack'@'%';

mysql>show grants for current_user();

c.回收权限

mysql> revoke delete on *.* from 'jack'@'localhost';

d.删除用户

mysql> drop user 'jack'@'localhost';

e.对账户重命名

mysql> rename user 'jack'@'%' to 'jim'@'%';

f.修改密码

i)用set password命令

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');

     ii)用mysqladmin

  [root@rhel5 ~]# mysqladmin -uroot -p123456 password 1234abcd

  备注：

  格式：mysqladmin -u用户名 -p旧密码 password 新密码

iii)用update直接编辑user表

  mysql> use mysql

  mysql> update user set PASSWORD = PASSWORD('1234abcd') where user = 'root';

  mysql> flush privileges;

       iv)在丢失root密码的时候

  [root@rhel5 ~]# mysqld_safe --skip-grant-tables &

  [root@rhel5 ~]# mysql -u root

mysql> update user set password = PASSWORD('123456') where user = 'root';

mysql> flush privileges;

以上是“MySQL中用户与权限的示例分析”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注亿速云行业资讯频道！