自動化的安全性設定

by adonis 12. 八月 2013 15:46

作    者:楊先民
審    稿:張智凱

 

前言

自動化一直是 SQL Server很重要的設定,尤其是每天都要做的事情,不安排個排程給 SQL Server 自行完成,實在浪費管理者寶貴的時間。然而自動化最常出現的問題就是安全性的權限不會設定,所以本期就來介紹 SQL Server自動化安全性的相關設定。

所謂的自動化

何謂自動化,就是時間到,SQL Server會自動執行某一個功能稱之。也許這個功能是匯入資料,也許這個功能是備份資料庫,總而言之,並不是由目前登入帳號的連線進行功能執行。
也就是說,將會有一個代理程式進行這項工作,所以權限的設定就變的很重要。
一般來說,如果 John這個帳號(非管理者),想要執行備份工作,你就必需給予它該資料庫中的備份操作員,如下:

image

但各位要知道,備份這個工作,包含了兩個動作,一個是對資料庫備份,另一個則是將備份的資料寫入到磁碟中。

其中,我們設定了 John是資料庫的備份操作員,所以 John可以備份,但是將備份資料寫入磁碟中,並不是 John這個 SQL帳號可以控制的。

也就是說,備份的兩個動作, John有備份權力,但實際寫入磁碟中的,是 SQL Server服務登入帳號所做的事情。

我們假設 SQL Server的服務登入帳號是 Local System帳號(也就是本機管理者,無法存取網路資源),當你把 Northwind資料庫備份到本機 c:\backups\目錄中時

1.    John有權力備份 (O)
2.    SQL Server服務 Local System帳號將備份檔存入 c:\backups\目錄中(O)

這兩點都成立,所以備份成功。

如果你想把資料庫備份到 \\vs100\share\目錄中,一個網路分享的目錄,你也設定了存入權限,這時 John同樣進行備份時:

1.    John有權力備份(O)
2.    SQL Server服務 Local System帳號將備份檔存入 \\vs100\share\目錄中 (X)
因為 SQL Server服務登入帳號為 Local System帳號並沒有辦法存取網路資源,所以這個備份會失敗,主要是出在服務帳號安全性的問題上。

如果上述的動作要成功,必需把 SQL Server的服務帳號設定成本機使用者或是網域使用者方能存取網路上的資源。

而如果上述的動作需要設定成自動化呢?也就是需要自動的備份資料庫到本機目錄呢?同樣是希望用 John這個帳號建立一個作業(Job),然後安全性的設定必需要考量的是下面三個:

1.    John需要有建立 Job的權力
2.    John需要有備份資料庫的權力
3.    SQL Server Agent服務帳號需有寫入本機目錄的權限。

如果 John這個帳號需要建立 Job的話,他需要有 msdb資料庫中 SQLAgentOperatorRole角色的權力,不然他進管理工具是看不到 SQL Agent的 icon,如下圖:

image

一但設定之後,才能看到 Job的建立:

image

image

利用 John建立一個備份的 Job,此 Job備份資料庫 Northwind到c:\backups目錄中。

由於 John有備份權力,而且是備份到本機,所以資料庫可以順利備份成功。

image

但如果情境改成 John能建立 Job,並將 Northwind資料庫備份到網路上的 share folder的話,這個工作就極有可能失敗,因為
1.    John需要有建立 Job的權力(O)。
2.    John需要有備份資料庫的權力(O)。
3.    SQL Server Agent服務帳號需有寫入網路目錄的權限(?)。

其中比較大問題的地方在3這個地方,1與2都不是問題因為我們確實都有權力。為何3的地方會是個問題主要是因為 SQL Server Agent的服務登入帳號如果是 local system這個帳號,是無法存取網路資源的。如果你是用 John這個帳號下指令,則需要 SQL Server的服務登入帳號有權力,但若是用 Job的話,則變成 SQL Server Agent的登入帳號要有權力才行。

如果要存取的是 SQL Server之外的外部資源

