实验三：自主存取控制实验

hellenionia

于 2022-12-25 11:20:26 发布

阅读量724

点赞数 2

分类专栏：【数据库原理】数据库系统概论（第五版）文章标签：数据库 oracle sql

本文链接：https://blog.youkuaiyun.com/weixin_56463218/article/details/128433752

版权

【数据库原理】数据库系统概论（第五版）专栏收录该内容

6 篇文章

订阅专栏

【实验目的】

掌握自主存取控制权限的定义和维护方法。掌握在ORACLE数据库中定义用户、角色，分配权限给用户、角色，回收权限，以相应用户登录数据库验证权限分配是否正确的方法。

【实验内容】

设有一个企业，由总裁负责管理采购、销售和客户3三个部门。总裁adam；采购部门负责人dav，采购部门员工jeff；销售部门负责人tom，销售部门员工jane；客户部门负责人kath，客户部门员工mike;该企业一个信息系统覆盖采购、销售和客户三个部门业务，使用ORAClE数据库存取数据，根据要求使用自主存取控制机制设计一个具体的权限分配和验证方案。

首先用sys（不是system）用户，连接方式选择sysdba的方式登录数据，运行一下3条语句：

grant select on V_$session to public;

grant select on V_$sesstat to public;

grant select on V_$statname to public;

然后关闭窗孔重新以system用户登录，连接方式选择normal的方式，登录数据运行以下内容：

--system用户登录数据
--建立默认表空间
CREATE TABLESPACE qy_data
LOGGING
DATAFILE 'D:\qy_data.dbf'
SIZE 50m
AUTOEXTEND ON
NEXT 50m MAXSIZE 20480m
EXTENT MANAGEMENT LOCAL;

--建立临时表空间
CREATE temporary TABLESPACE qy_temp
tempfile 'D:\qy_temp.dbf'
SIZE 50m
AUTOEXTEND ON
NEXT 50m MAXSIZE 20480m
EXTENT MANAGEMENT LOCAL;


--建立3个部门的表并填充数据

--建立采购表
create table purchase	 
( import_date date,         --入库时间
	item_code   varchar2(10), --商品代码
	item_name   varchar2(50), --商品名称
	amount      NUMBER        --入库数量
);

--建立销售表
create table sale
( sele_no    varchar2(10),  --销售订单编号
  sale_date  date,          --销售日期
	item_code   varchar2(10), --商品代码，对应采购表的商品代码
	item_name   varchar2(50), --商品名称
	amount      number,       --销售数量
  customer_no varchar2(10)  --客户编号，对应客户表的客户编码
);

--建立客户表
create table customer	 
( customer_no     varchar2(10),   --客户编号
	customer_name   varchar2(10)    --客户姓名
);


begin
--采购表插入数据
insert into purchase VALUES (to_date('2022-10-01','yyyy-mm-dd'),'10001','空调',100);
insert into purchase VALUES (to_date('2022-10-02','yyyy-mm-dd'),'10002','冰箱',200);
--销售表插入数据
insert into sale VALUES ('S20220001',to_date('2022-10-02','yyyy-mm-dd'),'10001','空调',1,'C0001');
insert into sale VALUES ('S20220002',to_date('2022-10-02','yyyy-mm-dd'),'10001','空调',2,'C0002');
insert into sale VALUES ('S20220003',to_date('2022-10-03','yyyy-mm-dd'),'10001','空调',2,'C0001');
insert into sale VALUES ('S20220004',to_date('2022-10-03','yyyy-mm-dd'),'10002','冰箱',2,'C0003');
insert into sale VALUES ('S20220005',to_date('2022-10-04','yyyy-mm-dd'),'10002','冰箱',1,'C0004');
--客户表插入数据
insert into customer VALUES ('C0001','张三');
insert into customer VALUES ('C0002','李四');
insert into customer VALUES ('C0003','王五');
insert into customer VALUES ('C0004','赵六');
commit;
end;

一、创建用户

为总裁、各部门负责人、各部门员工在数据库中创建用户，用户名称跟人员姓名一致。默认表空间都用’qy_data’，临时表空间都用’qy_temp’，密码都为’123456’;

个人答案：

------------1-------------
--1总裁角色
create user adam 		--用户名
identified by 123456 	--密码
default tablespace qy_data 	--默认表空间
temporary tablespace qy_temp	--临时表空间
--1采购部门负责人角色
create user dav 	--用户名
identified by 123456 	--密码
default tablespace qy_data 	--默认表空间
temporary tablespace qy_temp	--临时表空间
--1销售部门负责人角色
create user tom 		--用户名
identified by 123456 	--密码
default tablespace qy_data 	--默认表空间
temporary tablespace qy_temp	--临时表空间
--1客户部门负责人角色
create user kath		--用户名
identified by 123456 	--密码
default tablespace qy_data 	--默认表空间
temporary tablespace qy_temp	--临时表空间
--1采购部门员工角色
create user jeff		--用户名
identified by 123456 	--密码
default tablespace qy_data 	--默认表空间
temporary tablespace qy_temp	--临时表空间
--1销售部门员工角色
create user jane 		--用户名
identified by 123456 	--密码
default tablespace qy_data 	--默认表空间
temporary tablespace qy_temp	--临时表空间
--1客户部门员工角色
create user mike		--用户名
identified by 123456 	--密码
default tablespace qy_data 	--默认表空间
temporary tablespace qy_temp	--临时表空间

参考答案：

