修改SQL Server 2005執行環境

來源:互聯網
上載者:User

  執行環境是SQL Server中設定使用者權限的認證方式,例如,當您登入到SQL Server的時候,登入賬戶就被賦予了一定的許可權,其中可能包括登入的功能、訪問資料庫以及在資料庫中執行某些操作的功能。

  SQL Server 2005包含了EXECUTE AS語句,通過使用EXECUTE AS語句,您可以為批處理和過程轉換執行環境,這樣,調用該批處理或過程的使用者就可以使用不同的許可權來操作了。

  擁有權鏈結

  在我正式講解SQL Server 2005中執行環境的問題之前,先來簡單地說說擁有權鏈結的工作原理。

  當使用者執行一個預存程序的時候(假定該使用者擁有執行該預存程序的許可權),SQL Server將該預存程序的所有者與這個預存程序所涉及到的對象的所有者進行對比,如果他們的所有者相同,那麼就不必對這些引用對象的許可權進行評估了。

  所以,如果使用者Tim獲得了預存程序usp_ProcedureChain的許可權,而usp_ProcedureChain預存程序的所有者是dbo,那麼,如果dbo還同時擁有usp_ProcedureChain所調用的其他預存程序,那麼Tim在執行這個預存程序的時候就不會出現錯誤。

  執行環境的轉換

  在SQL Server 2000中,您可以使用SETUSER命令來類比SQL使用者的執行環境,但問題在於,只有系統管理員或者資料庫的所有者才能使用這個命令,而且Windows賬戶也不能使用該命令。

  在SQL Server 2005中,EXECUTE AS語句可以替代SETUSER來改變預存程序、觸發器、批處理或者函數的執行環境。如果執行環境變成了另外一個使用者,那麼SQL Server將檢查該使用者的許可權。如果您需要在建立或修改一個預存程序或函數的時候指定EXECUTE AS語句,您需要具備IMPERSONATE的許可權,以及建立該對象的許可權。

  執行個體

  正如我剛才所介紹的一樣,改變預存程序的執行環境非常有用,接下來我將通過執行個體來講解如何?這一功能。在這個例子中,您會看到如何使用EXECUTE AS將沒有確切許可權的使用者類比為所有者對錶格進行插入操作。

  在第一行語句中,我使用了REVERT命令,這樣,您就可以完整地返回到例子中,而不必擔心需要清除任何對象。

REVERT
GO

  在下面的代碼的第七行,我使用了清除語句,這樣可以檢查我在隨後的例子中要使用的對象是否已經存在,如果已經存在,就將其清除。

IF OBJECT_ID('usp_InsertMyTable','P')>0
DROP PROCEDURE usp_InsertMyTable
GO
IF OBJECT_ID('TableOwnerSchema.MyTable','U')>0
DROP TABLE TableOwnerSchema.MyTable
GO
IF EXISTS
(SELECT * FROM sys.schemas WHERE name = N'TableOwnerSchema')
DROP SCHEMA [TableOwnerSchema]
IF EXISTS
(SELECT * FROM sys.database_principals WHERE name = N'BaseUser')
DROP USER BaseUser
IF EXISTS
(SELECT * FROM sys.server_principals WHERE name = N'BaseUser')
DROP LOGIN BaseUser
IF EXISTS
(SELECT * FROM sys.database_principals WHERE name = N'TableOwner')
DROP USER TableOwner
IF EXISTS
(SELECT * FROM sys.server_principals WHERE name = N'TableOwner')
DROP LOGIN TableOwner

  以下的指令碼語句建立了兩個登入名稱和資料庫的使用者賬戶,注意,CHECK_EXPIRATION和CHECK_POLICY語句,這兩條語句是SQL Server 2005中新出現的。這些語句告訴SQL Server不要對這個使用者賬戶強制執行密碼截止期限策略,同時也不要進行任何類型的密碼原則檢查,對於強制安全性原則而言,這些是非常有效方法。

