实验基本要求

1.知道数据库安全性采用的方法。

2.掌握数据库用户的创建。

3.掌握数据库的权限的分配。

4.掌握数据表角色的操作。

实验概述

1.创建不同的用户;

2.分配对象权限给不同的用户;

3.创建角色;

4.授予角色权限;

实验环境

MySQL5.7;MySQL8.0;Navicat15;

实验内容

该实验全部基于实验二-数据定义

(1)创建用户U1,U2,U3,U4,U5,U6,U7,并设置密码;


CREATE USER 'u1'@'localhost' IDENTIFIED by 'u1password';

CREATE USER 'u2'@'localhost' IDENTIFIED by 'u2password';

CREATE USER 'u3'@'localhost' IDENTIFIED by 'u3password';

CREATE USER 'u4'@'localhost' IDENTIFIED by 'u4password';

CREATE USER 'u5'@'localhost' IDENTIFIED by 'u5password';

CREATE USER 'u6'@'localhost' IDENTIFIED by 'u6password';

CREATE USER 'u7'@'localhost' IDENTIFIED by 'u7password';

这里统一设置密码格式为用户 + password关键字,便于记忆,用户都为本地用户localhost

(2)把查询Student表权限授给用户U1;


GRANT SELECT ON TABLE stu.student TO 'u1'@'localhost';

我的数据库名叫做stu,所以指定stu数据库中的student表stu.student

(3)把对Student表和Course表的全部权限授予用户U2和U3;


GRANT ALL PRIVILEGES ON stu.student TO 'u2'@'localhost','u3'@'localhost';

GRANT ALL PRIVILEGES ON stu.course TO 'u2'@'localhost','u3'@'localhost';

studentcourse是两个表,这里分开设置。

(4)把查询Student表和修改学生学号的权限授给用户U4;


GRANT SELECT ON stu.student TO 'u4'@'localhost';

GRANT UPDATE(Sno) ON stu.student TO 'u4'@'localhost';

学生学号列名为Sno

(5)把对表SC的INSERT权限授予U5用户,并允许他再将此权限授予其他用户;


GRANT INSERT ON stu.sc TO 'u5'@'localhost' WITH GRANT OPTION;

允许授权其他用户,只需在原来的基础上加上WITH GRANT OPTION关键字即可。

(6)把对表SC的INSERT权限授予U6用户,并允许他再将此权限授予其他用户;


GRANT INSERT ON stu.sc TO 'u6'@'localhost' WITH GRANT OPTION;

(7)把对表SC的INSERT权限授予U7用户,并不允许他再将此权限授予其他用户;


GRANT INSERT ON stu.sc TO 'u7'@'localhost';

(8)把用户U4修改学生学号的权限收回。


REVOKE UPDATE(Sno) ON stu.student FROM 'u4'@'localhost';

(9)把用户U5对SC表的INSERT权限收回。


REVOKE INSERT ON stu.sc FROM 'u5'@'localhost';

(10)通过角色来实现将一组权限授予一个用户。

经查阅官方参考手册得知在MySQL5.7版本中,并没有角色功能,所以通过虚拟机重新安装了MySQL8.0的版本以及Navicat15。以下MySQL语句全部基于MySQL8.0


CREATE ROLE 'R1'@'localhost';

SET DEFAULT ROLE ALL TO 'root'@'localhost';

GRANT SELECT, UPDATE, INSERT ON TABLE Student TO 'R1'@'localhost';

GRANT 'R1'@'localhost' TO 'u1'@'localhost';

这一步是分4小步实现的:

1.首先创建一个角色 R1;

2.然后使用GRANT语句,使角色R1拥有Student表的SELECT、UPDATE、INSERT权限;

3.激活角色;

4.将这个角色授予用户u1,使他具有角色R1所包含的全部权限;

(11)角色的权限修改。


GRANT DELETE ON TABLE Student TO 'R1'@'localhost';

DELETE权限授予R1角色。

实验所用SQL语句汇总


CREATE USER 'u1'@'localhost' IDENTIFIED by 'u1password';

GRANT SELECT ON TABLE stu.student TO 'u1'@'localhost';



CREATE USER 'u2'@'localhost' IDENTIFIED by 'u2password';

CREATE USER 'u3'@'localhost' IDENTIFIED by 'u3password';

GRANT ALL PRIVILEGES ON stu.student TO 'u2'@'localhost','u3'@'localhost';

GRANT ALL PRIVILEGES ON stu.course TO 'u2'@'localhost','u3'@'localhost';



CREATE USER 'u4'@'localhost' IDENTIFIED by 'u4password';

GRANT SELECT ON stu.student TO 'u4'@'localhost';

GRANT UPDATE(Sno) ON stu.student TO 'u4'@'localhost';



CREATE USER 'u5'@'localhost' IDENTIFIED by 'u5password';

GRANT INSERT ON stu.sc TO 'u5'@'localhost' WITH GRANT OPTION;



CREATE USER 'u6'@'localhost' IDENTIFIED by 'u6password';

GRANT INSERT ON stu.sc TO 'u6'@'localhost' WITH GRANT OPTION;



CREATE USER 'u7'@'localhost' IDENTIFIED by 'u7password';

GRANT INSERT ON stu.sc TO 'u7'@'localhost';



REVOKE UPDATE(Sno) ON stu.student FROM 'u4'@'localhost';

REVOKE INSERT ON stu.sc FROM 'u5'@'localhost';



CREATE ROLE 'R1'@'localhost';

SET DEFAULT ROLE ALL TO 'root'@'localhost';

GRANT SELECT, UPDATE, INSERT ON TABLE Student TO 'R1'@'localhost';

GRANT 'R1'@'localhost' TO 'u1'@'localhost';

GRANT DELETE ON TABLE Student TO 'R1'@'localhost';