实验三 oracle的安全性和完整性控制

 

实验三  oracle的安全性和完整性控制

实验目的：

1. 通过本实验能够熟练应用sql语言进行用户权限的授予和回收。

2. 熟练掌握实体完整性，参照完整性及用户定义的完整性的定义。

3. 并体会oracle数据库系统在安全性和完整性保护方面的特性。

实验要求：

1. 在进行本实验之前，应熟练课程内容，在上机之前做好实验计划，编写好相应的代码。

实验内容：

Grant 语句的格式：

Grant <权限> [,<权限>]…

On <对象类型> <对象名> [,<对象类型> <对象名>]…

To <用户> [,<用户>]…

[with grant option]

Public 全体用户

如果指定了[with grant option]，则获得某种权限的用户还可以把这种权限再授予其他用户。但不允许循环授权。

（一） 授权

1. 创建用户”S学号u1”，S学号u2，S学号u3，S学号U4,并为其赋予connect角色。

其中，学号是指你的学号，比如你的学号为20130001，则你创建的用户应该是S20130001u1，S20130001u2，S20130001u3,S20130001u4

create user  s2015224476u1 identified by nefu1234;

create user  s2015224476u2 identified by nefu1234;

create user  s2015224476u3 identified by nefu1234;

create user  s2015224476u4 identified by nefu1234;

grant connect to s2015224476u1,s2015224476u4;

2. 假设你的用户名是S20130001（做实验时请根据你的账号修改），把你在数据库中创建的Student表的查询权限授给用户”S学号u1”， ”S学号u1”执行相应的查询。

grant select

on student

to s2015224476u1；

1)查询S20130001用户的Student表中全体学生的详细记录。

select * from s2015224476.student;

2)查询S20130001用户Student表中所有姓刘的学生的姓名、学号和性别。

select sname,sno,ssex from s2015224476.student

where sname like '刘%';

3)查询S20130001用户Student表中名字中第二字为“阳”字的学生的姓名和学号。

select sname,sno from s2015224476.student

where sname like '_阳%';

3. 把S20130001用户的Student表和Course表的全部权限授予用户”S学号u2”， ”S学号u3”；然后让”S学号u2”用户修改S20130001的数据。

  GRANT all privileges

ON student

TO s2015224476u2,s2015224476u3;

GRANT all privileges

ON course

TO s2015224476u2,s2015224476u3;

4. 把S20130001用户的表Student的修改学生学号的权限赋予用户” S学号U4”，然后让S20130001用户修改S20130001的student表的SNO数据。

GRANT UPDATE(Sno)

ON Student

TO s2015224476U4;

update s2015224476.Student set sno='2010' where sno='200915121';

5. 把S20130001用户的SC表的插入权限授予“S学号U5”用户，然后让“S学号U5”用户向SC表插入一条记录。

GRANT insert 

ON Sc 

TO s2015224476U5;

insert into s2015224476.sc values('200215122','2',88);

6. 把对表SC的查询权限授予所有用户。

  GRANT select

ON sc

TO public;

1） 让“S学号u2”用户查询S20130001用户的SC表中选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列

select sno,grade from s2015224476.sc order by grade desc;

2） 让“S学号u2”用户查询S20130001用户的SC表中各个课程号与相应的选课人数。

select cno,count(*) from s2015224476.sc group by cno;

（首先应该以新创建的用户的身份重新登陆数据库，然后再进行授权）

（二） 回收权限

1． 收回用户”S学号u2”修改学生学号的权限

REVOKE UPDATE

ON Student

FROM s2015224476U4;

2． 收回所有用户对表sc的查询权限

REVOKE select

ON Sc 

FROM public;

3． 收回用户”S学号U5”sc表的insert权限

REVOKE insert

ON Sc 

FROM s2015224476u5;

4． 在回收权限之后验证用户是否真正丧失了该权限（查询表，插入记录）

（三） 角色

1. 创建一个角色

CREATE  ROLE  u1;

2. 给角色授予权限

GRANT SELECT，UPDATE，INSERT

    ON  Student

    TO u1;

3. 将角色授予某一用户

GRANT  u1 

    TO s2015224476u1;

4. 检查用户是否具有相应的权限

select * from s2015224476.student;

检查此用户是否具有相应权限。

（四） 完整性

1． 建立教师表Teacher，要求教师名称Tname列取值唯一，教师编号TNO列为主码。

CREATE TABLE Teacher

               (Tno  varCHAR2(9)  PRIMARY KEY,

                 Tname  varchar2(20) unique);

2． 建立学生登记表Student，要求学号在9000至9999之间，年龄<29,性别只能是’男’或’女’，姓名非空。

 CREATE TABLE Student

        (Sno  varCHAR2(9)  PRIMARY KEY check(sno>9000 and sno<9999),

          Sname  varchar2(20) unique,     

          Ssex  varCHAR2(2) check(ssex in('男','女')),

          Sage   INTteger check(sage<29),

);

3． 修改表Student的结构，由年龄小于29改为小于40。

alter table student modify sage integer check(sage<40);

4． 建立课程表COURSE，要求课程表中的每门课程的学分不得超过7分，且主讲教师字段TNO参照Teacher表TNO字段，且当删除教师表中一行记录时，如果它被参照，则将Course表中相应记录中TNO的值设置为空。

   CREATE TABLE course (

cno   varchar2(9),

cname varchar2(9),

ccredit  varchar2(4) check(ccredit<7),

Tno  varCHAR2(9) references teacher(tno) on delete set null);

5. 建立表SC，要求SNO参照STUDENT表的学号，且当删除Student表中的一个学生记录时，级联删除学生的选课记录。

create table sc(

     sno varchar2(9) references student(sno) on delete cascade,

     cno varchar2(9));

对上述新建立和修改定义的表，每个表输入3条数据，其中1条数据符合完整性约束，2条违反约束条件的，验证和体会Oracle的实体完整性和参照完整性。

（五） 触发器（选做）

1. 创建after触发器，当student表的学生学号发生变化，而且此学号被sc表引用，就自动更改sc表中被引用的sno值。

create trigger trigger1
on student
for update
as
if update(sno)
update sc set sno=a.asno from (select inserted.sno as asno,deleted.sno as bsno from inserted,deleted) as a inner join sc as b on a.sno=b.sno;

 

2. 创建before触发器，当向sc表中插入一行记录时，如果Student表中没有对应的sno值，则自动向student中插入一条记录，sno值取插入的记录中的学号，姓名为空字符串或者“无名”。

 

3. 监测触发器是否执行。