数据库的⽤户和权限管理

应⽤场景

数据库服务安装成功后默认有⼀个root⽤户，可以新建和操纵数据库服务中管理的所有数据库。在真实的使⽤过程中，通常每个应⽤对应着⼀个数据库，我们只希望某个⽤户只能操纵和管理当前应⽤对应的那个数据库，⽽不能操纵和管理其他应⽤的数据库，这时就可以添加⼀个⽤户并指定⽤户的权限。

如上图所示：
• root 可以访问和操纵所有的数据库：DB1, DB2, DB3, DB4
• 普通⽤户1 只能访问和操纵数据库DB1
• 普通⽤户2 只能访问和操纵数据库DB3
• 只读⽤户1 只能访问数据库DB3
• 只读⽤户2 只能访问数据库DB4

⽤户

查看⽤户

MySQL的⽤户信息保存在mysql系统数据库的user表中，可以通过Select语句查看，如下所⽰：

# 选择数据库
use mysql;

# 查看所有的表
show tables;

# 查看表结构
desc user;

#查询user表中的记录
select host, user, authentication_string from user;

• host： 允许登录的主机，相当于⽩名单，如果是localhost，表⽰只能从本机登陆
• user： ⽤户名
• *_priv： ⽤户拥有的权限
• authentication_string： 加密后的⽤户密码

创建⽤户

语法

CREATE USER [IF NOT EXISTS] 'user_name'@'host_name' IDENTIFIED BY 'auth_string';

user_name: ⽤户名，⽤单引号包裹，区分⼤⼩写
host_name: 主机或IP(段)，⽤单引号包裹
auth_string: 真实密码，有些密码策略不允许使⽤简单密码

注意事项

• 如果不指定host_name相当于’user_name’@‘%’， %表⽰所有主机都可以连接到数据库，强烈建议不要这样设置，因为会导致严重的安全问题
• user_name和host_name分别⽤单引号包裹，如果写成’user_name@host_name’，相当于’user_name@host_name’@‘%’
• host_name可以通过⼦⽹掩码设置主机范围
• • 198.0.0.0/255.0.0.0 : A段⽹络中的任意⼀台主机
• • 198.51.0.0/255.255.0.0: 198.51 B段⽹络中的任意⼀台主机
• • 198.51.100.0/255.255.255.0: 198.51.100 C段⽹络中的任意⼀台主机
• • 198.51.100.1 :只包含特定IP地址的主机
• 从MySQL 8.0.23开始，指定为IPv4地址的主机值可以使⽤CIDR表⽰法写⼊，例198.51.100.44/24
• 允许在IP地址中使⽤%通配符，⽐如，主机值’%'匹配任何主机名， 198.51.100.% 匹配198.51.100 C段⽹络中的任何主机。MySQL 8.0.35中已弃⽤，以后可能会删除

示例

# 添加一个名为star的新用户，允许从本机登录
create user 'star'@'localhost' identified by '123456';

# 添加一个名为star1的新用户，允许从192.168.1.1/24网段登录
mysql> create user 'star1'@'192.168.1.1/24' identified by '123456';

# 查询，添加成功
select host, user, authentication_string from user\G;

# 使用新用户登录
mysql -ustar -p

修改密码

语法

# 为指定用户设置密码 【推荐】
ALTER USER 'user_name'@'host_name' IDENTIFIED BY 'auth_string';
# 为指定用户设置密码
SET PASSWORD FOR 'user_name'@'host_name' = 'auth_string';
# 为当前登录用户设置密码
SET PASSWORD = 'auth_string';

示例

# 以root身份登录，为'star'@'localhos
ALTER USER 'star'@'localhost' IDENTIFIED BY '987654';

# 以root身份登录，为'star'@'localhost'用户重置密码
SET PASSWORD FOR 'star'@'localhost' = '123456';

# 以star登录，并确认当前登录用户，USER()与CURRENT_USER()是同义词
select USER();

# 修改当前登录用户的密码
 SET PASSWORD = '111111';

删除⽤户

语法

DROP USER [IF EXISTS] 'user_name'@'host_name'[, ...];

# 删除用户'star1'@'192.168.1.1/24'
drop user 'star1'@'192.168.1.1/24';

# 删除成功
select host, user, authentication_string from user;

权限与授权

• MySQL内置⽀持的权限列表

给⽤户授权

刚创建的⽤户没有任何权限，需要给他授权，如下所⽰：

select user();
 
# 看不到其他的数据库
show databases;

语法

grant priv_type[, priv_type ...] on priv_level
	to 'user_name'@'host_name' [WITH GRANT OPTION]

• priv_type：根据类型，参考根据列表4.1中的Privilege列
• priv_level： * |* . * | db_name.* | db_name.tbl_name | tbl_name，⽐如*.*表⽰所有数据库下的所有表
• ‘user_name’@‘host_name’：指定⽤户
• [WITH GRANT OPTION]：可选，允许⽤户将⾃⼰的权限授权给其它⽤户

⽰例

• 为star@localhost⽤户授权于 java03 数据库的 select 权限

# 授权
grant select on java03.* to 'star'@'localhost';

# 查看数据库，可以看到java03
show databases;

# 选择数据库
use java03;

# 查看所有表
show tables;

# 查询数据
select * from student;

# 写入一条数据时失败，因为没有授权写入权限
insert into student values (null, '500001' ,'test', NULL, 1);

• 查看star@localhost⽤户权限， USAGE 表⽰在指定的priv_level上没有权限

show grants for 'star'@'localhost';

• 为star@localhost⽤户授权于java03数据库的所有权限

# ALL为所有权限
grant ALL on java03.* to 'star'@'localhost';

# 查看用户权限
show grants for 'star'@'localhost';

# 插入一条新记录
insert into student values (null, '500001' ,'test', NULL, 1);

# 不成功说明权限没有生效，需要刷新权限，如果还不成功可以重新登录
flush privileges;

# 重新执行插入操作
insert into student values (null, '500001' ,'test', NULL, 1);

# 查看记录
select * from student;

# 删除操作
delete from student where student_id = 9;

# 删除成功
select * from student;

回收权限

语法

REVOKE [IF EXISTS] priv_type[, priv_type] ... ON priv_level
	FROM 'user_name'@'host_name' [, 'user_name'@'host_name'] ...

⽰例

• 回收star@localhost⽤户对于 java03 数据库的权限

REVOKE all on *.* from 'star'@'localhost';

# 刷新权限
flush privileges;

# 查看权限，没有任何授权
show grants for 'satr'@'localhost';

# 查看所有数据库，没有可以访问的库
show databases;

关于数据库的⽤户和权限管理先了解到这里，希望这篇文章对大家有帮助，谢谢大家的阅读！！！