SQL Server中的代理帳戶

2024-08-31 00:52:57

字體：大中小

來源：轉載

供稿：網友

　　如果連接到SQL server 的應用程序只需要訪問SQL server 實例內部的對象和資源，那這是非常理想的。但是，通常一個應用程序需要訪問外部系統的資源，例如文件、網絡、環境變量或注冊表。舉例來說，應用程序可能需要運行xp_cmdshell 擴展存儲過程來調用一個Windows shell命令，并執行一個shell命令來獲取一個目錄下的文件列表。或者，這個應用程序安排一個SQL server Agent工作來執行維護任務。這個工作有一個Active Scripting工作步驟或一個Web Service任務來調用一個Web Service，以便驗證地理位置和郵編信息。

　　默認情況下，在SQL server 2000中，只有sysadmin固定服務器角色的成員才可以執行xp_cmdshell擴展存儲過程和Active Scripting工作步驟。當xp_cmdshell擴展存儲過程被sysadmin固定服務器角色的一個成員執行時，shell命令的Windows進程在SQL Server服務帳戶的安全上下文中運行。當sysadmin角色的一個成員的一個工作運行時，它的Active Scripting工作步驟運行在SQL server Agent服務帳戶的安全之下。但是，在大多數公司里，數據庫管理員角色和應用程序開發人員角色通常是分開的。基于安全考慮，應用程序開發人員不被允許具有sysadmin權限。為了使應用程序開發人員可以訪問外部資源而不必給他們過多的權限，SQL Server提供了代理帳戶的解決方案。

　　擴展存儲過程xp_sqlagent_PRoxy_account設置SQL server Agent和xp_cmdshell在執行工作或命令時對于不是sysadmin固定服務器角色成員的用戶所使用的代理帳戶信息。例如，下面的命令設置代理帳戶為一個域帳戶PowerDomain/PowerUser，然后使得非sysadmin登錄進來在域帳戶的安全上下文中執行Active Scripting工作步驟和xp_cmdshell。

　　 USE master
　　GO

　　-- Create a test login called testuser

　　EXEC sp_addlogin 'testuser', 'testuser'

　　-- Add a windows domain account PoweDomain/PowerUser as the proxy account.

　　EXECUTE xp_sqlagent_proxy_account N'SET' , N'PowerDomain' , N'PowerUser' , N'P@ssw0rd'

　　-- Enable non-sysadmin logins to run active Scripting job steps and execute xp_cmdshell.

　　EXECUTE msdb..sp_set_sqlagent_properties @sysadmin_only = 0

　　-- Grant database access to the sql server login account that you want to provide access.

　　EXEC sp_grantdbaccess 'testuser'

　　-- Grant execute permission on xp_cmdshell to the sql server login account.

　　GRANT exec ON xp_cmdshell TO [testuser]

　　GO

　　請注意，在SQL server 2000中只能指定一個代理帳戶。這個帳戶是用來執行xp_cmdshell和Active Scripting工作步驟的。

　　在SQL server 2005和2008中，為了允許一個非sysadmin登錄進來從而執行xp_cmdshell，你需要創建一個特定的系統憑證##xp_cmdshell_proxy_account##，這是通過運行外部存儲過程sp_xp_cmdshell_proxy_account并指定一個Windows帳戶來實現的。這個帳戶將被非sysadmin角色的成員用戶用來運行xp_cmdshell。

USE master
　　GO

　　-- Create a test login called testuser

　　CREATE LOGIN testuser WITH PASSWord='P3h4jek@x'

　　-- Create a proxy credential for xp_cmdshell.

　　EXEC sp_xp_cmdshell_proxy_account 'PowerDomain/PowerUser', 'P@ssw0rd';

　　-- Grant database access to the sql server login account that you want to provide access.

　　EXEC sp_grantdbaccess 'testuser'

　　-- Grant execute permission on xp_cmdshell to the sql server login account.

　　GRANT exec ON sys.xp_cmdshell TO [testuser]

　　為了確認##xp_cmdshell_proxy_account##憑證確實被創建了，你可以選擇sys.credentials視圖。

　　你還可以對SQL server 2005 and 2008中的SQL server Agent工作的代理進行更多和更好的控制。你可以指定不止一個的代理帳戶。你還可以指定你想應用一個代理帳戶到哪個子系統(工作步驟類型)上去。

　　在創建一個代理帳戶之前，你需要定義一個Windows憑證。在憑證創建之后，你可以創建一個代理帳戶并分配這個憑證給它。然后你授權這個代理訪問給一個或多個子系統。如果你使用sp_grant_proxy_to_subsystem存儲過程來授予訪問權限給多個子系統，那么你將需要多次執行這個存儲過程。在這之后，你可以授權這個權限給多個SQL Server登錄、msdb角色、和/或服務器角色來使用這個代理帳戶。

　　例如，我們想為執行SSIS包的工作步驟創建一個叫做SSISProxy的代理。這個代理將使用域帳戶PowerDomain/PowerUser的憑證。我們想允許testUser使用這個代理帳戶登錄進來執行它所擁有的SQL server Agent工作中的SSIS包。

　　 -- Create a credential containing the domain account PowerDomain/PowerUser and its password
　　CREATE CREDENTIAL PowerUser WITH IDENTITY = N'PowerDomain/PowerUser', SECRET = N'P@ssw0rd'

　　USE [msdb]

　　-- Create a new proxy called SSISProxy and assign the PowerUser credentail to it

　　EXEC msdb.dbo.sp_add_proxy @proxy_name=N'SSISProxy',@credential_name=N'PowerUser',@enabled=1

　　-- Grant SSISProxy access to the "SSIS package execution" subsystem

　　EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'SSISProxy', @subsystem_id=11

　　-- Grant the login testUser the permissions to use SSISProxy

　　EXEC msdb.dbo.sp_grant_login_to_proxy @login_name = N'testUser', @proxy_name=N'SSISProxy'

　　GO

　　在登錄進去之后，testUser授權這個訪問給代理帳戶SSISProxy，在testUser所擁有的一個工作中，如果一個工作步驟是要執行一個SSIS包，那么testUser可以選擇代理SSISProxy并在這個代理帳戶下運行這個步驟。

　　sql server 2005有11個子系統，羅列如下：

　　1 Value Description

　　2 microsoft ActiveX Script

　　3 Operating system (CmdExec)