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

实验题目:数据库安全性与完整性控制目 录一、实验目的1二、实验内容1三、实验要点及说明1四、实现方法1五、实验结果1六、源程序清单1七、思考及总结2一、实验目的通过本次实验，提高以下几个方面的能力1. 创建新用户2. 通过GRANT语句对新用户进行授权3. 通过REVOKE语句完成权限的回收4. 实体完整性的实现5. 参照完整性实现二、实验内容1. 完成教材中实例182. 完成教材中相应于完整性的实例。三、实验要点及说明1. 一定要熟练掌握GRANT语句与REVOKE语句的使用2. 一定要熟练掌握实体完整性与参照完整性控制3. 要读懂出错的提示信息四、实现方法 mysql 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 courseto 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 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 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, 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 | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA | u3 | % | *C4E74DDDC9CC9E2FDCDB7F63B127FB638831262E | u2 | % | *12033B78389744F3F39AC4CE4CCFCAD6960D8EA0 | u1 | % | *E6CC90B878B948C35E92B003C792C46C58C4AF40 | u4 | % | *908BE2B7EB7D7567F7FF98716850F59BA69AA9DB | u5 | % | *7534F9EAEE5B69A586D1E9C1ACE3E3F9F6FCC446 | u6 | % | *C3AB9ECDF746570BBF9DCAA9DB3586D25956DC93 | 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 | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA| u3 | % | *C4E74DDDC9CC9E2FDCDB7F63B127FB638831262E| u2 | % | *12033B78389744F3F39AC4CE4CCFCAD6960D8EA0| u1 | % | *E6CC90B878B948C35E92B003C792C46C58C4AF40| u4 | % | *908BE2B7EB7D7567F7FF98716850F59BA69AA9DB| u5 | % | *7534F9EAEE5B69A586D1E9C1ACE3E3F9F6FCC446| 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, 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)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 *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA WITH GRANT OPTION | GRANT PROXY ON TO rootlocalhost WITH GRANT OPTION |+-+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.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 for u1% |+-+| GRANT USAGE ON *.* TO u1% IDENTIFIED BY PASSWORD *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) - );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 like 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 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 thatcorresponds to your MySQL server version for the right syntax to use near courseto u3 at line 2只能给一个表授权mysql gran