实验四数据库安全性与完整性控制1

1、实验题目：数据库安全性与完整性控制目录一、实验目的1.二、实验内容1.三、实验要点及说明四、实现方法1.五、实验结果2.六、源程序清单 2.七、思考及总结6.、实验目的通过本次实验，提高以下几个方面的能力1. 创建新用户2. 通过 GRANT 语句对新用户进行授权3. 通过 REVOKE 语句完成权限的回收4. 实体完整性的实现5. 参照完整性实现二、实验内容1. 完成教材中实例 1 82. 完成教材中相应于完整性的实例。三、实验要点及说明1. 一定要熟练掌握GRANT语句与REVOKE语句的使用2. 一定要熟练掌握实体完整性与参照完整性控制3. 要读懂出错的提示信息四、实现方法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只能给一个表授权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不能定义可授权用户五、实验结果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只能给一个表授权mysql gran