数据库的⽤户和权限管理

应⽤场景

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

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

查察⽤户

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

1. # 选择数据库
2. use mysql;
4. # 查看所有的表
5. show tables;
7. # 查看表结构
8. desc user;
10. #查询user表中的记录
11. select host, user, authentication_string from user;

复制代码

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

语法

1. 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中已弃⽤，以后大概会删除
  

示例

1. # 添加一个名为star的新用户，允许从本机登录
2. create user 'star'@'localhost' identified by '123456';
4. # 添加一个名为star1的新用户，允许从192.168.1.1/24网段登录
5. mysql> create user 'star1'@'192.168.1.1/24' identified by '123456';
7. # 查询，添加成功
8. select host, user, authentication_string from user\G;

复制代码

1. # 使用新用户登录
2. mysql -ustar -p

复制代码

修改密码

语法

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

复制代码

示例

1. # 以root身份登录，为'star'@'localhos
2. ALTER USER 'star'@'localhost' IDENTIFIED BY '987654';
4. # 以root身份登录，为'star'@'localhost'用户重置密码
5. SET PASSWORD FOR 'star'@'localhost' = '123456';
7. # 以star登录，并确认当前登录用户，USER()与CURRENT_USER()是同义词
8. select USER();
10. # 修改当前登录用户的密码
11. SET PASSWORD = '111111';

复制代码

删除⽤户

语法

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

复制代码

1. # 删除用户'star1'@'192.168.1.1/24'
2. drop user 'star1'@'192.168.1.1/24';
4. # 删除成功
5. select host, user, authentication_string from user;

复制代码

权限与授权

• MySQL内置⽀持的权限列表

给⽤户授权

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

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

复制代码

语法

1. grant priv_type[, priv_type ...] on priv_level
2.         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 权限

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

复制代码

1. # 查看数据库，可以看到java03
2. show databases;
4. # 选择数据库
5. use java03;
7. # 查看所有表
8. show tables;
10. # 查询数据
11. select * from student;
13. # 写入一条数据时失败，因为没有授权写入权限
14. insert into student values (null, '500001' ,'test', NULL, 1);

复制代码

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

1. show grants for 'star'@'localhost';

复制代码

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

1. # ALL为所有权限
2. grant ALL on java03.* to 'star'@'localhost';
4. # 查看用户权限
5. show grants for 'star'@'localhost';

复制代码

1. # 插入一条新记录
2. insert into student values (null, '500001' ,'test', NULL, 1);
4. # 不成功说明权限没有生效，需要刷新权限，如果还不成功可以重新登录
5. flush privileges;
7. # 重新执行插入操作
8. insert into student values (null, '500001' ,'test', NULL, 1);
10. # 查看记录
11. select * from student;
13. # 删除操作
14. delete from student where student_id = 9;
16. # 删除成功
17. select * from student;

复制代码

回收权限

语法

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

复制代码

⽰例

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

1. REVOKE all on *.* from 'star'@'localhost';
3. # 刷新权限
4. flush privileges;
6. # 查看权限，没有任何授权
7. show grants for 'satr'@'localhost';

复制代码

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

复制代码

---

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

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

继续阅读请点击广告