【MySQL】我在广州学Mysql 系列——MySQL用户管理详解

ℹ️大家好，我是练小杰，本博客是春节前最后一篇了，在此感谢大佬们今年的支持！！🙏🙏
接下来将学习MYSQL用户管理的相关概念以及命令~~
回顾：👉【MYSQL触发器的使用】
数据库专栏：👉【数据库专栏】~
想了解更多，主页： 【练小杰的CSDN】

权限表

权限管理是通过一系列的权限表来实现的，这些表存储在mysql系统数据库中。理解这些权限表对于我们更高效地管理数据库安全性和用户权限至关重要。

权限管理的基本原则

• 最小权限原则：为用户分配完成其任务所需的最小权限，避免过度授权。

• 定期审查：定期检查和更新用户权限，确保权限的合理性和安全性。

• 使用角色：利用数据库角色（Roles）来简化权限管理，将一组权限分配给一个角色，然后将角色分配给用户。

user表

User表是MySQL中最重要的一个权限表，记录允许连接到服务器的帐号信息，里面的权限是全局级的。

• 主要字段如下：

Host：允许连接的主机名或IP地址。
User：用户名。
Password：用户密码（以哈希形式存储）。
Select_priv、Insert_priv、Update_priv等：各种全局权限，如SELECT、INSERT、UPDATE等。
Grant_priv：是否允许用户授予权限给其他用户。
Max_questions、Max_updates、Max_connections等：限制用户在一小时内可以执行的查询、更新和连接次数。

• 示例：查看mysql数据库权限表的信息

SELECT Host, User, Select_priv, Insert_priv FROM mysql.user;
+-----------+------------------+-------------+-------------+
| Host      | User             | Select_priv | Insert_priv |
+-----------+------------------+-------------+-------------+
| localhost | lxj              | Y           | Y           |
| localhost | mysql.infoschema | Y           | N           |
| localhost | mysql.session    | N           | N           |
| localhost | mysql.sys        | N           | N           |
| localhost | root             | Y           | Y           |
+-----------+------------------+-------------+-------------+
5 rows in set (0.00 sec)

db表和host表

db表和host表都是MySQL数据中非常重要的权限表。

• db表中存储了用户对某个数据库的操作权限，决定用户能从哪个主机存取哪个数据库。

• host表中存储了某个主机对数据库的操作权限，配合db权限表对给定主机上数据库级操作权限做更细致地控制。

• 示例：

SELECT Host, Db, User, Select_priv, Insert_priv FROM mysql.db;
+-----------+--------------------+---------------+-------------+-------------+
| Host      | Db                 | User          | Select_priv | Insert_priv |
+-----------+--------------------+---------------+-------------+-------------+
| localhost | performance_schema | mysql.session | Y           | N           |
| localhost | sys                | mysql.sys     | N           | N           |
+-----------+--------------------+---------------+-------------+-------------+
2 rows in set (0.00 sec)

其中，Host、Db、User：分别表示主机名、数据库名和用户名。

tables_priv表和columns_priv表

tables_priv表： 用来对表设置操作权限。
columns_priv表： 用来对表的某一列设置权限。

• 主要字段

• Host、Db、User、Table_name：分别表示主机名、数据库名、用户名和表名。
• Column_name：表示列名
• Grantor：授予权限的用户。
• Timestamp：权限授予的时间戳。
• Table_priv：表级别的权限，如SELECT、INSERT、UPDATE、DELETE等。
• Column_priv：列级别的权限，如SELECT、INSERT、UPDATE等。

• 示例如下：

SELECT Host, Db, User, Table_name, Table_priv FROM mysql.tables_priv;
+-----------+-------+---------------+------------+------------+
| Host      | Db    | User          | Table_name | Table_priv |
+-----------+-------+---------------+------------+------------+
| localhost | mysql | mysql.session | user       | Select     |
| localhost | sys   | mysql.sys     | sys_config | Select     |
+-----------+-------+---------------+------------+------------+
2 rows in set (0.00 sec)


SELECT Host, Db, User, Table_name, Column_name, Column_priv FROM mysql.columns_priv;

procs_priv表

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

• 主要字段：

