10. MySQL 用户管理

• 问题背景
  MySQL 是一个多用户数据库，具有功能强大的访问控制系统，可以为不同用户指定不同权限。在前面的章节中我们使用的 root 用户是超级管理员，拥有所有权限，包括创建用户、删除用户和修改用户密码等管理权限。而为了实际项目的需要，可以创建拥有不同权限的普通用户。

【 1. 权限表 】

• MySQL 在安装时会自动创建一个名为 mysql 的数据库，mysql 数据库中存储的都是 用户权限表 ：用户登录以后，MySQL 会根据这些权限表的内容为每个用户赋予相应的权限。

1.1 user 权限表

• user 表 是 MySQL 中最重要的一个权限表，用来 记录允许连接到服务器的账号信息，在 user 表里启用的所有权限都是全局级的，适用于所有数据库 。
• 显示 user 权限表

DESC mysql.user;

1.1.1 用户列

• 用户列 存储了用户连接 MySQL 数据库时需要输入的信息。
  MySQL 5.7 版本不再使用 Password 来作为密码的字段，而改成了 authentication_string。
• user 表的用户列
  用户登录时，如果这 3 个字段同时匹配，MySQL 数据库系统才会允许其登录。创建新用户时，也是设置这 3 个字段的值；修改用户密码时，实际就是修改 user 表的 authentication_string 字段的值。

1.1.2 权限列

• 权限列 的字段决定了用户的权限，用来描述在全局范围内允许对数据和数据库进行的操作。
• 权限大致分为两大类，分别是高级管理权限和普通权限：
  • 高级管理权限 主要对数据库进行管理。
    例如关闭服务的权限、超级权限和加载用户等；
  • 普通权限 主要操作数据库。
    例如查询权限、修改权限等。
• user表的权限列
  user 表的权限列包括 Select_priv、Insert_ priv 等以 priv 结尾的字段，这些字段值的数据类型为 ENUM，可取的值只有 Y 和 N：Y 表示该用户有对应的权限，N 表示该用户没有对应的权限。从安全角度考虑，这些字段的默认值都为 N。

1.1.3 安全列

• 安全列 主要用来 判断用户是否能够登录成功。

• 即使 password_expired 为“Y”即用户密码过期，用户也可以使用密码登录 MySQL，但是不允许做任何操作。

• 通常标准的发行版不支持 ssl，读者可以使用 SHOW VARIABLES LIKE “have_openssl” 语句来查看是否具有 ssl 功能。如果 have_openssl 的值为 DISABLED，那么则不支持 ssl 加密功能。

• user 表中的安全列

1.1.4 资源控制列

• 资源控制列 的字段用来 限制用户使用的资源。
• user 表中的资源控制列。
  • 以下字段的默认值为 0，表示没有限制。
  • 一个小时内用户查询或者连接数量超过资源控制限制，用户将被锁定，直到下一个小时才可以在此执行对应的操作。
  • 可以使用 GRANT 语句更新这些字段的值。

1.2 db 表

• db 表 比较常用，是 MySQL 数据库中非常重要的权限表，表中 存储了用户对某个数据库的操作权限。表中的字段大致可以分为两类，分别是用户列和权限列。

用户列

• db 表用户列有 3 个字段，分别是 Host、User、Db，标识 从某个主机连接某个用户对某个数据库的操作权限，这 3 个字段的组合构成了 db 表的主键。
• db 用户列

权限列

• db 表中的权限列和 user 表中的权限列大致相同，只是user 表中的权限是针对所有数据库的，而 db 表中的权限只针对指定的数据库，如果希望用户只对某个数据库有操作权限，可以先将 user 表中对应的权限设置为 N，然后在 db 表中设置对应数据库的操作权限。

1.3 tables_priv 表

• tables_priv 表 用来 对单个表进行权限设置。
• tables_priv 表

1.4 columns_priv 表

• columns_priv 表 用来 对单个数据列进行权限设置。
• tables_priv 表

1.5 procs_priv表

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

【 2. 用户管理 】

2.1 创建用户 CREATE USER

• 问题背景
  MySQL 在安装时，会默认创建一个名为 root 的用户，该用户 拥有超级权限，可以控制整个 MySQL 服务器。
  在对 MySQL 的日常管理和操作中，为了避免有人恶意使用 root 用户控制数据库，我们通常创建一些具有适当权限的用户，尽可能地不用或少用 root 用户登录系统，以此来确保数据的安全访问。
