今天學習了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 頁的上機練習。