• Host、Db、User、Routine_name、Routine_type：分别表示主机名、数据库名、用户名、存储过程/函数名和类型(FUNCTION或PROCEDURE)。
• Proc_priv：存储过程/函数级别的权限，如EXECUTE、ALTER ROUTINE等。

• 示例如下：

SELECT Host, Db, User, Routine_name, Routine_type, Proc_priv FROM mysql.procs_priv;

账户管理

登录和退出MySQL服务器

我们利用常用参数登录mysql服务器，通常用mysql命令行登录客户端，基本格式如下：

mysql -u [用户名] -p [数据库名] -h [主机名] -P [端口号]

• 说明：

-u [用户名] 或 --user=[用户名]：指定要登录的MySQL用户名。例如，-u root

---

-p 或 --password：提示输入密码。
若是在-p后面直接输入密码（例如-p123456），密码会以明文形式显示在命令行中，存在安全风险，因此推荐只使用-p，然后在提示时输入密码。

---

[数据库名]：可选参数，指定要连接的数据库。如果不指定，将连接到默认数据库。

---

-h [主机名] 或 --host=[主机名]：指定要连接的主机名或IP地址。默认是localhost，表示连接到本地MySQL服务器。

---

-P [端口号] 或 --port=[端口号]：指定要连接的端口号，默认为3306端口。

• 登录Mysql服务器示例：

1. 以root用户身份登录本地MySQL服务器：

 mysql -u root -p

执行后，系统会提示输入密码：

Enter password:

输入正确的密码后，即可登录成功。

2.以指定用户身份登录远程MySQL服务器：

mysql -u lianxiaojie -p -h 192.168.1.100  -P 3306

由以上命令可知，将登录到IP地址为192.168.1.100的远程MySQL服务器，端口号为3306，使用用户名为lianxiaojie的用户。

3.登录并连接到特定数据库：

mysql -u username -p database_name

将登录并连接到名为database_name的数据库。

• 退出MySQL服务器

退出MySQL客户端一般使用EXIT命令或者 QUIT命令，最后分号可加也可不加！！！

EXIT;  
//或者exit

QUIT;
//或者quit

新建普通用户

• 使用CREATE USER或GRANT语句。在更改用户权限后，需要使用 FLUSH PRIVILEGES 语句来使更改生效。

-- 创建一个新用户
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

-- 授予数据库级别的SELECT和INSERT权限
GRANT SELECT, INSERT ON database_name.* TO 'username'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

删除普通用户

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

若需要删除一个用户，可以使用 DROP USER 语句。

DROP USER 'username'@'host';

2. 使用DELETE语句删除用户

DROP USER 语句用于删除一个或多个MySQL账户及其所有权限。

DROP USER 'username'@'host';

• 示例如下:

-- 删除特定主机上的用户
DROP USER 'lxj'@'localhost';

-- 删除允许从任何主机连接的用户
DROP USER 'lxj'@'%';

-- 删除多个用户
DROP USER 'lxj'@'localhost', 'gem'@'192.168.1.%';

root用户修改自己的密码

1. 使用 ALTER USER 语句（推荐）

• 如果你当前已经以root用户登录，可以直接执行以下命令：

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

当你使用的是不同的主机名（例如远程主机），请将 'localhost' 替换为相应的主机名或IP地址。

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

适用于所有版本的MySQL，并且可以在不登录MySQL服务器的情况下修改密码。

mysqladmin -u root -p'current_password' password 'new_password'

⚠️在-p选项和current_password 之间不要有空格。

3. 直接修改mysql数据库的user表(不推荐，容易出错)

利用UPDATE 语句直接修改 mysql.user 表

USE mysql;
UPDATE user SET authentication_string=PASSWORD('new_password') WHERE User='root' AND Host='localhost';

FLUSH PRIVILEGES;

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

SET PASSWORD FOR 'root'@'localhost' = 'new_password';

-- 刷新权限(可选)
FLUSH PRIVILEGES;

root用户修改普通用户密码

1. 使用SET语句修改普通用户的密码

SET PASSWORD FOR 'john'@'localhost' = 'NewSecureP@ssw0rd!';

FLUSH PRIVILEGES;

