【MySQL】：用户管理

于 2025-02-10 16:46:12 发布
阅读量818 收藏 8
点赞数 19
CC 4.0 BY-SA版权
分类专栏： MySQL数据库 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Yikefore/article/details/145369771

朋友们、伙计们，我们又见面了，本期来给大家带来数据库用户管理的知识点，如果看完之后对你有一定的启发，那么请留下你的三连，祝大家心想事成！

C 语 言 专 栏：C语言：从入门到精通

数据结构专栏：数据结构

个  人  主  页 ：stackY、

C + + 专 栏   ：C++

Linux 专 栏  ：Linux

目录

1. 用户信息

1.1 创建用户

1.2 删除用户

1.3 修改用户登录密码 

2. 数据库权限 

2.1 给用户授权

2.2 查看用户现有权限 

2.3 回收权限 

如果我们只能使用root用户，这样就存在安全隐患。此时，就需要对MySQL的用户进行管理，让指定的用户操纵指定的库。

1. 用户信息

MySQL中的用户，都存储在系统数据库mysql的user表中
 

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| my_lib             |
| mysql              |
| performance_schema |
| scott              |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> use mysql;
Database changed

mysql> select host,user,authentication_string from user;
+-----------+------------------+------------------------------------------------------------------------+
| host      | user             | authentication_string                                                  |
+-----------+------------------+------------------------------------------------------------------------+
| localhost | debian-sys-maint | $A$005$Ir17{.dswvJU-PDolvS3oBXr2zxf7Z2T4Mi6U6UX/gzYiVWEd.jfzs8T/1 |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | root             |                                                                        |
+-----------+------------------+------------------------------------------------------------------------+
5 rows in set (0.00 sec)

字段解释：

  • host： 表示这个用户可以从哪个主机登陆，如果是localhost，表示只能从本机登陆
  • user： 用户名
  • authentication_string： 用户密码通过password函数加密后的

1.1 创建用户

语法：

create user '用户名'@'登陆主机/ip' identified by '密码';

示例：

-- 创建新用户
--                              只在本机登录
mysql> create user 'zhangsan'@'localhost' identified by '1234567';
Query OK, 0 rows affected (0.08 sec)

-- 创建成功后可以查看到新用户
mysql> select user,host,authentication_string from user;
+------------------+-----------+------------------------------------------------------------------------+
| user             | host      | authentication_string                                                  |
+------------------+-----------+------------------------------------------------------------------------+
| debian-sys-maint | localhost | $A$005$Ir17{.dswvJU-PDolvS3oBXr2zxf7Z2T4Mi6U6UX/gzYiVWEd.jfzs8T/1 |
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root             | localhost |                                                                        |
| zhangsan         | localhost | $A$005$8y_<%H (>D<X*M_bugafIhy5x0uRY3PkCC/jW/M5ZRETte3vgElN1zVsKrbxZ9 |
+------------------+-----------+------------------------------------------------------------------------+
6 rows in set (0.00 sec)

-- 如果没有显示出新用户时
-- 使用 flush privileges; 进行刷新

--                     这里设置为%表示的在任意主机都可以登录（有风险的）
mysql> create user'zhangsan'@'%' identified by '1234567';
Query OK, 0 rows affected (0.01 sec)


mysql> select user,host,authentication_string from user;
+------------------+-----------+------------------------------------------------------------------------+
| user             | host      | authentication_string                                                  |
+------------------+-----------+------------------------------------------------------------------------+
| zhangsan         | %         | $A$005$6:
                                          9+aQ/Kv)).#c
                                                      ^n7.xI7jvIlpP8/GwkBHLx6myuRRZl7.jWPv76YHY14f3 |
| debian-sys-maint | localhost | $A$005$Ir17{.dswvJU-PDolvS3oBXr2zxf7Z2T4Mi6U6UX/gzYiVWEd.jfzs8T/1 |
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root             | localhost |                                                                        |
+------------------+-----------+------------------------------------------------------------------------+
6 rows in set (0.00 sec)

注意：

新增用户时，不要轻易添加一个可以从任意地方登陆的user。

1.2 删除用户

语法：

drop user '用户名'@'主机名'
-- 如果直接给定用户名默认删除的是'%'

示例：

mysql> select user,host,authentication_string from user;
+------------------+-----------+------------------------------------------------------------------------+
| user             | host      | authentication_string                                                  |
+------------------+-----------+------------------------------------------------------------------------+
| zhangsan         | %         | $A$005$6:
                                          9+aQ/Kv)).#c
                                                      ^n7.xI7jvIlpP8/GwkBHLx6myuRRZl7.jWPv76YHY14f3 |
