實驗四數(shù)據(jù)庫安全性與完整性控制1

1、實驗題目：數(shù)據(jù)庫安全性與完整性控制目錄一、實驗?zāi)康?.二、實驗內(nèi)容1.三、實驗要點及說明四、實現(xiàn)方法1.五、實驗結(jié)果2.六、源程序清單 2.七、思考及總結(jié)6.、實驗?zāi)康耐ㄟ^本次實驗，提高以下幾個方面的能力1. 創(chuàng)建新用戶2. 通過 GRANT 語句對新用戶進行授權(quán)3. 通過 REVOKE 語句完成權(quán)限的回收4. 實體完整性的實現(xiàn)5. 參照完整性實現(xiàn)二、實驗內(nèi)容1. 完成教材中實例 1 82. 完成教材中相應(yīng)于完整性的實例。三、實驗要點及說明1. 一定要熟練掌握GRANT語句與REVOKE語句的使用2. 一定要熟練掌握實體完整性與參照完整性控制3. 要讀懂出錯的提示信息四、實現(xiàn)方法mysql g

2、rant all privileges - on table student,course- to u3;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near course to u3 at line 2只能給一個表授權(quán)mysql grant insert- on table sc- with grant option;ERROR 1064 (

3、42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near grant option at line 3不能定義可授權(quán)用戶五、實驗結(jié)果mysql create user u1% identified by 1;Query OK, 0 rows affected (0.00 sec) mysql create user u2% identified by 2;Query OK

4、, 0 rows affected (0.00 sec) mysql create user u3% identified by 3;Query OK, 0 rows affected (0.00 sec) mysql create user u4% identified by 4;Query OK, 0 rows affected (0.01 sec) mysql create user u5% identified by 5;Query OK, 0 rows affected (0.00 sec) mysql create user u6% identified by 6;Query OK

5、, 0 rows affected (0.00 sec) mysql create user u7% identified by 6;Query OK, 0 rows affected (0.00 sec) mysql create user u8% identified by 8;Query OK, 0 rows affected (0.00 sec) mysql select user, host, password from mysql.user;+ +| user | host| password |+ +| root | localhost | *E74858DB86EBA20BC3

6、3D0AECAE8A8108C56B17FA | u3 | %| *C4E74DDDC9CC9E2FDCDB7F63B127FB638831262E | u2| %| u1| %| u4| %| u5| %| u6| %| u7| %| u8| %+| *12033B78389744F3F39AC4CE4CCFCAD6960D8EA0 | *E6CC90B878B948C35E92B003C792C46C58C4AF40 | *908BE2B7EB7D7567F7FF98716850F59BA69AA9DB | *7534F9EAEE5B69A586D1E9C1ACE3E3F9F6FCC446

7、 | *C3AB9ECDF746570BBF9DCAA9DB3586D25956DC93 | *C3AB9ECDF746570BBF9DCAA9DB3586D25956DC93 | *6AF37A8C78E3A957D16D98F12788D1CFB2987A4C |9 rows in set (0.00 sec) mysql drop user u7%;Query OK, 0 rows affected (0.00 sec) mysql select user, host, password from mysql.user;+ +| user | host | password+ +| ro

8、ot | localhost | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA| u3| %| *C4E74DDDC9CC9E2FDCDB7F63B127FB638831262E| u2| %| *12033B78389744F3F39AC4CE4CCFCAD6960D8EA0| u1| %| *E6CC90B878B948C35E92B003C792C46C58C4AF40| u4| %| *908BE2B7EB7D7567F7FF98716850F59BA69AA9DB| u5| %| *7534F9EAEE5B69A586D1E9C1ACE3E3F9

9、F6FCC446| u6| %| *C3AB9ECDF746570BBF9DCAA9DB3586D25956DC93| u8| %| *6AF37A8C78E3A957D16D98F12788D1CFB2987A4C+8 rows in set (0.00 sec)mysql grant select- on table student- to u1;ERROR 1046 (3D000): No database selectedmysql use ch3;Database changedmysql grant select- on table student- to u1;Query OK,

10、 0 rows affected (0.00 sec)mysql grant all privileges- on table course- to u3;Query OK, 0 rows affected (0.00 sec)mysql grant update(sno),select- on table student- to u4;Query OK, 0 rows affected (0.00 sec)mysql grant insert- on table sc- to u5- with grant option;Query OK, 0 rows affected (0.00 sec)

11、mysql grant insert- on table sc- to u6;Query OK, 0 rows affected (0.00 sec)mysql show grants;+| Grants for rootlocalhost|+| GRANT ALL PRIVILEGES ON *.* TO rootlocalhost IDENTIFIED BY PASSWORD *E74 858DB86EBA20BC33D0AECAE8A8108C56B17FA WITH GRANT OPTION | GRANT PROXY ON TO rootlocalhost WITH GRANT OP

12、TION|+2 rows in set (0.00 sec)mysql show grants for u1;+| Grants for u1%|+| GRANT USAGE ON *.* TO u1% IDENTIFIED BY PASSWORD *E6CC90B878B948C35E92B003C792C46C58C4AF40 | GRANT SELECT ON ch3.stude nt TO u1%|+2 rows in set (0.00 sec)mysql revoke select- on student- from u1;Query OK, 0 rows affected (0.

13、00 sec)mysql show grants for u1;+| Grants for u1%|+PASSWORD| GRANT USAGE ON *.* TO u1% IDENTIFIED BY *E6CC90B878B948C35E92B003C792C46C58C4AF40 |+1 row in set (0.00 sec)mysql create table student(- sno char(9) primary key,- sname char(8) not null,- ssex char(2) check(ssex in(男，女)，- sage smallint,- sd

14、ept char(20)- );Query OK， 0 rows affected (0.09 sec)mysql create table student1(- sno char(9) primary key，- sname char(8) not null，- ssex char(2) ，- sage smallint，- sdept char(20)- ，- check(ssex=女or sn ame not like Ms.%)- );Query OK， 0 rows affected (0.08 sec)mysql create table student2(- sno numeri

15、c(6)- constraint c1 check (sno between 90000 and 99999)，- sname char(20)- constraint c2 not null，- sage numeric(3)- constraint c3 check (sage ssex char(2)- constraint c4 check (ssex in( 男 ，女)，- constraint skey primary key (sno)- );Query OK, 0 rows affected (0.02 sec)mysql alter table student2- drop constraint c1;六、源程序清單mysql grant all privileges- on table student,course- to u3;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near course to u3 at line 2只能給一個表授權(quán)mysql gran