这里是修改普通用户john的密码并刷新权限

2. 使用UPDATE语句修改普通用户的密码

USE mysql;
UPDATE user SET authentication_string=PASSWORD('new_password') WHERE User='lianxiaojie' AND Host='host';

3. 使用GRANT语句修改普通用户密码

-- 以root用户登录
mysql -u root -p

-- 修改用户john的密码
GRANT USAGE ON *.* TO 'john'@'localhost' IDENTIFIED BY 'NewSecureP@ssw0rd!';

-- 刷新权限
FLUSH PRIVILEGES;

• 命令说明：

USAGE 权限：表示不授予任何新的权限，仅用于修改用户属性（如密码）。
*.*： 表示全局级别，适用于所有数据库和表。
'username'@'host'： 指定要修改密码的用户和主机。
IDENTIFIED BY 'new_password'：设置新的密码。

普通用户修改密码

普通用户登录MySQL服务器后，通过SET语句设置自己的密码。
登录后，可以使用以下两种形式的 SET PASSWORD 语句来设置或修改密码。

• 设置当前用户密码：

SET PASSWORD = 'new_password';

• 为特定用户设置密码：

若需要为其他用户设置密码（前提是当前用户有足够的权限)，才能使用。

SET PASSWORD FOR 'username'@'host' = 'new_password';

示例：假设用户lxj 想要修改自己的密码。

• 登录mysql

mysql -u lxj -p

输入当前密码后登录。

• 修改密码

SET PASSWORD = 'sheniankuaile!';

• 然后，使用新密码重新登录，测试密码是否修改。

mysql -u lxj -p

输入 sheniankuaile! 以确认更改。

⚠️注意

• 使用 SET PASSWORD 语句后，MySQL会自动刷新权限，无需手动执行 FLUSH PRIVILEGES;

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

1. 使用--skip-grant-tables选项启动MySQL服务
2. 使用root用户登录和重新设置密码
3. 加载权限表

⚠️注意事项

• 安全性：确保在安全的环境下修改密码，避免密码泄露。
• 权限：修改root密码需要当前root用户具有足够的权限。
• 备份：在进行密码修改前，建议备份相关的系统表，以防止意外情况发生。
• 远程访问：如果需要远程访问MySQL服务器，确保防火墙和网络配置允许相应的连接。
• 避免直接操作 mysql.user 表，除非在特殊情况下并明确了解受到的影响。

权限管理

MySQL的各种账户权限信息被存储在mysql数据库的user、db、host、tables_priv、columns_priv和procs_priv表中。在MySQL启动时，服务器将这些数据库表内容读入内存。

授权

授权就是为某个用户授于权限。合理的授权可以保证数据库的安全。
MySQL中可以使用GRANT语句为用户授于权限。

收回权限

收回权限就是取消已经赋于用户的某些权限。收回用户不必要的权限可以在一定程度上保证系统的安全性。
MySQL中使用REVOKE语句取消用户的某些权限。

// 撤销数据库级别的INSERT权限
REVOKE INSERT ON database_name.* FROM 'username'@'localhost';

// 刷新权限
FLUSH PRIVILEGES;

查看权限

SHOW GRANTS语句可以显示指定用户的权限信息。
而使用SHOW GRANT语句查看当前账户信息。

• 基本语法：

SHOW GRANTS FOR 'user'@'host';

访问控制

连接核实阶段

当连接MySQL服务器时，服务器基于用户的身份以及用户是否能通过正确的密码验证身份来接受或拒绝连接。

• 验证user表中是否存在该用户。
• 验证3个字段（Host，User和Password）

请求核实阶段

建立了连接之后，对在此连接上进来的每个请求，服务器检查用户要执行的操作，然后检查是否有足够的权限来执行它。

• 核实user、db、host、tables_priv或columns_priv表的权限信息

• Mysql请求核实的过程如下：
  

本文有关Mysql数据库用户管理相关内容已经讲完了，年后再见啦👋
主页：【练小杰的CSDN】😆
ℹ️欢迎各位在评论区踊跃讨论，积极提出问题，解决困惑！！！
⚠️若博客里的内容有问题，欢迎指正，我会及时修改！！