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 页的上机练习。