[MySQL实践] MySQL中的用户创建与授权

徐锦洪  金牌会员 | 2024-8-1 11:07:13 | 显示全部楼层 | 阅读模式
打印 上一主题 下一主题
楼主

主题 976|帖子 976|积分 2928

一、环境阐明

操作系统：win10专业版
MySQL版本：8.0.19
二、对用户的操作

2.1、创建用户

可以简单地创建用户：
  1. CREATE USER xh;
复制代码
也可以创建用户的同时设置密码：
  1. CREATE USER xh IDENTIFIED BY '12345678';
复制代码
也可以创建用户的时间，设置可以登录的Host：
  1. # 只能在MySQL服务器登录
  2. CREATE USER 'xh'@localhost IDENTIFIED BY '12345678';
  3. # or 只能在指定ip登录
  4. CREATE USER 'xh'@'192.168.10.1' IDENTIFIED BY '12345678';
  5. # or 在所有ip都可以登录
  6. CREATE USER 'xh'@'%' IDENTIFIED BY '12345678';
复制代码
阐明：


  • 用户名可以不指定Host，此时默认的Host就是%，表现在所有ip的主机都可以登录。
  • 用户名会出现在如下sql语句中：CREATE USER , GRANT 和 SET PASSWORD。
  • 如用户名中含特别字符（如：空格 或 -），则必须使用引号。例如：test-user 或是xiao hui。
  • 如主机名中含有特别字符（如：. 或 %），则必须使用引号。例如：192.168.10.2 或是% 。
  • 如果使用引号，用户名和主机名必须单独引用。如'test-user@192.168.10.%' 和'test-user'@'192.168.10.%' 是不同的。
关于账户名的详细规则，可以参考官方文档：
   https://dev.mysql.com/doc/refman/8.0/en/account-names.html
  当你想深入相识用户创建的语法，或是记不清 create user 的用法了，可以通过如下方式打开帮助：
  1. # 终端中执行。如果使用的是Navicat，F6可以打开命令行终端。
  2. mysql> HELP 'CREATE USER';
  3. # 结果
  4. | CREATE USER | Syntax:
  5. CREATE USER [IF NOT EXISTS]
  6.     user [auth_option] [, user [auth_option]] ...
  7.     DEFAULT ROLE role [, role ] ...
  8.     [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
  9.     [WITH resource_option [resource_option] ...]
  10.     [password_option | lock_option] ...
  11. ...
  12. URL: https://dev.mysql.com/doc/refman/8.0/en/create-user.html
复制代码
2.2、检察用户

  创建好的用户信息将会保存到mysql系统数据库的user表中。这个表的字段如下（部分）：
  1. -- DESCRIBE mysql.`user`;
  2. +--------------------------+-----------------------------------+------+-----+-----------------------+-------+
  3. | Field                    | Type                              | Null | Key | Default               | Extra |
  4. +--------------------------+-----------------------------------+------+-----+-----------------------+-------+
  5. | Host                     | char(255)                         | NO   | PRI |                       |       |
  6. | User                     | char(32)                          | NO   | PRI |                       |       |
  7. | Select_priv              | enum('N','Y')                     | NO   |     | N                     |       |
  8. | Insert_priv              | enum('N','Y')                     | NO   |     | N                     |       |
  9. | Update_priv              | enum('N','Y')                     | NO   |     | N                     |       |
  10. | Delete_priv              | enum('N','Y')                     | NO   |     | N                     |       |
  11. | Create_priv              | enum('N','Y')                     | NO   |     | N                     |       |
  12. | Drop_priv                | enum('N','Y')                     | NO   |     | N                     |       |
  13. ...
  14. +--------------------------+-----------------------------------+------+-----+-----------------------+-------+
  15. 51 rows in set (0.14 sec)
复制代码
字段阐明：


  • User列：存储所有的MySQL账户名
  • Host列：存储可登录的主机名
  • 其他列：存储了该用户拥有的权限、登录密码等信息
 
1).检察当前用户
可使用函数检察当前用户
  1. -- 返回当前用户的用户名和可登录主机名
  2. -- 或者： SELECT USER();
  3. mysql> select user() from dual;
  4. +----------------+
  5. | user()         |
  6. +----------------+
  7. | root@localhost |
  8. +----------------+
  9. 1 row in set (0.03 sec)
  11. -- 或者使用current_user()函数
  12. mysql> select current_user();
  13. +----------------+
  14. | current_user() |
  15. +----------------+
  16. | root@%         |
  17. +----------------+
  18. 1 row in set (0.02 sec)
  20. --help 'user';
