数据库安全性习题

1、例9.1 将创建表和视图的权限授予user01和user02用户：GRANT create table, create view TO user01, user02例9.2 从user02收回创建视图的权限：REVOKE create view FROM user02例9.3 将存储过程proSearchBySno的执行权限授予用户u1、u2和u3：GRANT excute ON proSearchBySno TO u1, u2, u3例9.4 将对班级表Class的查询、插入权限授予用户u1，且用户u1可以转授其所获得的权限给其它用户：GRANT select, insert ON Clas

2、s TO u1 WITH GRANT OPTION例9.5 将对学生表的性别、出生日期的查询和修改权限授予用户u3、u4和u5，且不可以转授权限： GRANT select, update ON Student(sex, birthday) TO u3, u4, u5例9.6 将表Score的若干权限分别授予用户u1、u2、u3、u4、u5和u6。1.将表Score的所有权限授予用户u1，且可以转授权限GRANT all ON Score TO u1 WITH GRANT OPTION2.用户u1将表Score的所有权限授予用户u2，且可以转授权限GRANT all ON Score

3、 TO u2 WITH GRANT OPTION3.用户u2将表Score的查询和插入权限授予用户u5，且不可以转授GRANT select, insert ON Score TO u54.用户u2将表Score的所有权限授予用户u4，且可以转授权限GRANT all ON Score TO u4 WITH GRANT OPTION5.用户u4将表Score的查询和删除权限授予用户u6，且可以转授GRANT select, delete ON Score TO u6 WITH GRANT OPTION例9.7 用户u2将转授给用户u4的对表Score的修改和查询权限收回：REVOKE sele

4、ct, update ON Score FROM u4 CASCADE例9.8 用户u4将转授给用户u6的对表Score的查询权限收回：REVOKE select ON Score FROM u6例9.11 在班级表Class中将classNo定义为主码。CREATE TABLE Class ( classNo char(6) NOT NULL , className varchar(30) unique NOT NULL, institute varchar(30) NOT NULL, grade smallint default 0 NOT NULL, classNum tinyint N

5、ULL, CONSTRAINT ClassPK PRIMARY KEY (classNo) )例9.12 在学生成绩表Score中将studentNo、courseNo定义为主码。CREATE TABLE Score (studentNo char(7) NOT NULL , courseNo char(3) NOT NULL , score numeric(5, 1) default 0 NOT NULL, ) 例9.13 在学生成绩表Score中将studentNo、courseNo定义为外码。CREATE TABLE Score ( studentNo char(7) NOT NULL,

6、 courseNo char(3) NOT NULL, score numeric(5, 1) default 0 NOT NULL ) 例9.14 在学生成绩表Score中将studentNo、courseNo定义为外码，且studentNo外码定义为级联删除和修改操作，courseNo外码定义为级联修改操作。   CREATE TABLE Score ( studentNo char(7) NOT NULL, courseNo char(3) NOT NULL, score numeric(5, 1) default 0 NOT NULL, )例9.15 在学

7、生表Student中定义属性studentNo取值必须为数字，性别只能取男或女，民族缺省值为汉族。CREATE TABLE Student (studentNo char(7) NOT NULL CHECK ( studentNo LIKE '0-90-90-90-90-90-90-9' ), studentName varchar(20) NOT NULL, sex char(2) NULL CHECK ( sex IN ( '男', '女') ),birthday datetime NULL , native varchar(20) NULL

8、 , nation varchar(30) default '汉族' NULL, classNo char(6) NULL, CONSTRAINT StudentPK PRIMARY KEY (studentNo),CONSTRAINT StudentFK FOREIGN KEY (classNo) REFERENCES Class(classNo)例9.16 在班级表中定义班级名称唯一。CREATE TABLE Class ( classNo char(6) NOT NULL, className varchar(30) unique NOT NULL, institute

9、varchar(30) NOT NULL, grade smallint default 0 NOT NULL, classNum tinyint NULL, CONSTRAINT ClassPK PRIMARY KEY (classNo) )例9.17 在学生表Student中定义：如果是男同学，则其姓名不能以刘开头 CREATE TABLE Student ( . sex char(2) NULL CHECK ( sex IN ( '男', '女') ), . CONSTRAINT SexCK CHECK (sex='女' OR s

10、tudentName NOT LIKE '刘%' ), .)例9.18 列级约束CREATE TABLE Student ( sNo   char(5) NOT NULL CHECK ( sNo LIKE '0-90-90-90-90-9' ) PRIMARY KEY, sName char(10) NULL unique,      sSex  char(2)  NULL,         &

11、#160;  sDate datetime NULL,            sAge  tinyint DEFAULT 17 NOT NULL CHECK ( sAge>0 AND sAge<60 ), sMZ   char(10) NULL,            sDept char(2) NOT NULL,  

12、;      sTotal decimal(8, 2) DEFAULT 0.00 NOT NULL )例9.19 元组级约束。CREATE TABLE Student ( sNo char(5) NOT NULL, CONSTRAINT sNoCK CHECK ( sNo LIKE '0-90-90-90-90-9' ), sName char(10) NULL UNIQUE , sSex char(2) NULL , sDate datetime NULL , sAge tinyint DEFAULT 17 NOT NULL,

13、 sMZ char(10) NULL, sDept char(2) NOT NULL, sTotal decimal(8, 2) DEFAULT 0.00 NOT NULL, CONSTRAINT ageCK CHECK ( sAge>0 AND sAge<60 ), PRIMARY KEY (sNo) )例9.23 输入某个同学的学号，统计该同学的平均分。CREATE PROCEDURE proStudentByNo1(sNo char(7)AS SELECT a.studentNo, studentName, avg(score) FROM Student a, Score b

14、 WHERE a.studentNo=b.studentNo AND a.studentNo=sNo GROUP BY a.studentNo例9.27 执行存储过程proStudentByNo1 EXECUTE proStudentByNo1 '0800001'例9.32 创建触发器，保证学生表中的性别仅能取男和女。CREATE TRIGGER sexIns ON StudentFOR INSERTAS IF EXISTS ( SELECT * FROM inserted WHERE sex NOT IN ( '男', '女') ) ROLLB

15、ACKCREATE TRIGGER sexUpt ON StudentFOR UPDATEAS IF EXISTS ( SELECT * FROM inserted WHERE sex NOT IN ( '男', '女') ) ROLLBACK例9.33 创建触发器，当输入某个同学选课成绩时，如果他是少数民族人，其成绩自动加5分。CREATE TRIGGER ScoreIns ON ScoreFOR INSERTAS IF EXISTS ( SELECT * FROM inserted, Student WHERE inserted.studentNo=Student.studentNo AND student.nation<>