今天学习了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;