复制代码
2).检察指定用户
  1. SELECT User,Host,authentication_string FROM mysql.user WHERE User='xh';
复制代码
执行结果如下：
  1. +------+-----------+-------------------------------------------+
  2. | User | Host      | authentication_string                     |
  3. +------+-----------+-------------------------------------------+
  4. | xh   | %         |                                           |
  5. | xh   | localhost | *84AAC12F54AB666ECFC2A83C676908C8BBC381B1 |
  6. +------+-----------+-------------------------------------------+
  7. 2 rows in set (0.06 sec)
复制代码
3).检察所有的用户
  1. SELECT User,Host FROM mysql.user;
复制代码
执行结果如下：
  1. +------------------+-----------+
  2. | User             | Host      |
  3. +------------------+-----------+
  4. | nacos            | %         |
  5. | root             | %         |
  6. | xh               | %         |
  7. | mysql.infoschema | localhost |
  8. | mysql.session    | localhost |
  9. | mysql.sys        | localhost |
  10. +------------------+-----------+
  11. 8 rows in set (0.05 sec)
复制代码
 
2.3、修改密码

1).修改当前用户的密码
可通过如下脚本修改当前用户的密码：
  1. SET PASSWORD='3456789';
复制代码
2).修改指定用户的密码
可通过如下脚本修改指定用户的密码：
  1. SET PASSWORD FOR xh='2345678';
  3. -- or 更建议的密码修改方式（修改账号xh的密码）
  4. ALTER USER xh IDENTIFIED BY '2345678';
复制代码
更推荐使用 ALTER USER 的方式修改用户密码。
本节参考：
   https://dev.mysql.com/doc/refman/8.0/en/set-password.html
   
2.4、用户登录

打开下令行cmd，然后输入下面的下令：
  1. mysql -u xh -p
  2. # 提示输入密码
  3. Enter password:
复制代码
输入正确的密码后，就可以进入mysql的下令行终端。
新创建的用户还没有授权，那么它的授权信息会是什么呢？
  1. SHOW GRANTS;
复制代码
结果如下：
  1. +----------------------------------------+
  2. | Grants for xh@localhost                |
  3. +----------------------------------------+
  4. | GRANT USAGE ON *.* TO `xh`@`localhost` |
  5. +----------------------------------------+
  6. 1 row in set (0.00 sec)
复制代码
那么再看一下它可以访问哪些数据库吧：
  1. show databases;
复制代码
结果如下所示：
  1. show databases;
  2. +--------------------+| Database           |+--------------------+| information_schema |+--------------------+1 row in set (0.00 sec)
复制代码
阐明该账号只能访问表：information_schema。
2.5、删除用户

删除账号xh：
  1. DROP USER 'xh';
复制代码
 
三、授权操作

3.1、用户授权

创建用户xh，并只允许其检察my_database上t_user表的权限：
  1. -- 方法一：
  2. USE my_database;
  3. GRANT SELECT ON TABLE t_user TO xh;
  5. -- or 方法二：
  6. GRANT SELECT ON my_database.t_user TO xh;
复制代码
以xh用户登录，看到其可以访问数据库my_database了：

3.2、角色授权

公司有一个数据分析组，有xh和nacos两个成员；要求数据分析组拥有my_database数据库中所有表的查询权限：
  1. -- 创建角色
  2. create role analysis;
  3. -- 给角色添加成员
  4. grant analysis to xh, nacos;
  5. -- 将权限赋给角色
  6. grant select on  my_database.* to analysis;
  7. -- 刷新权限
  8. flush privileges;
复制代码
 
3.3、检察授权

检察xh用户的授权：
  1. SHOW GRANTS FOR xh;
复制代码
结果如下：
  1. +----------------------------------------------------+
  2. | Grants for xh@%                                    |
  3. +----------------------------------------------------+
  4. | GRANT USAGE ON *.* TO `xh`@`%`                     |
  5. | GRANT SELECT ON `my_database`.`t_user` TO `xh`@`%` |
  6. +----------------------------------------------------+
  7. 2 rows in set (0.02 sec)
复制代码
检察当前用户的授权：
  1. SHOW GRANTS;
  2. --  检察当前用户SELECT USER();
复制代码
 
3.4、修改授权