• 使用 CREATE USER 语句来创建 MySQL 用户，并设置相应的密码。
  基本语法：
  • 用户
    指定创建用户账号，格式为 user_name’@'host_name。这里的user_name是用户名，host_name为主机名，即用户连接 MySQL 时所用主机的名字。
    如果在创建的过程中，只给出了用户名，而没指定主机名，那么主机名默认为“%”，表示一组主机，即 不指定主机名则默认对所有主机开放权限。
  • IDENTIFIED BY ‘password’
    用于指定用户密码。新用户可以没有初始密码，若该用户不设密码，可省略此子句，但是从安全的角度来说，不推荐这种做法。
    • ‘password’ 表示用户登录时使用的密码，需要用单引号括起来。
  • 使用 CREATE USER 语句 必须拥有 mysql 数据库的 INSERT 权限或全局 CREATE USER 权限。
  • 使用 CREATE USER 语句创建一个用户后，MySQL 会在 mysql 数据库的 user 表中添加一条新记录。
  • CREATE USER 语句可以同时创建多个用户，多个用户用逗号隔开。

CREATE USER <用户> 
[ IDENTIFIED BY 'password' ] 
[ ,用户 [ IDENTIFIED BY 'password' ]]

• 新创建的用户拥有的权限很少，它们只能执行不需要权限的操作。如登录 MySQL、使用 SHOW 语句查询所有存储引擎和字符集的列表等。
• 如果两个用户的用户名相同，但主机名不同，MySQL 会将它们视为两个用户，并允许为这两个用户分配不同的权限集合。
• 实例
  使用 CREATE USER 创建一个用户，用户名是 test1，密码是 test1，主机名是 localhost。
  CREATE USER 'test1'@'localhost' IDENTIFIED BY 'test1';
  

2.2 用户的登陆、退出

登陆 MySQL

• 启动 MySQL 服务后，可以使用以下命令来登录。
  • -h：指定连接 MySQL 服务器的地址。可以用 hostname 或 hostIP 表示，
    • hostname 为主机名，
    • hostIP 为主机 IP 地址。
  • -p：指定连接 MySQL 服务器的端口号。
    • port 为连接的端口号。MySQL 的默认端口号是 3306，因此如果不指定该参数，默认使用 3306 连接 MySQL 服务器。
  • u：指定连接 MySQL 服务器的用户名，
    • username ：为用户名。
  • -p：提示输入密码，即提示 Enter password。
    也可以直接在 mysql 命令的 -p 后加上登录密码，登录密码与 -p 之间没有空格，此时的命令执行后，后面不会提示输入密码。
  • DatabaseName：指定连接到 MySQL 服务器后，登录到哪一个数据库中。如果没有指定，默认为 mysql 数据库。
  • -e：指定需要执行的 SQL 语句，登录 MySQL 服务器后执行这个 SQL 语句，然后退出 MySQL 服务器。

mysql -h hostname|hostlP [-p port] -u username -p DatabaseName  -e "SQL语句"

• CMD 中 登陆 root 用户：mysql -u root -p
• 实例
  通过值 localhost 指定 MySQL 服务器的地址，参数 -u 指定了登录 MySQL 服务器的用户账户，参数 -p 表示会出现输入密码提示信息，最后值“mytest”指定了登录成功后要使用的数据库。
  mysql -h localhost -u root -p test
  
• 实例2
  使用 root 用户登录到自己计算机的 mysql 数据库，同时查询 tb_tudent表的表结构。
  mysql -h localhost -u root -p mytest -e"DESC tb_tudent"
  

退出 MySQL

• 退出 MySQL 服务器的方式很简单，只要在命令行输入 EXIT 或 QUIT 即可。“\q”是 QUIT 的缩写，也可以用来退出 MySQL 服务器。

QUIT;

#或者
\q;

2.3 重命名用户 RENAME USER

• 基本语法
  • <旧用户>：系统中已经存在的 MySQL 用户账号。
  • <新用户>：新的 MySQL 用户账号。
  • 使用 RENAME USER 语句，必须拥有 mysql 数据库的 UPDATE 权限或全局 CREATE USER 权限。
  • 若系统中旧账户不存在或者新账户已存在，该语句执行时会出现错误。

RENAME USER <旧用户> TO <新用户>

• 实例
  1. 使用 RENAME USER 语句将用户名 test1 修改为 testUser1，主机是 localhost。
     RENAME USER 'test1'@'localhost' TO 'testUser1'@'localhost';
     
  2. 在 cmd 命令行工具中，使用 testUser1 用户登录数据库服务器。
     mysql -h localhost -u testUser1 -p
     

2.4 删除用户 DROP/DELETE USER

DROP USER 删除普通用户

