MySQL 用户管理（十二）

MySQL 用户管理

• MySQL 是一个多用户数据库，可以为不同用户指定允许的权限。

权限表

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

user 表

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

1. 用户列

   user 表的用户列包括 Host、User、authentication_string，分别表示主机名、用户名和密码。其中，User 和 Host 为 User 表的联合主键。

2. 权限列

   权限列的字段决定了用户的权限，描述了在全局范围内允许对数据和数据库进行的操作。包括查询权限、修改权限等普通权限，还包括了关闭服务器、超级权限和加载用户等高级权限。普通权限用于操作数据库；高级权限用于数据库管理。如果要修改权限，可以使用 GRANT 语句或 UPDATE 语句更改 user 表的这些字段来修改用户对应的权限。

3. 安全列

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

4. 资源控制列

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

   （1）max_questions——用户每小时允许执行的查询操作次数。

   （2）max_updates——用户每小时允许执行的更新操作次数。

   （3）max_connections——用户每小时允许执行的连接操作次数。

   （4）max_user_connections——用户允许同时建立的连接次数。

   一个小时内用户查询或者连接数量超过资源控制权限，用户将被锁定，直到下一个小时才可以在此执行对应的操作。

db 表和 host 表

• Db 表中存储了用户对某个数据库的操作权限，决定用户能从哪个主机存取哪个数据库。Host 表中存储了某个主机对数据库的操作权限。这些权限表不受 GRANT 和 REVOKE 语句的影响。

1. 用户列

   db 表用户列有 3 个字段，分别是 Host、User、Db，标识从某个主机连接某个用户对某个数据库的操作权限，这 3 个字段的组合构成了 db 表的主键。host 表不存储用户名称，用户列只有 2 个字段，分别是 Host 和 Db，表示从某个主机连接的用户对某个数据库的操作权限，其主键包括 Host 和 Db 两个字段。一般情况下 db 表就可以满足权限控制需求了。

2. 权限列

   权限列决定用户操作的权限。当有用户连接到 MySQL 服务器时，db 表中没有用户登录的主机名称，则 MySQL 会从 host 表中查找相匹配的值，并根据查询的结果决定用户的操作是否被允许。

tables_priv 表和 columns_priv 表

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

procs_priv 表

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

账户管理

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

登录和退出 MySQL 服务器

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

  （1）-h 主机名，可以使用该参数指定主机名或 ip，如果不指定，默认是 localhost。

  （2）-u 用户名，可以使用该参数指定用户名。

  （3）-p 密码，可以使用该参数指定登录密码。如果该参数后面有一段字段，则该段字符串将作为用户的密码直接登录。如果后面没有内容，在登录的时候就会提示输入密码。注意：该参数后面的字符串和-p 之间不能有空格。

  （4）-P 端口号，该参数后面接 MySQL 服务器的端口号，默认为 3306。

  （5）数据库名，可以在命令的最后指定数据库名。

  （6）-e 执行 SQL 语句。如果指定了该参数，就将在登录后执行-e 后面的命令或 SQL 语句并退出。

新建普通用户

• 在 MySQL 数据库中，有两种方式创建新用户：一种是使用 CREATE USER 或 GRANT 语句；另一种是直接操作 MySQL 授权表。

1. 使用 CREATE USER 语句创建新用户

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

     CREATE USER user_specification
     	[,user_specification] ...
     
     user_specification:
     	user@host
     	[
             IDENTIFIED BY [PASSWORD] 'password'
           | IDENTIFIED WITH auth_plugin [AS 'auth_string']
         ]
     

     IDENTIFIED BY 表示用来设置用户的密码；

     [PASSWORD] 表示使用哈希值设置密码，该参数可选；

     'password'表示用户登录时使用的普通明文密码；

     IDENTIFIED WITH 语句为用户指定一个身份验证插件；

     auth_plugin 是插件的名称，可以是一个带单引号的字符串，或者带引号的字符串，或者带引号的字符串；

     auth_string 是可选的字符串参数，传递给身份验证插件，由插件解释该参数的意义。

     注意：user 和 host 都为字符串形式，需要加引号。

   • 使用 CREATE USER 语句的用户必须有全局的 CREATE USER 权限或 MySQL 数据库的 INSERT 权限。新添加的用户没有任何权限。

   • 如果在创建用户时未指明主机名，则主机名部分默认为 '%' （对所有的主机开放权限）。

   • 如果用户登录不需要密码，可以省略 IDENTIFIED BY 部分。

   • 对于使用插件认证连接的用户，服务器调用指定名称的插件。如果创建用户时或者连接服务器时，服务器找不到对应的插件，将返回一个错误。

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

