文章目录
- MySQL
- 1. 用户管理
- 1.1 用户
- 1.1.1 用户信息
- 1.1.2 创建用户
- 1.1.3 删除用户
- 1.1.4 修改用户密码
- 1.2 数据库的权限
- 1.2.1 给用户权限
- 1.2.2 回收权限
MySQL
1. 用户管理
为什么MySQL要引入用户管理？
如果我们只能使用root用户，这样存在安全隐患。因为root 用户拥有最高权限，一旦其账号密码被泄露或被攻击者获取，整个数据库将面临巨大风险，而且 root 用户权限过大，在日常操作中可能会因为误操作而引发严重问题。
为了降低这些风险，保障数据库的安全和稳定，就需要使用MySQL的用户管理。
MySQL用户管理是什么？
MySQL 用户管理 是对 MySQL 数据库中用户账号的创建、配置、修改和删除等操作的综合管理。
通过使用MySQL用户管理我们可以很好的保障数据安全，提高系统稳定性，满足合规要求，优化资源分配，责任追溯，提升用户体验。
1.1 用户
1.1.1 用户信息
MySQL中的用户，都存储在系统数据库mysql的user表中：
mysql> use mysql;
Database changedmysql> select host,user,authentication_string from user;
+-----------+------------------+------------------------------------------------------------------------+
| host | user | authentication_string |
+-----------+------------------+------------------------------------------------------------------------+ |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | root | *01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C |
+-----------+------------------+------------------------------------------------------------------------+
5 rows in set (0.00 sec)
--可以通过desc user初步查看一下表结构
字段解释：
host： 表示这个用户可以从哪个主机登陆，如果是localhost，表示只能从本机登陆
user： 用户名
authentication_string： 用户密码通过password函数加密后的
*_priv： 用户拥有的权限
1.1.2 创建用户
语法：create user '用户名'@'登陆主机/ip' identified by '密码';
案例：
mysql> create user 'clover'@'localhost' identified by 'clover123';
Query OK, 0 rows affected (0.06 sec)mysql> select host,user,authentication_string from user;
+-----------+------------------+------------------------------------------------------------------------+
| host | user | authentication_string |
+-----------+------------------+------------------------------------------------------------------------+
| localhost | clover | *664D2BC607BAA85C7DCA8E655CAB8BCE97951607 |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | root | *01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C |
+-----------+------------------+------------------------------------------------------------------------+
5 rows in set (0.00 sec)-- 此时便可以使用新账号新密码进行登陆啦
--备注：可能实际在设置密码的时候，因为mysql本身的认证等级比较高，一些简单的密码无法设置，会爆出如下报错：
-- ERROR 1819 (HY000): Your password does not satisfy the current policy requirements-- 解决方案：https://blog.csdn.net/zhanaolu4821/article/details/93622812
--查看密码设置相关要求：SHOW VARIABLES LIKE 'validate_password%';
--关于新增用户这里，需要大家注意，不要轻易添加一个可以从任意地方登陆的user。
1.1.3 删除用户
语法：drop user '用户名'@'主机名'
示例：
mysql> select host,user,authentication_string from user;
+-----------+------------------+------------------------------------------------------------------------+
| host | user | authentication_string |
+-----------+------------------+------------------------------------------------------------------------+
| localhost | clover | *664D2BC607BAA85C7DCA8E655CAB8BCE97951607 |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | root | *01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C |
+-----------+------------------+------------------------------------------------------------------------+
5 rows in set (0.00 sec)mysql> drop user clover;
ERROR 1396 (HY000): Operation DROP USER failed for 'clover'@'%'
--直接给个用户名，不能删除，它默认是%，表示所有地方可以登陆的用户mysql> drop user 'clover'@'localhost'; --删除用户
Query OK, 0 rows affected (0.00 sec)mysql> select user,host,authentication_string from user;
+------------------+-----------+------------------------------------------------------------------------+
| user | host | authentication_string |
+------------------+-----------+------------------------------------------------------------------------+
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root | localhost | *01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C |
+------------------+-----------+------------------------------------------------------------------------+
4 rows in set (0.00 sec)
1.1.4 修改用户密码
语法：
自己改自己密码：set password=password('新的密码');
root用户修改指定用户的密码：set password for '用户名'@'主机名'=password('新的密码')
上面适用于MySQL较低的版本，MySQL 8.0 及更高版本中使用这个：alter user 'clover'@'localhost' identified by '123456';
mysql> create user 'clover'@'localhost' identified by 'clover';
Query OK, 0 rows affected (0.01 sec)mysql> select user,host,authentication_string from user;
+------------------+-----------+------------------------------------------------------------------------+
| user | host | authentication_string |
+------------------+-----------+------------------------------------------------------------------------+
| clover | localhost | *93DC66DF002C44E794BD2E91B6A54696D2D7CDB1 |
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root | localhost | *01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C |
+------------------+-----------+------------------------------------------------------------------------+
5 rows in set (0.00 sec)mysql> ALTER USER 'clover'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.01 sec)mysql> select user,host,authentication_string from user;
+------------------+-----------+------------------------------------------------------------------------+
| user | host | authentication_string |
+------------------+-----------+------------------------------------------------------------------------+
| clover | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root | localhost | *01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C |
+------------------+-----------+------------------------------------------------------------------------+
5 rows in set (0.00 sec)
1.2 数据库的权限
MySQL数据库提供的权限列表：
1.2.1 给用户权限
刚创建的用户没有任何权限。需要给用户授权。
语法: grant 权限列表 on 库.对象名 to '用户名'@'登陆位置' [identified by '密码']
说明：权限列表，多个权限用逗号分开。
grant select on ...
grant select, delete, create on ....
grant all [privileges] on ... -- 表示赋予该用户在该对象上的所有权限
. : 代表本系统中的所有数据库的所有对象（表，视图，存储过程等）
库. * : 表示某个数据库中的所有数据对象 (表，视图，存储过程等)
identified by可选。 如果用户存在，赋予权限的同时修改密码，如果该用户不存在，就是创建用户。
案例：
--使用root账号
--终端A
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| myisam_test |
| mysql |
| performance_schema |
| scsc |
| spj |
| sys |
| t |
| test |
| test4 |
+--------------------+
10 rows in set (0.00 sec)mysql> use test;
Database changedmysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| sales |
| user1 |
+----------------+
2 rows in set (0.00 sec)--给用户clover赋予test数据库下所有文件的select权限
mysql> grant select on test.* to 'clover'@'localhost';
Query OK, 0 rows affected (0.01 sec)--使用clover账号
--终端B
C:\Users\huawei>mysql -u clover -p123456mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)--暂停等root用户给clover赋完权之后，在查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| performance_schema |
| test |
+--------------------+
3 rows in set (0.00 sec)mysql> use test;
Database changedmysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| sales |
| user1 |
+----------------+
2 rows in set (0.00 sec)mysql> select * from sales;
+--------+
| amount |
+--------+
| 100 |
| 200 |
| NULL |
| 300 |
| NULL |
+--------+
5 rows in set (0.00 sec)--没有删除权限
mysql> delete from sales;
ERROR 1142 (42000): DELETE command denied to user 'clover'@'localhost' for table 'sales'--备注：特定用户现有查看权限
mysql> show grants for 'clover'@'localhost';
+--------------------------------------------------+
| Grants for clover@localhost |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO `clover`@`localhost` |
| GRANT SELECT ON `test`.* TO `clover`@`localhost` |
+--------------------------------------------------+
2 rows in set (0.00 sec)mysql> show grants for 'root'@'%';
+-------------------------------------------------------------+
| Grants for root@% |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------+
1 row in set (0.00 sec)
注意：如果发现赋权限后，没有生效，执行如下指令：flush privileges;
1.2.2 回收权限
语法：revoke 权限列表 on 库.对象名 from '用户名'@'登陆位置'；
示例：
-- 回收clover对test数据库的所有权限
--root身份，终端A
mysql> revoke all on test.* from 'clover'@'localhost';
Query OK, 0 rows affected (0.01 sec)--clover身份，终端B
C:\Users\huawei>mysql -u clover -p123456-- 修改前
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| performance_schema |
| test |
+--------------------+
3 rows in set (0.00 sec)-- 修改后
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
2 rows in set (0.00 sec)
![[C++][opencv]基于opencv实现photoshop算法可选颜色调整](https://i-blog.csdnimg.cn/direct/e99672cffc4f4999b41ebd88fe91636b.gif)
![[图解]需要≠需求-《分析模式》漫谈](https://i-blog.csdnimg.cn/direct/1cc5a6af8e164ed7bd82dceb5cb71b23.png)
