[SQL]视图和权限

有时让用户看到整个基本表是不合适的，出于安全考虑，可能需要向用户隐藏特定的数据。如在instructor关系中隐藏工资值：

select ID,name,dept_name
from instructor;

创建更符合特定用户直觉的个人化的关系集合，如学生成绩表由学生关系和课程关系连接得到。

• 视图是从一个或几个基本表导出的表
• 视图是一个虚关系,数据库中只存放视图的定义而不存放视图对应的数据，其数据存放在导出视图的基本表中，在使用视图时通过执行查询计算出虚关系。
• 在概念上与基本表等同。视图一经定义，就可以和基本表一样被查询、被删除，也可以在视图上再定义视图。

视图的作用

1. 视图使用户能以多种角度看待同一数据
2. 视图能够对机密数据提供安全保护
3. 视图能够简化用户的操作
4. 适当的利用视图可以更清晰地表达查询

视图定义

• 语句格式

CREATE VIEW <视图名> [(<列名> [， <列名>]…)]
	AS <子查询>

• 组成视图的属性列名：全部省略或全部指定

• • 如果省略了视图的各个属性列名，则隐含该视图由子查询SELECT子句目标列中的诸字段组成
  • 三种情况必须指定所有列名：目标列是聚集函数或列表达式；多表连接时出现同名列作为视图的属性；需要在视图中为某个列更改名字

• 子查询可以是任何合法地查询表达式

• RDBMS执行CREATE VIEW语句时只是把视图定义存入数据字典，并不执行其中的SELECT语句。
• 在对视图查询时，按视图的定义从基本表中将数据查出。

删除视图

语句的格式:

DROP VIEW <视图名>;

• 该语句从数据字典中删除指定的视图定义
• 如果该视图上还导出了其他视图,使用CASCADE级联删除语句,把该视图和由它导出的所有视图一起删除
• 删除基本表时,由该基本表导出的所有视图定义都必须显式地使用DROP VIEW语句删除.

视图定义示例

创建一个视图，它包含除salary外的教师属性

create view faculty as
select ID, name, dept_name
from instructor;

利用show tables;即可查询

• 创建一个视图，它列出Physics系在2017年秋季学期所开设的所有课程段，以及每个课程段在哪栋建筑的哪个房间授课的信息

create view physics_fall_2017 as
select course.course_id, sec_id, building, room_number
from course, section
where course.course_id = section.course_id
and course.dept_name = ’Physics’
and section.semester = ’Fall’
and section.year = 2017;

视图上再定义视图

创建一个视图，它列出2017年秋季学期Physics系在Watson建筑所开设的课程信息

create view physics_fall_2017_watson as
select course_id, room_number
from physics_fall_2017
where building= 'Watson';

等价于

create view physics_fall_2017_watson as
select course_id, room_number
from (select course.course_id, building, room_number
from course, section
where course.course_id = section.course_id
and course.dept_name = 'Physics'
and section.semester = 'Fall'
and section.year = 2017)
where building= 'Watson';

视图的属性名自定义给出

创建一个视图,它给出每个系中教师的工资总和

create view departments_total_salary(dept_name, total_salary) as
select dept_name, sum(salary)
from instructor
group by dept_name

视图删除示例

• 删除视图faculty

• • drop view faculty;

• 删除视图physics_fall_2017

• • drop view physics_fall_2017;
  • drop view physics_fall_2017 cascade;

cascade代表删除该视图的同时删除其他和该视图有关系的视图

SQL查询中使用视图

• 用户角度:查询视图与查询基本表相同
• RDBMS实现视图查询的方法:视图消解法

• • 进行有效性检查
  • 转换成等价的对基本表的查询
  • 执行修正后的查询

使用视图physics_fall_2017，找到所有于2017年秋季学期在Watson大楼开设的physics课程

select course_id
from physics_fall_2017
where building= 'Watson';

视图消解转换后的查询语句

select course.course_id
from course, section
where course.course_id = section.course_id
	and course.dept_name = 'Physics'
	and section.semester = 'Fall'
	and section.year = 2017
	and building= 'Watson';

物化视图

特定数据库系统（如： Oracle）允许存储视图，即物化视图。

需要使用到物化视图的情况:

1. 频繁使用的视图
2. 需要快速响应基于大关系上聚集计算的特定查询

物化视图的维护

1. 构成视图定义的任何关系更新时,可以马上进行视图维护
2. 在视图被访问时才执行视图维护
3. 周期性的更新物化视图.

视图更新

视图的更新、插入或删除可能带来严重的后果

向视图faculty中插入新教师的记录

insert into faculty
values ('30765', 'Green', 'Music');

• 处理方式

• • 拒绝插入,并向用户返回一个错误信息
  • 向instructor关系插入元组('30765','Green','Music',null)

考虑视图:大学里每个教师的ID,name和建筑名

create view instructor_info as
	select ID,name,building
	from instructor,department
	where instructor.dept_name = department.dept_name;

假设没有ID号为69987的教师，也没有位于Taylor大楼的系，向视图instructor_info中插入记录

insert into instructor_info
values ('69987', 'White', 'Taylor');

• 唯一可能的处理方式：向instructor中插入元组

• • (’69987’, ’White’, null, null)，并向department中插入元组(null, ’Taylor’, null)；

• 但是，视图instructor_info中仍然不包含元组(’69987’, ’White’, ’Taylor’)。

