MySQL用户通过身份认证后，会进行权限的分配，分配权限是按照user表、db表、tables_priv表、columns_priv表的顺序依次进行验证。即先检查全局级权限表user，如果user表中对应的权限为Y，则此用户对所有数据库的权限都为Y，将不再检查db表、tables_priv表、columns_priv表；如果user表中对应的权限为N，则到数据库级权限表db中检查此用户对应的具体数据库的权限，如果得到db表中对应的权限为Y，将不再检查tables_priv表、columns_priv；如果db表中对应的权限为N，则检查表级权限表tables_priv中此数据库对应的具体表的权限，以此类推。

2、用户管理操作及示例

主要操作包括创建用户、修改用户、删除用户、查询用户。每个新建的用户都会在user表中有对应的记录。

其中创建用户的语法为：

CREATE USER 'username'@'host' IDENTIFIED BY 'password';
1

username：你将创建的用户名
host：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%；还可以指定的ip地址范围
password：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器

在MySQL5.7之前，user表中还有一个名为password的字段用于存储用户的密码，但是在MySQL5.7之后，密码存储authentication_string字段中。

用户操作示例：

-- 查询用户列表  
use mysql  
select * from user  
-- 所有的用户信息保存在mysql数据库的user数据库表中  
  
-- 创建用户  
create user 'zhangsan' identified by 'zhangsan'  
create user 'lisi'@'localhost' identified by 'lisi'  
create user lisi@localhost identified by 'lisi'   
-- %:代表可以在所有主机（包括当前主机）登录数据库  
-- localhost  表示只可以在本地登录（客户端和服务器在同一台电脑上）
-- 也可以指定具体的ip或者ip范围
 
-- 修改用户  
-- 无法使用navicat连接数据库，需要修改密码验证插件  
alter user'zhangsan'@'%' identified with mysql_native_password by 'zhangsan'
-- 修改用户密码  
-- 方法1 alter user  
alter user 'zhangsan'@'%' identified  by 'zhangsan2'  
-- 方法2：set password  
set password for 'zhangsan'@'%'  = 'zhangsan3'  
   
-- 删除用户  
drop user 'lisi'@'localhost'  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

二、权限管理

1.权限管理语法

MySQL通过权限管理机制可以给不同的用户授予不同的权限，从而确保数据库中数据的安全性。权限管理机制包括查看权限、授予权限以及收回权限。

其中授予权限的语法为：

grant privileges ON dbname.tablename TO 'username'@'host'
1

privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL。新创建的用户默认权限为USAGE，除了可以登录数据库外，没有任何权限
databasename：数据库名
tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用*表示

2.权限操作示例

-- 查询权限  
-- 查询当前用户权限  
show grants   
-- 查询指定用户的权限  
show grants for 'zhangsan'@'%'  
  
--  特殊权限  
-- USAGE  :只有登录权限  
-- ALL :所有权限  
	  
-- 授予权限和撤销权限1  
grant  all  on mydb.*  to  'zhangsan'@'%'  
revoke all  privileges  on mydb.*  from  'zhangsan'@'%'  
  
-- 授予权限和撤销权限2  
grant  select,delete,update,insert on mydb.dept  to 'zhangsan'@'%'  
revoke delete on mydb.dept  from 'zhangsan'@'%'  
  
-- 刷新权限  
flush  privileges;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

三、角色管理

1、角色管理入门

MySQL 数据库中通常都会出现多个拥有相同权限集合的用户，在之前版本中只有分别向多个用户授予和撤销权限才能实现单独更改每个用户的权限。在用户数量比较多的时候，这样的操作是非常耗时的。

MySQL 8.0 为了用户权限管理更容易，提供了一个角色管理的新功能。角色是指定的权限集合，和用户帐户一样可以对角色进行权限的授予和撤消。如果用户被授予角色权限，则该用户拥有该角色的权限。

MySQL 8.0 提供的角色管理功能如下：

CREATE ROLE 角色创建
DROP ROLE 角色删除
GRANT 为用户和角色分配权限
REVOKE 为用户和角色撤销权限
SHOW GRANTS 显示用户和角色的权限
SET DEFAULT ROLE 指定哪些帐户角色默认处于活动状态
SET ROLE 更改当前会话中的活动角色
CURRENT_ROLE() 显示当前会话中的活动角色

2、角色操作示例

-- 创建角色  
create role 'dev_read','dev_write'  
 
-- 给角色分配权限  
grant select on mydb.* to 'dev_read';  
grant update,insert,delete on mydb.* to 'dev_write';  
	  
-- 创建用户  
create user 'user1'@'localhost' identified by 'user1';  
create user 'user2'@'localhost' identified by 'user2';  
create user 'user3'@'localhost' identified by 'user3';  
  
-- 给用户分配角色  
grant dev_read to 'user1'@'localhost'  
grant dev_read to 'user2'@'localhost'  
grant dev_write to 'user3'@'localhost'  
 
-- 激活角色，设置用户帐户连接到数据库服务器时指定哪些角色应该处于活动状态
set default role all to 'user1'@'localhost','user2'@'localhost'  ,'user3'@'localhost'    
 
-- 给用户撤销角色  
revoke select on mydb.* from 'dev_read';  
revoke update,insert,delete on mydb.* from 'dev_write'  
  
-- 删除角色  
drop role 'dev_read';  
drop role 'dev_write'  
 
-- 删除用户  
drop user  'user1'@'localhost','user2'@'localhost'  ,'user3'@'localhost'  

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

总结

MySQL中的用户管理是非常重要的一步，它可以帮助我们控制和管理数据库的访问权限，保证数据的安全性和完整性。在实际应用中，我们需要根据实际情况来设置用户的访问权限，并定期检查和更新用户的权限信息。

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/一键难忘520/article/detail/841520