实验四 数据库的安全性实验 【数据库原理】

前言

以下内容源自数据库原理实验
仅供学习交流使用

推荐

实验四 数据库的安全性实验

实验7 触发器

1．实验目的

学习触发器的使用，体会触发器执行的时机，加深对触发器功能和作用的理解。

2．实验内容

触发器是一种实施复杂数据完整性的特殊存储过程，在对表或视图执行INSERT、UPDATE或DELETE语句时自动触发执行，以防止对数据进行不正确、未授权或不一致的修改。它不可以像调用存储过程一样由用户直接调用执行。

创建触发器可以在查询分析器中用SQL语句完成，也可以用企业管理器完成。在企业管理器中书写触发器时，通过右键单击创建触发器的表，在弹出的快捷菜单中依次选择“所有任务”→“管理触发器”，就打开“触发器属性”对话框，然后在该对话框的“文本”框中输入创建触发器的SQL语句。

要求在Market数据库中，完成以下（1）～（5）题创建触发器的操作，然后在相关的表上执行INSERT、UPDATE或DELETE语句，观察他们的执行结果。

（1）在Customers表上建立删除触发器，实现Customers表和Orders表的级联删除。

（2）在Orders表上建立插入触发器，当向表中添加一条订货记录时，若订单中的商品状态为整理（Status=1），则不能插入该条记录。

（3）在Orders表上建立插入触发器，当添加订单时，减少Goods表中相应商品的库存量。

（4）在Orders表上建立触发器，不允许对订单日期进行修改。

（5）建立触发器，实现参照完整性约束，即若在Orders表中添加一条订单记录时，则该订单中的商品必须在Goods表中存在，否则不允许添加该记录。

实验8 安全性实验

1．实验目的

（1）理解SQL Server验证用户身份的过程，掌握设置身份验证模式的方法。

（2）理解登录账号的概念，掌握混合认证模式下登录账号的建立与取消方法。

（3）掌握混合认证模式下数据库用户的建立与取消方法。

（4）掌握数据库用户权限的设置方法。

（5）理解角色的概念，掌握管理角色技术。

2．实验内容

数据库的安全性主要是用户管理和权限管理。在A.5节中介绍了SQL Server的安全性管理，复习其内容，并完成以下实验。

（1）在企业管理器中打开“SQL Server属性（配置）”对话框，设置身份验证模式为“SQL Server和Windows”模式（即混合模式）。

在A.5节中已经介绍，一个用户要访问SQL Server 2000数据库中的数据，他必须要经过3个认证过程。第一，验证用户连接到SQL Server 2000数据库服务器的资格；第二，验证对具体数据库的访问权；第三，验证用户是否具有对所操作对象的操作权。理解和体会这一认证过程。

（2）创建、管理数据库服务器的登录账号。

① 用T-SQL语句创建、查看、删除登录账号。

创建一个名为student、密码为111、使用的默认数据库为JWGL的登录账号。

EXEC sp_addlogin 'student', '111', 'JWGL'

查看登录账号为EXEC sp_helplogins

删除登录账号为EXEC sp_droplogin 'student'

② 在企业管理器中使用A.5.2节中介绍的方法创建一个登录账号。

（3）创建、管理数据库用户。用户是基于数据库的名称是和登录账号相关联的。只有DBA和数据库所有者才有执行系统存储过程sp_granddbaccess的权力。

① 用T-SQL语句创建、查看、删除数据库用户。

为数据库JWGL创建一个用户user1，该用户登录SQL Server服务器的账号为wang，登录密码为secret，写出相应的程序代码为

EXEC sp_addlogin 'wang', 'secret', 'JWGL'

GO

EXEC sp_grantdbaccess 'wang', 'user1'

查看数据库用户为EXEC sp_helpuser

删除数据库中的“wang”用户为EXEC sp_revokedbaccess 'wang'

② 使用企业管理器创建、查看、删除数据库用户。

使用企业管理器为数据库JWGL创建一个用户user2，该用户登录SQL Server服务器的账号为zheng，登录密码为123456。

（4）管理用户权限。用户权限的管理可以使用企业管理器，也可以使用T-SQL语句。分别使用企业管理器和T-SQL语句完成第4章习题中的用户管理和用户权限管理。

