Oracle数据库的安全性和完整性控制

99次阅读

共计 4954 个字符，预计需要花费 13 分钟才能阅读完成。

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

（一）授权
1. 以 dba 用户的身份登陆 oracle，创建用户 u1+ 学号后四位，u2+ 学号后四位。
SQL> create user u1_3985 identified by “123”;
SQL> create user u2_3985 identified by “123”;
SQL> create user u3_3985 identified by “123”;
SQL> create user u4_3985 identified by “123”;
2. 对 1. 中创建的用户授予 connect，resource 的权限。
SQL> grant connect,resource to u1_3985,u2_3985;
3. 用户 jsj*** 把查询 Student 表权限授给用户 u1+ 学号后四位，u1 执行相应的查询。
SQL> grant select on student to u1_3985;
SQL> con u1_3985/123@orcl;
1）查询 jsj*** 用户的全体学生的详细记录。
SQL> select * from j2014213985.student ;
2）查询 jsj*** 用户的所有姓刘的学生的姓名、学号和性别。
SQL> select sname,sno,ssex from j2014213985.student where sname like ‘ 刘 %’;
3）查询 jsj*** 用户的名字中第二字为“勇”字的学生的姓名和学号。
SQL> select sname,sno from j2014213985.student where sname like ‘_明 ’;
4. 用户 jsj*** 把对 Student 表和 Course 表的全部权限授予用户 u2+ 学号后四位，u3+ 学号后四位；u2+ 学号后四位用户修改 jsj*** 的数据。
SQL> grant all privileges on student to u2_3985,u3_3985;
SQL> grant all privileges on course to u2_3985,u3_3985;
SQL> update j2014213985.student set sage=24 where sname=’ 刘明 ’;
5. 把对表 SC 的查询权限授予所有用户。
SQL> grant select on SC to public;
1）查询选修了 3 号课程的学生的学号及其成绩，查询结果按分数的降序排列
SQL> con u1_3985/123@orcl;
SQL> select sno,grade from j2014213985.SC where cno=3 order by grade desc;
2）查询各个课程号与相应的选课人数。
SQL> select cno,count(*) from j2014213985.SC group by cno;
6. 用户 jsj*** 授予用户 u4+ 学号后四位对 student 表的 insert 权限，并允许此权限传播。
SQL> con j2014213985/j123456@orcl;
SQL> grant insert on student to u4_3985 with grant option;
7. 用户 u4+ 学号后四位将对表 student 的 insert 权限授予 u5+ 学号后四位，并允许将权限转授给其他用户。（首先应该以 u5+ 学号后四位的身份重新登陆数据库，然后再进行授权）
SQL> con u4_3985/123@orcl;
SQL> grant insert on j2014213985.student to u5_3985 with grant option;
SQL> con u5_3985/123@orcl;
SQL> grant insert on j2014213985.student to u1_3985 with grant option;
（二）回收权限
1．收回所有用户对表 sc 的查询权限
SQL> revoke select on SC from public;
2．收回用户 u4 对 student 表的 insert 权限
SQL> revoke insert on student from u4_3985;
3．在回收权限之后验证用户是否真正丧失了该权限（查询表，插入记录）
SQL> select * from j2014213985.SC;
select * from j2014213985.SC
ORA-00942: 表或视图不存在
SQL> insert into j2014213985.student values(‘201421′,’ 小屋 ’,’ 男 ’,100,’MA’);
insert into j2014213985.student values(‘201421′,’ 小屋 ’,’ 男 ’,100,’MA’)
ORA-00942: 表或视图不存在
（三）角色
1. 创建一个角色
SQL> create role ro1;
2. 给角色授予权限
SQL> grant insert,update,select on student to ro1;
3. 将角色授予某一用户
SQL> grant ro1 to u1_3985;
4. 检查用户是否具有相应的权限
SQL> con u1_3985/123@orcl;
SQL> select * from j2014213985.student;
SQL> insert into j2014213985.student values(‘20070002′,’ 徐梅 ’,’ 女 ’,29,’MA’);
SQL> update j2014213985.student set sage=25 where sname=’ 刘明 ’;
检查此用户是否具有相应权限。
SQL> con j2014213985/j123456@orcl;
SQL> insert into student values(‘20070001′,’ 张悦 ’,’ 女 ’,22,’MA’);
SQL> select * from student;
SQL> update student set sage=18 where sname=’ 刘明 ’;
（四）完整性
1．建立部门表 DEPT，要求部门名称 Dname 列取值唯一，部门编号 Deptno 列为主码
create table DEPT(
Deptno varchar(10) constraint a1 primary key,
Dname varchar(10) unique not null,
Dnum number(4));
（1）SQL> insert into DEPT values(‘201411′,’ 宏光实业 ’,1000);
（2）SQL> insert into DEPT values(‘201412′,’ 宏光实业 ’,2000);
insert into DEPT values(‘201412′,’ 宏光实业 ’,2000)
ORA-00001: 违反唯一约束条件 (J2014213985.SYS_C0039963)
（3）SQL> insert into DEPT values(‘201411′,’ 长青实业 ’,2000);
insert into DEPT values(‘201411′,’ 长青实业 ’,2000)
ORA-00001: 违反唯一约束条件 (J2014213985.A1)
2．建立学生登记表 Student，要求学号在 9000 至 9999 之间，年龄 <29, 性别只能是’男’或’女’，姓名非空。
create table Student2(
sno number(10) check(sno>=9000 and sno<=9999),
sanme varchar(10) not null,
sage number(4) constraint a2 check(sage<29),
ssex varchar(4) check(ssex in(‘ 男 ’,’ 女 ’)));

