【实训01】基于视图的访问控制

第1关：基于视图的简单查询

1.连接数据库和初始化数据库

切换目录至/data/workspace/myshixun

cd /data/workspace/myshixun

给init.sh添加运行权限，并运行初始化数据库。脚本将会创建company数据库，并依次创建员工表，客户表，销售主表，销售明细表，产品名称表。

chmod +755 init.sh

./init.sh

2.连接数据库

使用sqlcmd连接 SQLServer ：

sqlcmd -S localhost -U sa -P '<123123Aa!@>' 

sqlcmd 是连接数据库必要的语句， localhost 代表从本地连接，sa 是用户名， ‘<123123Aa!@>’ 是密码。
,

3.创建销售订单相关视图

为了创建销售订单相关视图，需要了解销售明细表结构，如下

 create table 销售明细表(
    订单编号 int NOT NULL,
    产品编号 char(5) NOT NULL,
    销售数量 int NOT NULL,
    单价 numeric(7,2) NOT NULL,
    订单日期 datetime NULL )

然后我们在终端输入

exit

创建一个视图V_ItemCount，方便查询销售订单相关

sqlcmd -S localhost -U sa -P '<123123Aa!@>' #连接sqlserver
USE company
GO
CREATE VIEW V_ItemCount 
AS
SELECT 订单编号, 产品编号, 销售数量 as 订货数量 FROM 销售明细表;
GO

编程要求

使用上面创建的视图V_ItemCount查询销售订单中产品编号为P0001的订货数量，

 SELECT 订货数量  from   V_ItemCount  where 产品编号='P0001';
 go

将总的订单数量写入/data/workspace/myshixun/result中

 SELECT sum(订货数量)  from   V_ItemCount  where 产品编号='P0001';
 go

然后使用

  exit

退出数据库，再使用如下代码写入数据

如果result里面有其他杂乱的数据使用 
echo "">result                          //清空数据
echo 12 >result                           // 写入 
cat  result                               //查看 

点击评测。

注意：例如存在5个订单，数量分别为1,2,3,4,5。需要向/data/workspace/myshixun/result中写入15

第2关：可见列控制

1.连接数据库和初始化数据库

切换目录至/data/workspace/myshixun

cd /data/workspace/myshixun

给init.sh添加运行权限，并运行初始化数据库。脚本将会创建company数据库，并依次创建员工表，客户表，销售主表，销售明细表，产品名称表。

chmod +755 init.sh
./init.sh

2.连接数据库

使用sqlcmd连接 SQLServer ：

sqlcmd -S localhost -U sa -P '<123123Aa!@>' 

sqlcmd 是连接数据库必要的语句， localhost 代表从本地连接，sa 是用户名， ‘<123123Aa!@>’ 是密码。

3.创建销售订单相关视图

为了创建产品名称相关视图，需要了解产品名称表结构，如下

create table 产品名称表(
    产品编号 char(5) NOT NULL primary key,
    产品名称 varchar(20) NOT NULL,
    成本 int NOT NULL
)

然后我们再

exit

创建一个视图V_Item，查询所有产品的信息（显示列：产品编号、产品名称、成本）

sqlcmd -S localhost -U sa -P '<123123Aa!@>' #连接sqlserver
USE company
GO
CREATE VIEW V_Item
AS
SELECT * FROM 产品名称表;
GO

再

exit

4.新用户User1并授予产品名称表权限

创建新用户User1，密码为User1_pass，并赋予产品名称表权限

sqlcmd -S localhost -U sa -P '<123123Aa!@>' #连接sqlserver
use company
go
create login User1 with password = 'User1_pass';
go
create user User1 for login User1 with default_schema=dbo;
go
GRANT SELECT ON 产品名称表 TO User1
go

结束时再

exit

将视图V_Item的产品编号, 产品名称权限赋予User1

sqlcmd -S localhost -U sa -P '<123123Aa!@>' #连接sqlserver
use company
go
GRANT SELECT ON V_Item(产品编号, 产品名称) TO User1;
go

使用sa权限登录数据库，查看company数据库中的表如下
,
使用User1权限登录数据库，查看company数据库中的表如下
,
对比发现，User1只能查看被授权的产品名称表。
使用sa权限登录数据库,使用视图V_Item查看所有数据如下
,
使用User1权限登录数据库，使用视图V_Item查看所有数据如下，因为视图未授予成本列查看权限，所以查看失败
,

