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

1、例9.1 將創(chuàng)建表和視圖的權(quán)限授予user01和user02用戶(hù)：GRANT create table, create view TO user01, user02例9.2 從user02收回創(chuàng)建視圖的權(quán)限：REVOKE create view FROM user02例9.3 將存儲(chǔ)過(guò)程proSearchBySno的執(zhí)行權(quán)限授予用戶(hù)u1、u2和u3：GRANT excute ON proSearchBySno TO u1, u2, u3例9.4 將對(duì)班級(jí)表Class的查詢(xún)、插入權(quán)限授予用戶(hù)u1，且用戶(hù)u1可以轉(zhuǎn)授其所獲得的權(quán)限給其它用戶(hù)：GRANT select, insert ON Clas

2、s TO u1 WITH GRANT OPTION例9.5 將對(duì)學(xué)生表的性別、出生日期的查詢(xún)和修改權(quán)限授予用戶(hù)u3、u4和u5，且不可以轉(zhuǎn)授權(quán)限： GRANT select, update ON Student(sex, birthday) TO u3, u4, u5例9.6 將表Score的若干權(quán)限分別授予用戶(hù)u1、u2、u3、u4、u5和u6。1.將表Score的所有權(quán)限授予用戶(hù)u1，且可以轉(zhuǎn)授權(quán)限GRANT all ON Score TO u1 WITH GRANT OPTION2.用戶(hù)u1將表Score的所有權(quán)限授予用戶(hù)u2，且可以轉(zhuǎn)授權(quán)限GRANT all ON Score

3、 TO u2 WITH GRANT OPTION3.用戶(hù)u2將表Score的查詢(xún)和插入權(quán)限授予用戶(hù)u5，且不可以轉(zhuǎn)授GRANT select, insert ON Score TO u54.用戶(hù)u2將表Score的所有權(quán)限授予用戶(hù)u4，且可以轉(zhuǎn)授權(quán)限GRANT all ON Score TO u4 WITH GRANT OPTION5.用戶(hù)u4將表Score的查詢(xún)和刪除權(quán)限授予用戶(hù)u6，且可以轉(zhuǎn)授GRANT select, delete ON Score TO u6 WITH GRANT OPTION例9.7 用戶(hù)u2將轉(zhuǎn)授給用戶(hù)u4的對(duì)表Score的修改和查詢(xún)權(quán)限收回：REVOKE sele

4、ct, update ON Score FROM u4 CASCADE例9.8 用戶(hù)u4將轉(zhuǎn)授給用戶(hù)u6的對(duì)表Score的查詢(xún)權(quán)限收回：REVOKE select ON Score FROM u6例9.11 在班級(jí)表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 在學(xué)生成績(jī)表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 在學(xué)生成績(jī)表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 在學(xué)生成績(jī)表Score中將studentNo、courseNo定義為外碼，且studentNo外碼定義為級(jí)聯(lián)刪除和修改操作，courseNo外碼定義為級(jí)聯(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 在學(xué)

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 在班級(jí)表中定義班級(jí)名稱(chēng)唯一。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 在學(xué)生表Student中定義：如果是男同學(xué)，則其姓名不能以劉開(kāi)頭 CREATE TABLE Student ( . sex char(2) NULL CHECK ( sex IN ( '男', '女') ), . CONSTRAINT SexCK CHECK (sex='女' OR s

10、tudentName NOT LIKE '劉%' ), .)例9.18 列級(jí)約束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 元組級(jí)約束。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 輸入某個(gè)同學(xué)的學(xué)號(hào)，統(tǒng)計(jì)該同學(xué)的平均分。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í)行存儲(chǔ)過(guò)程proStudentByNo1 EXECUTE proStudentByNo1 '0800001'例9.32 創(chuàng)建觸發(fā)器，保證學(xué)生表中的性別僅能取男和女。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ā)器，當(dāng)輸入某個(gè)同學(xué)選課成績(jī)時(shí)，如果他是少數(shù)民族人，其成績(jī)自動(dòng)加5分。CREATE TRIGGER ScoreIns ON ScoreFOR INSERTAS IF EXISTS ( SELECT * FROM inserted, Student WHERE inserted.studentNo=Student.studentNo AND student.nation<>