【MySQL】——用户和权限管理（一）

💻博主现有专栏：

C51单片机（STC89C516），c语言，c++，离散数学，算法设计与分析，数据结构，Python，Java基础，MySQL，linux，基于HTML5的网页设计及应用，Rust（官方文档重点总结），jQuery，前端vue.js，Javaweb开发，Python机器学习等
🥏主页链接：

Y小夜-CSDN博客

🎯用户与权限

🎃user表

🎃bd表

🎃 tables_priv表和columns_priv表

🎃 procs_priv表

🎃访问控制过程

🎯用户管理

🎃添加普通用户

🎃普通用户修改自己的密码

🎃root用户修改自己的密码和普通用户的密码

🎃修改用户名

🎃删除普通用户

🎯 MySQL 8 中root用户密码丢失的解决办法

🎯用户与权限

        数据库的安全性是指只允许合法用户进行其权限范围内的数据库相关操作，保护数据库以防止任何不合法的使用所造成的数据泄露、更改或破坏。

        MySQL提供了用户认证、授权等来实现和维护数据的安全，以避免用户恶意攻击或者越权访问数据库中的数据对象，并能根据不同用户分配在数据库中的权限。也就是说数据库安全性措施主要涉及用户认证和访问权限两个方面的问题。

        MySQL用户主要包括系统用户和普通用户。

root用户是系统管理员，拥有操作MySQL数据库的所有权限。
普通用户只拥有创建该用户时赋予它的权限。

        当MySQL服务启动时，会读取MySQL中的权限表，并将表中的数据加载到内存，当用户进行数据库访问操作时，MySQL会根据权限表中的内容对用户做相应的权限控制。

🎃user表

MySQL中的所有用户信息都保存在user表中。user表是mysql 数据库中最重要的一个表。它记录了允许连接到服务器的账号信息及一些全局级的权限信息。user表账号字段确定是拒绝还是允许传入连接。对于允许的连接，用户表中授予的任何权限都表示用户的全局权限。此表中授予的任何权限都适用于服务器上的所有数据库。

MySQL 8.0.17中user表有51个字段，这些字段共分为4类，分别是账号字段、权限字段、安全字段和资源控制字段。可以通过“desc user;”命令来查看user表的结构

🎃bd表

db表账号字段（Host、DB、User）三个字段组合成复合主键，来确定哪些用户可以从哪些主机访问哪些数据库。剩下的权限字段决定允许的操作。在数据库级别授予的权限适用于数据库和数据库中的所有对象，如表和存储程序。

输入命令desc db;可以查看db表的结构。

🎃 tables_priv表和columns_priv表

        tables_priv和columns_priv表，与db表类似，但具有更小的粒度：它们应用于表和列级别，而不是数据库级别。在表级别授予的权限适用于表及其所有列。在列级别授予的权限仅适用于特定列。

        tables_priv表可以对单个表进行权限设置，tables_priv表包含8个字段，前4个字段Host、Db、User、Table_name分别表示主机名、数据库名、用户名和表名。后4个字段Grantor、Timestamp 、Table_priv和Column_priv。分别表示权限是谁设置的、修改权限的时间、对表进行操作的权限和对列的操作权限。

        columns_priv表可以对单个数据列进行权限设置，包含7个字段。前5个字段Host、Db、User、Table_name和Column_name分别表示主机名、数据库名、用户名、表名和列名。Timestamp和Column_priv表示修改权限的时间和对表中的数据列进行操作的权限。

🎃 procs_priv表

        procs_priv表适用于存储例程（存储过程和函数）。在例程级别授予的权限仅适用于单个过程或函数。

        输入命令：desc procs_priv;查看procs_pric表的结构。

        procs_priv表包含8个字段，分别是Host、Db、User、Routine_name、Routine_type、 Grantor 、Proc_priv 和Timestamp 。分别表示主机名、数据库名、用户名、例程的名称、例程的类型、存储权限是谁设置的、拥有的权限和更新的时间。

🎃访问控制过程

当客户端连接到MySQL服务器时，它会经历两个访问控制阶段：连接验证阶段和请求验证阶段。

1. 连接验证阶段

当用户尝试连接到MySQL服务器时，服务器会根据以下条件接受或拒绝连接：

（1）用户的身份以及是否可以通过提供正确的密码来验证其身份。

（2）用户的账号是锁定还是解锁。

        服务器首先检查凭证，然后检查账号锁定状态。任何一个步骤的失败都会导致服务器完全拒绝对用户的访问。否则，服务器接受连接，然后进入第2阶段并等待请求。

        使用三个执行凭证检查user表账号字段（Host，User和 authentication_string）。锁定状态记录在user表 account_locked列中。服务器仅在当某些user表行中的Host列和User列与客户端主机名和用户名匹配，客户端提供该行中指定的密码，并且account_locked值为“N”时，服务器才接受连接。

