数据库控制和触发器

一、  数据安全性控制 （1） 授权和回收 在之前建立的TEST数据库中创建U1~U7共7个数据库用户，为其授予不用的权限，然后查看用户是否真正拥有被授予的权限。 创建七个登陆用户！ create login login_u1withpassword='u1' create login login_u2withpassword='u2' create login login_u3withpassword='u3' create login login_u4withpassword='u4' create login login_u5withpassword='u5' create login login_u6withpassword='u6' create login login_u7withpassword='u7' 创建七个数据库用户 create user u1forlogin login_u1 create user u2forlogin login_u2 create user u3forlogin login_u3 create user u4forlogin login_u4 create user u5forlogin login_u5 create user u6forlogin login_u6 create user u7forlogin login_u7 1) 把查询Student表的权限授给用户U1,然后以U1身份登录数据库，并验证U1是否具有相应的权限；        grant select on student to u1        select * from student        insert into student values('1006','杜华伟','男',10,10); 2) 把对Student表和Course表的全部操作权限授予用户U2和U3,然后以U2身份登录数据库，并验证U2是否具有相应的权限； grant all privilegeson studentto u2,u3; grant all privilegeson courseto u2,u3; select * from student select * from course insert into studentvalues('1006','杜华伟','男',20,20); select * from student 3)  把对表SC的查询权限授予所有用户； grant select on sc to public; 以u5的身份登陆进行查询 select*from sc 4) 把查询Student表和修改学生学号的权限授给用户U4, 然后以U4身份登录数据库，并验证U4是否具有相应的权限； grant update(sno)on studentto u4; grant select on student to u4 update student set sno='1007'where sno='1006'; 5) 把对表SC的INSERT权限授予U5用户，并允许将此权限再授予用户U6； grant  insert on scto u5withgrant option       在u5登陆后：grantinserton scto u6 6）回收用户U4修改学生学号的权限；      revoke update(sno)on studentfrom u4          更改数据：update studentset sno='1006'where sno='1007'     7）收回所有用户对表SC的查询权限； （2）数据库角色的使用 1） 创建角色ROLE1；   create role role1 2） 为角色授权，使得角色ROLE1拥有对Student表的SELECT、UPDATE、INSERT权限； grant insert,update,selecton studentto role1 3） 将用户U1,U3和U7添加到角色ROLE1中；  sp_addrolemember role1,u1;  sp_addrolemember role1,u3;  sp_addrolemember role1,u7; 4） 对角色ROLE1的权限进行修改，增加对Student表的DELETE权限，并回收对Student表的INSERT权限； grant delete on student to role1; revoke insert on student from role1; 以用户u7的身份登陆：insertinto student(sno,sname)values('1007','张丹') 5） 删除角色ROLE1; sp_droprolemember role1,u1; sp_droprolemember role1,u3; sp_droprolemember role1,u7; drop role role1;   二、数据完整性控制 （1）定义表Student，并将其中的SNO属性定义为主码；属性 Ssex的值只能取男或女 ； create table Student (   sno char(8)primarykey,   Sname char(20),   Sage numeric(3),   Ssex char(2)check(Ssex='女'or Ssex='男') ) （2）定义表Course，并将其中的CNO属性定义为主码； create table Course (     cno char(2)primarykey,    cname char(20)notnull,    credit int ) （3）定义表SC，将其中的属性SNO+CNO定义为主码；GRADE属性不能取空值； create table sc (   sno char(8)notnull,   cno char(2)notnull,   grade smallint notnull, primary key(sno,cno) ) （4）定义表SC中的外码； alter table sc add foreign key(cno) references course(cno) alter table sc add foreign key(sno) references student(sno) （5）建立部门表，要求部门表名称DNMAE取值唯一； create table dapt (  deptno numeric(2)primarykey,  dname char(8)unique ) （6）用CONSTRAINT对完整性约束条件命名，定义表Student，要求学号在90000~99999之间，姓名不能取空值，年龄小于30，性别只能是男或女； create table student (   sno numeric(6)constraint c1check(snobetween 90000and 99999),  sname char(8)constraint c2notnull,  sage  smallint constraint c3check(sage<30),  ssex  char(2)constraint c4check(ssex='女'or ssex='男'),   constraint studentkey primary key(sno) ) （7）修改表Student的完整性限制，去除对性别所做的限制，并将年龄限制由小于30该为小于40； alter table student drop constraint c4 alter table student drop constraint c3 alter table student add constraint c3check(sagebetween 30and 40) (8) 定义表TAB，只有一个属性COL（int）,在对TAB插入和更新数据前进行检查。如果所插入或更新的值在100~1000之前，将值设置为50；如果值大于1000，则给出新值不允许大于1000的提示信息,最后给出测试该触发器的语句； create table tab (   col int not null ) create trigger insert_tab on tab instead of insert as    begin        declare @newcol int;     set @newcol=(select colfrom inserted);         if(@newcol>1000)              print '新值不允许大于';          else if(@newcol>=100)              begin                 set  @newcol=50;                 insert into tab values(@newcol);              end           else               insert into tab values(@newcol); end Go create trigger update_tab on tab instead of update as    begin       declare @newcol int;          declare @oldcol int;       set @newcol=(select colfrom inserted);       set @oldcol=(select colfrom deleted);       if(@newcol>1000)            print '修改的新值不允许大于';      else if(@newcol>=100)          begin            set @newcol=50;            update tab set col=@newcol where col=@oldcol;          end     else          update tab set col=@newcolwhere col=@oldcol; end Go insert into tabvalues(60) update tab set col=1100 where col=50 (9) 创建一个触发器，在修改SC表的成绩时，判断日期是不是期末（1月或7月），如果不是，则不允许修改，并给出测试该触发器的语句；    create trigger update_sc on sc instead of update as   begin       declare @ismonth int;       declare @sno char(4);       declare @cno char(2);       declare @newgrade int;        set @ismonth=month(getDate());   if update(grade)       if(@ismonth=1or @ismonth=7)           begin             set @sno=(select snofrom inserted);                      set @cno=(select cnofrom inserted);             set @newgrade=(select gradefrom inserted);                      update sc set grade=@newgrade where sno=@sno and cno=@cno;                 end       else           print '现在不是期末时间,不可以修改学生成绩！'; end Go update sc set grade=90where sno='1001'and cno='1' 当把系统时间改为7月的时间：