SQL Server 2005 資料庫安全性與參考完整性實作指南

資料庫安全性架構

SQL Server 2005 採用分層安全模型，核心包含登入帳號、資料庫使用者、角色與權限授予。正確配置這些元素能確保資料存取安全。

建立登入與使用者

-- 建立 SQL Server 登入帳號
CREATE LOGIN username WITH PASSWORD = 'passWord';

-- 在資料庫中建立對應使用者
CREATE USER username FOR LOGIN username;

權限授予與 GRANT OPTION

授予特定資料表查詢權限，並允許受權者進一步轉授權：

GRANT SELECT ON Student TO user_tom WITH GRANT OPTION;

角色基礎管理

透過角色簡化權限管理，將權限指派給角色再將使用者加入角色：

-- 建立資料庫角色
CREATE ROLE dbnormuser;

-- 授予角色權限
GRANT SELECT ON Student TO dbnormuser;

-- 將使用者加入角色（需搭配 sp_addrolemember）
EXEC sp_addrolemember 'dbnormuser', 'username';

伺服器層級固定角色

系統管理員角色具備最高權限，謹慎指派：

-- 加入 sysadmin 角色
EXEC sp_addsrvrolemember 'marry', 'sysadmin';

-- 移除角色成員
EXEC sp_dropsrvrolemember 'marry', 'sysadmin';

參考完整性與外鍵約束

外鍵確保資料跨表一致性，支援級聯操作與預設值設定。

基本外鍵操作

-- 新增外鍵約束
ALTER TABLE test_sub 
ADD CONSTRAINT main_id_cons 
FOREIGN KEY (main_id) REFERENCES test_main;

-- 移除外鍵
ALTER TABLE test_sub DROP CONSTRAINT main_id_cons;

級聯刪除與更新

當主表記錄變動時，自動同步從表資料：

-- 級聯刪除
ALTER TABLE test_sub 
ADD CONSTRAINT main_id_cons 
FOREIGN KEY (main_id) REFERENCES test_main ON DELETE CASCADE;

-- 級聯更新
ALTER TABLE test_sub 
ADD CONSTRAINT main_id_cons 
FOREIGN KEY (main_id) REFERENCES test_main ON UPDATE CASCADE;

設定 NULL 值策略

主鍵被刪除或更新時，將外鍵欄位設為 NULL：

ALTER TABLE test_sub 
ADD CONSTRAINT main_id_cons 
FOREIGN KEY (main_id) REFERENCES test_main ON DELETE SET NULL;

核心限制

參考完整性原則： 存在外鍵參考的主鍵記錄無法直接刪除，必須先處理從表資料或啟用級聯選項。