（5）创建、管理数据库角色。

若一个小组共3个成员，他们对JWGL具有相同的操作权限，具体权限为对Student、Course表只能进行数据查询；对SC表可以进行查询、修改、删除和插入。

可以创建一个角色（如role），给该角色赋予相应的权限，然后给每个成员建立一个登录账号（如分别为lin，wang，zheng），并将每个成员的登录账号添加为数据库角色role的成员。其代码如下

EXEC sp_addrole 'role'

GO

GRANT SELECT ON student, course TO role

GRANT SELECT, UPDATE, DELETE, INSERT ON SC TO role

GO

EXEC sp_addrolemember 'role', 'lin'

EXEC sp_addrolemember 'role', 'wang'

EXEC sp_addrolemember 'role', 'zheng'

删除角色role，然后再使用企业管理器完成以上任务。

删除角色role时，必须保证它不包含任何数据库用户，即要先删除其下的所有成员。

EXEC sp_droprolemember 'role', 'lin'

EXEC sp_droprolemember 'role', 'wang'

EXEC sp_droprolemember 'role', 'zheng'

GO

EXEC sp_droprole 'role'

附录

Market4.sql

#实验四 数据库的安全性实验   
#实验7 触发器



/*

CREATE TRIGGER trigger_name
BEFORE DELETE ON table1_name
FOR EACH ROW
BEGIN
  DELETE FROM table2_name WHERE table2_id = OLD.id;
END;

“trigger_name”是触发器的名字（自己定义）；
“table1_name”是建立该触发器的表。也就是要执行删除语句的表。
“table2_name”是触发器关联的表。即要级联删除的表。
“BEGIN END”关键词中间执行 删除的级联表中数据语句，多条语句用“；”间隔。
“old.”是关键字。代表级联表中的外键字段（具体删除哪条数据）。

*/

#要求在Market数据库中，完成以下（1）～（5）题创建触发器的操作，然后在相关的表上执行INSERT、UPDATE或DELETE语句，观察他们的执行结果。


#（1）在Customers表上建立删除触发器，实现Customers表和Orders表的级联删除。
#准备数据
INSERT INTO customers VALUES(7,'测试','西安长安区','西安','18820221126','西安邮电大学','2022/11/26',1);
INSERT INTO orders VALUES(10,9,7,100,20000,'2022/11/26');
INSERT INTO orders VALUES(11,9,7,100,20000,'2022/11/26');
#创建触发器
CREATE TRIGGER del_t
BEFORE DELETE ON customers
FOR EACH ROW
DELETE FROM orders WHERE CustomerID=old.CustomerID;
-- -------------------
#显示触发器
SHOW TRIGGERS;
#删除触发器
DROP TRIGGER del_t;
-- --------------------
#测试级联删除触发器
DELETE FROM customers
WHERE CustomerID=7;
/*
查询：DELETE FROM customers WHERE CustomerID=7

共 1 行受到影响
*/


/*
成功删除
实现Customers表和Orders表的级联删除
select *
from customers
where CustomerID=7;
#返回了 0 行   
SELECT *
FROM orders
WHERE CustomerID=7;
#返回了 0 行      
*/
    
#（2）在Orders表上建立插入触发器，当向表中添加一条订货记录时，若订单中的商品状态为整理（Status=1），则不能插入该条记录。
#准备数据
INSERT INTO goods VALUES(10,'测试ins1',200,'测试ins1',1200,'Blue',1);
#自定义mysql结束符
DELIMITER //
#创建触发器
CREATE TRIGGER ins1_t 
BEFORE INSERT ON orders 
FOR EACH ROW   
BEGIN
	DECLARE zhengli TINYINT;  #声明局部变量
	SELECT `STATUS` INTO zhengli  FROM goods WHERE goods.GoodsID=NEW.GoodsID;
	IF zhengli=1 
		THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '正在整理货物!';  #错误代码和提示信息
	END IF;          #结束if语句
END//
DELIMITER ;

#显示触发器
SHOW TRIGGERS;

#测试数据
INSERT INTO orders VALUES(12,10,6,100,20000,'2022/11/26');

/*
插入失败 
错误代码： 1644
正在整理货物!

#结果
select * from orders
where OrderID=12;
#返回了 0 行     
*/


