[MySQL实践] MySQL中的用户创建与授权
一、环境阐明操作系统：win10专业版
MySQL版本：8.0.19
二、对用户的操作
2.1、创建用户
可以简单地创建用户：
CREATE USER xh;
也可以创建用户的同时设置密码：
CREATE USER xh IDENTIFIED BY '12345678';
也可以创建用户的时间，设置可以登录的Host：
# 只能在MySQL服务器登录
CREATE USER 'xh'@localhost IDENTIFIED BY '12345678';
# or 只能在指定ip登录
CREATE USER 'xh'@'192.168.10.1' IDENTIFIED BY '12345678';
# or 在所有ip都可以登录
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 的用法了，可以通过如下方式打开帮助：
# 终端中执行。如果使用的是Navicat，F6可以打开命令行终端。
mysql> HELP 'CREATE USER';
# 结果
| CREATE USER | Syntax:
CREATE USER
user [, user ] ...
DEFAULT ROLE role [, role ] ...
tls_option] ...}]
...]
...
...
URL: https://dev.mysql.com/doc/refman/8.0/en/create-user.html
2.2、检察用户
创建好的用户信息将会保存到mysql系统数据库的user表中。这个表的字段如下（部分）：
-- DESCRIBE mysql.`user`;
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(255) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
...
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
51 rows in set (0.14 sec)
字段阐明：
[*]User列：存储所有的MySQL账户名
[*]Host列：存储可登录的主机名
[*]其他列：存储了该用户拥有的权限、登录密码等信息
1).检察当前用户
可使用函数检察当前用户
-- 返回当前用户的用户名和可登录主机名
-- 或者： SELECT USER();
mysql> select user() from dual;
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.03 sec)
-- 或者使用current_user()函数
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| root@% |
+----------------+
1 row in set (0.02 sec)
--help 'user';
2).检察指定用户
SELECT User,Host,authentication_string FROM mysql.user WHERE User='xh';
执行结果如下：
+------+-----------+-------------------------------------------+
| User | Host | authentication_string |
+------+-----------+-------------------------------------------+
| xh | % | |
| xh | localhost | *84AAC12F54AB666ECFC2A83C676908C8BBC381B1 |
+------+-----------+-------------------------------------------+
2 rows in set (0.06 sec)
3).检察所有的用户
SELECT User,Host FROM mysql.user;
执行结果如下：
+------------------+-----------+
| User | Host |
+------------------+-----------+
| nacos | % |
| root | % |
| xh | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
+------------------+-----------+
8 rows in set (0.05 sec)
2.3、修改密码
1).修改当前用户的密码
可通过如下脚本修改当前用户的密码：
SET PASSWORD='3456789';
2).修改指定用户的密码
可通过如下脚本修改指定用户的密码：
SET PASSWORD FOR xh='2345678';
-- or 更建议的密码修改方式（修改账号xh的密码）
ALTER USER xh IDENTIFIED BY '2345678';
更推荐使用 ALTER USER 的方式修改用户密码。
本节参考：
https://dev.mysql.com/doc/refman/8.0/en/set-password.html
2.4、用户登录
打开下令行cmd，然后输入下面的下令：
mysql -u xh -p
# 提示输入密码
Enter password:
输入正确的密码后，就可以进入mysql的下令行终端。
新创建的用户还没有授权，那么它的授权信息会是什么呢？
SHOW GRANTS;
结果如下：
+----------------------------------------+
| Grants for xh@localhost |
+----------------------------------------+
| GRANT USAGE ON *.* TO `xh`@`localhost` |
+----------------------------------------+
1 row in set (0.00 sec)
那么再看一下它可以访问哪些数据库吧：
show databases;
结果如下所示：
show databases;
+--------------------+| Database |+--------------------+| information_schema |+--------------------+1 row in set (0.00 sec) 阐明该账号只能访问表：information_schema。
2.5、删除用户
删除账号xh：
DROP USER 'xh';
三、授权操作
3.1、用户授权
创建用户xh，并只允许其检察my_database上t_user表的权限：
-- 方法一：
USE my_database;
GRANT SELECT ON TABLE t_user TO xh;
-- or 方法二：
GRANT SELECT ON my_database.t_user TO xh;
以xh用户登录，看到其可以访问数据库my_database了：
https://i-blog.csdnimg.cn/blog_migrate/3c6528bf7585d6c5549a5722b0cbd8f4.png
3.2、角色授权
公司有一个数据分析组，有xh和nacos两个成员；要求数据分析组拥有my_database数据库中所有表的查询权限：
-- 创建角色
create role analysis;
-- 给角色添加成员
grant analysis to xh, nacos;
-- 将权限赋给角色
grant select onmy_database.* to analysis;
-- 刷新权限
flush privileges;
3.3、检察授权
检察xh用户的授权：
SHOW GRANTS FOR xh;
结果如下：
+----------------------------------------------------+
| Grants for xh@% |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO `xh`@`%` |
| GRANT SELECT ON `my_database`.`t_user` TO `xh`@`%` |
+----------------------------------------------------+
2 rows in set (0.02 sec)
检察当前用户的授权：
SHOW GRANTS;
--检察当前用户SELECT USER();
3.4、修改授权
增长表的修改权限
由于xh用户只能检察t_user表的数据，但是作为开辟职员，须要表的所有操作权限。故追加相干授权：
GRANT INSERT,UPDATE,DELETE ON my_database.t_user TO 'xh';
-- or
GRANT INSERT,UPDATE,DELETE,CREATE,DROP ON my_database.t_user TO 'xh';
增长库的修改权限
后续又须要增长在my_database中建表和删除表的权限：
GRANT INSERT,UPDATE,DELETE,CREATE,DROP ON my_database.* TO 'xh';
增长库的所有权限
可以进一步放开在my_database中的权限：
GRANT ALL ON my_database.* TO 'xh';
增长所有权限
可以访问MySQL上的所有资源：
GRANT ALL ON *.* TO 'xh';
3.5、取消授权
取消所有权限
REVOKE ALL ON *.* FROM 'xh'@'%';
取消某个库的所有权限
REVOKE ALL ON my_database.* FROM 'xh'@'%';
取消某个库的修改权限
REVOKE INSERT,UPDATE,DELETE,CREATE,DROP ON my_database.* TO 'xh';
取消某张表的修改权限
REVOKE INSERT,UPDATE,DELETE,CREATE,DROP ON my_database.t_user TO 'xh';
注：只要对权限进行了修改，末了都不要忘记执行下面的语句，否则设置会不生效：
flush privileges;
四、题目
4.1、如何创建用户并指定访问的数据库，并要求登录后必须设定新密码？
Joe 要给数据组的 John 创建一个用户，他盼望John 能够从 192.168.7.42 登录 goods 数据库查询数据， 用户第一次登录时使用密码 goods123，登录后必须设定一个新密码，那么应该用哪个语句？
答：
-- 创建用户，并设置密码（登录后即失效）
create user 'john'@'192.168.7.42' identified by 'goods123' password expire;
-- 授权select
grant select on goods.* to 'john'@'192.168.7.42';
-- 刷新权限
flush privileges;
4.2、如何修改密码，并要求登录后修改新密码？
Fred 有一个名为 ‘fred’@‘%’ 的 MySQL 账户，但是他忘了密码，须要 Joe 帮他修改一个新口令， Joe 准备将这个账户的口令初始化为 goods123fred ， 并设置为登录后修改新口令。他应该怎么做？
答：
ALTER USER 'fred'@'%' IDENTIFIED BY 'goods123fred' password expire;
-- 错误的修改密码方法：
-- ALTER USER 'fred'@'%' set password 'goods123fred' expire;
-- 不可以使用GRANT方式修改用户密码
-- GRANT USAGE ON *.* TO 'fred'@'%' IDENTIFIED BY 'goods123fred';
MySQL8.0中不可以使用GRANT来创建用户或修改密码了
-- 创建用户
GRANT USAGE ON *.* TO 'xh_01'@'localhost' IDENTIFIED BY '123456';
-- 会报错：1410 - You are not allowed to create a user with GRANT
参考：https://zhuanlan.zhihu.com/p/266149646
4.3、如何限制账号的资源
Joe 须要限制数据分析组（role analysis）的用户, 每小时查询次数不能超过10000次。应该怎么操作？
题目泉源：限制用户使用资源
答：
alter user analysis set MAX_QUERIES_PER_HOUR 10000;
五、总结
本文解说了在MySQL8.0中如何创建用户和如何进行授权的相干知识。通过场景和代码相联合的方式，使得知识的学习更直观。
参考：
https://blog.csdn.net/qq_42826747/article/details/106203232
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]