對 SQL Server 本身來講安全性有三個層級

• 執行個體
• 資料庫
• 物件

安全性階層

SQL Server 的安全性層級由上往下 ( 含 windows )

1. Windows 網域
2. 本機伺服器
3. SQL Server Instance
4. Database
5. Object

Windows 層級的只會討論一點點，這邊著重討論 SQL Server 的。

這些層級的架構基本上是由，Principal、Securable、Permission 組成。

Principal = 誰

principal 就是 “誰要使用 SQL Server”

Securable = 去哪裡

Securable 就是 ”SQL Server 裡可以被保護、可以設定權限的東西”

Permission = 做什麼

Permission 就是 “這個人對這個東西可以做什麼”

所以這個指令就是
授予 TomuUser ( Princple ) 在 Customers ( Securable ) 上面 SELECT ( Permission ) 的權限

GRANT SELECT ON dbo.Customers TO TomUser;

我知道很多人或是中文版的 SSMS 會翻成什麼主體、安全性實體、權限，權限還好，阿其他的是誰看得懂，所以我習慣用這樣解釋，有點類似當兵的那個夜哨口令 “站住，口令，誰”。

常見公司 SQL Server 登入架構

有一些小公司小專案，會直接用 sa 當作萬用登入，理由是很方便完全不用管理，可是這很危險。

首先，SQL Server 在用 SQL Login 登入驗證的時候，不會限制登入失敗幾次就鎖帳號，所以有人可以連到你的 SQL Server 的話，他可以用 sa 當帳號，然後密碼無限制的暴力測試。一旦他測試成功，sa 是整個 instance 最大權限，你頭就會開始痛。

如果真的不想用 AD 驗證方式，執意要打開 SQL 驗證的話，那起碼 sa 要停用，或是把 sa 帳號換一個名稱

ALTER LOGIN sa WITH NAME = PROSQLADMINSA ;

所以一般企業應該遵循以下這種方式去建立帳號 :

1. 在 AD 網域建立使用者
2. 把使用者加入對應的 AD 群組
3. 在 SQL Server 上建立這個群組的登入，就是 CREATE LOGIN…( 這是 Instance 層級的登入 )

建立一個登入的時候，SQL Server 會在 master 系統資料庫上記錄這個主體的 SID 。然後他再用這個 SID 去網域找使用者。所以系統資料庫損毀的時候，就會無法登入。

4. 在 SQL Server 上建立這個群組的 Database 的 user，就是 CREATE USER ….( 這是 Database 層級的登入 )

CREATE LOGIN 是讓群組的人可以登入 INSTANCE
但是他登入 INSTANCE 之後什麼事情都不能幹
因為 SQL SERVER 有第二層機制是 USER
所以還要在他應該去的 DATABASE 上建立一個 USER

5. 在 SQL Server 的 database 上建立適合的 ROLE，把權限授予這個 ROLE
6. 最後在把這個 ROLE 授予剛剛建立的 USER。

在第一天安裝的時候我有寫過一件事情就是，不要把權限分得太細。

雖然微軟還有網路上的文章都是寫以最小權限去處理這件事情，但是權限分到太細會導致如果今天有 DR 發生，你要還原回去，會變得非常困難。

所以我當時建議，一個應用程式，開一兩個 AD 群組，去管理，你不要每一個功能，都去開一個群組，一個負責 SELECT、一個負責備份….等等，這樣會搞死自己。

同一個群組 SQL SERVER 授予權限會一樣，群組內有特別的人、主管或是群組內有 Agent 用的帳號，都可以個別在授予特殊權限。

實作

預先建立這個章節會用到的測試 table

--Create SensitiveData table

CREATE TABLE dbo.SensitiveData
(
ID INT PRIMARY KEY IDENTITY,
SensitiveText NVARCHAR(100)
) ;

--Populate SensitiveData table

DECLARE @Numbers TABLE
(
ID INT
)

;WITH CTE(Num)
AS
(
SELECT 1 AS Num
UNION ALL
SELECT Num + 1
FROM CTE
WHERE Num < 100
)

INSERT INTO @Numbers
SELECT Num
FROM CTE ;

INSERT INTO dbo.SensitiveData
SELECT 'SampleData'
FROM @Numbers ;

執行個體-伺服器角色

SQL Server 內建有一些伺服器角色，都是常見的需求，無法變更這些角色有什麼權限，只能指派出去，所以這個叫做固定伺服器角色。

