1、實驗題目:數據庫安全性與完整性控制目錄一、實驗目的1.二、實驗內容1.三、實驗要點及說明1.四、實現方法1.五、實驗結果2.六、源程序清單 2.七、思考及總結6.、實驗目的通過本次實驗，提高以下幾個方面的能力1 .創建新用戶2 .通過GRANT語句對新用戶進行授權3 .通過REVOKE語句完成權限的回收4 .實體完整性的實現5 .參照完整性實現二、實驗內容1 .完成教材中實例1 82 .完成教材中相應于完整性的實例。三、實驗要點及說明1 .一定要熟練掌握GRANT語句與REVOKE語句的使用2 . 一定要熟練掌握實體完整性與參照完整性控制3 .要讀懂出錯的提示信息四、實現方法mysql>

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

3、gt; with grant option;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual thatcorresponds to your MySQL server version for the right syntax to use near 'grantoption' at line 3不能定義可授權用戶五、實驗結果mysql> create user 'u1''%' identified by '1'Query OK

4、, 0 rows affected (0.00 sec)mysql> create user 'u2''%' identified by '2'Query OK, 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 b

5、y '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, 0 rows affected (0.00 sec)mysql> create user 'u7'

6、9;%' 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 | *E74858DB86EBA20BC33D

7、0AECAE8A8108c56B17FA | u3 | %| *C4E74DDDC9CC9E2FDCDB7F63B127FB638831262E | u2| %|*12033B78389744F3F39AC4CE4CCFCAD6960D8EA0 | u1| %|*E6CC90B878B948C35E92B003C792C46C58C4AF40 | u4| %|*908BE2B7EB7D7567F7FF98716850F59BA69AA9DB | u5| %|*7534F9EAEE5B69A586D1E9C1ACE3E3F9F6FCC446 | u6| %|*C3AB9ECDF746570BBF

8、9DCAA9DB3586D25956DC93 | u7| %|*C3AB9ECDF746570BBF9DCAA9DB3586D25956DC93 | u8| %|*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+|

9、root | localhost | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FAu3| %| *C4E74DDDC9CC9E2FDCDB7F63B127FB638831262Eu2| %| *12033B78389744F3F39AC4CE4CCFCAD6960D8EA0u1| %| *E6CC90B878B948C35E92B003C792C46C58C4AF40u4| %| *908BE2B7EB7D7567F7FF98716850F59BA69AA9DBu5| %| *7534F9EAEE5B69A586D1E9C1ACE3E3F9F6FCC446

10、u6| %| *C3AB9ECDF746570BBF9DCAA9DB3586D25956DC93u8| %+| *6AF37A8C78E3A957D16D98F12788D1CFB2987A4C 8 rows in set (0.00 sec) mysql> grant select- > on table student- > to u1;ERROR 1046 (3D000): No database selected mysql> use ch3;Database changed mysql> grant select- > on table stude

11、nt- > to u1;Query OK, 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 s

12、c- > to u5- > with grant option;Query OK, 0 rows affected (0.00 sec) 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 'root''localhost' IDENTIFIED BY

13、PASSWORD '*E74 858DB86EBA20BC33D0AECAE8A8108c56B17FA' WITH GRANT OPTION | GRANT PROXY ON ”" TO 'root''localhost' WITH GRANT OPTION | +2 rows in set (0.00 sec) mysql> show grants for u1; + | Grants for u1% | +| GRANT USAGE ON *.* TO 'u1''%' IDENTIFIED B

14、Y PASSWORD '*E6CC90B878B948C35E92B003C792C46C58C4AF40' | | GRANT SELECT ON 'ch3'.'student' TO 'u1''%' |+2 rows in set (0.00 sec)mysql> revoke select- > on student- > from u1;Query OK, 0 rows affected (0.00 sec)mysql> show grants for u1; +| Grants f

15、or u1% I+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'男','女&

16、#39;)，- > sage smallint,- > sdept 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 sname not l

17、ike 'Ms.%')- > );Query OK, 0 rows affected (0.08 sec)mysql> create table student2(- > sno numeric(6)- > constraint c1 check (sno between 90000 and 99999),- > sname char(20)- > constraint c2 not null,- > sage numeric(3)- > constraint c3 check (sage<=30),- > ssex

18、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 只