第四章作业

学生 班级模式

先建立两个模式

学生(学号，姓名，年龄，性别，家庭住址，班级号)

班级(班级号，班级名，班主任，班长)

create table Class
	   (
	   Cno int primary key,
	   Cname char(10) not null,
	   Chead char(5) not null,
	   Cmonitor char(5) not null
	   );
create table Student
       (
	   Sno char(15) primary key,
	   Sname char(5) not null,
	   Sage smallint,
	   Ssex char(2) check(Ssex in('男','女')), 
	   Saddress char(50),
	   Sclass int
	   foreign key(Sclass) references Class(Cno)
	   );
	   .

新建用户U1,U2和角色R1,可以直接右键建立也可以用SQL语句，不在细说

(1）授予用户U1对两个表的所有权限，并可给其他用户授权。

grant all privileges
on Class
to U1
with grant option;
grant all privileges
on Student
to U1
with grant option;

(2）授予用户U2对学生表具有查看权限，对家庭住址具有更新权限。

grant select,update(Saddress)
on Student 
to U2

(3）将对班级表查看权限授予所有用户。

grant select
on Class
to public

(4）将对学生表的查询、更新权限授予角色R1。

grant select,update
on Student
to R1;

(5）将角色RI授予用户U1,并且U1可继续授权给其他角色。

grant R1
to U1
with admin option --标准SQL

alter role R1
add member U1;--T-SQL语句

执行完语句之后可以双击用户查看

安全对象中可以查看对那些表有哪些权限，成员身份中可以找到角色成员

下方可以查看权限

职工 部门模式

先建立两个模式

职工（职工号，姓名，年龄，职务，工资，部门号）

部门（部门号，名称，经理名，地址，电话号）

create table Department
		(
		Dno int  primary key,
		Dname char(5),
		Dmanager char(5),
		Daddress char(5),
		Dphone char(11)
		);
create table Employee
		(
		 Eno char(20) primary key,
		 Ename char(2) not null,
		 Eage int,
		 Ework char(10),
		 Esalary int,
		 Edep int  
		 foreign key(Edep) references Department(Dno)
		);

(1）用户王明对两个表有SELECT权限。

grant select 
on Employee
to 王明;
grant select 
on Department
to 王明;

(2）用户李勇对两个表有INSERT和DELETE权限。

grant insert,delete
on Employee
to 李勇;
grant insert,delete
on Department
to 李勇;

（3）每个职工只对自己的记录有SELECT权限。

grant select 
to  Empolyee
when user()=name
to all --标准SQL

create view new1
as 
select * 
from Employee
where Employee.Ename = user
--分开执行
grant select
on new1
to public; --TSQL

(4）用户刘星对职工表有SELECT权限，对工资字段具有更新权限。

grant select,update(Esalary)
on Employee
to 刘星

(5）用户张新具有修改这两个表的结构的权限。

grant alter
on Employee
to 张新
grant alter
on Department
to 张新

(6）用户周平具有对两个表的所有权限(读、插、改、删数据)，并具有给其他用户授权的权限。

grant select,insert,update,delete
on Employee
to 周平
with grant option
grant select,insert,update,delete
on Department
to 周平
with grant option

(7）用户杨兰具有从每个部门职工中SELECT最高工资、最低工资、平均工资的权限，他不能查看每个人的工资。

create view salary(dep,ma,mi,av)
as
select Edep,max(Esalary),min(Esalary),avg(Esalary)
from Employee
group by Employee.Edep;
--分开执行
grant select
on salary
to 杨兰;

撤回权限

(1)

revoke select
on Department
from 王明;
revoke select
on Employee
from 王明

(2)

revoke insert,delete
on Department
from 李勇;
revoke insert,delete
on Employee
from 李勇;

(3)

revoke select
on new1
from public;
drop view new1;
--或者直接删除视图
drop view new1;

(4)

revoke select,update(Salary)
on Employee
from 刘星;

(5)

revoke alter
on Employee
from 张新;
revoke alter
on Department
from 张新;

(6)

revoke select,update,delete,insert
on Employee
from 周平;
revoke select,update,delete,insert
on Department
from 周平;

(7)

revoke select
on salary
from 杨兰;
drop view salary;
--或者直接删除视图
drop view salary;

总结

本章作业主要是权限的授权和收回，通过习题对用户和角色的关系有了更深一步的认识，角色可以直接作为用户的成员。另外，也了解到，为了更好的实现权限分配和数据保密，可以建立视图。如职工只能查看自己记录，设置只能查看部门的最高工资等，只要设立视图，然后只把对视图的相关权限授予用户。