从入门到精通【MySQL】视图与用户权限管理_mysql视图权限怎么设置
文章目录
📕1. 视图
✏️1.1 视图的基本概念
视图是一个虚拟的表，它是基于一个或多个基本表或其他视图的查询结果集。视图本身不存储数据，而是通过执行查询来动态生成数据。用户可以像操作普通表⼀样使用视图进行查询、更新和管理。视图本身并不占用物理存储空间，它仅仅是一个查询的逻辑表示，物理上它依赖于基础表中的数据。
✏️1.2 试图的基本操作
🔖1.2.1 创建视图
create view 列名1，列名2，列名3 as select 查询语句...view 是视图的关键字
🔖1.2.2 使用视图
🌰例如：只查询用户的姓名和总分(隐藏学号和各科成绩)
# 使⽤真实表进⾏查询select s.name, sum(sc.score) total from student s, score sc where s.id = sc.student_idgroup by sc.student_id order by s.id;-- 缺点：可以随时在select关键字后加上学号和各科成绩字段，会暴露学生信息，不安全# 创建视图create view v_student_total_points asselect s.id, s.name, sum(sc.score) total from student s, score sc where s.id = sc.student_id group by s.id order by s.id;-- 使用视图进行查询select * from v_student_total_points;+-----------+-------+| name | total |+-----------+-------+| 唐三藏 | 469 || 孙悟空 | 179.5 || 猪悟能 | 200 || 沙悟净 | 218 || 宋江 | 118 || 武松 | 178 || 李逹 | 172 |+-----------+-------+-- 只能查询出姓名和总分，进一步保护了学生的个人信息🌰例如：视图和真实表进行表连接查询
select * from v_student_total_points v, student s where v.id = s.id;-- 视图本质上是一张虚拟的表，所以可以用视图与真实表进行表连接查询🔖1.2.3 修改数据
- 通过真实表修改数据，会影响视图
# 修改唐三藏的JAVA成绩为99分 update score set score = 99 where student_id = 1 and course_id = 1;# 查询视图，发现唐三藏这条记录已被修改select * from v_student_socre;- 通过视图修改数据会影响基表
# 更新视图update v_student_socre_v1 set score = 99 where score_id = 3;# 是看真实表数据已被修改select * from score where student_id = 1 and course_id = 5;注意事项：
1. 修改真实表会影响视图，修改视图同样也会影响真实表
2. 以下视图不可更新：
-------创建视图时使用聚合函数的视图
-------创建视图时使用 DISTINCT
-------创建视图时使用 GROUP BY 以及 HAVING子句
-------创建视图时使用 UNION 或 UNION ALL
-------查询列表中使用子查询
-------在FROM子句中引用不可更新视图
🔖1.2.4 删除视图
# 语法drop view 视图名;✏️1.3 视图的优点
- 简单性：视图可以将复杂的查询封装成一个简单的查询。例如，针对一个复杂的多表连接查询，可以创建一个视图，用户只需查询视图而无需了解底层的复杂逻辑。
- 安全性：通过视图，可以隐藏表中的敏感数据。例如，⼀个系统的用户表中，可以创建一个不包含密码列的视图，普通用户只能访问这个视图，而不能访问原始表，进一步保证了安全问题。
- 逻辑数据独立性：视图提供了一种逻辑数据独立性，即使底层表结构发生变化，只需修改视图定义，而无需修改依赖视图的应用程序。确保了应用程序与数据库的解耦
- 重命名列：视图允许用户重命名列名，以增强数据可读性。
📕2. 用户与权限管理
数据库服务安装成功后默认有一个root用户，可以新建和操纵数据库服务中管理的所有数据库。在真
实的使用过程中，通常每个应用对应着一个数据库，我们只希望某个用户只能操纵和管理当前应用对
应的那个数据库，而不能操纵和管理其他应用的数据库，这时就可以添加⼀个用户并指定用户的权限
如上图所示：
root 可以访问和操纵所有的数据库：DB1, DB2, DB3, DB4
普通用户1 只能访问和操纵数据库DB1
普通用户2 只能访问和操纵数据库DB3
只读用户1 只能访问数据库DB3
只读用户2 只能访问数据库DB4
✏️2.1 用户
🔖2.1.1 查看用户
-- 选择数据库use mysql;-- 查看表结构desc user;-- 查看用户表select * from user;
host： 允许登录的主机，相当于白名单，如果是localhost，表示只能从本机登陆
user： 用户名
*_priv： 用户拥有的权限,Y表示有权限,N表示没有权限
authentication_string： 加密后的用户密码
🔖2.1.2 创建用户
create user if not exists \'user_name\'@\'host_name\' identified by \'auth_string\';user_name: 用户名，用单引号包裹，区分大小写
host_name: 主机或IP(段)，⽤单引号包裹
auth_string: 真实密码，有些密码策略不允许使用简单密码
例如:创建名为zhuxulong 密码为123456 的账户
create user if not exists \'zhuxulong\'@\'172.20.109.85\' identified by \'123456\';
注意事项:
- 如果不指定host_name相当于’user_name’@‘%’， %表示所有主机都可以连接到数据库，强烈建
议不要这样设置，因为会导致严重的安全问题 - user_name和host_name分别用单引号包裹，如果写成’user_name@host_name’, 相当于’user_name@host_name’@‘%’
- host_name可以通过子网掩码设置主机范围
A: 198.0.0.0 : A段网络中的任意一台主机
B: 198.51.0.0 B段网络中的任意一台主机’
C: 198.51.100.0 C段网络中的任意一台主机
D: 198.51.100.1 :只包含特定IP地址的主机
🔖2.1.3 修改密码
# 为指定⽤⼾设置密码 【推荐】ALTER USER \'user_name\'@\'host_name\' IDENTIFIED BY \'auth_string\';# 为指定⽤⼾设置密码SET PASSWORD FOR \'user_name\'@\'host_name\' = \'auth_string\';# 为当前登录⽤⼾设置密码SET PASSWORD = \'auth_string\';🔖2.1.4 删除用户
DROP USER [IF EXISTS] \'user_name\'@\'host_name\'[, ...];✏️2.2 权限与授权
MySQL内置支持的权限列表
🔖2.2.1 给用户授权
刚刚创建的用户没有任何权限,我们需要手动为新用户授权.
grant 权限名 on priv_level to \'user_name\'@\'host_name\' [WITH GRANT OPTION]权限名：根据类型，参考根据列表4.1中的Privilege列
priv_level： * | . | db_name.* | db_name.tbl_name | tbl_name，比如*.*表示所有数据库下的所有表
‘user_name’@‘host_name’：指定用户
[WITH GRANT OPTION]：可选，允许用户将自己的权限授权给其它用户
示例: 为刚刚创建的zhuxulong用户授予test数据库student表的select权限
grant select on test.student to \'zhuxulong\'@\'172.20.109.85\';🔖2.2.2 回收用户授权
revoke 权限名 on 数据库名.表名 from \'user_name\'@\'host_name\';
