SQL Server Logins, Database Users, Database Roles and Schemas

 

[引子]

 You work as a database administrator for your company. A SQL Server Login named Tom is used only within the AdventureWorks database.

A new company security policy is being implemented. This new policy states that SQL Server Authentication cannot be used. In preparation for implementation of the policy, you must remove the Tom Login.

The new policy states that users associated with SQL Server Logins must be removed, and the ownership of anything that is owned by these users must be transferred to dbo.

 

 

要完成此任務，下面是一個推薦的執行步驟

1．Database – AdventureWorks – Security – Schema – Tom(if exists) – Properties – General, change the Schema owner from Tom to dbo.

2．Database – AdventureWorks – Security – Users – Tom, Delete Tom.

3．Server – Security – Logins, Delete the Tom

 

Ok，任務是完成了。但是我們不能僅僅就此滿足了，你是否真的完全知道為什麼要按照這個順序來做，以及每個步驟中涉及的術語你是否真的明白了。

 

[本文]

 

1. SQL Server Logins

(1) 建立一個可以登入到資料庫的使用者。輸入登入名稱，密碼和選擇預設資料庫，但是如果你沒有在User Mapping中沒有相應的指定該資料庫，你可以成功建立該使用者，但是該使用者登入的時候，將會提示4064錯誤。解決方案自然很明顯了。

(2) 當你在User Mapping中指定了相應的資料庫後，SQL Server會自動在該資料庫下面建立一個同名的使用者。並預設其架構為dbo。這個時候，你登入資料庫成功，但是當你執行任何T-SQL語句卻發現沒有任何許可權。因為他不屬於任何資料庫角色。即便他擁有了一個架構。

 

2. Database Users

(1) 資料庫使用者是資料庫層級上的主體。每個資料庫使用者都是 public 角色的成員。

(2)你可以為使用者選擇某個架構，並具體為該使用者佈建該架構下的各個資料庫物件(表，視圖，函數，預存程序)的許可權。

3. Database Roles

(1) 兩種資料庫級角色：SQL Server預置的固定角色，使用者自訂的角色。

(2)可以向資料庫角色中添加資料庫使用者

(3) 可以為角色選擇某個架構，並具體為該使用者佈建該架構下的各個資料庫物件(表，視圖，函數，預存程序)的許可權。【同2.(2)】

 

4. Database Schemas

(1) 架構是指包含表、視圖、過程等的容器。它位於資料庫內部，而資料庫位於伺服器內部。這些實體就像嵌套框放置在一起。伺服器是最外面的框，而架構是最裡面的框。架構包含下面列出的所有安全性實體，但是它不包含其他框。
(2) 可以為架構，選擇使用者角色和使用者，在這裡設定許可權後，使用者或者角色的許可權對將該架構下所有對象有效。

 

 

5 練習。

(1)建立一個資料庫DB1, 建立一個架構hr，

(2)建立三個使用者角色hr_reader, hr_writer和hr_admin. hr_reader的只有select許可權, hr_writer擁有select和update許可權，hr_admin擁有select, update, delete和execute許可權。

(3)建立三個登入hra, hrr, hrw分別對應資料庫角色hr_reader, hr_writer和hr_admin.

(4) 建立一個表hr.Employee, 欄位em_id, first_name, last_name

(5) 建立一個存貯過程sp_ListEmployees

(6) 分別測試各個使用者的許可權是否設定正確。