数据库系统实验 (25).pdf

《数据库系统实验 (25).pdf》由会员分享，可在线阅读，更多相关《数据库系统实验 (25).pdf（18页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、实验（第实验（第十十周）周）视图和访问控制视图和访问控制 1. 实验目的实验目的 (1) 了解视图的概念； (2) 学习创建视图、查看视图、更新视图和删除视图的方法； (3) 了解组角色、角色、用户的概念及他们之间的区别； (4) 了解数据库的不同权限；掌握为用户分配权限及收回权限的方法。 2. 实验环实验环境境（写清硬件配置和软件版本） (1) 硬件： 设备：DESKTOP-H21G86S (2) 操作系统：Windows10 专业版 (3) 数据库管理系统：pgAdmin4 postgreSQL12 (4) 其它软件：无 3. 实验内容实验内容 (1) 创建视图：分别在单表上和多表上创建。

2、 (2) 查看视图； (3) 更新视图； (4) 删除视图； (5) 创建用户并赋予用户不同的权限； (6) 收回用户权限。 4. 实验数据实验数据 医院数据库包括医生表 doctor，医生表包含医生工号（doc_id） ，医生姓名 （doc_name） ，医生性别（doc_sex） ，医生年龄（doc_age） ，医生所在科室 （doc_dep） ；科室表 department，科室表包含科室编号（dep_id） ，科室名称 （dep_name） ，科室地址（dep_addr） 。 5. 实验作业实验作业 (1) 创建数据表 doctor。 属性 类型 长度 是否为主键 doc_id int

3、 是 doc_name varchar 20 否 doc_sex varchar 20 否 doc_age int 否 doc_dep varchar 20 否 说明：创建表的语句为 create table 表名（属性名 属性数据类型，属性名 属 性数据类型） create table doctor( doc_id int primary key, doc_name varchar (20), doc_sex varchar (20), doc_age int, doc_dep varchar (20) ) 说明：在提示创建成功后，刷新可以看到 Tables 下有我们新建的表，右击 doct

4、or 选择 Properties 查看表的属性 说明：可以看到表的属性，数据类型与题目一致，且 doc_id 为该表的主键 (2) 向 doctor 表中插入数据。 doc_id doc_name doc_sex doc_age doc_dep 1 aa male 35 aaaa 2 bb female 32 bbbb 3 cc male 42 cccc 4 dd female 41 dddd 说明：向已存在表中插入数据的语句为 insert into 表名 values (各属性值),() insert into doctor values (1,aa,male,35,aaaa), (2,b

5、b,female,32,bbbb), (3,cc,male,42,cccc), (4,dd,female,41,dddd); 说明：在提示数据插入成功后，右击 doctor 选择 View/Edit Data 选择 All Rows 查看表 中所有数据 说明：可以看到，表中数据插入成功且与要求插入数据一致 (3) 创建数据表 department。 属性 类型 长度 是否为主键 dep_id int 是 dep_name varchar 20 否 dep_addr varchar 20 否 说明：与创建表 doctor 类似 create table department( dep_id in

6、t primary key, dep_name varchar (20), dep_addr varchar (20) ) 说明：提示创建成功后用同样的方法查看表的结构，与题目要求一致 (4) 向 department 表中插入数据。 dep_id dep_name dep_addr 1 aaaa floor 1 2 bbbb floor 2 3 cccc floor 2 4 dddd floor 3 说明：与向表 doctor 中插入数据方法一样 insert into department values (1,aaaa,floor 1), (2,bbbb,floor 2), (3,cccc

7、,floor 3), (4,dddd,floor 4); 说明：提示插入成功后，用同样的方法查看表 department 中的数据，数据与题目要 求一致 (5) 在 doctor 表上创建一个名为 doc_view 的视图，视图只包括 doc_id 和 doc_name 两 个字段，并给这两个字段取名为 doid 和 doname；创建完后，查看该视图。 说明：视图可以理解为一个虚表，创建视图的语句为 create view 视图名(属性名) as select create view doc_view(doid,doname) as select doc_id,doc_name from d

8、octor; 说明：提示创建成功后我们使用“select * from doc_view”来查看该视图 (6) 创建视图 dd_view，视图包括医生工号、医生姓名、医生所在科室和科室的地址； 创建完后，查看该视图。 说明：基于多表查询的视图需要标明 select 的属性来自于哪个表，必要时还需要加 入 where 条件判断等子语句 create view dd_view(docid,docname,depname,depaddr) as select doctor.doc_id,doctor.doc_name,doctor.doc_dep,department.dep_addr from d

9、octor,department where doctor.doc_dep = department.dep_name; 说明：提示创建成功后我们使用“select * from dd_view”来查看该视图 (7) a) 在 doctor 表上创建一个名为 doc_view2 的视图，要求只显示女性医生的信息； 向该视图中插入数据 doc_id=5,doc_name=ee，观察能否插入成功并展示执行 后视图 doc_view2 和表 doctor 内的数据； create view doc_view2 as select * from doctor where doc_sex=female;