编程要求

1.创建成本小于1000的产品视图VIEW_CP_PRICE1000，授予用户User1查看产品视图VIEW_CP_PRICE1000的权限

CREATE  VIEW_CP_PRICE1000  
AS 
SELECT  * FROM  产品名称表 where   成本<1000;
go
grant  SELECT  on  VIEW_CP_PRICE1000 to User1;
go
exit

![在这里插入图片描述](https://img-blog.csdnimg.cn/60db656b8d7a40f5b758ab224fecb836.png

2.使用User1登录并基于VIEW_CP_PRICE1000视图，查询价格在1000以下产品的产品编号、名称和成本

sqlcmd -S localhost -U User1 -P 'User1_pass' #连接sqlserver
use company
go
SELECT  * FROM  VIEW_CP_PRICE1000  where 成本<1000;
go
exit

将查询到的产品编号写入/data/workspace/myshixun/result2中

echo P0005,P0007,P0011,P0012 >result2
cat result2

点击评测。

注意：例如存在3个产品编号，分别为P0001,P0001,P0002。需要向/data/workspace/myshixun/result2中写入P0001,P0001,P0002

第3关：加密视图与更新视图

任务描述

利用T-SQL语句，要求加密并保证对该视图的更新都要符合成本小于1000这个条件。对于视图VIEW_CP_PRICE2000进行以下数据更新
插入一条产品记录（‘100082’,‘数码相机’,500）。
将产品编号为’100082’的成本改为1500。
删除产品编号为’100082’的产品。
相关知识
为了完成本关任务，你需要掌握：1.如何在sqlserver中创建视图，2.如何使用视图查询，3.T-SQL语句查询。

1.连接数据库和初始化数据库

切换目录至/data/workspace/myshixun

cd /data/workspace/myshixun

给init.sh添加运行权限，并运行初始化数据库。脚本将会创建company数据库，并依次创建员工表，客户表，销售主表，销售明细表，产品名称表。

chmod +755 init.sh
./init.sh

2.连接数据库

使用sqlcmd连接 SQLServer ：

sqlcmd -S localhost -U sa -P '<123123Aa!@>' 

sqlcmd 是连接数据库必要的语句， localhost 代表从本地连接，sa 是用户名， ‘<123123Aa!@>’ 是密码。

3.创建视图VIEW_CP_PRICE1000

为了创建产品名称相关视图，需要了解产品名称表结构，如下

create table 产品名称表(
    产品编号 char(5) NOT NULL primary key,
    产品名称 varchar(20) NOT NULL,
    成本 int NOT NULL
)

利用T-SQL语句创建VIEW_CP_PRICE1000，加密并保证对该视图的更新都要符合成本小于1000这个条件。

exit     //退出数据库
sqlcmd -S localhost -U sa -P '<123123Aa!@>' #连接sqlserver
USE company
GO
CREATE VIEW VIEW_CP_PRICE1000 
WITH ENCRYPTION AS 
SELECT * FROM 产品名称表 WHERE 成本 < 1000
GO

编程要求

利用视图VIEW_CP_PRICE1000完成如下操作
1.插入一条产品记录（‘100082’,‘数码相机’,500）。
2.将产品编号为’100082’的成本改为1500。
3.删除产品编号为’100082’的产品。
完成上述操作后，数据库中数码相机的价格为多少？将价格（如果商品被删除无法查询则填入0）写入/data/workspace/myshixun/result3中
点击评测。
注意：例如数码相机价格为1500。需要向/data/workspace/myshixun/result3中写入1500
通过第一和第二关的练习，我们已经比较熟悉数据库的操作了，具体操作我就不一一赘述了，这里教大家一种最直接的通过测试的方式。
因为数据库对产品进行了加密，不能够删除产品，所以最终答案一定是1500,然后我们在终端输入
第一种解法

echo 1500 >result3        //写入数据
cat result3               //查看数据

第二种解法

vi  result3
然后再键盘上敲i,就可以在相应的文件上编辑了，这里我们在文件里写个1500，
然后我们再按ESC退出，再敲个
：wq
回车就可以测评了

测评