实验七数据库安全性实验.docVIP

实验七 数据库安全性实验 一、实验目的 学习巩固课本第四章数据库安全性知识，练习Grant语句与Revote语句。 二、实验内容 课本P148第8、9题（除c小题外）。 三、概要设计 略。 四、主要SQL语句 --数据库安全性实验（Grant、Revote语句练习。课本P148第、题，除c小题外） create table D (dno char(20) primary key, dname char(20), mn char(20), addr char(200), tel char(20) ); create table E (eno char(20) primary key, ename char(20), eage smallint, job char(20), esa int, dno char(20), foreign key(dno)references D(dno) ) --第题 --a grant select on object::E to WangMing grant select on object::D to WangMing --b grant insert on object::E to LiYong grant delete on object::E to LiYong grant insert on object::D to LiYong grant delete on object::D to LiYong --c(不做) --d grant select on object::E to LiuXing grant update on object::E(esa) to LiuXing--对列的授权 --e grant alter on object::E to ZhangXin grant alter on object::D to ZhangXin --f不建议用all,可以利用视图化界面 grant select on object::E to ZhouPing with grant option grant select on object::D to ZhouPing with grant option grant insert on object::E to ZhouPing with grant option grant insert on object::D to ZhouPing with grant option grant update on object::E to ZhouPing with grant option grant update on object::D to ZhouPing with grant option grant delete on object::E to ZhouPing with grant option grant delete on object::D to ZhouPing with grant option --g利用视图来做 create view sal(hi_sal,lo_sal,avg_sal) as select max(esa),min(esa),avg(esa) from E grant select on object::sal to YangLan --第9题 --a revoke select on object::E to WangMing revoke select on object::D to WangMing --b revoke insert on object::E to LiYong revoke delete on object::E to LiYong revoke insert on object::D to LiYong revoke delete on object::D to LiYong --c(不做) --d revoke select on object::E to LiuXing revoke update on object::E(esa) to LiuXing --e revoke alter on object::E to ZhangXin revoke alter on object::D to ZhangXin --f revoke select on object::E to ZhouPing cascade --with grant option revoke select on object::D to ZhouPing cascade --with grant option revoke insert on object::E to ZhouPing