数据库安全和数据库完整性——沐雨先生

一、实验目的及原理

1.熟悉SQL中涉及安全管理的命令

2. 熟悉SQL中涉及安完整性的命令

二、实验用品及设备

1. PC机

2. 操作系统Windows xp, Windows 7, Windows 10等

3. 要求有一DBMS软件,例如SQL Sever2008,MySQL等

三、实验内容及步骤

1. 使用SQL Sever2008创建用户，对用户权限和角色进行管理

2. 熟悉Grant命令和Revoke命令

3. 要求学生能使用SQL语言用PRIMARY KEY、CHECK、FOREIGN KEY… REFERENCES,NOT NULL、UNIQUE等关键字验证实体完整性、参照完整性及用户定义的完整性

4. 了解触发器

四、具体内容

（一） 安全性

1. 创建一登录账户， 登录名为U1,默认数据库为retail

Create login U1 with password='123456',default_database=retail;

2. 在retail数据库里，创建一用户， 用户名为U1，关联登录账户U1

create user U1 for login U1;

3.将对表customer的查询权限授予用户U1

grant select on Customer to U1;

4. 用U1登录， 查看授权结果

（二） 完整性约束

1. 创建一数据库jxgl， 在其中建表teacher和department

• Department(DepartNo,DepartName)
• 【系（ 系号， 系名）】
  其中， DepartNo为主码， DepartName不能为空，不能重复
• Teacher（TeacherNO, TeacherName, age, Gender, title ,salary, departNO）
• 【教师（教师号， 姓名， 年龄， 性别， 职称，工资，所在系编号】
  其中， TeacherNO是主码， TeacherName不能为空，Gender取值男或女，age在18到65之间

create database jxgl; create table Department (Departno int primary key, departname varchar(8) not null unique );

create table Teacher ( Teacherno char(6) primary key, teachername varchar(20) not null, age int check(age between 18 and 65), gender char(2) check(gender in ('男','女')), title varchar(6), salary money, Departno int foreign key references department (departno) );

2. 添加完整性约束，并自己举例验证

1）职称取值为教授，副教授，讲师，助教
2）职称为教授的老师， 工资应超过6000
3）departNo 为teacher表的外码， 当一个系撤销时，
这个院系的教师所在院系编号设置为NULL。当一个系的编号修改时，级联修改
alter table Teacher add constraint c1 check (title in('教授','副教授','讲师','助教'));

alter table Teacher add constraint c2 check (title='副教授' or title='讲师' or title='助教' or salary>=6000)

Alter table teacher add constraint a3 check (foreign key (departNO) references department (departNO) on delete set null on update cascade );

（三） 触发器

1．在数据库retail里， 创建表unusual（CustNo, FName, LastName, oldCreditLimit, newCreditlimit）

create table unusual (CustNo char(8), FName char(20), LastName char(20), oldCreditLimit int, newCreditlimit int, );

2. 定义触发器当顾客的信用卡额度提高超过10%以上时，将相关信息复制到unusual 这个表里

on Customer
after update
as
begin
	if update(CreditLimit)
		insert into unusual
		select d.CustNo,d.FName,d.LastName,d.CreditLimit,i.Creditlimit
		from deleted d,inserted i
		where d.CustNo=i.CustNo and i.Creditlimit>d.CreditLimit*1.1
End;

3．创建表 FormerSalsRep(SalsRepNO, FName, LastName, DepartNO)

create table FormerSalsRep (SalesRepNo char(8), Fname char(8), LastName char(8), DepartNo char(8) );

4. 定义触发器当一个销售代表离开公司了，将该销售代表从表SalesRep中删除，同时将该销售代表的信息拷贝到FormerSalesRep表里。

on SalesRep
after delete
as
begin
	insert into FormerSalsRep
	select *
	from deleted
end

5. 定义触发器,当在orderline表里添加新的数据时，自动更新客户的账户余额和商品的库存量

on orderline
after insert
as
begin
	update Customer
    set Balance=Balance-inserted.PurchasePrice*inserted.QtyPurchased)
	where custno in (select custno
from inserted)
	update Product
	set Qtyonhand= Qtyonhand-inserted.QtyPurchased
	where productno in (select productno
from inserted)
end