create user adam identified by 123456 default tablespace qy_data	temporary tablespace qy_temp;
create user dav identified by 123456 default tablespace qy_data	temporary tablespace qy_temp;
create user jeff identified by 123456 default tablespace qy_data	temporary tablespace qy_temp;
create user tom identified by 123456 default tablespace qy_data	temporary tablespace qy_temp;
create user jane identified by 123456 default tablespace qy_data	temporary tablespace qy_temp;
create user kath identified by 123456 default tablespace qy_data	temporary tablespace qy_temp;
create user mike identified by 123456 default tablespace qy_data	temporary tablespace qy_temp;

二、创建角色并分配权限

1、创建一个总裁角色president，总裁角色有所有表数据的查询、修改、插入和删除权限。

个人答案：


--2-1
create role president;
grant select,update,insert,delete
on purchase to president;
grant select,update,insert,delete
on sale to president;
grant select,update,insert,delete
on customer to president;

参考答案：

create roles president;
grant select any table to president;
grant update any table to president;
grant insert any table to president;
grant delete any table to president;

2、各部门分别创建部分负责人角色:

采购部门负责人角色purchase_charge，包含查询和修改本部门表purchase权限，查询其它部门表权限

销售部门负责人角色sale_charge，包含查询和修改本部门表sale权限，查询其它部门表权限

客户部门负责人角色customer_charge，包含查询和修改本部门表customer权限，查询其它部门表权限

个人答案：

--2-2
--采购部门负责人角色
create role purchase_charge;
grant select,update
on purchase to purchase_charge;
grant select
on sale to purchase_charge;
grant select
on customer to purchase_charge;
--客户部门负责人角色
create role customer_charge;
grant select,update
on customer to customer_charge;
grant select
on sale to customer_charge;
grant select
on purchase to customer_charge;
--销售部门负责人角色
create role sale_charge;
grant select,update
on sale to sale_charge;
grant select
on purchase to sale_charge;
grant select
on customer to sale_charge;

参考答案：

create roles purchase_charge;
grant select any table to purchase_charge;
grant update on purchase to purchase_charge;

create roles sale_charge;
grant select any table to sale_charge;
grant update on sale to sale_charge;

create roles customer_charge;
grant select any table to customer_charge;
grant update on customer to customer_charge;

3、各部门分别创建部分员工角色:

采购部门员工角色purchase_staff，包含查询表purchase权限

销售部门员工角色sale_staff，包含查询本部门表sale权限

客户部门员工角色customer_staff，包含查询本部门表customer权限

个人答案：

--2-3
create role purchase_staff;
grant select
on purchase to purchase_staff;
create role sale_staff;
grant select
on sale to sale_staff;
create role customer_staff;
grant select
on customer to customer_staff;

参考答案：

create roles purchase_staff;
grant select on purchase to purchase_charge;

create roles sale_staff;
grant select on sale to purchase_charge;

create roles customer_staff;
grant select on customer to purchase_charge;

4、给用户分配权限

给这7个新创建的用户授予登录权限

给总裁用户授予总裁角色权限

给部门负责人用户授予对应的部门负责人角色权限

给部门员工授予对应的部门员工角色权限

个人答案：

---------4--------
grant connect,president to adam;
grant connect,purchase_charge to dav;
grant connect,sale_charge to tom;
grant connect,customer_charge to kath;
grant connect,purchase_staff to jeff;
grant connect,sale_staff to jane;
grant connect,customer_staff to mike;

参考答案：

grant connect,president to adam;
grant connect,purchase_charge to dav;
grant connect,purchase_staff to jeff;
grant connect,sale_charge to tom;
grant connect,sale_staff to jane;
grant connect,customer_charge to kath;
grant connect,customer_staff to mike;

5、权限调整

销售部门负责人角色sale_charge权限内容调整包含为查询和修改本部门表sale权限

回收mike客户部门员工权限

个人答案：

revoke select any table from sale_charge;
grant select,update 
on sale to sale_charge;
revoke customer_staff from mike;

参考答案：

revoke select any table from sale_charge;
grant select on sale to sale_charge;
revoke customer_staff from mike;

注意：如果期间执行语句失败可以删除相关表重新建立运行

drop user adam;
drop user dav;
drop user jeff;
drop user tom;
drop user jane;
drop user kath;
drop user mike;
drop role president;
drop role purchase_charge;
drop role sale_charge;
drop role customer_charge;
drop role purchase_staff;
drop role sale_staff;

6、权限验证

（1）以adam用户登录数据库，查询各个客户商品购买情况。查询结果内容为客户姓名、商品名称、购买总数量

个人答案：

select a.customer_name 客户姓名,
b.item_name 商品名称,
sum(b.amount) 购买总数量
from system.customer a
left join system.sale b on a.customer_no = b.customer_no
group by a.customer_name,
b.item_name

参考答案：

select a.customer_name 客户姓名,
        b.item_name 商品名称,
        sum(b.amount) 购买总数量
from system.customer a
left join system.sale b on a.customer_no = b.customer_no
group by a.customer_name ,
         b.item_name;
--执行成功

（2）以tom用户登录数据库，查询各个商品剩余数据。剩余数量=入库数量-出库数量。查询结果内容为商品名称、剩余数量

个人答案：使用mike系统登陆后得到正确的执行错误情况

select a.item_name 商品名称,
a.amount - b. amount 剩余库存
from system.purchase a
left join (select item_code , sum(amount)amount
from system.sale
group by item_code) b on a.item_code = b.item_code

参考答案：

select a.item_name 商品名称,
       a.amount - b.amount 剩余库存
from system.purchase a
left join (select item_code ,sum(amount) amount
     from system.sale
     group by item_code) b on a.item_code = b.item_code;
--执行失败,失败原因为：ORA-00942：表或视图不存在