实验四--数据库的安全性、完整性控制

--一、创建新的数据库，并且没有任何的约束
--创建三个表 没有任何的约束
create table Student(Sno varchar(20) , Sname varchar(10), Ssex varchar(2), Sage int, Sdept varchar(5) ) --设置Sno为主键

create table Course(Cno int , Cname varchar(20), Cpno int, Ccredit int ); 

create table SC(Sno varchar(20), Cno int, Grade int);

--插入相应的数据
insert into Student(Sno,Sname,Ssex,Sage,Sdept) values('201215121','李勇','男',20,'CS');
insert into Student(Sno,Sname,Ssex,Sage,Sdept) values('201215122','刘晨','女',19,'CS');
insert into Student(Sno,Sname,Ssex,Sage,Sdept) values('201215123','王敏','女',18,'MA');
insert into Student(Sno,Sname,Ssex,Sage,Sdept) values('201215125','张立','男',19,'IS');

insert into Course(Cno,Cname,Cpno,Ccredit) values(1,'数据库','5','4');
insert into Course(Cno,Cname,Cpno,Ccredit) values(2,'数学','','2');
insert into Course(Cno,Cname,Cpno,Ccredit) values(3,'信息系统','1','4');
insert into Course(Cno,Cname,Cpno,Ccredit) values(4,'操作系统','6','3');
insert into Course(Cno,Cname,Cpno,Ccredit) values(5,'数据结构','7','4');
insert into Course(Cno,Cname,Cpno,Ccredit) values(6,'数据处理','','2');
insert into Course(Cno,Cname,Cpno,Ccredit) values(7,'PASCAL语言','6','4');

insert into SC(Sno,Cno,Grade) values('201215121','1','92');
insert into SC(Sno,Cno,Grade) values('201215121','2','85');
insert into SC(Sno,Cno,Grade) values('201215121','3','88');
insert into SC(Sno,Cno,Grade) values('201215122','2','90');
insert into SC(Sno,Cno,Grade) values('201215122','3','80');


--二、设置相应的约束
--1. 参照学生数据库中各表给出的主键、外键设置的设置要求，在自己创建的表中进行相应的设置。



--修改学生表不能为空的字段
alter table Student alter column SNo varchar(20) not null

--修改课程表不能为空的字段
alter table Course alter column Cno int not null 
alter table Course alter column Cpno int  null

--修改成绩表不能为空的字段
alter table SC alter column SNo varchar(20) not null
alter table Sc alter column Sno varchar(10) not null
alter table Sc alter column grade varchar(5) not null



--修改学生表中的Sno学号为主键
alter table Student add constraint Sno primary key(Sno)


--修改课程表的主键和外键
alter table Course add constraint Cno primary key(Cno)

use school



alter table Course add constraint Cpno foreign key(Cpno) references Course(Cno)




exec sp_help Student


--2. 参照以上各表给出的各种约束条件设置要求，在自己创建的表中进行相应的设置。



--3. 输入各种数据以验证约束条件的作用。



--4. 创建三个该数据库的用户。

--首先在数据的服务器级别创建登录账户，密码
create login zhenghui1 with password='123456';
create login zhenghui2 with password='123456';
create login zhenghui3 with password='123456';

--创建数据库用户  在创建数据库用户的时候的前提下是：必须创建好服务器登录账户
create user zhenghui1;
create user zhenghui2;
create user zhenghui3;


--5. 用SQL命令和可视化环境两种方式分别实现为所有用户授予对某个表的查询权限。
--授权所有用户对Student表可查询   
--授权对象可以为用户名和public，public的话，就是把权限授权给所有用户
grant select on  Student to public


--6. 用SQL命令和可视化环境两种方式分别实现为一个用户授予对某表中某字段的查询权限，不允许其将该权限继续传递下去。验证该权限的作用。
grant select(Sno) on Student to zhenghui1

grant select(Sno) on Student to zhenghui3

--sp_addrolemember zhenghui1,zhenghui2


--7. 用SQL命令和可视化环境两种方式分别实现为另一个用户授予对某表中某字段的修改权限，并允许其将该权限继续传递下去。验证该权限的作用。
--授权对学生表的姓名字段可以有修改的权限，并允许将此权限传递下去
grant update(Sname) on  Student to zhenghui2  with grant option;

--sp_addrolemember zhenghui2,zhenghui3;

grant delete(Sname,Sno) on Student to zhenghui3


--8. 收回为所有用户授予的对某表的查询权限。
revoke select  on Student  from public;

--9. 收回为用户授予对某表中某字段的查询权限。
revoke select(Sno) on Student from zhenghui1 ;


--10. 收回为用户授予对某表中某字段的修改权限。
--cascade 是必须的，不然会报错，因为上面已经把zhenghui2用户的权限授权给了zhenghui3，加了cascade命令之后，会把权限全部挨个的收回
revoke update(Sname) on Student from zhenghui2 cascade;