• 用户的删除不会影响他们之前所创建的表、索引或其他数据库对象，因为 MySQL 并不会记录是谁创建了这些对象。
  基本语法：
  • 用户 用来指定需要删除的用户账号。
  • DROP USER 语句可用于删除一个或多个用户，并撤销其权限。
  • 使用 DROP USER 语句 必须拥有 mysql 数据库的 DELETE 权限或全局 CREATE USER 权限。
  • 在 DROP USER 语句的使用中，若没有明确地给出账户的主机名，则该主机名默认为“%”。

DROP USER <用户1> [ , <用户2> ]…

• 实例1
  1. 使用 DROP USER 语句删除用户’testUser1@‘localhost’。
     DROP USER 'testUser1'@'localhost';
     
  2. 在 cmd 命令行工具中，使用 test1 用户登录数据库服务器，发现登录失败，说明用户已经删除。
     mysql -h localhost -u test1 -p
     

DELETE 删除普通用户

• 基本语法
  • 可以使用 DELETE 语句直接删除 mysql.user 表中相应的用户信息，但必须拥有 mysql.user 表的 DELETE 权限
  • Host 和 User 这两个字段都是 mysql.user 表的主键。因此，需要两个字段的值才能确定一条记录。

DELETE FROM mysql.user WHERE Host='hostname' AND User='username';

2.5 查看用户权限

• mysql 数据库下的 user 表中存储着用户的基本权限，而新创建的用户只有登录 MySQL 服务器的权限，没有任何其它权限，不能查询 user 表。
• 使用 SELECT 语句查询用户的权限。
  基本语法：
  • 要执行该语句，必须拥有对 user 表的查询权限。

SELECT * FROM mysql.user;

• 也可以使用 SHOW GRANTS 语句查询用户的权限。
  基本语法：
  • username 表示用户名。
  • hostname 表示主机名或主机IP。

SHOW GRANTS FOR 'username'@'hostname';

• 实例1
  1.创建 testuser1 用户。
  CREATE USER 'testuser1'@'localhost';
  2.查询权限。
  USAGE ON *.*表示该用户对任何数据库和任何表都没有权限。
  SHOW GRANTS FOR 'testuser1'@'localhost';
  
• 实例2：
  查询 root 用户的权限
  SHOW GRANTS FOR 'root'@'localhost';
  

2.6 用户授权 GRANT

• 授权 就是为某个用户赋予某些权限。
  例如，可以为新建的用户赋予查询所有数据库和表的权限。
• 为了安全起见，最好不要授予普通用户 SUPER 权限、GRANT 权限。
• 基本语法
  • priv_type 参数表示权限类型；
  • columns_list 参数表示权限作用于哪些列上，省略该参数时，表示作用于整个表；
  • database.table 用于指定权限的级别，值有：
    • *：表示当前数据库中的所有表。
    • .：表示所有数据库中的所有表。
    • db_name.*：表示某个数据库中的所有表，db_name 指定数据库名。
    • db_name.tbl_name：表示某个数据库中的某个表或视图， - db_name 指定数据库名，tbl_name 指定表名或视图名。
    • db_name.routine_name：表示某个数据库中的某个存储过程或函数，routine_name 指定存储过程名或函数名。
    • TO 子句：如果权限被授予给一个不存在的用户，MySQL 会自动执行一条 CREATE USER 语句来创建这个用户，但同时必须为该用户设置密码。
  • user 参数表示用户账户，由用户名和主机名构成，格式是“‘username’@‘hostname’”；
  • IDENTIFIED BY 参数用来为用户设置密码；
  • password 参数是用户的新密码。
  • WITH 关键字后面带有一个或多个 with_option 参数。这个参数有 5 个选项，详细介绍如下：
    • GRANT OPTION：被授权的用户可以将这些权限赋予给别的用户；
    • MAX_QUERIES_PER_HOUR count：设置每个小时可以允许执行 count 次查询；
    • MAX_UPDATES_PER_HOUR count：设置每个小时可以允许执行 count 次更新；
    • MAX_CONNECTIONS_PER_HOUR count：设置每小时可以建立 count 个连接;
    • MAX_USER_CONNECTIONS count：设置单个用户可以同时具有的 count 个连接。

GRANT priv_type [(column_list)] ON database.table
TO user 
[, user] ...
[WITH with_option [with_option]...]

• MySQL 中可以授予的权限

2.6.1 数据库的权限类型

• 授予数据库权限时，<权限类型>可以指定为以下值：

2.6.2 表的权限类型

• 授予表权限时，<权限类型>可以指定为以下值：

2.6.3 列的权限类型