(1)SQL> insert into student2 values(9000,’ 李二 ’,27,’ 男 ’);
(2)SQL> insert into student2 values(8888,’ 张三 ’,23,’ 男 ’);
insert into student2 values(8888,’ 张三 ’,23,’ 男 ’)
ORA-02290: 违反检查约束条件 (J2014213985.SYS_C0039970)
(3)SQL> insert into student2 values(9999,’ 张三 ’,30,’ 男 ’);
insert into student2 values(9999,’ 张三 ’,30,’ 男 ’)
ORA-02290: 违反检查约束条件 (J2014213985.A2)
3．修改表 Student 的结构，由年龄小于 29 改为小于 40。
SQL> alter table Student2 drop constraint a2;
SQL> alter table Student2 add constraint a2 check(sage<40);

(1)SQL> insert into student2 values(9994,’ 张三 ’,30,’ 男 ’)
(2)SQL> insert into student2 values(9996,’ 王五 ’,40,’ 男 ’);
insert into student2 values(9996,’ 王五 ’,40,’ 男 ’)
ORA-02290: 违反检查约束条件 (J2014213985.A2)
(3)SQL> insert into student2 values(9992,”,38,’ 男 ’);
insert into student2 values(9992,”,38,’ 男 ’)
ORA-01400: 无法将 NULL 插入 (“J2014213985″.”STUDENT2″.”SANME”)
4．建立职工表 EMP，要求每个职工的应发工资不得超过 3000 元。应发工资实际上就是实发工资列 Sal 与扣除项 Deduct 之和。
create table EMP(
sname varchar(10) primary key,
salary number(10) check(salary<=3000),
Sal number(10),
Deduct number(10));
(1)SQL> insert into EMP values(‘ 李二 ’,2600,2300,300);
(2)SQL> insert into EMP values(‘ 张三 ’,3100,2900,200);
insert into EMP values(‘ 张三 ’,3100,2900,200)
ORA-02290: 违反检查约束条件 (J2014213985.SYS_C0039991)
(3)SQL> insert into EMP values(‘ 李四 ’,3001,2900,101);
insert into EMP values(‘ 李四 ’,3001,2900,101)
ORA-02290: 违反检查约束条件 (J2014213985.SYS_C0039991)

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

实验分析：

在本次数据库实验中，我完成了实验要求。本次实验内容是关于 sql 语言进行用户权限的授予和回收，实体完整性，参照完整性及用户定义的完整性的定义。在课堂上，老师讲授了 oracle 的安全性和完整性控制相关知识，我也用笔练习写了 sql 语句，但是感觉印象还不是很深刻，有些不太理解。在实验课中我练习了 sql 语句，对课堂上所学的知识有了更深的理解，收获很多。实验中，我遇到了一些问题，通过查询资料和老师同学帮助最终解决了。遇到的问题如下：

1、在授予权限时，grant < 权限 > on table < 表名 > to < 用户 >；这样写后一直报错，后来同学告诉我不能加 table，因为 oracle 的数据库与数据库标准原理有一点区别，修改后，果然对了。

2、在切换用户时，我不知道怎么切换，记得老师讲过，就是想不起来具体怎么做，后来问了同学，同学告诉我这样写 con user/pass@orcl; 这样写果然切换用户成功了。

本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-12/138978.htm

正文完

星哥说事-微信公众号