資料庫---實驗四 oracle的安全性和完整性控制

實驗內容：實驗分析：在本次資料庫實驗中，我完成了實驗要求。本次實驗內容是關於sql語言進行用戶許可權的授予和回收，實體完整性，參照完整性及用戶定義的完整性的定義。在課堂上，老師講授了oracle的安全性和完整性控制相關知識，我也用筆練習寫了sql語句，但是感覺印象還不是很深刻，有些不太理解。在實驗 ...

實驗內容：

（一）    授權
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; 這樣寫果然切換用戶成功了。

在本次試驗中收穫很多，很開心。