SQL Server 伺服器角色/資料庫角色/權限給予

在SQL Server中用到角色概念
可說是大型資料庫中特有的設定

角色可以是想像成公司的職位：

　執行長　可以操作整個Serve

安全部長　可以管理修改資料庫登入使用者的權限

由於SQL Server是在微軟體系中建立
所以只能佈屬在微軟的作業系統上
這有好有壞

好處是可以借用windows的安全性驗證
windows server環境下可佈署Active Directory
假設整個公司都是用windows環境，那就簡單了
可以用「windows驗證」來連入，不用打帳號密碼
只要能登入Window，就能連SQL Server
但給不給登入就要另外授予權限了

壞處自然是不能在另外OS系統上安裝
一切都要跟windows挷在一起
若有別的系統要連上會相對麻煩一點點

伺服器層級的角色如下：

• sysadmin　最大的，伺服器所有活動都可以處理
• serveradmin　可配置伺服器的設定
• securityadmin　管理登入伺服器的權限，援予或取消
• processadmin　管理伺服器的進程，例如可取消某某使用者的查詢
• setupadmin　加入或移除伺服器
• bulkadmin　有權匯入大量的資料
• diskadmin　可管理伺服器上的磁碟
• dbcreator 可建、改、缷、刪資料庫
• public　所有的角色的都是public

伺服器角色是為了Server的運作及安全性所創建
Server像是個大房子，而資料庫像是房間
就算設定user可以登入Server了，但不代表就可以進到房間
所以在資料庫中也有角色設定，層級如下：

• db_owner　擁有者，最大的
• db_securityadmin　可修改管理其他使用者的權限
• db_accessadmin　可增加或刪除登入使用者
• db_backupoperator　可備份資料庫
• db_ddladmin　可執行資料庫定義語言(ddl)，如CREATE、ALTER
• db_datawriter　可寫、加、刪資料庫裡的資料
• db_datareader　可讀資料(SELECT)
• db_denydatawriter　禁止寫入的角色
• db_denydatareader　禁止讀取的角色

以上這些都是為了使系統管理更為安全
可依人員的能力及性質授予一定的權限
伺服器的登入語法如下：

--建立對應Windows帳戶John的登入帳號
CREATE LOGIN [MB-206-TEACHER\John] FROM WINDOWS

--建立對應Windows會計群組的登入帳號
CREATE LOGIN [MB-206-TEACHER\會計群] FROM WINDOWS

--不用驗證規則
CREATE LOGIN John WITH PASSWORD='1234',CHECK_POLICY=OFF;

--要驗證規則，第一次登入要重改密碼，有密碼逾期原則
CREATE LOGIN Susan WITH PASSWORD='xXyY123' MUST_CHANGE,CHECK_POLICY=ON,CHECK_EXPIRATION=ON;

--若有用戶被鎖了，可以用UNLOCK解開，第一次登入要改密碼
ALTER LOGIN Susan WITH PASSWORD='xXyY123' MUST_CHANGE UNLOCK

drop login John   --刪掉John
drop login Susan  --刪掉Susan

--查詢所有伺服器角色----------------------------------------
EXEC sp_helpsrvrole

--將john加入/移除 securityadmin 角色群中
--securityadmin可以賦予使用者有新增/刪除login帳戶的能力
EXEC sp_addsrvrolemember 'John','securityadmin'
EXEC sp_dropsrvrolemember 'John','securityadmin'

--將john加入/移除 dbcreator 角色群中
--dbcreator可以賦予使用者有新/刪除database能力
--特別的是，若當初是A建的資料庫，即使把他的能力抽掉
--A仍有刪除他建的資料庫的能力
EXEC sp_addsrvrolemember 'John','dbcreator'
EXEC sp_dropsrvrolemember 'John','dbcreator'

同一個人在伺服器的名稱，與在資料庫的名稱
不一定要相同，在伺服器是用LOGIN，在資料庫是用USER做區別

USE 練練
--建立同名帳戶
CREATE USER JOHN

--建立 登入 與 使用者 非同名的帳戶
CREATE USER MOUSE FROM LOGIN JOHN

SELECT DB_NAME() AS 資料庫名稱
    ,USER_NAME() AS 使用者名稱
    ,SUSER_SNAME() AS 登入名稱

--每個資料庫都有4個使用者帳戶，都不能刪
--dbo   對應伺服器角色中的sysadmin
--guest 預設角角，供操作者使用加入
--其餘2個sys，INFORMATION_SCHEMA是資料庫系統角色

資料庫角色的語法如下：

USE 練練
--查詢資料庫角色(記得要USE 資料庫)
EXEC sp_helprole

EXEC sp_addrolemember 'db_datareader','MOUSE' --可讀
EXEC sp_addrolemember 'db_datawriter','MOUSE' --可寫可改可刪
EXEC sp_addrolemember 'db_backupoperator','MOUSE' --可備份整個資料

--一個個給挺麻煩的，所以可以自訂角色，再將該角色賦予使用者
CREATE ROLE 資訊部
EXEC sp_addrolemember 'db_datareader','資訊部'
EXEC sp_addrolemember 'db_datawriter','資訊部'
EXEC sp_addrolemember 'db_backupoperator','資訊部'
EXEC sp_addrolemember '資訊部','MOUSE' --性賦予能力

--除了資料庫角色，也可自訂伺服器同色
CREATE SERVER ROLE 其他伺服器角色

由於SQL Server的角色中有可能會權限過大
像是db_datawriter，除了可以新增，還可以刪、改
這不是理想的權限授予，所以可以針對DCL語言來授予
GRANT　授予
DNEY　拒絕
REVOKE　撒回 (不留記錄，也就是初始狀態)

/*
--DCL語法
GRANT 權限,...  GRANT | DNEY | REVOKE
ON 物件
TO 對象
*/

GRANT SELECT,INSERT
ON 巨巨
TO Mouse

GRANT SELECT,INSERT
ON 巨巨
TO 資訊部

--查詢目前資料庫的權限授予狀態
EXEC sp_helprotect NULL,NULL,NULL,'o'

--查詢巨巨表的權限授予狀態
EXEC sp_helprotect '巨巨',NULL,NULL,'o'

--查詢user為Mouse在本資料庫的權限狀態
EXEC sp_helprotect NULL,'Mouse',NULL,'o'

下一篇會針對權限授予或拒絕解說