其中帶有 ## 的角色，是 SQL Server 2022 加入的，目的是方便識別這是固定伺服器角色，但原本的也會有保留，是為了向下兼容。所以如果是新的 SQL Server Instance 權限最好都給 ## 的或是自己建角色，難保有一天新版本這些原本角色就沒了。

-- 示範有一個高度可用環境，依賴可用性群組，然後賦予這些權限讓他可以去管理 HA
USE Master
GO

CREATE LOGIN [AD網域名稱\AD網域上的使用者 OR 群組] FROM WINDOWS;
GO

CREATE SERVER ROLE 角色名字
GO

GRANT ALTER ANY AVAILABILITY GROUP TO 角色名字;

GRANT ALTER ANY ENDPOINT TO 角色名字;

GRANT CREATE AVAILABILITY GROUP TO 角色名字;

GRANT CREATE ENDPOINT TO 角色名字;

ALTER SERVER ROLE 角色名字
ADD MEMBER [AD網域名稱\AD網域上的使用者 OR 群組] 

或是也可以用GUI 去新增

執行個體-登入

GUI 圖片中的框框

1. 密碼原則如果有勾的話會依據 SQL Server 安裝的 Windows 去決定，Windows 有網域就用網域的沒有就用本機的。
2. 預設資料庫會讓這個登入直接去這個資料庫，設定這個意思就是，如果有一天這個資料庫被刪除了，那這個登入也無法登入了。
3. 憑證、非對稱金鑰等等會在加密章節提。

建立登入的 GUI，也可以用語法

USE Master
GO

CREATE LOGIN WU
    WITH PASSWORD=N'你的密碼' 
GO

USE TEST
GO

CREATE USER WU FOR LOGIN WU;
GO

執行個體-權限

指派權限有三種動作

• GRANT
  會將某個安全性實體上的權限授與給主體

• DENY
  明確拒絕登入在某個安全性實體上的權限；DENY 會覆蓋 GRANT

• REVOKE
  會移除某個安全性實體上的權限關聯。這包含 DENY 關聯與 GRANT 關聯。

GRANT ALTER ANY LOGIN TO WU;
GO
-- 這是雖然給 WU ALTER 任意 LOGIN 權限，
-- 但是禁止他去 ALTER [NT Service\MSSQL$PROSQLADMIN] 這個 LOGIN 要這樣寫
DENY ALTER ON LOGIN::[NT Service\MSSQL$PROSQLADMIN] TO WU;

--Add 把 WU 加進伺服器腳色

ALTER SERVER ROLE ##MS_DatabaseManager## ADD MEMBER WU;
GO

--Add 把 WU 移出伺服器腳色

ALTER SERVER ROLE ##MS_DatabaseManager## DROP MEMBER Danielle ;
GO

資料庫角色

就像執行個體層級有伺服器角色可以幫助管理權限一樣，資料庫層級也有資料庫角色，可以將主體群組在一起，以指派共同權限。

USE TEST;
GO

-- 建立 Database User，讓 Danielle 在 Chapter10 裡面有身份
CREATE USER WU FOR LOGIN WU;
GO

-- 建立自訂 Database Role
CREATE ROLE db_ReadOnlyUsers AUTHORIZATION dbo;
GO

-- 把 Danielle 這個 Database User 加進 Role
ALTER ROLE db_ReadOnlyUsers ADD MEMBER WU;
GO

-- 給 Role 查詢 dbo.SensitiveData 的權限
GRANT SELECT ON dbo.SensitiveData TO db_ReadOnlyUsers;
GO

-- 明確拒絕 Role 對 dbo.SensitiveData 寫入、修改、刪除
DENY INSERT ON dbo.SensitiveData TO db_ReadOnlyUsers;
DENY UPDATE ON dbo.SensitiveData TO db_ReadOnlyUsers;
DENY DELETE ON dbo.SensitiveData TO db_ReadOnlyUsers;
GO

-- 這個操作完成之後就會得到
-- WU有SELECT SensitiveData 權限，但是沒有其他 DML 權限

儘管 DENY 很好用，可是頻繁的大量使用會增加整個安全性管理的複雜度，建議在可以的狀況下盡量使用 GRANT 來執行最小權限原則。

SCHEMA

這是一個邏輯命名，他是抽象概念；跟 Partition 那種實體分割結構，是不一樣的東西。

在資料庫角色裡面有寫到如何針對一張 table 去授權。

但是現實中一定不只一張 table，這時候就需要加上 SCHEMA 去管理。

所以我可以有以下這種結構
Sales.Customers
Sales.Orders
Sales.OrderDetails

HR.Employees
HR.Salary

