[Day12] 資料使用安全(SQL權限)

2017 iT 邦幫忙鐵人賽

DAY 12

Security

資料安全與簡單加密演算法見面會系列第 12 篇

2017鐵人賽 sql server

StanleyJui

2016-12-27 20:03:15

24431 瀏覽

前兩天我們複習了SQL Server 2016推出的動態資料遮罩(DDM)及限閱戶(RLS)，實現了單一資料表內從資料行和資料列兩個方向作權限限制，回到資料表身上，那麼有沒有對於資料表這一層的權限管理?

有的，今天我們回到最基本的使用者權限複習。

認證(Authentication)及授權(Authorization)

在授與使用者存取資料庫的資源前，我們必須完成SQL Server的認證(Authentication)及授權(Authorization)兩項工作:

認證是指識別這個使用者。
授權是指可以給這個使用者那些權限。

舉個例子來說，認證就像是護照查驗，授權就像機票的艙等，要先通過海關，接著要有足夠的權限才能走進商務艙(business class)或是頭等艙(first class)。

在SQL Server的認證中，我們可以設定Windows帳號/群組或是SQL帳號，如果環境允許，常用的是Windows Domain帳號登入。

認證(Authentication)也分為兩個層級，資料庫執行個體登入及資料庫登入。
都認證完之後，才進行授權。此時SQL Server 才會看使用者的角色及權限回應使用者想存取的資源。

*資料庫執行個體(instance)，SQL Server服務，一台機器可以裝很多個SQL Server，每一個都是就是獨立的instance。

今年年初msdn發行了一版設定資料庫物件的權限的課程，我們仿照了步驟來試試。

建立登入

USE [master] 
GO 
CREATE LOGIN [day10] WITH PASSWORD=N'1qaz2wsx!' 
GO

USE [SecurityDB];   
GO   
CREATE USER [day10] FOR LOGIN [day10];   
GO

授權測試

--檢查目前登入 
SELECT SUSER_NAME(), USER_NAME();

--切換登入   
EXECUTE AS LOGIN = 'day10';   
--檢查登入   
SELECT SUSER_NAME(), USER_NAME();   

--查詢資料表 
select * from [dbo].[cardholder]

尚未賦予權限前沒辦法查詢!

建立授權dbo schema

--回復身份 
REVERT;  
--授與權限 select dbo schema 
use [SecurityDB] 
GO 
GRANT SELECT ON SCHEMA::[dbo] TO [day10] 
GO 
--切換登入   
EXECUTE AS LOGIN = 'day10';   
--檢查登入   
SELECT SUSER_NAME(), USER_NAME();   
  
--查詢資料表 
select * from [dbo].[cardholder]

所有dbo schema下的資料表都可以查詢。
回到今天一開始的題目，通常我們都會賦予SQL查詢帳號有Select dbo schema權限，在這樣的條件下，我們來限制使用者不可以查詢。

限制資料表查詢

作法就是DENY SELECT ON Table!

--回復身份  
REVERT;   
--拒絕權限  
use [SecurityDB]  
GO  
DENY SELECT ON cardholder  TO [day10]  
GO  
 
--切換登入    
EXECUTE AS LOGIN = 'day10';    
--檢查登入    
SELECT SUSER_NAME(), USER_NAME();    
  
--查詢資料表  
select * from [dbo].[cardholder]