增长表的修改权限
由于xh用户只能检察t_user表的数据，但是作为开辟职员，须要表的所有操作权限。故追加相干授权：
  1. GRANT INSERT,UPDATE,DELETE ON my_database.t_user TO 'xh';
  2. -- or
  3. GRANT INSERT,UPDATE,DELETE,CREATE,DROP ON my_database.t_user TO 'xh';
复制代码
增长库的修改权限
后续又须要增长在my_database中建表和删除表的权限：
  1. GRANT INSERT,UPDATE,DELETE,CREATE,DROP ON my_database.* TO 'xh';
复制代码
增长库的所有权限
可以进一步放开在my_database中的权限：
  1. GRANT ALL ON my_database.* TO 'xh';
复制代码
增长所有权限
可以访问MySQL上的所有资源：
  1. GRANT ALL ON *.* TO 'xh';
复制代码
 
3.5、取消授权

取消所有权限
  1. REVOKE ALL ON *.* FROM 'xh'@'%';
复制代码
取消某个库的所有权限
  1. REVOKE ALL ON my_database.* FROM 'xh'@'%';
复制代码
取消某个库的修改权限
  1. REVOKE INSERT,UPDATE,DELETE,CREATE,DROP ON my_database.* TO 'xh';
复制代码
取消某张表的修改权限
  1. REVOKE INSERT,UPDATE,DELETE,CREATE,DROP ON my_database.t_user TO 'xh';
复制代码
注：只要对权限进行了修改，末了都不要忘记执行下面的语句，否则设置会不生效：
  1. flush privileges;
复制代码
 
四、题目

4.1、如何创建用户并指定访问的数据库，并要求登录后必须设定新密码？

   Joe 要给数据组的 John 创建一个用户，他盼望John 能够从 192.168.7.42 登录 goods 数据库查询数据， 用户第一次登录时使用密码 goods123，登录后必须设定一个新密码，那么应该用哪个语句？
  答：
  1. -- 创建用户，并设置密码（登录后即失效）
  2. create user 'john'@'192.168.7.42' identified by 'goods123' password expire;
  3. -- 授权select
  4. grant select on goods.* to 'john'@'192.168.7.42';
  5. -- 刷新权限
  6. flush privileges;
复制代码
4.2、如何修改密码，并要求登录后修改新密码？

   Fred 有一个名为 ‘fred’@‘%’ 的 MySQL 账户，但是他忘了密码，须要 Joe 帮他修改一个新口令， Joe 准备将这个账户的口令初始化为 goods123fred ， 并设置为登录后修改新口令。他应该怎么做？
  答：
  1. ALTER USER 'fred'@'%' IDENTIFIED BY 'goods123fred' password expire;
  3. -- 错误的修改密码方法：
  4. -- ALTER USER 'fred'@'%' set password 'goods123fred' expire;
  5. -- 不可以使用GRANT方式修改用户密码
  6. -- GRANT USAGE ON *.* TO 'fred'@'%' IDENTIFIED BY 'goods123fred';
复制代码
MySQL8.0中不可以使用GRANT来创建用户或修改密码了
  1. -- 创建用户
  2. GRANT USAGE ON *.* TO 'xh_01'@'localhost' IDENTIFIED BY '123456';
  3. -- 会报错：1410 - You are not allowed to create a user with GRANT
复制代码
参考：https://zhuanlan.zhihu.com/p/266149646
4.3、如何限制账号的资源

   Joe 须要限制数据分析组（role analysis）的用户, 每小时查询次数不能超过10000次。应该怎么操作？
题目泉源：限制用户使用资源
  答：
  1. alter user analysis set MAX_QUERIES_PER_HOUR 10000;
复制代码
五、总结

  本文解说了在MySQL8.0中如何创建用户和如何进行授权的相干知识。通过场景和代码相联合的方式，使得知识的学习更直观。
参考：
https://blog.csdn.net/qq_42826747/article/details/106203232

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

本帖子中包含更多资源

您需要 登录 才可以下载或查看，没有账号？立即注册

x
回复

使用道具 举报

0 个回复

倒序浏览
返回列表

快速回复

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

徐锦洪

金牌会员
这个人很懒什么都没写!

楼主热帖

标签云

AI 运维 CIO 存储 服务器
微信订阅号
微信服务号
微信客服
小程序
H5
关于我们 商务合作 网站地图
©Copyright 2022-2024 杭州祥升科技有限公司 版权所有 浙ICP备20004199号
快速回复 返回顶部 返回列表