Inventory.Products
Inventory.Stock

很多公司幾乎都是 (ERP 公司最多)
dbo.Customers
dbo.Orders
dbo.OrderDetails
dbo.Products
dbo.Employees
dbo.Salary
dbo.Invoices
dbo.Vendors

如果現在想給報表人員讀銷售資料，但不想讓他看到薪資資料，按照前面的方法，你只能這樣寫

GRANT SELECT ON dbo.Customers TO ReportRole;
GRANT SELECT ON dbo.Orders TO ReportRole;
GRANT SELECT ON dbo.OrderDetails TO ReportRole;

如果你這樣寫

GRANT SELECT ON SCHEMA::dbo TO ReportRole;

他會連薪水那些都看的到。當然我知道不會有人把這種資料放在同一個 DB 這裡是演練說明而已。

但是如果我們一開始有把 SCHEMA 做好，那之後只需要

GRANT SELECT ON SCHEMA::Sales TO ReportRole;

賦予 ReportRole 在 SCHEMA Sales 上有 SELECT 權限這樣就可以了。

CREATE SCHEMA TESTSCHEMA;
GO

GRANT SELECT ON SCHEMA::TESTSCHEMA TO WU;

CREATE TABLE TransferTest
(
    ID int
);
GO

ALTER SCHEMA TESTSCHEMA TRANSFER dbo.TransferTest ;
GO
-- 如果再建立 TABLE 沒有事先指定，預設會是 dbo，可以事後改。

應該要按照業務規則去建立 SCHEMA，不要依照技術規則。

自主資料庫登入

自主資料庫登入是一種不需要對應 Instance Login 的 Database User。

一般 SQL Server 安全模型是：
Login 存在 Instance 層級，User 存在 Database 層級，User 透過 FOR LOGIN 對應到 Login。

Contained User 則是把 User 直接建立在 Database 裡，可以是 Windows/AD 使用者或群組，也可以是帶密碼的 Database User。

這樣做的目的，是降低 Database 對 Instance 的依賴，讓資料庫搬移、還原到其他 Instance，或 Always On Failover 時，不需要另外同步 Instance Login 或處理 orphan user。

使用前必須：

1. 在 Instance 層級啟用 contained database authentication

   EXEC sp_configure 'show advanced options', 1 ;
   GO
   
   RECONFIGURE ;
   GO
   
   EXEC sp_configure 'contained database authentication', '1' ;
   GO
   
   RECONFIGURE WITH OVERRIDE ;
   GO
   

2. 在目標 Database 設定 CONTAINMENT = PARTIAL

   USE Master
   GO
   
   ALTER DATABASE [TEST] SET CONTAINMENT = PARTIAL WITH NO_WAIT ;
   GO
   

3. 在 Database 裡 CREATE USER，不使用 FOR LOGIN

   USE TEST
   GO
   
   CREATE USER 帳號名稱 WITH PASSWORD = N'密碼', DEFAULT_SCHEMA=dbo ;
   GO
   

上面的做法，是會在 TEST 這個資料庫中，建立一個自主帳號，
但是如果今天這個自主帳號要同時管理其他 DB，就要注意以下操作。
因為，SQL Server 認定的是 SID 不是 帳號名稱。

USE Master
GO

CREATE DATABASE TEST2;
GO

ALTER DATABASE TEST2 SET CONTAINMENT = PARTIAL WITH NO_WAIT ;
GO

USE TEST2
GO

CREATE USER  帳號名稱 WITH PASSWORD = '密碼',
SID =
0x0105000000000009030000009134B23303A7184590E152AE6A1197DF ;


--其中這個 SID 是從這裡來的
SELECT sid
FROM sys.database_principals
WHERE name = '帳號名稱' ;

還有一個要注意，因為如果這樣建立帳號，代表會跨資料庫執行命令

所以這個也要開，這是同意資料庫可以跨資料庫去操作

ALTER DATABASE TEST SET TRUSTWORTHY ON;

這個功能常出現的場景是 HA，因為一般 login 存在 instance 的 mater 裡，

如果這個不同步，failover 過去會出現 database user 找不到對應的 login
failover 就會失敗。

但風險就是少一層驗證、要多管裡一個登入、因為這功能是只存在 database，有人會拿來建立測試帳號方便，然後最後正式上線忘記裡面還有測試的帳號很大權限。

補充一個
GRANT SELECT ON dbo.SensitiveData ([SensitiveText]) TO ContainedUser ;
這個是授予 SELECT 權限 但僅限於 SensitiveText 這個欄位