#（3）在Orders表上建立插入触发器，当添加订单时，减少Goods表中相应商品的库存量。
#准备数据
INSERT INTO goods VALUES(11,'测试ins2',200,'测试ins2',1200,'Blue',0);

#创建触发器
CREATE TRIGGER ins2_t
AFTER INSERT ON orders
FOR EACH ROW
UPDATE goods SET `Storage`=`Storage`-new.Quantity WHERE GoodsID=new.GoodsID;

#显示触发器
SHOW TRIGGERS;

#测试数据
INSERT INTO orders VALUES(13,11,6,100,20000,NOW());

SELECT * FROM orders WHERE OrderID=13;
/*
OrderID	GoodsID	CustomerID	Quantity	OrderSum	OrderDate
13	11	6		100		20000	2022-11-26 00:00:00
*/
SELECT * FROM goods WHERE GoodsID=11;
/*
GoodsID	GoodsName	Price	Description	Storage	Provider	STATUS
11	测试		200	测试插入触发器	1100	Blue		1
*/


#（4）在Orders表上建立触发器，不允许对订单日期进行修改。

#准备数据
INSERT INTO orders VALUES(14,11,6,100,20000,NOW());
/*
OrderID	GoodsID	CustomerID	Quantity	OrderSum	OrderDate
14	11	6		100		20000		2022-11-26 21:38:46
*/


#自定义mysql结束符
DELIMITER $
#创建触发器
CREATE TRIGGER upd_t
BEFORE UPDATE ON orders
FOR EACH ROW
BEGIN
	DECLARE old_time TIMESTAMP;
	SELECT OrderDate INTO old_time FROM orders WHERE OrderID=NEW.OrderID;
	IF old_time!=NEW.OrderDate
		THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='不允许对订单日期进行修改';
	END IF;
END $
DELIMITER ;

#显示触发器
SHOW TRIGGERS;

#测试数据
UPDATE orders SET OrderDate=NOW() WHERE OrderID=14;
/*
错误代码： 1644
不允许对订单日期进行修改
*/

#结果
SELECT * FROM orders WHERE OrderID=14;
/*
OrderID	GoodsID	CustomerID	Quantity	OrderSum	OrderDate
14	11	6		100		20000		2022-11-26 21:38:46
*/

#（5）建立触发器，实现参照完整性约束，即若在Orders表中添加一条订单记录时，则该订单中的商品必须在Goods表中存在，否则不允许添加该记录。

#自定义mysql结束符
DELIMITER $
#创建触发器
CREATE TRIGGER ins3_t
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
	DECLARE insert_GoodsID INT;  #声明局部变量
	SELECT GoodsID INTO insert_GoodsID FROM goods WHERE GoodsID=NEW.GoodsID;
	IF insert_GoodsID IS NULL
		THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '该订单中的商品必须在Goods表中存在';	
	END IF;	
END $
DELIMITER ;

#显示触发器
SHOW TRIGGERS;

#测试数据
INSERT INTO orders VALUES(15,12,6,100,20000,NOW());
/*
错误代码： 1644
该订单中的商品必须在Goods表中存在
*/

#结果
SELECT * FROM orders WHERE OrderID=15;
#返回了 0 行

-- --------
#查看触发器
SHOW TRIGGERS;
-- --------

# 实验8 安全性实验

-------------------
#实验前还原数据
#保证无wang zheng lin member
SELECT `user`,`HOST` FROM mysql.user;
DROP USER 'wang';
DROP USER 'zheng';
DROP USER 'lin';
DROP ROLE 'member'@'%';
SELECT `user`,`HOST` FROM mysql.user;
-------------------


/*
（1）在企业管理器中打开“SQL Server属性（配置）”对话框，设置身份验证模式为“SQL Server和Windows”模式（即混合模式）。

在A.5节中已经介绍，一个用户要访问SQL Server 2000数据库中的数据，他必须要经过3个认证过程。
第一，验证用户连接到SQL Server 2000数据库服务器的资格；
第二，验证对具体数据库的访问权；
第三，验证用户是否具有对所操作对象的操作权。理解和体会这一认证过程。

*/
/*
（2）创建、管理数据库服务器的登录账号。

① 用T-SQL语句创建、查看、删除登录账号。

创建一个名为student、密码为111、使用的默认数据库为JWGL的登录账号。

          EXEC sp_addlogin 'student', '111', 'JWGL'

查看登录账号为EXEC sp_helplogins

删除登录账号为EXEC sp_droplogin 'student'

② 在企业管理器中使用A.5.2节中介绍的方法创建一个登录账号。
*/



