数据库实验四 数据库安全性和完整性

一．实验目的

1. 加深对数据库安全性和完整性的理解
2. 学会授权与回收
3. 理解并体会数据库实体完整性、参照完整性、用户定义的完整性约束条件的作用

二. 实验内容

1.题目要求

对已建好的各表和用户进行权限的授权和回收操作，进行完操作之后检查授权过的用户是否真正具有授予的数据操作的权力了，进行完权力收回操作之后的用户是否确实丧失了收回的数据操作的权力。

定义各种完整性约束条件，之后输入各种数据以验证约束条件的作用。

1. 设置用户a对SPJ表的查询权限。以a登录，验证a的权限。
2. 设置用户b对S表、P表具有修改的权限，并要求b能够将该权限授予其它用户c。分别以b、c登录，验证b、c的权限。
3. 收回用户a、b的权限，并验证用户c权限的情况。
4. 对实验1中创建的表，用图形用户界面建立外键联系，并验证外键的作用。
5. 对实验1中创建的表，设置零件的颜色必须在红、橙、黄、绿、青、蓝、紫 七种颜色范围内，零件重量不能超过50的约束条件，并给这两种约束条件命名，名字为自己姓名的全拼。
6. 设置SPJ表中供应零件的数量都不能超过1000
7. 设置S表中的供应商号以字母‘S’开头
8. 验证各个表的实体完整性。

2.实验结果

代码

SET  sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

create user 'a'@'%' identified with mysql_native_password by '123456';
create user 'b'@'%' identified with mysql_native_password by '123456';
create user 'c'@'%' identified with mysql_native_password by '123456';
-- 1.	设置用户a对SPJ表的查询权限。以a登录，验证a的权限。
GRANT SELECT
ON spj
TO a;
show grants for a;
-- 2.	设置用户b对S表、P表具有修改的权限，并要求b能够将该权限授予其它用户c。分别以b、c登录，验证b、c的权限。
GRANT UPDATE 
ON s
TO b
WITH GRANT OPTION;
GRANT UPDATE 
ON p
TO b
WITH GRANT OPTION;

SELECT *
FROM mysql.tables_priv
WHERE `User`='b';

SELECT *
FROM mysql.tables_priv
WHERE `User`='c';

-- 登录用户b后的操作
-- GRANT UPDATE 
-- ON s
-- TO c;
-- GRANT UPDATE 
-- ON p
-- TO c;
-- show grants for b;
-- 登录用户c后的操作
-- show grants for c;
-- 3.	收回用户a、b的权限，并验证用户c权限的情况。
REVOKE SELECT ON spj FROM a;
REVOKE UPDATE ON s FROM b;
REVOKE UPDATE ON p FROM b;
show grants for c;
-- 4.	对实验1中创建的表，用图形用户界面建立外键联系，并验证外键的作用。
INSERT INTO spj
VALUES('SS','PP','JJ',50);
-- 验证：
DELETE
FROM S
WHERE sno='s1';
-- 5.	对实验1中创建的表，设置零件的颜色必须在红、橙、黄、绿、青、蓝、紫 七种颜色范围内，零件重量不能超过50的约束条件，并给这两种约束条件命名，
-- 名字为自己姓名的全拼。
ALTER TABLE p
ADD CONSTRAINT xingmingquanpin1 CHECK(COLOR in ('红','橙','黄','绿','青','蓝','紫'));
ALTER TABLE p
ADD CONSTRAINT xingmingquanpin2 CHECK(WEIGHT<=50);
-- 6.	设置SPJ表中供应零件的数量都不能超过1000
ALTER TABLE spj
ADD CONSTRAINT xingmingquanpin3 CHECK(QTY<=1000);
-- 7.	设置S表中的供应商号以字母‘S’开头
ALTER TABLE s
ADD CONSTRAINT xingmingquanpin4 CHECK(SNO LIKE 'S%');
-- 5、6、7题验证
SELECT *
FROM information_schema.`TABLE_CONSTRAINTS`
WHERE CONSTRAINT_NAME='xingmingquanpin1' or CONSTRAINT_NAME='xingmingquanpin2' or CONSTRAINT_NAME='xingmingquanpin3' or CONSTRAINT_NAME='xingmingquanpin4';
-- 8.	验证各个表的实体完整性。
INSERT INTO s
VALUES('S1','sname','sstatus','city');

INSERT INTO p
VALUES('p1','pname','红',10);

INSERT INTO j
VALUES('j1','jname','city');

INSERT INTO spj
VALUES('S1','p1','j1',100);

实验结果截图

创建用户

第一题

第二题

登录b将该权限授予其它用户c并验证b的权限：

从mysql数据库的tables_priv表进行查询：

登录c并验证c的权限：

从mysql数据库的tables_priv表进行查询：

第三题

第四题

用图形用户界面建立外键联系：

验证外键的作用：

第五到七题

验证添加约束的结果

第八题