茶飘香~

lakers009

　　实验内容：
　　

（一）    授权　　

1.    以dba用户的身份登陆oracle，创建用户u1+学号后四位，u2+学号后四位。　　
SQL
> create user u1_3985>
SQL> create user u2_3985>
SQL> create user u3_3985>
SQL> create user u4_3985>
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
>>
SQL>>
　　
(
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;  这样写果然切换用户成功了。
　　在本次试验中收获很多，很开心。

mayiwen123456

查询jsj***用户的名字中第二字为“勇”字的学生的姓名和学号。
> select sname,sno from j2014213985.student where sname like '_明';　　
上面的应该是明，