我们这里进行修改的是拥有两个base table 的视图,所以不能更改

SQL数据控制

创建用户

方法一： create user用于创建用户，并在权限表中创建一条记录。

create user user_name [identified by [password]]

方法二：向权限表中插入一条记录

insert into mysql.user (host, user, password)
values (host_name, user_name, password);

创建一个新用户，用户名为Amit，密码为123

create user Amit identified by '123';
insert into mysql.user (host, user, password)
values ('%', 'Amit', password('123'));

删除用户

• 方法一:drop user删除用户

drop user user_name;

• 方法二:从权限表中删除给定用户名的记录

delete from mysql.user
where user = user_name;

数据操作权限

select、 insert、 update和delete权限， all privileges是所有权限的简写

• Select权限用于读取关系中的元组
• Insert权限允许用户往关系中插入元组
• Update权限允许用户修改关系中的任意元组
• Delete权限允许用户从关系中删除元组。

• 一个创建了新关系的用户将自动被授予该关系上的所有权限。
• 当用户提交查询或更新时，基于该用户曾获得过的权限， SQL执行检查此查询或更新是否是授权过的。如果查询或更新没有经过授权，那么将被拒绝执行。

权限的授予与收回(grant)

• grant 语句用来授予权限

基本格式
grant<权限列表>
on<关系名或视图名>
to<用户或角色列表>;

权限授予示例

授予数据库用户Amit和Sam在department关系上的select权限

grant select on department to Amit, Sam;

授予用户Amit和Sam在department关系的budget属性上的更新权限

grant update (budget) on department
to Amit, Sam;

update和insert权限既可以在关系的所有属性上授予，又可以只在

某些属性上授予。

如果省略属性列表，则在关系的所有属性上授权。

权限的收回revoke

revoke语句收回权限

语句格式：
revoke <权限列表>
on <关系名或视图名>
from <用户或角色列表>;

收回用户Amit和Sam在department关系上的select权限

revoke select on department
from Amit, Sam;

收回用户Amit和Sam在department关系的budget属性上的更新权限

revoke update (budget) on department
from Amit, Sam;

创建用户以及授予权限示例

• 查看当前数据库的用户

创建新用户“Amit”和“Sam "

这里我在IDEA里登录Amit 的用户,是已经输入了密码123的

视图的授权的

考虑一位工作人员Amit ，他需要知道一个给定系（比如Biology系）里所有员工的工资，可以创建一个视图，并将视图上的查询权限授予该工作人员

create view Bio_instructor as
  (select *
  from instructor
  where dept_name = ’Biology’);
grant select on Bio_instructor to Amit;

创建视图的用户对视图的权限依赖定义视图的关系的权限

• 如果一个创建视图的用户在用来定义视图的关系上没有update权限，那么该用户不能得到视图上的update权限。
• 在Bio_instructor视图例子中，视图的创建者必须在instructor关系上具有select权限。

角色

• 角色并不对应于某个具体的用户，而是对于一类具有共同特征的用户的总称。

• 可以为一组具有相同权限的用户创建一个角色，以简化授权的过程。

• 任何可以授予给用户的权限都可以授予角色
• 给用户授予角色类似于给用户授权

create role instructor;
grant select on takes to instructor;

角色可以授予给用户，也可以授予给其他角色

create role dean;
grant dean to Amit;
grant instructor to dean;
grant dean to Sam;

一个用户或角色的权限包括：

• 直接授予用户/角色的所有权限
• 授予给用户/角色所拥有的角色的所有权限

权限的转移

默认方式下，被授予权限的用户/角色无权把得到的权限再授予给另外的用户/角色。

• 权限的转移是允许的。实现方式：在相应的grant命令后面附加with grant option子句。

授予Amit在department上的select权限，并且允许Amit将该权限授予给其他用户

grant select on department to Amit
with grant option;

一个对象（关系/视图/角色）的创建者拥有该对象上的所有权限，包括给其他用户授权的权限授予给其他用户

grant select on department to Amit
with grant option;

一个对象（关系/视图/角色）的创建者拥有该对象上的所有权限，包括给其他用户授权的权限。

权限授权图

考虑teaching数据库中teaches关系上update权限的授予，U1,….,U5是用户， DBA代表数据库管理员。

权限的级联收回

级联收回：从一个用户/角色那里收回权限可能导致其他用
户/角色也失去该权限，用cascade关键字实现级联收回。

关键字restrict可以防止级联收回。

大多数的数据库系统中，级联收回是默认行为。

收回用户Amit和Sam在department关系上的select权限，并且不做级联收回

revoke select on department from Amit,
Sam restrict;

收回用户U1在teaches关系上的update权限

revoke update on teaches from U1;

权限汇总

数据库的权限

表和视图的权限

存储过程和文件访问的权限

服务器管理的权限

• 对于普通数据库用户，授予查询、插入、更新、删除数据库中表数据的权限；

• 对于数据库开发人员，授予创建表、索引、视图、存储过程、存储函数…….等权限；

• 对于DBA，授予数据库的所有权限。

grant all privileges on *.* to dba with grant option; 

SQL灵活的授权机制

• DBA：拥有所有对象的所有权限

• • 不同的权限授予不同的用户

• 用户:拥有自己建立的对象的全部的操作权限

• • GRANT:授予其他用户

• 被授予的用户

• • "继续授权"许可:再授权

• 所有授予出去的权限在必要的时候又都可以用REVOKE语句来收回