iT邦幫忙

2017 iT 邦幫忙鐵人賽
DAY 12
1

前兩天我們複習了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();   

http://ithelp.ithome.com.tw/upload/images/20161227/20103434kCzNZ614gD.png

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

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

http://ithelp.ithome.com.tw/upload/images/20161227/20103434x3k06Iw2QM.png

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

建立授權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] 

http://ithelp.ithome.com.tw/upload/images/20161227/20103434taEwrMNUFI.png

所有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]  

http://ithelp.ithome.com.tw/upload/images/20161227/20103434gYOkC9JVAE.png

從資料表的權限觀察:

http://ithelp.ithome.com.tw/upload/images/20161227/201034348R9M8V4SZE.png

也可以限制使用者新增(insert)、刪除(delete)及修改(update)。

--最後別忘了回復身份

REVERT;   

如果我們只想讓使用者查看到這個資料表部分欄位,也可以使用**檢視(view)預存程序(stored procedure)**來限制使用者查詢。

參考:


Authentication in SQL Server
https://msdn.microsoft.com/en-us/library/bb669066(v=vs.110).aspx

第 2 課:設定資料庫物件的權限
https://msdn.microsoft.com/zh-tw/library/ms365345(v=sql.120).aspx

建立登入
https://msdn.microsoft.com/zh-tw/library/ms365326(v=sql.120).aspx

建立檢視和預存程序
https://msdn.microsoft.com/zh-tw/library/ms365311(v=sql.120).aspx

EXECUTE AS (Transact-SQL)
https://msdn.microsoft.com/zh-tw/library/ms365311(v=sql.120).aspx


捷克警車
http://ithelp.ithome.com.tw/upload/images/20161227/20103434tyT19RXjRy.jpg

2013.05攝於Český Krumlov,Czech


上一篇
[Day11] 資料使用安全(SQL資料遮罩)
下一篇
[Day13] 資料使用安全(SQL連線加密)
系列文
資料安全與簡單加密演算法見面會 30

尚未有邦友留言

立即登入留言