同步课 / 2016级数据库原理

create database student

create schema S_C

create table S_C.Class(
	CNO tinyint primary key,
	CNAME char(10),
	CTEACHER char(10),
	CMonItoR char(10)
);

create table S_C.Student(
	SNO char(10) primary key,
	SNAME char(10),
	SAGE tinyint,
	SSEX char(2),
	saddress varchar(30),
	CNO tinyint,
	foreign key(CNO)references S_C.Class(CNO)
);

create login S1 with password='1'
create login S2 with password='2'

create user U1 for login S1
create user U2 for login S2

grant all privileges 
on S_C.Class
to U1
with grant option
grant all privileges
on S_C.Student
to U2
with grant option

grant select,update(SADDRESS)
on S_C.Student
to U2

grant select
on S_C.Class
to public

create role R1

grant select,update
on S_C.Student
to R1

exec SP_ADDROLEMEMBER R1,U1

create database work

create schema D_W

create table D_W.Department(
	DNO char(10) primary key,
	DNAME char(20),
	DMANAGER char(10),
	DADDRESS char(20),
	DTEL char(10)
);

create table D_W.Worker(
	WNO char(10) primary key,
	WNAME char(10),
	WAGE tinyint,
	WPOST char(10),
	WSALARY INT,
	DNO char(10),
	foreign key(DNO)references D_W.Department(DNO)
);

create login 王明 with password='1'
create login 李勇 with password='1'
create login 刘星 with password='1'
create login 周平 with password='1'
create login 杨兰 with password='1'

create user 王明 for login 王明
create user 李勇 for login 李勇
create user 刘星 for login 刘星
create user 周平 for login 周平
create user 杨兰 for login 杨兰

grant select
on D_W.Department
to 王明
grant select
on D_W.Worker
to 王明

grant INSERT,DELETE
on D_W.Department
to 李勇
grant INSERT,DELETE
on D_W.Worker
to 李勇

grant select,update(WSALARY)
on D_W.Worker
to 刘星

grant all privileges
on D_W.Department
to 周平
with grant option
grant all privileges
on D_W.Worker
to 周平
with grant option

create VIEW D_W.Salary(最高工资,最低工资,平均工资)
AS
select MAX(WSALARY),MIN(WSALARY),AVG(WSALARY)
from D_W.Worker
grant select
on D_W.Salary
to 杨兰

revoke select
on D_W.Department
from 王明
revoke select
on D_W.Worker
from 王明

revoke INSERT,DELETE
on D_W.Department
from 李勇
revoke INSERT,DELETE
on D_W.Worker
from 李勇

revoke select,update(WSALARY)
on D_W.Worker
from 刘星

revoke all privileges
on D_W.Department
from 周平 CASCADE
revoke all privileges
on D_W.Worker
from 周平 CASCADE

revoke select
on D_W.Salary
from 杨兰

任务已更新