10、 说明：先如上题操作创建一个视图 doc_view2，使用“select * from doc_view2; ”查看该视图，再尝试向该视图中插入数据 insert into doc_view2(doc_id,doc_name) values (5,ee); select * from doc_view2 select * from doctor 说明：向视图中插入数据成功了，查看视图，并没有我们刚刚插入的数据；再 查看原表，出现了我们新插入的数据 b) 若在创建视图时加上 with check option 的约束呢？还能成功插入吗？请说明原 因（说明：新建视图 doc_view3，插入数据改

11、为 doc_id=6,doc_name=ff，其余 不变） 。 说明：创建视图语句不变，在最后加上 with check option 即可 create view doc_view3 as select * from doctor where doc_sex=female with check option; 说明：在带检查的视图创建成功后，“insert into doc_view3(doc_id,doc_name) values (6,ff);”尝试向视图中插入数据 插入失败，原因是违反了视图的检查选项，也就是性别为女这个条件 (8) 删除所有视图。 说明：在 Views 目录下查看当前

12、已经创建的视图 使用“drop view if exists dd_view,doc_view,doc_view2,doc_view3”将所有视图删除 说明：可以看到，语句执行完毕后，Views 下为空 (9) 创建用户 user1，密码为12345。 说明：创建用户的语句为 create user 用户名 password 密码 create user user1 password 12345; (10) 授予用户 user1 对 doctor 表的查询权限，并进行验证。 说明：对用户进行授权的语句为 grant 权限 to 用户名 grant select on table doctor

13、to user1; 注意注意，使用超级用户 postgres 进行授权 说明：新建一个 user1 的服务器，步骤如下，右击一个已存在的服务器，选择 CreateServer 出现创建的选项后，给服务器命名为“User1”，Host name/address 改为 localhost，填 写用户名和密码后创建 说明：postgres 授权成功后，新建一个服务器 user1，使用该服务器进行对表的查 询，结果如下，说明 user1 拥有查询表 doctor 的权力 (11) 收回用户 user1 对 doctor 表的查询权限，并进行验证。 说明：回收权限的语句为：revoke 权限 from

14、用户名 revoke select on table doctor from user1; 说明：提示收回成功后，在 user1 的服务器上查询表，提示权限不够，收回成功 (12) a) 创建用户 user2，密码为12345，授予其对 doctor 表的查询权限和修改字段 doc_name 的权限，并允许将此权限授予其他用户；验证 user2 对 doctor 表的查 询和更新权限（将 doc_id 为 1 的医生姓名更新为xxx） ； 说明：创建用户和授权方法不做赘述，语句如下 create user user2 password 12345 ; grant select,update o

15、n table doctor to user2 with grant option; 说明：执行成功后，先创建一个新的服务器，在新服务器中向表先修改数据再 进行查询 update doctor set doc_name=xxx where doc_id=1; select *from doctor; 说明：可以看到，数据被成功修改 b) 创建用户 user3，密码为12345，授予其对 doctor 表的查询权限，并允许将此 权限授予其他用户；验证 user3 对 doctor 表的查询权限； 说明：依次进行创建用户，赋权限，新建服务器 create user user3 password 1

16、2345 ; grant select on table doctor to user3 with grant option; 说明：在新的服务器 user3 中查询表，检验其权限 select * from doctor; 如下图所示，成功查询 c) 创建用户 user4，密码为12345，验证 user4 对 doctor 表的查询权限； 说明：创建用户，不进行赋权，新建服务器 user4 create user user4 password 12345 ; 说明：select * from doctor;查询失败，权限不够 d) 用户 user2 将 doctor 表的查询权限授予 us

17、er4，验证 user4 对 doctor 表的查询权 限； 说明：用户 user2 对 user4 赋权 grant select on table doctor to user4; 说明：select * from doctor;赋权成功后查询成功 e) 用户 user3 将 doctor 表的查询权限授予 user4，验证 user4 对 doctor 表的查询权 限； 说明：用户 user3 对 user4 赋权 grant select on table doctor to user4; 赋权成功后查询表，检测权限 select * from doctor; f) 用户 user3

18、撤销 user4 对 doctor 表的查询权限，验证 user4 对 doctor 表是否还有 查询权限； 说明：在服务器 user3 上执行收回权限的语句，之后在服务器 user4 上执行查 询语句 revoke select on table doctor from user4; select * from doctor; 说明：依然可以查询成功，因为 user4 还拥有 user2 对其赋予的查询表的权限 g) 用户 user2 撤销 user4 对 doctor 表的查询权限，验证 user4 对 doctor 表是否还有 查询权限。 说明：在服务器 user2 上执行收回权限的语句，之后在服务器 user4 上执行查 询语句 revoke select on table doctor from user4; select * from doctor; 说明：如下图，查询失败，权限不够 分析原因如下 因为之前 user3 已经收回权限了，此时 user4 只拥有 user2 对其赋予的权限，而 当 user2 再次回收后，user4 不具备任何权限，故而查询失败