2.请求验证阶段

        在请求验证阶段，服务器会检查用户是否有足够的权限执行每项操作。

        建立连接后，服务器进入访问控制的第2阶段。对于通过该连接发出的每个请求，服务器将确定要执行的操作，然后检查用户是否有足够的权限执行该操作。这就是权限表中的权限字段发挥作用的地方。这些权限可以来自user、db、tables_priv、columns_priv或procs_priv表中的任何一个。

🎯用户管理

        MySQL用户包括普通用户和系统用户。这两种用户的权限是不一样的。root用户是系统管理员，拥有所有的权限。
         从MySQL 8.0.16开始，MySQL引入了基于SYSTEM_USER权限的用户账号类别的概念。

        结合用户账号类别的概念，根据系统用户和普通用户是否具有SYSTEM_USER权限来区分它们。

系统用户：具有SYSTEM_USER权限的用户。
普通用户：没有 SYSTEM_USER特权的用户。

🎃添加普通用户

        要使用CREATE USER，必须具有全局CREATE USER权限或mysql系统数据库的INSERT权限。启用只读系统变量时，CREATE USER还需要CONNECTION_ADMIN 或 SUPER权限。

        基本语法格式为：

CREATE USER [IF NOT EXISTS]

user [auth_option] [, user [auth_option]] ...

DEFAULT ROLE role [, role ] ...

[REQUIRE {NONE | tls_option [[AND] tls_option] ...}]

[WITH resource_option [resource_option] ...]

[password_option | lock_option] ...

其中：

（1）user：用户，由用户名和主机名组成，格式为 'user_name'@'host_name'。

（2）auth_option：用户身份验证选项，下面几种情况中选其一。

        IDENTIFIED BY ‘auth_string’：默认用户身份验证插件（caching_sha2_password。mysql_native_password
        IDENTIFIED WITH auth_plugin：使用指定的身份验证插件auth_plugin对空字符串（未设置用户密码）进行加密。
        IDENTIFIED WITH auth_plugin BY 'auth_string' IDENTIFIED WITH auth_plugin AS 'hash_string’

（3）DEFAULT ROLE ：默认角色（无），role：角色。

（4）tls_option: 加密选项，默认是无。

1. 创建最简单的用户

如果不指定主机地址，则将采用%（任意主机）。
基本语法格式为：
create user '用户名'@'主机地址';

（注意：在创建用户时，若不指定主机地址、密码以及相关的用户选项，则表示此用户在访问MySQL服务器时，不限定客户端、不需要密码。）

2. 创建含有密码的用户

创建含有密码的用户，需要用到IDENTIFIED BY子句。
基本语法格式为：
create user '用户名'@'主机地址' identified [with ‘身份验证插件类型’] by '密码';

（注意：创建用户时，用户主机名可以将引号省略。如果两个用户具有相同的用户名但主机不同，MySQL将其视为不同的用户，允许为这两个用户分配不同的权限集合。如果没有输入密码，那么MySQL允许相关的用户不使用密码登录。但是从安全的角度并不推荐这种做法。）

🎃普通用户修改自己的密码

如果以非匿名用户身份连接，则可以使用USER()函数更改自己的密码。

基本语法格式为：

ALTER USER USER() IDENTIFIED BY 'password';
要查看服务器验证当前登录用户，可以调用CURRENT_USER()函数。

🎃root用户修改自己的密码和普通用户的密码

修改用户信息的基本语法格式为：

ALTER USER [IF EXISTS]

user [auth_option] [, user [auth_option]] ...

[REQUIRE {NONE | tls_option [[AND] tls_option] ...}]

[WITH resource_option [resource_option] ...]

[password_option | lock_option] ...

其中参数的含义与CREATE USER中的参数一样。使用ALTER USER语句可以修改用户身份验证插件、用户密码、角色、SSL/TLS、资源限制和密码管理属性，账号锁定。

🎃root用户修改自己的密码和普通用户的密码

1. 修改root账号密码 root用户修改自己的密码，有两种方法。

（1）使用ALTER USER USER()修改密码，

        基本语法格式为： ALTER USER USER() IDENTIFIED BY 'password';

（2）使用mysqladmin命令修改root用户密码

         基本语法为： mysqladmin -u username -h hostname -p password "newpassword"

         其中username通常是指root，password是关键字，"newpassword"是指新密码，此处只能用双引号。

2. 修改普通用户的密码

（1）使用root用户修改普通用户的密码，可以使用ALTER USER命令进行修改。

         基本语法格式为： ALTER USER 用户名 IDENTIFIED BY '明文密码';

（2）使用SET PASSWORD命令修改用户密码

        root用户也可以使用SET PASSWORD来修改普通用户的密码。

        基本语法格式为：

        SET PASSWORD FOR 用户=‘new_password’;

        其中，用户格式为用户名@主机名，new_password为用户设置的新密码。

🎃修改用户名

可以使用RENAME USER语句来修改一个已经存在的MySQL用户的名字。