2. 使用 GRANT 语句创建用户

   • CREATE USER 语句创建的新用户没有任何权限，还需要使用 GRANT 语句赋予用户权限。而 GRANT 语句不仅可以创建新用户，还可以在创建的同时对用户授权。

   • GRANT 还可以指定用户的其他特点，如使用安全连接、限制使用服务器资源等。

   • 使用 GRANT 语句创建新用户时必须有 GRANT 权限，GRANT 语句的基本语法格式如下：

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

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

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

   • 注意：User 表中的 user 和 host 字段区分大小写，在查询的时候要指定正确的用户名称或者主机名。

3. 直接操作 MySQL 用户表

   • 可以使用 INSERT 语句向 user 表直接插入一条记录来创建一个新的用户。使用 INSERT 语句，必须拥有对 MySQL.user 表的 INSERT 权限。使用 INSERT 语句创建新用户的基本语法格式如下：

     INSERT INTO MySQL.user(Host,User,authentication_string,[privilegelist])
     VALUES('host','username',PASSWORD('passwod'),privilegevaluelist);
     

     privilegevaluelist 为对应的权限的值，只能取 'Y' 或者 'N' 。

删除普通用户

• 在 MySQL 数据库中，可以使用 DROP USER 语句删除用户，也可以直接通过 DELETE 从 MySQL.user 表中删除对应的记录来删除用户。

1. 使用 DROP USER 语句删除用户

   • DROP USER 语句语法如下：

     DROP USER user [,user];
     

   • 要使用 DROP USER，必须拥有 MySQL 数据库的全局 CREATE USER 权限或 DELETE 权限。删除用户时可以指定主机名以防出错，例如 'user'@'localhost'。

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

2. 使用 DELETE 语句删除用户

   DELETE 语句基本语法格式如下：

   DELETE FROM MySQL.user WHERE host = 'hostname' AND user = 'username';
   

修改用户的密码

1. 使用 mysqladmin 命令在命令行指定新密码

   mysqladmin 命令的基本语法格式如下：

   mysqladmin -u username -h hostname -p password "newpwd"
   

   注意：新密码要用双引号。

2. 修改 MySQL 数据库的 user 表

   用户登录到 MySQL 服务器后，使用 UPDATE 语句修改 MySQL 数据库的 user 表的 authentication_string 字段，从而修改用户的密码。使用 UPDATE 语句修改用户密码的语句如下：

   UPDATE MySQL.user SET authentication_string = PASSWORD("rootpwd") WHERE user = "username" AND host = "hostname";
   

   密码修改成功之后，使用 FLUSH PRIVILEGES 语句重新加载权限。

3. 使用 SET 语句修改用户的密码

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

   SET PASSWORD [FOR 'username'@'hostname'] = PASSWORD("newpwd");
   

4. 使用 GRANT 语句修改用户密码

   可以在全局级别使用 GRANT USAGE 语句（* . *）指定某个账户的密码而不影响账户当前的权限，使用 GRANT 语句修改密码，必须拥有 GRANT 权限。GRANT USAGE 的基本语句格式如下：

   GRANT USAGE ON *.* TO 'username@hostname'
   IDENTIFIED BY 'passwd';
   

   使用 GRANT...IDENTIFIED BY 语句或 mysqladmin password 命令设置密码，它们均会加密密码。在这种情况下，不需要使用 PASSWORD()。

root 用户密码丢失的解决方法

• 对于 root 用户密码丢失这种特殊情况，MySQL 实现了对应的处理机制。可以通过特殊方法登录到 MySQL 服务器，然后在 root 用户下重新设置密码。

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

   以 skip-grant-tables 选项启动时，MySQL 服务器将不加载权限判断，任何用户都能访问数据库。在 Windows 操作系统中，可以使用 mysqld 或 mysqld-nt 来启动 MySQL 服务进程。若 MySQL 目录为添加到环境变量中，则需要先在命令行下切换到 MySQL 的 bin 目录。

   mysqld 命令如下：

   mysqld --skip-grant-tables
   

   mysqld-nt 命令如下：

   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 命令如下：

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

   启动 mysqld 服务前需先停止当前 MySQL 服务进程，命令如下：

   NET STOP MySQL
   

   注意：命令执行之后，用户无法输入指令，需要打开另外一个命令行窗口，输入不加密码的登录指令

   mysql -u root
   

权限管理

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

授权

• MySQL 中可以使用 GRANT 语句为用户授予权限。授予的权限可以分为多个层级。

1. 全局层级

   全局权限适用于一个给定服务器中的所有数据库。这些权限存储在 mysql.user 表中。GRANT ALL ON * . * 和 REVOKE ALL * . * 只授予和撤销全局权限。

