数据库Oracle-实验三 数据库的安全性和完整性控制

一、实验目的

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

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

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

二、实验环境

计算机

windows7操作系统，

Oracle 11g， SQL Developer

三、实验内容及结果

授权

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

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

代码： create user s2019214221u1 identified by nefu1234;

create user s2019214221u2 identified by nefu1234;

create user s2019214221u3 identified by nefu1234;

create user s2019214221u4 identified by nefu1234;

截图：

（之前做的当时忘记截图了，所以截了用户表）

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

代码：grant select

on jmy2019214221.student

to s2019214221u1；

截图：

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

代码：select * from jmy2019214221.student;

截图：

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

代码：select sno,sname,ssex from jmy2019214221.student where sname like ‘刘%’;

截图：

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

代码：select sname,sno from s2016214220. student

where sname like ‘_阳%’;

截图：

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

代码：GRANT all PRIVILEGES ON jmy2019214221.student

TO s2019214221u2,s2019214221u3;

GRANT all PRIVILEGES ON jmy2019214221.course

TO s2019214221u2,s2019214221u3;

截图：

修改：

DELETE FROM s2016214220.student WHERE s2016214220.student.sname = ‘李阳’；

截图：

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

代码：GRANT UPDATE(Sno)

ON Student

TO s2019214221U4;

截图：

修改：update s2019214221.Student set sno=‘2010’ where sno=‘200915121’;

截图：

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

代码：create USER s2019214221u5 identified by nefu1234;

grant insert

on jmy2019214221.sc to s209214221u5;

insert into jmy2019214221.sc values(200215121,7,85);

截图：

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

代码：grant select

on jmy2019214221.sc

to public；

截图：

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

代码：select sno,grade

from jmy2019214221.sc

where cno=‘3’

order by grade desc

截图：

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

代码： select cno,count(*)

from jmy2019214221.sc

group by cno;

截图：

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

回收权限

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

代码：revoke update

on student

from s2019214221u2；//应先赋予u2该权限方可收回

截图：

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

代码： revoke select

on sc

from public

截图：

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

代码： grant insert

on jmy2019214221.student

to s2019214221u5;

revoke insert

on sc

from s2019214221u5;

截图：

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

代码：select * from sc；

  insert into jmy2019214221.sc values(200215121,7,85);

截图：

角色
        创建一个角色

代码：create role aowu;

截图：

        给角色授予权限

代码： grant select on jmy2019214221.student TO aowu;

截图：

        将角色授予某一用户

代码：grant aowu to s2019214221U1;

截图：

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

代码： select * from JMY2019214221.STUDENT;

截图：

完整性

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

代码：create table Teacher

(Tno char (10) primary key,

Tname char (10) unique

);

截图：

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

代码：create table student1

(Sno NUMBER primary key ,

Sname char (20) not null,

SSex char (2) check (SSex in (‘男’,‘女’)),

Sage int ,

check(Sno>9000 and Sno<9999 and Sage<29)

);

截图：

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

代码：CREATE TABLE course1 (

cno char(9),

cname char(9),

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

Tno char(9) references teacher(tno) on delete set null

);

截图：

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

代码：create table sc1

( sno char (9) REFERENCES student (sno) on delete CASCADE,

cno char(9)

);