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

《试验四数据库安全性与完整性控制1》由会员分享，可在线阅读，更多相关《试验四数据库安全性与完整性控制1（7页珍藏版）》请在装配图网上搜索。

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

2、nt 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- with grant option;ERROR 1064 (420

3、00): 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, 0 rows affected (0.00 sec)mysql create user u2% identified by 2;Query OK, 0 ro

4、ws 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, 0 rows a

5、ffected (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 | *E74858DB86EBA20BC33D0AECAE8A8108c56

6、B17FA | u3 | %| *C4E74DDDC9CC9E2FDCDB7F63B127FB638831262E | u2| %|*12033B78389744F3F39AC4CE4CCFCAD6960D8EA0 | u1| %|*E6CC90B878B948C35E92B003C792C46C58C4AF40 | u4| %|*908BE2B7EB7D7567F7FF98716850F59BA69AA9DB | u5| %|*7534F9EAEE5B69A586D1E9C1ACE3E3F9F6FCC446 | u6| %|*C3AB9ECDF746570BBF9DCAA9DB3586D25

7、956DC93 | 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+| root | localhost | *E74858DB86EB

8、A20BC33D0AECAE8A8108C56B17FAu3| %| *C4E74DDDC9CC9E2FDCDB7F63B127FB638831262Eu2| %| *12033B78389744F3F39AC4CE4CCFCAD6960D8EA0u1| %| *E6CC90B878B948C35E92B003C792C46C58C4AF40u4| %| *908BE2B7EB7D7567F7FF98716850F59BA69AA9DBu5| %| *7534F9EAEE5B69A586D1E9C1ACE3E3F9F6FCC446u6| %| *C3AB9ECDF746570BBF9DCAA9

9、DB3586D25956DC93u8| %+| *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 student- to u1;Query OK, 0 rows affected (0.00 sec) mysql grant

10、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) mysql grant insert- on table sc- to

11、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 OPTION | +2 rows in set (0.00 se

12、c) mysql show grants for u1; + | Grants for u1% | +| GRANT USAGE ON *.* TO u1% IDENTIFIED BY 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 gra

13、nts for u1; +| Grants for 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男,女)，- sage smallint,- sdept char(20)- );Quer

14、y 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 like Ms.%)- );Query OK, 0 rows affected (0.08 sec)mysql create table student2(- sno numeric(6)- constraint c1 c

15、heck (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;六、源程序清单my

16、sql 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 grant insert- on table sc- with grant option;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 grant option at line 3 不能定义可授权用户七、思考及总结在实验中代码很是困难。我会多加练习的