MySQL中的用户管理

系列文章目录

MySQL常见的几种约束

MySQL中的函数

MySQL中的事务

MySQL中的视图

MySQL中的索引

---

---

前言

MySQL中可创建不同的用户，并分配不同的权限，保证MySQL中数据的安全性。MySQL8中提供了角色，是权限的集合，可以直接赋予用户以角色。用户管理主要包括用户管理、权限管理、安全管理内容。

---

一、用户管理

1、用户管理入门

MySQL用户主要包括两种：root用户和普通用户。root用户为超级管理员，拥有MySQL提供的所有权限，而普通用户的权限取决于该用户在创建时被赋予的权限有哪些。实际开发中很少直接使用root用户，权限过大，操作不当会具有很大的危险性。

MySQL中有一个自带数据库mysql，其中有多个和用户权限有关的数据库表。

• user表中存储了允许连接到服务器的用户信息以及全局级（适用于所有数据库）的权限信息。这是最关键的表；
• db表中存储了某个用户对相关数据库的权限（数据库级权限）信息；
• 表级权限表tables_priv，可以实现单张表的权限设置；
• 列级权限表columns_priv，可以实现单个字段的权限设计；

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';

• 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.权限管理语法

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

其中授予权限的语法为：

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

• 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、角色管理入门

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'  

---

总结

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