第四章作业

6题

创建表：

create table Student(
	Sno varchar(11) primary key,
	Sname varchar(8),
	Sage int,
	Ssex varchar(2) check(Ssex in('男','女')),
	Shome varchar(20),
	Sclass int
	foreign key(Sclass) references Class(Cclass)
);

create table Class(
	Cclass int primary key,
	Cname varchar(20),
	Cbzr varchar(9),
	Cbz varchar(9)
);

(1)

grant all
on Student
to U1,U2;
with grant option;

grant all
on Class
to U1,U2;
with grant option;

(2)

grant select
on Student
to U2;

grant update(Shome)
on Student
to U2;

(3)

grant select
on Student
to public;

(4)

create role R1;

grant select,update
on Student
to R1;

(5)

grant R1--标准sql
to U1
with admin option;

exec sp_addrolemember 'R1','U1';--T-SQL
alter role Rl
add member U1;

7题

创建表：

create table Worker(
	Wno varchar(11) primary key,
	Wname varchar(8),
	Wage smallint,
	Wpost varchar(11),
	Wslary int,
	Wdno varchar(11)
	foreign key(Wdno) references Department(Dno)
);


create table Department(
	Dno varchar(11) primary key,
	Dname varchar(8),
	Dceo varchar(8),
	Daddress varchar(10),
	Dnumber varchar(15)
);

答案：

grant select
on table Worker，Department
to 王明;--1

grant insert,delete
on table Worker，Department
to 李勇;--2

grant select
on table Worker
when user()=Wno
to all;--3

grant select,update(Sslary)
on table Worker
to 刘星;--4

grant alter
on table Workers,Department
to 张新--5

grant select,insert,update,delete
on table Worker,Department
to 周平--6

create view 各部门的工资
as
select Dno,max(Sslary),min(Sslary),avg(Sslary)
from Worker,Department
where Worker.Wdno=Department.Dno
goup by Worker.Wdno;--7

grant select
on table 各部门的工资
to 杨兰;

8题

revoke select 
on table Worker,Department 
from 王明; -- 1

revoke insert,delete
on table Worker,Department 
from 李勇; -- 2

revoke select 
on table Worker 
when user()=name 
from all; -- 3

revoke select,update(Wslary) 
on table Worker 
from 刘星; -- 4

revoke alter table 
on table Worker,Department 
from 张新; -- 5

revoke all privileces 
on table Worker,Department 
from 周平; -- 6

revoke select 
on table 各部门的工资 
from 杨兰; -- 7