shujuku
4.1把查询Student的权限授给用户U1
建立新的连接
CREATE USER U1 IDENTIFIED by "U1@123456" ;
GRANT SELECT
ON TABLE student
to U1;
SELECT *
FROM student
WHERE sname='田军';
4.2把对Student表和Course表的全部操作权限授予用户U2和用户U3
CREATE USER U2 IDENTIFIED by "U2@123456" ;
CREATE USER U3 IDENTIFIED by "U3@123456" ;
GRANT all PRIVILEGES
on TABLE student,course
to U2,U3;
验证语句:
INSERT INTO student VALUES ('200215131','张为民','男',18,'CS');
4.3把对表SC的查询权限授予所有用户
GRANT SELECT
on TABLE sc
to public;
验证语句:
SELECT *
FROM sc
WHERE grade BETWEEN 70 AND 80
INSERT INTO sc VALUES('20181206','1224','100');
4.4把查询Student表和修改学生学号的权限授给用户U4
CREATE USER U4 IDENTIFIED by "U4@123456" ;
GRANT UPDATE(Sno),SELECT
ON TABLE student
TO U4;
验证语句:
UPDATE student
SET sno='200215127'
WHERE sname='王芳';
UPDATE student
SET sname='李雷'
WHERE sno='200215127';
4.5把对表SC的 insert权限授予U5用户,并将此权限在授予其他用户。
CREATE USER U5 IDENTIFIED by "U5@123456";
GRANT INSERT
ON TABLE sc
to U5
WITH GRANT OPTION;
验证语句:
INSERT INTO sc VALUES('200215135','0204','1');//正确语句,sno、cno在student和course中是存在的
GRANT INSERT
ON TABLE sc
to U4;