数据库实验：SQL的数据控制

数据控制

数据控制SQL语句(DCL)是一类可对用户数据访问权进行控制的操作语句，可以控制特定用户或角色对数据表、视图、存储过程、触发器等数据对象的访问权限。主要有GRANT、REVOKE、DENY语句操作来实现。

这个文档中有SQL Server的一些相关操作：
https://docs.microsoft.com/zh-cn/sql/t-sql/tutorial-writing-transact-sql-statements?view=sql-server-ver15

实验目的

（1）理解MySql数据库的安全性相关的概念
（2）掌握MySql的验证模式、登录管理、用户管理、角色管理、权限管理等操作

实验内容

（1）MySql的安全认证模式
（2）管理数据库用户
（3）管理数据库角色
（4）权限管理

实验要求

（1）掌握MySql的安全认证模式的概念及设置方法
（2）掌握MySql登录账号、数据库用户、数据库角色的添加、修改、删除等操作，以及对数据库用户、角色进行权限授予和回收等操作
（3）理解架构(Schema)、用户(User)、角色(Role)和登录(Login)等基本概念及其关系

实验过程

实验内容提纲

利用S_T.sql建立示例数据库S_T

添加SQL Server登录帐号：4个
tempuser, temp1, temp2, temp3;
密码分别为：tempqq, qq1, qq2, qq3
为S_T数据库添加(数据库)用户：4个
tempuser, temp1, temp2, temp3
（与SQL Server登录帐号一一对应（映射））
为S_T数据库添加自定义(数据库)角色: 1个，即db_operator
将数据库用户temp2和temp3添加到db_operator角色中，成为其角色成员
将在S_T数据库中创建表、创建视图的权限授予数据库用户tempuser
将SELECT，UPDATE(Sname,Sage,Ssex,Sdept) ON Student的权限授予数据库用户temp1
将SELECT ON Student,Course,SC的权限授予数据库角色db_operator
检验结果：用创建的登录账号替换sa系统账号，来尝试与数据库服务器建立连接，对数据表中的数据进行存取操作，并记录与分析可能会遇到的问题。尝试通过授予更高的权限来解决问题
从数据库用户temp1回收UPDATE(Sname,Ssex,Sdept) ON Student的权限，从数据库角色db_operator回收SELECT ON SC的权限，然后检验结果的正确性

实验过程

创建一个MySQL用户testuser，密码为qq，数据库为S_T，默认语言为英语：

CREATE USER ‘testuser’@‘localhost’ IDENTIFIED BY ‘qq’;
GRANT ALL PRIVILEGES ON S_T.* TO ‘testuser’@‘localhost’;
SET GLOBAL init_connect=‘SET NAMES utf8’;

创建一个MySQL用户test1，密码为qq1，数据库为S_T，默认语言为英语：

CREATE USER ‘test1’@‘localhost’ IDENTIFIED BY ‘qq1’;
GRANT ALL PRIVILEGES ON S_T.* TO ‘test1’@‘localhost’;
SET GLOBAL init_connect=‘SET NAMES utf8’;

创建一个MySQL用户test2，密码为qq2，数据库为S_T_1，默认语言为英语：

CREATE USER ‘test2’@‘localhost’ IDENTIFIED BY ‘qq2’;
GRANT ALL PRIVILEGES ON S_T_1.* TO ‘test2’@‘localhost’;
SET GLOBAL init_connect=‘SET NAMES utf8’;

创建一个MySQL用户test3，密码为qq3，数据库为S_T_1，默认语言为英语：

CREATE USER ‘test3’@‘localhost’ IDENTIFIED BY ‘qq3’;
GRANT ALL PRIVILEGES ON S_T_1.* TO ‘test3’@‘localhost’;
SET GLOBAL init_connect=‘SET NAMES utf8’;

修改用户testuser的密码为testqq：

ALTER USER ‘testuser’@‘localhost’ IDENTIFIED BY ‘testqq’;

删除用户testuser：

DROP USER ‘testuser’@‘localhost’;

使用数据库S_T：

USE S_T;

授予用户testuser、test1、test2、test3数据库访问：

GRANT ALL PRIVILEGES ON S_T.* TO ‘testuser’@‘localhost’;
GRANT ALL PRIVILEGES ON S_T.* TO ‘test1’@‘localhost’;
GRANT ALL PRIVILEGES ON S_T.* TO ‘test2’@‘localhost’;
GRANT ALL PRIVILEGES ON S_T.* TO ‘test3’@‘localhost’;

收回用户test3的数据库访问：

REVOKE ALL PRIVILEGES ON S_T.* FROM ‘test3’@‘localhost’;

使用数据库S_T：

USE S_T;

创建一个角色db_operator：

CREATE ROLE ‘db_operator’;

添加用户test2、test3为角色db_operator的成员：

GRANT ‘db_operator’ TO ‘test2’@‘localhost’, ‘test3’@‘localhost’;

删除角色db_operator：

DROP ROLE ‘db_operator’;

系统管理员授予用户testuser如下权限：CREATE TABLE、CREATE VIEW：

GRANT CREATE TABLE, CREATE VIEW TO ‘testuser’@‘localhost’;

授予用户test1对Student表的SELECT、UPDATE(Sname,Sage,Ssex,Sdept)权限：

GRANT SELECT, UPDATE(Sname,Sage,Ssex,Sdept) ON Student TO ‘test1’@‘localhost’;

授予角色db_operator对Student、Course、SC表的SELECT权限：

GRANT SELECT ON Student, Course, SC TO ‘db_operator’;

收回用户testuser的CREATE TABLE、CREATE VIEW权限：

REVOKE CREATE TABLE, CREATE VIEW FROM ‘testuser’@‘localhost’;

收回用户test1对Student表的SELECT、UPDATE(Sname,Ssex,Sdept)权限：

REVOKE SELECT, UPDATE(Sname,Ssex,Sdept) ON Student FROM ‘test1’@‘localhost’;

收回角色db_operator对Student表的SELECT权限：

REVOKE SELECT ON Student FROM ‘db_operator’;