mysql的安全性实验报告_数据库安全性与完整性实验报告

数据库安全性与完整性实验报告 (一)

P148——8，9：

8(a)

GRANT SELECT ON

EMPTO 王明; GRANT SELECT ON

DEPTTO 王明; (b) GRANT DELETE ,INSERTON

EMPTO 李勇; GRANT DELETE ,INSERTON

DEPTTO 李勇; (d) GRANT SELECT ON

EMP TO 刘星; GRANT UPDATE(SAL) ON

EMP TO 刘星; (e) GRANT ALTER TABLE ON

EMP TO 张新; (f) GRANT ALL PRIVILEGES ON

EMP TO 周平WITH GRANT OPTION; GRANT ALL PRIVILEGES ON

DEPT TO 周平WITH GRANT OPTION; (g) ALTER TABLE EMP ALTER COLUMN SAL SMALLINT;

CREATE VIEW DS(DEPTNO,MAX,MIN,AVG) AS SELECT

EMP.DEPTNO,MAX(SAL),MIN(SAL),AVG(SAL) FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO GROUP BY EMP.DEPTNO ;

GRANT SELECT ON

DS TO 杨兰;

9(a)

REVOKE SELECT ON

EMP FROM 王明; REVOKE SELECT ON

DEPT FROM 王明; (b) REVOKE DELETE ,INSERTON

EMP FROM 李勇; REVOKE DELETE ,INSERTON

DEPT FROM 李勇; (d) REVOKE SELECT ON

EMP FROM 刘星; REVOKE UPDATE(SAL) ON

EMP FROM 刘星; (e) REVOKE ALTER TABLE ON

EMP FROM 张新; (f) REVOKE ALL PRIVILEGES ON

EMP FROM 周平 CASCADE; REVOKE ALL PRIVILEGES ON

DEPT FROM 周平 CASCADE; (g)

REVOKE SELECT ON

DS FROM 杨兰;

(二)

P164——6，8：

6：

CREATE TABLE DEPT(DEPTNO NUMERIC(2)PRIMARY KEY,(主码)

DNAME VARCHAR(10),MNANE VARCHAR(10),PHONE CHAR(12)); CREATE TABLE EMP(EMPNO NUMERIC(2)PRIMARY KEY, (主码)

NAME VARCHAR(10),AGE SMALLINT check(AGE <= 60),JOB CHAR(12),SAL

SMALLINT,DEPTNO NUMERIC(2),

(用户定义完整性)

FOREIGN KEY (DEPTNO)REFERENCES DEPT(DEPTNO));(参照完整性)

8：

CREATE TABLE TEACHER(Eno NUMERIC(4)PRIMARY KEY,Ename CHAR(10),Job CHAR(8),Sal NUMERIC(7,2),Deduct NUMERIC(2), CONSTRAINT C1 CHECK (Sal + Deduct >= 3000));

CREATE TRIGGER Insert_Or_Update_Sal BEFORE INSERT OR UPDATE ON

Teacher FOR EACH ROW AS BEGIN IF(new.Job = "教授")AND (new.Sal < 4000)THEN new.Sal := 4000; END IF; END;