| debian-sys-maint | localhost | $A$005$Ir17{.dswvJU-PDolvS3oBXr2zxf7Z2T4Mi6U6UX/gzYiVWEd.jfzs8T/1 |
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root             | localhost |                                                                        |
+------------------+-----------+------------------------------------------------------------------------+
6 rows in set (0.00 sec)

-- 删除指定的用户
mysql> drop user 'zhangsan'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> select user,host,authentication_string from user;
+------------------+-----------+------------------------------------------------------------------------+
| user             | host      | authentication_string                                                  |
+------------------+-----------+------------------------------------------------------------------------+
| debian-sys-maint | localhost | $A$005$Ir17{.dswvJU-PDolvS3oBXr2zxf7Z2T4Mi6U6UX/gzYiVWEd.jfzs8T/1 |
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root             | localhost |                                                                        |
+------------------+-----------+------------------------------------------------------------------------+
5 rows in set (0.01 sec)

1.3 修改用户登录密码 

语法：

-- 自己改自己密码
set password=password('新的密码');

-- root用户修改指定用户的密码
set password for '用户名'@'主机名'=password('新的密码');
-- 在MySQL 8.0 中可以使用
-- alter user '用户名'@'主机名' identified by '新密码';

示例：

mysql> select user,host,authentication_string from user;
+------------------+-----------+------------------------------------------------------------------------+
| user             | host      | authentication_string                                                  |
+------------------+-----------+------------------------------------------------------------------------+
| debian-sys-maint | localhost | $A$005$Ir17{.dswvJU-PDolvS3oBXr2zxf7Z2T4Mi6U6UX/gzYiVWEd.jfzs8T/1 |
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root             | localhost |                                                                        |
| zhangsan         | localhost | $A$005$S+U%r   a41VEVyJY.NMMCFiVlpfUpKxtRJjtvbyoRP8eDX0H2.ZZ9 |
+------------------+-----------+------------------------------------------------------------------------+
6 rows in set (0.00 sec)

-- 修改指定用户的密码
mysql> set password for 'zhangsan'@'localhost'=password('87654321');
Query OK, 0 rows affected (0.00 sec)

-- 进行刷新
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> select user,host,authentication_string from user;
+------------------+-----------+------------------------------------------------------------------------+
| user             | host      | authentication_string                                                  |
+------------------+-----------+------------------------------------------------------------------------+
| debian-sys-maint | localhost | $A$005$Ir17{.dswvJU-PDolvS3oBXr2zxf7Z2T4Mi6U6UX/gzYiVWEd.jfzs8T/1 |
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root             | localhost |                                                                        |
| zhangsan         | localhost | $A$005$>QSpmKg&(&b%">l_ZahhPYqyCLQCnVU2YRiAx8Yu3k0HyrxqcnML9jzWCg4. |
+------------------+-----------+------------------------------------------------------------------------+
6 rows in set (0.00 sec)

2. 数据库权限 

MySQL数据库提供的权限列表：

2.1 给用户授权

刚创建的用户没有任何的权限，需要给用户进行授权操作。

语法：

grant 权限列表 on 库.对象名 to '用户名'@'登陆位置' [identified by '密码']

说明：

  • 权限列表，多个权限用逗号分开
grant select on ...
grant select, delete, create on ....
grant all [privileges] on ... -- 表示赋予该用户在该对象上的所有权限
  • *.* ：代表本系统中的所有数据库的所有对象（表，视图，存储过程等）
  • 库.* ：表示某个数据库中的所有数据对象(表，视图，存储过程等)
  • identified by可选； 如果用户存在，赋予权限的同时修改密码,如果该用户不存在，就是创建用户

示例：

2.2 查看用户现有权限 

语法：

show grants for '用户'@'主机登录位置';

示例：

-- 查看root用户权限
mysql> show grants for 'root'@'localhost'\G
*************************** 1. row ***************************
Grants for root@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION
*************************** 2. row ***************************
Grants for root@localhost: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION
*************************** 3. row ***************************
Grants for root@localhost: GRANT PROXY ON ``@`` TO `root`@`localhost` WITH GRANT OPTION
3 rows in set (0.00 sec)

-- 查看zhangsan用户权限
mysql> show grants for 'zhangsan'@'localhost'\G
*************************** 1. row ***************************
Grants for zhangsan@localhost: GRANT USAGE ON *.* TO `zhangsan`@`localhost`
*************************** 2. row ***************************
Grants for zhangsan@localhost: GRANT SELECT ON `my_lib`.* TO `zhangsan`@`localhost`
2 rows in set (0.00 sec)

2.3 回收权限 

语法：

revoke 权限列表 on 库.对象名 from '用户名'@'登陆位置';

示例：

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

stackY、

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值