2. 数据库层级

   数据库权限适用于一个给定数据库中的所有目标。这些权限存储在 mysql.db 和 mysql.host 表中。GRANT ALL ON db_name.* 和 REVOKE ALL ON db_name.* 只授予和撤销数据库权限。

3. 表层级

   表权限适用于一个给定表中的所有列。这些权限存储在 mysql.tables_priv 表中。GRANT ALL ON db.name.tb1_name 和 REVOKE ALL ON db_name.tb1.name 只授予和撤销表的权限。

4. 列层级

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

5. 子程序层级

   CREATE ROUTINE、ALTER ROUTINE、EXECUTE 和 GRANT 权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。除了 CREATE ROUTINE 外，这些权限可以被授予子程序层级，并存储在 mysql.procs_priv 表中。

• 要使用 GRANT 或 REVOKE，必须拥有 GRANT OPTION 权限，并且必须用于正在授予或撤销的权限。GRANT 的语法如下：

  GRANT priv_type [(columns)] [,priv_type [(columns)] ] ....
  ON [object_type] table1,table2,...tablen
  TO user [IDENTIFIED BY [password] 'password']
  [,user [IDENTIFIED BY [password] 'password'] ]...
  	[WITH GRANT OPTION]
  
  object_type = TABLE | FUNCTION | PROCEDURE
  

  object_type 指定授权作用的对象类型包括 TABLE（表）、FUNCTION（函数）和 PROCEDURE（存储过程）；

  user 参数表示用户账户，由用户名和主机名构成。

  WITH 关键字后可以跟一个或多个 with_option 参数。这个参数有 5 个权限，意义如下：

  1. GRANT OPTION：被授权的用户可以将这些权限授予别的用户。
  2. MAX_QUERIES_PER_HOUR count：设置每小时可以执行 count 次查询。
  3. MAX_UPDATES_PER_HOUR count：设置每小时可以执行 count 次更新。
  4. MAX_CONNECTIONS_PER_HOUR count：设置每小时可以建立 count 个连接。
  5. MAX_USER_CONNECTIONS count：设置单个用户可以同时建立 count 个连接。

收回权限

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

1. 收回所有用户的所有权限，用于取消对于已命名的用户的所有全局层级、数据库层级、表层级和列层级的权限，其语法如下：

   REVOKE ALL PRIVILEGES,GRANT OPTION
   FROM 'user'@'host' [,'user'@'host'...];
   

   REVOKE 语句必须和 FROM 语句一起使用，FROM 语句指明需要收回权限的用户。

2. 长格式的 REVOKE 语句，基本语法如下：

   REVOKE priv_type [(columns)] [,priv_type [(column)] ]...
   ON table1,table2,...tablen
   FROM 'user'@'host' [,'user'@'host'...]
   

   要使用 REVOKE 语句，必须拥有 MySQL 数据库的全局 CREATE USER 权限或 UPDATE 权限。

• 当从旧版本的 MySQL 升级时，如果要使用 EXECUTE、CREATE VIEW、SHOW VIEW、CREATE USER、CERATE ROUTINE 和 ALTER ROUTINE 权限，必须首先升级授权表。

查看权限

• SHOW GRANTS 语句可以显示指定用户的权限信息。基本语法格式如下：

  SHOW GRANTS FOR 'user'@'host';
  

• 通过 user 表查看指定用户的权限信息，基本语法格式如下：

  SELECT privileges_list FROM MySQL.user WHERE user = 'username' AND host = 'hostname';
  

访问控制

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

连接核实阶段

• 当连接 MySQL 服务器时，服务器基于用户的身份以及用户是否能通过正确的密码身份验证来接受或拒绝连接。客户端连接请求中会提供用户名称、主机地址名和密码，MySQL 使用 user 表中的 3 个字段（Host、User 和 Password）执行身份检查。

请求核实阶段

• MySQL 通过向下层级的顺序检查权限表（从 user 表到 columns_priv 表），检查用户是否有足够的权限执行指定的操作，这些权限可以来自 user、db、host、tables_priv 或 columns_priv 表。

注意事项

• 数据库安全需要注意 user 数据表中是否存在匿名用户。在 user 表中匿名用户的 User 字段值为空字符串，这会允许任何人连接到数据库，检测是否存在匿名登录用户的方法的 SQL 语句如下：

  SELECT * FROM MySQL.user WHERE User = '';
  

• 创建用户的方法有：GRANT 语句、CREATE USER 语句和直接操作 user 表。一般情况下，最好使用 GRANT 或者 CREATE USER 语句，而不要直接将用户信息插入 user 表，因为 user 表存储了全局级别的权限以及其他的账户信息，如果意外破坏了 user 表中的记录，则会对 MySQL 服务器造成很大的影响。