MySQL实验07

create database lienci1114;
use lienci1114;

CREATE TABLE student
(sno CHAR(9) PRIMARY KEY,
sname CHAR(20),
ssex CHAR(2),
sage SMALLINT,
sdept CHAR(20));

CREATE TABLE course
(cno CHAR(4) PRIMARY KEY,
cname CHAR(40),
cpno CHAR(4),
ccredit SMALLINT,FOREIGN KEY(cpno)REFERENCES course(cno));

CREATE TABLE sc
(sno CHAR(9),
cno CHAR(4),
grade SMALLINT,
PRIMARY KEY(sno,cno),
FOREIGN KEY(cno)REFERENCES course(cno),
FOREIGN KEY(sno)REFERENCES student(sno));

INSERT INTO student VALUES('2019001','李勇','男',19,'CS');
INSERT INTO student VALUES('2019002','刘晨','女',19,'CS');
INSERT INTO student VALUES('2019003','王敏','女',18,'CS');
INSERT INTO student VALUES('2019004','刘星','男',21,'IS');
INSERT INTO student VALUES('2019005','王离','女',18,'CS');
INSERT INTO student VALUES
('2019006','董琦','女',19,'IS'),
('2019007','王离','女',22,'IS'),
('2019008','李明明','男',18,'CS'),
('2019009','徐伟','男',18,'CS');

INSERT INTO course VALUES('1','数据库',NULL,4);
INSERT INTO course VALUES('2','数学',NULL,2);
INSERT INTO course VALUES('3','信息系统',NULL,4);
INSERT INTO course VALUES('4','操作系统',NULL,3);
INSERT INTO course VALUES('5','数据结构',NULL,4);
INSERT INTO course VALUES('6','数据处理',NULL,2);

INSERT INTO sc VALUES('2019001','1',92);
INSERT INTO sc VALUES('2019001','2',85);
INSERT INTO sc VALUES('2019002','3',88);
INSERT INTO sc VALUES('2019002','2',90);
INSERT INTO sc VALUES('2019003','3',80);
INSERT INTO sc VALUES
('2019003','6',80),
('2019004','4',85),
('2019005','6',57),
('2019006','3',50),
('2019009','5',89),
('2019008','3',96),
('2019007','5',80);

SELECT * FROM mysql.user;

CREATE USER U1@localhost IDENTIFIED BY '123';
CREATE USER U2@localhost IDENTIFIED BY '123';
CREATE USER U3@localhost IDENTIFIED BY '123';
CREATE USER U4@localhost IDENTIFIED BY '123';
CREATE USER U5@localhost IDENTIFIED BY '123';
CREATE USER U6@localhost IDENTIFIED BY '123';
CREATE USER U7@localhost IDENTIFIED BY '123';
CREATE USER U8@localhost IDENTIFIED BY '123';
CREATE USER U9@localhost IDENTIFIED BY '123';

GRANT SELECT ON TABLE sc TO U1@localhost;
GRANT SELECT ON TABLE sc TO U2@localhost;

/*在root用户下把对Student表和Course表的全部权限授予用户U2和U3*/
GRANT ALL PRIVILEGES 
      ON TABLE Student
      TO U2@localhost,U3@localhost;

GRANT ALL PRIVILEGES 
      ON TABLE Course
      TO U2@localhost,U3@localhost;

/*把查询Student表和修改学生姓名的权限授给用户U4，对属性列的授权时必须明确指出相应属性列名*/
GRANT UPDATE(Sname), SELECT 
		ON TABLE Student 
		TO U4@localhost;

/*把对表SC的查看和INSERT权限授予U5用户，并允许他再将此权限授予其他用户*/
  GRANT INSERT ,select
    ON TABLE SC 
    TO U5@localhost
   WITH GRANT OPTION;
/*登录新用户U5对授权后的用户权限验证，验证U5用户插入SC表的权限*/
GRANT SELECT,INSERT 
             ON TABLE SC 
             TO U6@localhost
             WITH GRANT OPTION;

/*在用户U5下授权，U6还可以将此权限授予U7。*/
GRANT SELECT,INSERT 
             ON TABLE SC 
             TO U6@localhost
             WITH GRANT OPTION;
/*登录新用户U6对授权后的用户权限验证，验证U6用户插入SC表的权限*/

/*在用户U6下授权 但U7不能再传播此权限。*/
GRANT SELECT,INSERT 
	         ON TABLE SC 
             TO U7@localhost;

/*在用户U7下测试是否出错,在用户U8下能看到数据库和表吗
*/
GRANT SELECT,INSERT 
	         ON TABLE SC 
             TO U8 @localhost;

/*把用户U4修改学生姓名的权限收回*/
REVOKE UPDATE(Sname)
		ON TABLE Student 
		FROM U4@localhost;

/*收回U2，U3用户对表student的所有权限*/
REVOKE ALL PRIVILEGES
		ON TABLE student 
		FROM U2@localhost,U3@localhost;

/* 把用户U5对SC表的INSERT权限收回*/
REVOKE SELECT,INSERT 
		ON TABLE SC 
		FROM U5@localhost CASCADE;

/* 把用户U5对SC表的INSERT权限收回*/
REVOKE SELECT, INSERT   
ON SC   
FROM U5@localhost;

/*在用户U5下测试查看*/`
INSERT INTO sc VALUES('2019009','1',92);

/*在用户U6下测试*/
INSERT INTO sc VALUES('2019001','5',92);