CREATE LOGIN [BaseUser] WITH PASSWORD=N'baseuser',
DEFAULT_DATABASE=[TRS],
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
CREATE USER [BaseUser] FOR LOGIN [BaseUser]
GO
CREATE LOGIN [TableOwner] WITH PASSWORD=N'tableowner',
DEFAULT_DATABASE=[TRS],
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
CREATE USER TableOwner FOR LOGIN TableOwner
GO

  在SQL Server 2005中,模式不再是和資料庫使用者相同的事情了,對於所包含的對象而言,它處於完全不同的名稱空間。使用者和模式的分離是SQL Server 2005中的一大進步,這樣做使對象的所有權可以分離,而且比SQL Server 2000更易於管理,以下的語句建立了我們將要使用的資料庫模式:

CREATE SCHEMA [TableOwnerSchema] AUTHORIZATION [TableOwner]
GO
Now I enable logins so they can be used:
ALTER LOGIN [TableOwner] ENABLE
ALTER LOGIN [BaseUser] ENABLE
GO
GRANT CREATE TABLE TO TableOwner
GO

  首先,我使用了EXECUTE AS命令,我將當前的執行環境設定為TableOwner,在運行了這個命令之後,所有的許可權評估將以TableOwner運行,而以前的系統管理員許可權將不再適用。

EXECUTE AS USER = 'TableOwner'
GO

  運行這個語句就能夠表明現在的執行環境是TableOwner:

SELECT SESSION_USER
GO

  這個指令碼將在TableOwnerSchema的模式中建立一個名為MyTable的表格,因為我已經賦予了該使用者CREATE TABLE 的許可權,所以TableOwner可以執行這條語句。

CREATE TABLE TableOwnerSchema.MyTable
(
Field1 INT
)
GO

  當我運行REVERT語句的時候,可以在執行環境鏈中回退一步,在SQL Server 2005中,執行環境是可以嵌套的,所以如果您在同一個資料庫連接中有很多使用者在運行,您可能需要多次執行該語句以返回到原始的登入環境。

REVERT
GO
SELECT SESSION_USER
GO

  現在我要對新的表格進行快速選擇以確認它的存在:

SELECT * FROM TableOwnerSchema.MyTable
GO

  以下的指令碼建立了一個過程可以插入新的TableOwnerSchema.MyTable表格,注意我在流程定義中使用了WITH EXECUTE AS 'TableOwner'語句,這意味著該過程被執行的時候,它將在TableOwner的執行環境中被執行。

CREATE PROCEDURE usp_InsertMyTable
WITH EXECUTE AS 'TableOwner'
AS
BEGIN
INSERT INTO TableOwnerSchema.MyTable(Field1)VALUES(8)
END
GO

  我還可以將執行許可權賦予一個使用者賬戶,在這種情況下,我使用以前建立的名為BaseUser的使用者。

GRANT EXEC ON usp_InsertMyTable TO BaseUser
GO

  接下來,我將執行環境轉換為BaseUser並嘗試運行預存程序:

EXECUTE AS USER = 'BaseUser'
GO
EXEC usp_InsertMyTable
GO

  現在我可以向TableSchema.MyTable表格中添加記錄了,因為在這個過程中TableOwner允許我這樣做,而BaseOwner並沒有明確的許可權可以向該表格添加記錄,所以該使用者的任何嘗試都會導致錯誤的發生。為了示範這個問題,可以運行以下的指令碼,該指令碼改變了我們剛才的過程,改為運行在調用者的執行環境中。

REVERT
GO
ALTER PROCEDURE usp_InsertMyTable
AS
BEGIN
INSERT INTO TableOwnerSchema.MyTable(Field1)VALUES(8)
END
GO
EXECUTE AS USER = 'BaseUser'
GO
EXEC usp_InsertMyTable
GO
REVERT

  開發人員和資料庫管理員會發現在執行預存程序的時候轉換許可權非常有用,尤其是您處理TRUNCATE TABLE語句的時候,這個方法能幫上大忙,因為TRUNCATE TABLE並沒有可以指定的許可權。您可以將許可權賦予將要進行截取表格操作的使用者,然後在操作結束的時候再將原有的許可權設定恢複就可以了。



相關文章

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。