/*

（3）创建、管理数据库用户。用户是基于数据库的名称是和登录账号相关联的。只有DBA和数据库所有者才有执行系统存储过程sp_granddbaccess的权力。

① 用T-SQL语句创建、查看、删除数据库用户。

为数据库JWGL创建一个用户user1，该用户登录SQL Server服务器的账号为wang，登录密码为secret，写出相应的程序代码为

EXEC sp_addlogin 'wang', 'secret', 'JWGL'

GO

EXEC sp_grantdbaccess 'wang', 'user1'

查看数据库用户为EXEC sp_helpuser

删除数据库中的“wang”用户为EXEC sp_revokedbaccess 'wang'

② 使用企业管理器创建、查看、删除数据库用户。

使用企业管理器为数据库JWGL创建一个用户user2，该用户登录SQL Server服务器的账号为zheng，登录密码为123456。
*/





#创建用户
CREATE USER 'wang' IDENTIFIED BY 'secret';
GRANT ALL ON jwgl.* TO 'wang'@'%';

#查看用户
SELECT `user`,`HOST` FROM mysql.user;
/*

user			HOST
tom			%
zhang3			%
mysql.infoschema	localhost
mysql.session		localhost
mysql.sys		localhost
root			localhost
*/

#删除用户
DROP USER 'wang';

CREATE USER 'zheng' IDENTIFIED BY '123456';
GRANT ALL ON jwgl.* TO 'zheng'@'%';

SHOW GRANTS FOR 'zheng'@'%';
/*
Grants for zheng@%
GRANT USAGE ON *.* TO `zheng`@`%`
GRANT ALL PRIVILEGES ON `jwgl`.* TO `zheng`@`%`
*/


/*
（4）管理用户权限。用户权限的管理可以使用企业管理器，也可以使用T-SQL语句。分别使用企业管理器和T-SQL语句完成第4章习题中的用户管理和用户权限管理。
*/


/*
（5）创建、管理数据库角色。

若一个小组共3个成员，他们对JWGL具有相同的操作权限，具体权限为对Student、Course表只能进行数据查询；对SC表可以进行查询、修改、删除和插入。

可以创建一个角色（如role），给该角色赋予相应的权限，然后给每个成员建立一个登录账号（如分别为lin，wang，zheng），并将每个成员的登录账号添加为数据库角色role的成员。其代码如下

EXEC sp_addrole 'role'

GO

GRANT SELECT ON student, course TO role

GRANT SELECT, UPDATE, DELETE, INSERT ON SC TO role

GO

EXEC sp_addrolemember 'role', 'lin'

EXEC sp_addrolemember 'role', 'wang'

EXEC sp_addrolemember 'role', 'zheng'

删除角色role，然后再使用企业管理器完成以上任务。

删除角色role时，必须保证它不包含任何数据库用户，即要先删除其下的所有成员。

EXEC sp_droprolemember 'role', 'lin'

EXEC sp_droprolemember 'role', 'wang'

EXEC sp_droprolemember 'role', 'zheng'

GO

EXEC sp_droprole 'role'


*/

#准备工作
#把上面的创建用户的权限都删除了
REVOKE ALL ON jwgl.* FROM wang;
REVOKE ALL ON jwgl.* FROM zheng;
SHOW GRANTS FOR 'wang'@'%';
SHOW GRANTS FOR 'zheng'@'%';
#只有登录权限了
/*
Grants for wang@%
GRANT USAGE ON *.* TO `wang`@`%`
*/

CREATE USER 'lin' IDENTIFIED BY '123456';

SHOW GRANTS FOR 'lin'@'%';
/*
Grants for lin@%
GRANT USAGE ON *.* TO `lin`@`%`

*/

