2024年5月24日第五十五篇
今天学习了sql server数据库中安全管理有关的操作。
成功创建 students 数据库。
在 SQL Server 管理器中，创建一个新的 windows 登录名。
（1） 新建了一个名为MSI的登录名。
在 SQL Server 管理器中为自己建立一个 SQL SERVER 服务器登录名（自己学号）。
（2） 新建了一个名为20224125的sql server的服务器登录名。
为所属的 SQL 服务器设置混合认证安全认证模式。
（3） 设置了安全认证模式
用自己创建的登录名登录服务器。
（4） 用自己的登录名登陆了服务器。
（5） 中为自己建立一个 students 数据库用户（20224125-user）和 角色（20224125-role）。
（6） 用管理器实现如下权限的分配：
① 将 student 表中“sname”、“ssex”、“sage”的修改权限赋予自己创建的数据库用 户。
GRANT UPDATE (sname, ssex, sage) ON dbo.student TO [20224125-user];
② 将 student 表、course 表、sc 表的查询权限授予自己创建的角色。
GRANT SELECT ON dbo.student TO [20224125-role];
GRANT SELECT ON dbo.course TO [20224125-role];
GRANT SELECT ON dbo.sc TO [20224125-role];
③ 拒绝自己创建的角色拥有 sc 表中 grade 的修改权限。
DENY UPDATE (grade) ON dbo.sc TO [20224125-role];
④ 将自己创建的数据库用户添加为自己创建的角色成员。
ALTER ROLE [20224125-role] ADD MEMBER [20224125-user];
（7） 用管理器创建触发器实现如下安全性和完整性：
①定义 course 表中 credit 属性为“3”的缺省。
ALTER TABLE dbo.course ADD CONSTRAINT DF_CourseCredit DEFAULT 3 FOR credit;
②建立 course 与 sc 表间来维护参照完整性而使用的一个级联删除触发器、一个级 联修改触发器和一个受限插入触发器。
级联删除触发器
CREATE TRIGGER trg_CascadeDelete
ON dbo.course
AFTER DELETE
AS
BEGIN
DELETE FROM dbo.sc WHERE cno IN (SELECT cno FROM deleted);
END;
级联修改触发器
CREATE TRIGGER trg_CascadeUpdate
ON dbo.course
AFTER UPDATE
AS
BEGIN
UPDATE s
SET s.cno = i.cno
FROM dbo.sc s
INNER JOIN inserted i ON s.cno = i.cno;
END;
受限插入触发器
（8） CREATE TRIGGER trg_RestrictedInsert
（9） ON dbo.sc
（10） INSTEAD OF INSERT
（11） AS
（12） BEGIN
（13） -- 检查插入的数据是否符合要求
（14） IF EXISTS (SELECT 1 FROM inserted WHERE grade < 0 OR grade > 100)
（15） BEGIN
（16） -- 如果插入的数据不符合要求，则抛出错误信息并终止插入操作
（17） RAISERROR ('Invalid grade. Grade must be between 0 and 100.', 16, 1);
（18） ROLLBACK TRANSACTION; -- 可选，如果需要回滚插入操作
（19） RETURN; -- 可选，如果需要终止触发器的执行
（20） END
（21）
（22） -- 如果插入的数据符合要求，则执行插入操作
（23） INSERT INTO dbo.sc ( cno, grade)
（24） SELECT cno, grade
（25） FROM inserted;
（26） END;
（27） 对“students 数据库”编写存储过程，完成下面功能： ① 统计成绩 60 分以下的人数；
（28） CREATE PROCEDURE sp_CountBelow60
（29） AS
（30） BEGIN
（31） SELECT COUNT(*) AS Below60Count
（32） FROM dbo.sc
（33） WHERE grade < 60;
（34） END;
② 统计给定 cno 的课程的平均成绩，并返回平均成绩；
CREATE PROCEDURE sp_AvgGradeByCourse
@cno INT
AS
BEGIN
SELECT AVG(grade) AS AvgGrade
FROM dbo.sc
WHERE cno = @cno;
END;
③将 sc 表中 grade 从百分制改为等级制（5、4、3、2、1）。即 0-20 分为 1，21-40 为 2，4 1-60 为 3，61-80 为 4,81-100 为 5。
CREATE PROCEDURE sp_ConvertGrades
AS
BEGIN
UPDATE dbo.sc
SET grade =
CASE
WHEN grade BETWEEN 0 AND 20 THEN 1
WHEN grade BETWEEN 21 AND 40 THEN 2
WHEN grade BETWEEN 41 AND 60 THEN 3
WHEN grade BETWEEN 61 AND 80 THEN 4
WHEN grade BETWEEN 81 AND 100 THEN 5
ELSE NULL
END;
END;
(9) 完成课本 P172 页的上机练习。
