數(shù)據(jù)庫安全性習題

1、例9.1 將創(chuàng)建表和視圖的權限授予user01和user02用戶：GRANT create table, create view TO user01, user02例9.2 從user02收回創(chuàng)建視圖的權限：REVOKE create view FROM user02例9.3 將存儲過程proSearchBySno的執(zhí)行權限授予用戶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外碼定義為級聯(lián)刪除和修改操作，courseNo外碼定義為級聯(lián)修改操作。   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取值必須為數(shù)字，性別只能取男或女，民族缺省值為漢族。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 輸入某個同學的學號，統(tǒng)計該同學的平均分。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 執(zhí)行存儲過程proStudentByNo1 EXECUTE proStudentByNo1 '0800001'例9.32 創(chuàng)建觸發(fā)器，保證學生表中的性別僅能取男和女。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 創(chuàng)建觸發(fā)器，當輸入某個同學選課成績時，如果他是少數(shù)民族人，其成績自動加5分。CREATE TRIGGER ScoreIns ON ScoreFOR INSERTAS IF EXISTS ( SELECT * FROM inserted, Student WHERE inserted.studentNo=Student.studentNo AND student.nation<>