SQL上机实验（创建用户并授权）

今天学习了SQL用户，角色，登录名，写实验题检验一下~~

1：对指定关系模式使用SQL的Grant和revoke（加上视图机制）完成授权或存取控制
职工（职工号，姓名，年龄，职务，工资，部门号）
部门（部门号，名称，经理名，地址，电话号）
（1）用户A对两表有select权，用户B对两个表有insert和delete权，用户C对工资有更新权；
（2）用户D对两个表有所有权力，并具有给其他用户授权的权力；
（3）用户E可从每个部门职工中查询最高工资，最低工资和平均工资，但不能查看每个人工资。

CREATE DATABASE APP
/*建立职工，部门两张表*/
USE APP
CREATE TABLE 部门(
部门号 CHAR(20) PRIMARY KEY,
名称 VARCHAR(10) UNIQUE NOT NULL,
经理名 CHAR(10) NOT NULL,
地址 CHAR(20),
电话 CHAR(12),
);
USE APP
CREATE TABLE 职工(
职工号 char(20) PRIMARY KEY,
姓名 CHAR(20)NOT NULL,
年龄 INT,
职务 CHAR(20),
工资 Money,
部门号 CHAR(20),
FOREIGN KEY(部门号) REFERENCES 部门(部门号)
);
/*先创建(登录名,密码，数据库）*/
USE master EXEC sp_addlogin A ,'111','APP'
USE master EXEC sp_addlogin B ,'111','APP'
USE master EXEC sp_addlogin C ,'111','APP'
USE master EXEC sp_addlogin D ,'111','APP'
USE master EXEC sp_addlogin E ,'111','APP'
/*然后在该数据库APP下创建用户A1，B1，C1，D1（要在安全性里找）*/
/*1:用户A1对两表有select权，用户B1对两个表有insert和delete权，用户C1对工资有更新权；*/
USE APP
GRANT SELECT ON 部门 TO A1;
GRANT SELECT ON 职工 TO A1;
GRANT INSERT,DELETE ON 部门 TO B1;
GRANT INSERT,DELETE ON 职工 TO B1;
GRANT UPDATE ON 职工(工资) TO C1;
/*2:用户D1对两个表有所有权力，并具有给其他用户授权的权力；*/
GRANT ALL PRIVILEGES ON 部门 TO D1 WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON 职工 TO D1 WITH GRANT OPTION;
/*3:用户E1可从每个部门职工中查询最高工资，最低工资和平均工资，但不能查看每个人工资。*/
CREATE VIEW 部门工资
AS 
SELECT MAX(工资) as N'最高工资',MIN(工资) as N'最低工资',AVG(工资) as N'平均工资'
FROM 职工
GROUP BY 部门号
//或者
CREATE VIEW 部门工资
as 
select 部门.名称,MAX(工资),MIN(工资),AVG(工资)
from 职工,部门
where 职工.部门号=部门.部门号
group by 职工.部门号


GRANT SELECT
ON 部门工资
TO E1;