• 授予列权限时，<权限类型>的值只能指定为 SELECT、INSERT 和 UPDATE，同时权限的后面需要加上列名列表 column-list。

2.6.4 用户的权限类型

• 最有效率的权限是用户权限。授予用户权限时，<权限类型>除了可以指定为授予数据库权限时的所有值之外，还可以是下面这些值：
  • CREATE USER：表示授予用户可以创建和删除新用户的权限。
  • SHOW DATABASES：表示授予用户可以使用 SHOW DATABASES 语句查看所有已有的数据库的定义的权限。
• 实例
  1. 用户 testuser1 对所有的数据有查询、插入权限，并授予 GRANT 权限。
     GRANT SELECT,INSERT ON *.* TO 'testuser1'@'localhost' WITH GRANT OPTION;
  2. 查询用户 testuser1 的权限。
     SHOW GRANTS FOR 'testuser1'@'localhost';
     

2.6 删除用户权限 REVOKE

• 问题背景
  在 MySQL 中，可以使用 REVOKE 语句删除某个用户的某些权限（此用户不会被删除），在一定程度上可以保证系统的安全性。
  例如，如果数据库管理员觉得某个用户不应该拥有 DELETE 权限，那么就可以删除 DELETE 权限。
• 要使用 REVOKE 语句，必须拥有 MySQL 数据库的全局 CREATE USER 权限或 UPDATE 权限。

删除用户某些特定的权限

• 基本语法
  • priv_type 参数表示权限的类型；
  • column_list 参数可选，表示权限作用于哪些列上，没有该参数时作用于整个表上；
  • user 参数由用户名和主机名构成，格式为“username’@‘hostname’”。

REVOKE priv_type [(column_list)]...
ON database.table
FROM user [, user]...

• 实例1
  1. 使用 REVOKE 语句取消用户 testuser1 的插入权限
     REVOKE INSERT ON *.* FROM 'testuser1 '@'localhost';
  2. 查看 testuser1 的权限
     结果显示， testuser1 用户没有了INSERT权限，删除 testuser1 用户的 INSERT 权限成功。
     SHOW GRANTS FOR 'testuser1 '@'localhost';
     

删除特定用户的所有权限

• 基本语法

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

2.8 修改用户密码

修改普通用户密码

• 在 MySQL 中，只有 root 用户可以通过更新 MySQL 数据库来更改密码。使用 root 用户登录到 MySQL 服务器后，可以修改普通用户密码。
  基本语法：
  • username 参数是普通用户的用户名，
  • hostname 参数是普通用户的主机名，
  • newpwd 是要更改的新密码。

ALTER USER 'username'@'localhost' IDENTIFIED BY 'newpwd';
# 或
SET PASSWORD FOR 'username'@'localhost' = 'newpwd';

• 实例
  1. 创建一个没有密码的 testuser 用户
     CREATE USER 'testuser'@'localhost';
  2. root 用户登录 MySQL 服务器后，再使用 SET 语句将 testuser 用户的密码修改为“newpwd”，
     ALTER USER 'testuser'@'localhost' IDENTIFIED BY 'newpwd';
     
  3. 退出 MySQL 服务器，使用 testuser 用户登录，输入密码“newpwd”
     由运行结果可以看出，testuser 用户登录成功，修改密码成功。
     mysql -utestuser -p
     

修改root用户密码

• mysqladmin 命令可在Windows 命令行窗口（cmd）中修改root密码。
  基本语法：
  • usermame 指需要修改密码的用户名称，在这里指定为 root 用户；
  • hostname 指需要修改密码的用户主机名，该参数可以不写，默认是 localhost；
  • password 为关键字，而不是指旧密码；
  • newpwd 为新设置的密码，必须用双引号括起来。如果使用单引号会引发错误，可能会造成修改后的密码不是你想要的。

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

• SET PASSWORD 命令可以用来重新设置其他用户的登录密码或者自己使用的账户的密码。
  • NEWPWD当前账户的新密码。

SET PASSWORD = "NEWPWD";

• 实例1：mysqladmin 命令修改用户密码。
  1. 使用 mysqladmin 将 root 用户的密码修改为“rootpwd”
     运行结果中，输入密码后会提示在命令行界面上使用密码可能不安全的警告信息，因为在命令行输入密码时，MySQL 服务器就会提示这些安全警告信息。
     mysqladmin -u root -p password "rootpwd"
  2. 用修改后的“rootpwd”密码登录 root 用户。
     mysql -u root -p
     
• 实例2：SET PASSWORD命令修改用户密码。
  使用 root 用户登录到 MySQL 服务器后，更改root密码。
  SET PASSWORD = "rootpwd3";