前面的情境,我們是設定自動備份 Northwind資料庫,再怎麼說還是 SQL Server內部的工作,但如果今天情境改成「自動的刪除作業系統上的某個檔案」時,就安全性的角度來說是不會過關的,為何?因為如果 John是一個 SQL驗證的登入帳號,他是沒有作業系統檔案的權力,所以即便 John可以建立 Job,設定刪除檔案的作業, John依舊無法讓該 Job執行成功。
如果完全不用設定就可以成功,那也不用安全性了,任何只要有能力建立 Job的 SQL登入帳號,都可以隨意的對作業系統或是非 SQL Server資源進行處理。

所以如果希望 John這個 SQL驗證的帳號能夠順利執行非 SQL Server資源的 Job,我們必需為此 Job設定 proxy帳號。

設定 Proxy帳號,請按照下圖的順序:

image

這裡的 「Resource」就是你要對 SQL Server 或是以外的什麼東西有權限,例如作業系統的某個檔案夾。
而 Credential則是對應到一個 Windows帳號,而這個 Windows帳號是有能力使用該 Resource的。再設定 Proxy帳號,對應到 Credential,並且設定哪一個帳號能夠使用這個 Proxy帳號。

在沒有 Proxy帳號之前,所有大大小小的事情都是交由 SQL Server Agent的服務帳號進行 SQL Server之外資源的使用,這最後會造成一個問題,就是你會發現 SQL Server Agent的服務帳號都變成了網域或是本機管理者的帳號,因為這樣比較方便嘛(有管理者這樣說),不過這樣就很容易造成安全的問題。


開始設定 Proxy帳號



所以我們要開始設定 proxy帳號,讓這個 proxy帳號給 Job使用。設定方式如下:
1.    先設定 Credential帳號,在 Security底下可以找到:
image image

credential name名稱請自取,並且選定 Windows帳號(圖中只是範例,我使用 Windows管理者帳號,但其實並不需要),並且輸入這個 Windows帳號的密碼(當你按 ok時, SQL Server並不會檢查,而是真的執行時才會出現錯誤訊息)。

2.    接下來是設定 proxy帳號,對應到步驟1所設定的 credential name:

image
這裡的 proxy有分類,這樣可以讓安全性的設定更有彈性,因為即便你的 credential name所指向的 Windows帳號是管理者,還是只能執行部分的功能。

image
3.    通常大家設定完2的步驟就忘了,還要設定這個 proxy帳號誰可以使用,不然每個登入者都能使用嗎?所以還要再設定左邊的 Principals帳號,指定誰可以使用。

image

4.    如此一來,當 John設定 Job時,就可以設定以 proxy帳號的身份執行,這樣該 Job在執行時就利用 proxy帳號執行,而不會出現安全性的問題了。
image
這裡需要附帶一提一件事,如果你的登入帳號,明明有設定具有建立 Job的權力,但是卻看不到 SQL Agent這個 icon,似乎也無法建立 Job時,你需要看看是否你曾經把伺服器角色 public的「view any database」的權力拿掉,如果權力拿掉,即便你具有建立 Job的權力,你依然無法在管理工具上看到 SQL Agent這個 icon,也就無法透過這個帳號,使用圖型介面來建立 Job了。

image
不過因為 public角色在 SQL Server 2008時是隱藏的(在 SQL 2012又出現了),所以到底有沒有更改過類似的權限只有你自己才知道了。

 

image

Tags:

新增評論




  Country flag
biuquote
  • 評論
  • 線上預覽
Loading






NET Magazine國際中文電子雜誌

NET Magazine國際中文電子版雜誌,由恆逸資訊創立於2000,自發刊日起迄今已發行超過500篇.NET相關技術文章,擁有超過40000名註冊讀者群。NET Magazine國際中文電子版雜誌希望藉於電子雜誌與NET Developer達到共同學習與技術新知分享,歡迎每一位對.NET 技術有興趣的朋友們多多支持本雜誌,讓作者群們可以有持續性的動力繼續爬文。<請加入免費訂閱>

月分類Month List