基本语法格式： RENAME USER 老用户 TO 新用户 [, ... ]

说明：要使用RENAME USER，必须拥有全局CREATE USER权限或mysql 数据库UPDATE权限。如果旧用户不存在或者新用户已存在，则会出现错误。【例13-12】将用户test1的名字修改为test。

（注意：root用户也可以使用update来修改用户名。flush privileges;语句用来刷新权限，此处不可省。如果省略当我们下一次用新用户名test1登录时，会发现无法登陆，这是因为我们没有刷新权限。）

🎃删除普通用户

在MySQL数据库中，可以使用DROP USER语句来删除普通用户，也可以直接在mysql.user表中删除用户。

（1）使用DROP USER语句来删除普通用户

基本语法格式为： DROP USER [IF EXISTS] user [, user] ...

注意：

DROP USER语句用于删除一个或多个MySQL用户，中间用逗号分隔，并取消其权限。
要使用DROP USER，必须拥有mysql 数据库的全局CREATE USER权限或DELETE权限。

（2）使用DELETE命令来删除普通用户

注意：特别要提醒一点，DROP USER语句删除用户，底层是修改权限表，和DELETE语句直接修改权限表的效果是一样的。但是，采用封装好的语句肯定不会出错，如果直接修改权限表，难免会漏掉某些表（全局权限表user、数据库级权限表db……）。所以推荐使用DROP USER语句来删除用户。

🎯 MySQL 8 中root用户密码丢失的解决办法

        在Windows操作系统中，使用以下过程重置MySQL数据库中“root”@“localhost”用户的密码。要更改具有不同主机名部分的root用户的密码，请修改主机名。

步骤如下：

（1）以管理员身份登录到系统。

（2）如果MySQL服务器正在运行，请停止它。对于作为Windows服务运行的服务器，请转到“服务管理器”：从“开始”菜单中，选择“控制面板”，然后选择“管理工具”，再选择“服务”。在列表中找到MySQL服务并停止它。如果服务器未作为服务运行，则可能需要使用任务管理器强制其停止。

（3）在单行上创建包含密码分配语句的文本文件。语句如下： ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass'; 将MyNewPass替换成要使用的密码。（4）保存文件。假设将文件命名为C:\ mysql-init.txt。

（5）打开控制台窗口进入DOS命令提示符：从“开始”菜单中选择“运行”，然后输入cmd作为要运行的命令。

（6）启动MySQL服务器，并将init_file系统变量设置为文件名（请注意，选项值中的反斜杠是两个）：

        在DOS命令提示符输入下面两条命令：

① C:\> cd "C:\Program Files\MySQL\MySQL Server 8.0\bin"
② C:\> mysqld --init-file=C:\\mysql-init.txt

         注意：

         如果将MySQL安装到其他位置，请相应的调整cd命令。
        服务器在启动时执行由init_file系统变量命名的文件的内容，并更改“root”@“localhost”用户密码。
        要使服务器输出显示在控制台窗口而不是日志文件中，请将--console选项添加到mysqld命令中，改为mysqld --console --init-file=C:\\mysql-init.txt
        如果是使用MySQL安装向导安装的MySQL，则可能需要指定--defaults-file文件选项。将之改为： C:\> mysqld --defaults-file="C:\\ProgramData\\MySQL\\MySQL Server 8.0\\my.ini" --init-file=C:\\mysql-init.txt
        可以使用服务管理器找到--defaults-file设置：从“开始”菜单中，选择“控制面板”，然后选择“管理工具”，再选择“服务”。在列表中找到MySQL服务，右键单击它，然后选择“属性”选项。在“常规”选项卡下的“可执行文件路径”字段包含--defaults-file设置。

（7）服务器成功启动后，删除C:\ mysql-init.txt。现在应该可以使用新密码以root身份连接到MySQL服务器。停止MySQL服务器并正常重启。

        root用户密码丢失，也可以使用--skip-grant-tables参数跳过授权表，重新设置root用户密码来解决，步骤如下：

（1）停止MySQL服务器

        首先, 关闭MySQL服务器，可以使用 net stop mysql80 关闭服务,也可以在本地服务中关闭。

（2）mysqld --skip-grant-tables启动MySQL服务

         以管理员权限打开DOS命令提示符，输入以下命令开启服务, 并绕过权限检查。 mysqld --console --skip-grant-tables --shared-memory

（3）mysql -u root命令重新登录

         启动另一个DOS命令提示符, 输入 mysql -u root 直接登陆, 接着输入以下命令, 将 root 密码置为空。 UPDATE mysql.user SET authentication_string='' WHERE user='root' and host='localhost';

（4）重新设置root用户密码

         关闭两个DOS命令提示符, 重新启动 MySQL 服务, 输入 mysql -u root 直接登陆, 通过以下命令设置 root 用户的密码。 ALTER USER 'root'@'localhost' IDENTIFIED BY 'your password'; 其中your password为要设置的密码。