文章目录
- 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)