#查看用户
SELECT `user`,`HOST` FROM mysql.user;
/*
user	HOST
lin	%
tom	%
wang	%
zheng	%
mysql.infoschema	localhost
mysql.session	localhost
mysql.sys	localhost
root	localhost

*/


#创建角色
CREATE ROLE 'member'@'%';

#查看用户
SELECT `user`,`HOST` FROM mysql.user;
/*
user	HOST
lin	%
member	%
tom	%
wang	%
zheng	%
mysql.infoschema	localhost
mysql.session	localhost
mysql.sys	localhost
root	localhost
*/

#授权给角色member
GRANT SELECT ON jwgl.* TO member;
GRANT UPDATE, DELETE, INSERT ON jwgl.sc TO member;

SHOW GRANTS FOR 'member'@'%';


/*
Grants for member@%
GRANT USAGE ON *.* TO `member`@`%`
GRANT SELECT ON `jwgl`.* TO `member`@`%`
GRANT INSERT, UPDATE, DELETE ON `jwgl`.`sc` TO `member`@`%`

*/



#给用户授予角色
GRANT 'member'@'%' TO 'lin'@'%';
GRANT 'member'@'%' TO 'wang'@'%';
GRANT 'member'@'%' TO 'zheng'@'%';

SHOW GRANTS FOR 'wang'@'%';
SHOW GRANTS FOR 'zheng'@'%';
SHOW GRANTS FOR 'lin'@'%';

/*
Grants for lin@%
GRANT USAGE ON *.* TO `lin`@`%`
GRANT `member`@`%` TO `lin`@`%`

*/



#注意：角色默认是不激活的，所有必须要手动激活

SET DEFAULT role 'member'@'%' TO 'lin'@'%';
SET DEFAULT role 'member'@'%' TO 'wang'@'%';
SET DEFAULT role 'member'@'%' TO 'zheng'@'%';

#重新登录上面的账号就可以操作了

#操作演示如下

#在lin.sql文件中

#删除角色
#DROP ROLE 'member'@'%';

#如果你删除了角色，那么用户也就失去了通过这个角色所获得的所有权限。

lin.sql

#mysql -ulin -p123456

SHOW GRANTS;
/*
Grants for lin@%
GRANT USAGE ON *.* TO `lin`@`%`
GRANT SELECT ON `jwgl`.* TO `lin`@`%`
GRANT INSERT, UPDATE, DELETE ON `jwgl`.`sc` TO `lin`@`%`
GRANT `member`@`%` TO `lin`@`%`

*/

#有的权限
SELECT * FROM student;

SELECT * FROM course;

SELECT * FROM sc;
INSERT INTO sc VALUES(20221102,1,90);
UPDATE sc SET Grade=91 WHERE Sno=20221102 AND cno=1;
DELETE FROM sc WHERE Sno=20221102 AND cno=1;


#没有的权限 
#这里只测试针对student的操作
INSERT INTO student VALUES (20221126,'lin','男',15,'188120221126','CS');
/*
INSERT command denied to user 'lin'@'localhost' for table 'student'
*/


DELETE FROM student WHERE Sno=980013;
/*
DELETE command denied to user 'lin'@'localhost' for table 'student'
*/

UPDATE student SET Sex='女' WHERE Sno=980013;
/*
UPDATE command denied to user 'lin'@'localhost' for table 'student'
*/

最后

这篇博客能写好的原因是：站在巨人的肩膀上

这篇博客要写好的目的是：做别人的肩膀

开源：为爱发电

学习：为我而行

# 数据库 # 服务器 # java
Logo
腾讯云开发者社区

腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。

更多推荐

cover

计算机网络微课堂笔记

avatar 腾讯云开发者社区
cover

Rabbitmq在java中的使用

avatar 腾讯云开发者社区

java try catch 之后定位不到具体报错行_JAVA入门（三）上

点击蓝字｜关注我们一、异常与异常处理异常简介代码中：阻止当前方法或作用域继续实现的，称之为异常java中的所有异常类都继承Throwable类，Exception 的父类是 Throwable编码 环境 用户操作输入出现问题由java虚拟机自动抛出和自动捕获需要手动添加抛出和捕获语句文件找不到ThrowableErrorException虚拟机错误 VirtualMachineError...

avatar 腾讯云开发者社区