SQL 使用者權限（表、函數、預存程序等）—收藏

第5章 安全與許可權 數 據 庫 管 理 權 限       對資料的訪問是通過SQL Server 2008的許可權階層進行管理的。可以通過GRANT、DENY和REVOKE語句來管理這個許可權階層。 ●       GRANT：允許一個資料庫使用者或角色執行所授許可權指定的操作。 ●       DENY：拒絕一個資料庫使用者或角色的特定許可權，並且阻止它們從其他角色中繼承這個許可權。 ●       REVOKE：取消先前被授予或拒絕的許可權。 5.5.1  管理對SQL Server執行個體和資料庫的訪問1. 控制登入操作 SQL Server 2008提供了一個精細的許可權結構，能夠更出色地控制登入操作。可以通過GRANT、DENY和REVOKE語句來控制許可權，通過sys.Server_permissions目錄檢視來擷取有關服務許可權的資訊。 如下語句將為登入名稱Marylogin授予建立和執行SQL Server Profiler跟蹤的許可權： GRANT ALTER TRACE TO Marylogin; 使用者可以通過使用fn_my_permissions函數來瞭解自己的許可權。如下語句將顯示使用者的許可權： SELECT * FROM fn_my_permissions (NULL, 'SERVER'); 2. 為資料庫角色授予許可權 除了使用固定資料庫角色之外，還可以為資料庫角色授予小粒度的資料庫許可權。下面的語句將為資料庫使用者Peteruse授予BACKUP DATABASE(備份資料庫)許可權： -- Change the connection context to the database 執行個體資料庫. USE 執行個體資料庫; GO -- Grant permissions to the database user Peteruse -- to backup the database 執行個體資料庫. GRANT BACKUP DATABASE TO Peteruse; 5.5.2  管理對錶和列的訪問1. 更改對錶的訪問 使用者對錶所擁有的有效許可權控制著使用者對錶的訪問行為。可以通過管理表的許可權來控制資料庫使用者對錶的訪問。如表5-4所示的就是可以管理的表的許可權。可以對資料庫使用者或角色指定這些許可權。 表5-4  表的許可權 權    限 描    述 ALTER 可以更改表屬性 CONTROL 提供所有權之類的許可權 DELETE 可以從表中刪除行 INSERT 可以向表中插入行 REFERENCES 可以通過外鍵引用其他表 SELECT 可以在表中選擇行 TAKE OWNERSHIP 可以取得表的所有權 UPDATE 可以在表中更新行 VIEW DEFINITION 可以訪問表的中繼資料 可以使用GRANT語句授權資料庫使用者或者角色對錶的訪問。如下語句將授予使用者Peteruse對錶Adminschema.Student的SELECT、INSERT和UPDATE許可權： -- Change the connection context to the database 執行個體資料庫. USE 執行個體資料庫; GO -- Grant some permissions to Peteruse on the Adminschema.Student table. GRANT SELECT,INSERT,UPDATE ON Adminschema.Student TO Peteruse; 限制對錶的訪問，有兩種不同的情況。如果已經為使用者授予了表的這種許可權，則應該使用REVOKE語句清除之前授與權限。樣本如下： -- Change the connection context to the database 執行個體資料庫. USE 執行個體資料庫; GO -- Revoke SELECT permissions from Peteruse on the Adminschema.Student table REVOKE SELECT ON Adminschema.Student TO Peteruse; 然而，如果使用者隸屬於某個具備此許可權的角色，則使用者可能依然具備通過REVOKE語句取消的許可權。在這種情況下，需要使用DENY語句來拒絕該使用者的訪問。樣本如下： -- Change the connection context to the database 執行個體資料庫. USE 執行個體資料庫; GO -- Deny DELETE permission to Peteruse on the Adminschema.Student table, -- regardless of what permissions this user might -- inherit from roles. DENY DELETE ON Adminschema.Student TO Peteruse; 2. 提供對列的單獨訪問 SQL Server 2008提供了授予或拒絕訪問單獨列的許可權，這個特性提供了靈活的拒絕訪問機制，例如，保護某些列上的機密資料。如表5-5所示的是可以管理的列許可權。 表5-5  列許可權 權    限 描    述 SELECT 可以選擇列 UPDATE 可以更新列 REFERENCE 可以通過外鍵引用列   授權對列的訪問，也使用GRANT語句。以下樣本為Peteruse使用者授予了在表Adminschema.Student的StudentDate和ClientID列上SELECT和UPDATE的許可權。 -- Change the connection context to the database 執行個體資料庫. USE 執行個體資料庫; GO -- Grant SELECT and UPDATE permissions to Peteruse -- on some specific columns of the Adminschema.Student table GRANT SELECT,UPDATE ( StudentDate, ClientID) ON Adminschema.Student TO Peteruse; 取消對列的訪問授權，與取消對錶的訪問授權類似，可以使用REVOKE語句來實現，但如果要阻止一個使用者獲得某種許可權，則需要使用DENY語句。 -- Change the connection context to the database 執行個體資料庫. USE 執行個體資料庫; GO -- Revoke previosly granted or denied permissions -- from Peteruse on the StudentDate column. REVOKE UPDATE (StudentDate) ON Adminschema.Student TO Peteruse; 5.5.3  管理對可程式化對象的訪問 可程式化對象，如預存程序及使用者定義的函數，具有自己的安全上下文。資料庫使用者需要獲得授權才能執行預存程序、函數和程式集。一旦資料庫引擎檢查了執行可程式化對象的許可權，就會在可程式化對象內部對其所執行的操作進行許可權檢查。當資料庫物件按順序相互訪問時，該訪問順序將形成一個擁有權鏈結。 1. 管理預存程序的安全性 在各種資料庫物件中，預存程序是資料庫開發人員最常使用的資料庫物件。與其他資料庫物件一樣，預存程序也是需要保護的對象。使用者需要具備執行操作的許可權，就像建立一個預存程序一樣，使用者需要具備相應的許可權來執行一個預存程序。如表5-6所示是可以為預存程序授與權限。 表5-6  預存程序許可權 權    限 描    述 ALTER 可以更改預存程序屬性 CONTROL 可以提供所有權之類的許可權 EXECUTE 可以執行預存程序 TAKE OWNERSHIP 可以取得預存程序的所有權 VIEW DEFINITION 可以查看預存程序的中繼資料 在執行一個預存程序時，SQL Server會檢查當前資料庫使用者是否具有該預存程序的EXECUTE許可權。下面的語句將為資料庫使用者Peteruse 授予預存程序dbo.uspGetBillOfMaterials的EXECUTE許可權： -- Change the connection context to the database 執行個體資料庫. USE 執行個體資料庫; GO -- Grant EXECUTE permission to Peteruse on a stored procedure. GRANT EXECUTE On dbo.uspGetBillOfMaterials TO Peteruse; 同樣地，如果要阻止一個使用者執行某個預存程序，可以取消或者拒絕該使用者的EXECUTE許可權。 2. 系統管理使用者定義函數的安全性 使用者定義函數和預存程序一樣，也是可程式化對象。主要存在兩種類型的使用者定義函數：只返回單一值的純量涵式和返回一個表資料類型值的資料表值函式。根據使用者定義函數類型的不同，可以對函數授予EXECUTE或SELECT許可權，如表5-7所示。 表5-7  使用者定義函數許可權 權    限 描    述 ALTER 可以更改函數屬性 CONTROL 可以提供所有權之類的許可權 TAKE OWNERSHIP 可以取得函數的所有權 VIEW DEFINITION 可以查看函數的中繼資料 SELECT 可以選擇資料表值函式所返回的資料(只對錶值函數有效) EXECUTE 可以執行使用者定義函數(只對純量涵式有效)   對於執行資料表值函式，SQL Server將檢查使用者是否擁有此函數所返回的表的SELECT許可權。可以採用與為表授予SELECT許可權相同的方式來為資料表值函式授予SELECT許可權。如下語句將授予資料庫使用者Peteruse對使用者定義函數dbo.ufnGetContactInformation的SELECT許可權： -- Change the connection context to the database 執行個體資料庫. USE 執行個體資料庫; GO -- Grant permission to Peteruse to execute a user defined function. GRANT SELECT ON dbo.ufnGetContactInformation TO Peteruse; 注意： 資料表值函式還有另外一種類型，叫做內嵌函式。內嵌函式在功能上等同於視圖，但是它支援參數。從安全形度來講，這種類型的函數等同於視圖。 對於執行純量涵式，資料庫使用者需要在函數上具備EXECUTE許可權。可以採用與為預存程序授予EXECUTE許可權相同的方式來為純量涵式授予EXECUTE許可權。如下語句將授予資料庫使用者Peteruse對使用者定義函數dbo.ufnGetContactInformation的EXECUTE許可權： -- Change the connection context to the database 執行個體資料庫. USE 執行個體資料庫; GO -- Grant Peteruse permission to execute a user defined function. GRANT EXECUTE ON dbo.ufnGetStock TO Peteruse; 3. 管理程式集的安全性 SQL Server 2008提供了在資料庫引擎內部包含.NET程式集(引用.dll檔案的對象)，並在預存程序及函數中調用這些程式集的能力。可以為程式集分配與預存程序一樣的許可權，這些許可權如表5-6所示。 (1) 許可權集 建立一個程式集時，需要指定一個許可權集。許可權集指定了程式集在SQL Server中所授予的一個代碼存取權限的集合。許可權集具有如下3種不同的類型。 ●       SAFE類型：程式集執行的代碼不能訪問外部系統資源。SAFE類型是最受限制的許可權集合，並且是預設的類型。 ●       EXTERNAL_ACCESS類型：程式集可以訪問外部系統資源。 ●       UNSAFE類型：程式集可以執行Unmanaged 程式碼。 對於不需要訪問外部資源的程式集，推薦使用SAFE類型的許可權集。 (2) 執行一個程式集 當一個應用程式嘗試訪問程式集中的對象時，資料庫引擎會檢查目前使用者是否具有該程式集的EXECUTE許可權。如下語句將授予資料庫使用者Peteruse對程式集的EXECUTE許可權： -- Change the connection context to the database 執行個體資料庫. USE 執行個體資料庫; GO -- Grant Peteruse permission to execute an assembly. GRANT EXECUTE ON <AssemblyName> TO Peteruse; 通過為一個程式集授予EXECUTE許可權，可以為資料庫使用者授予對程式集中所有對象的EXECUTE許可權。 4. 管理擁有權鏈結 擁有權鏈結是資料對象互相訪問的順序。例如，在一個預存程序中，向一個表中插入一行資料，預存程序稱為調用對象，表稱為被調用對象。SQL Server遍曆這個鏈中的連結時，與單獨訪問資料庫物件時的方式不同，資料庫引擎會以另一種方式評估對對象的存取權限。 在一個鏈中訪問對象時，SQL Server首先會比較對象的所有者與調用對象的所有者。如果兩個對象的所有者相同，則不評估被引用對象的許可權。這個特性對管理對象許可權非常有用。例如，假設資料庫使用者Peteruse建立了一個名稱為Person.SupplierContacts的表，並在一個名稱為Person.InsertSupplierContacts的預存程序中向PersonSupplierContacts表中插入了行。由於這兩個資料對象具有同樣的所有者Peteruse，因此，只需授予其他使用者對預存程序Person.InsertSupplier- Contacts的EXECUTE許可權，以允許其他使用者在訪問表PersonSupplierContacts時，依然具有EXECUTE許可權。 注意： 擁有權鏈結提供了一種強大的封裝演算法。一個資料可以被設計成只通過充分文檔化的公用介面(例如預存程序和使用者定義函數)來對外提供資料訪問，這些預存程序和使用者定義函數隱藏了資料設計實現的複雜性。資料庫開發人員可以充分利用擁有權鏈結，在拒絕所有使用者對資料庫中所有表的訪問的同時，仍然可以允許其訪問資料。 5. 管理執行內容 執行內容由串連到相應會話的使用者、登入名稱或者由執行(調用)相應模組的使用者或登入名稱確定。在SQL Server 2008進行對象許可權檢查時，登入名稱和使用者令牌為其提供了所需的資訊。在SQL Server 2008中，可以使用EXECUTE AS語句來更改執行內容。這一操作稱為切換執行內容。 (1) 運行EXECUTE AS EXECUTE AS語句允許顯式地定義當前串連的執行內容。可以用EXECUTE AS更改當前串連的登入名稱或者資料庫使用者。內容相關的變化在另一個上下文變更發生前、串連關閉前或者一個REVERT語句執行前始終是有效。如下語句使用了EXECUTE AS語句為資料庫使用者Peteruse更改執行內容： z-- Change the connection context to the database 執行個體資料庫. USE 執行個體資料庫; GO -- Change the execution context to the user Peteruse. EXECUTE AS USER=' Peteruse '; -- The following statement will be executed under Peteruse 's credentials. TRUNCATE TABLE dbo.ErrorLog; 由於使用者Peteruse沒有truncate表的許可權，因此，上述代碼將會產生一個錯誤。而去下truncate表的語句則能成功執行： -- Change the execution context back to the original state REVERT; -- Now the following statement will be executed under -- the original execution context. TRUNCATE TABLE dbo.ErrorLog; (2) 管理環境切換 除了控制批處理(批處理是包含一個或多個Transact-SQL語句的組，這個組從應用程式一次性地發送到SQL Server執行，就像前面的TRUNCATE TABLE樣本一樣)的執行內容，還可以控制預存程序和使用者定義函數的執行內容。在這些模組中切換上下文時，可以控制在這些預存程序或者函數中使用哪個使用者帳戶來訪問它所引用的資料庫物件。為此，只需要對EXECUTE AS語句進行如下改動即可。 ●       CALLER：預存程序或者使用者定義函數內的語句都在模組調用者的上下文中執行。 ●       SELF：所有語句在建立或者更改預存程序或者使用者定義函數的使用者的上下文中執行。 ●       OWNER：所有語句在預存程序或者使用者定義函數的當前所有者的上下文中執行。 ●       <User>：所有語句在指定資料庫使用者或者登入名稱的上下文中執行。 以下樣本將切換上下文到資料庫使用者dbo的上下文中以建立一個預存程序。然後，為資料庫使用者Peteruse授予這個建立預存程序的EXECUTE許可權，並更改上下文以測試預存程序的執行： -- Create a stored procedure to execute statements -- as dbo. CREATE PROCEDURE dbo.usp_TruncateErrorLog     WITH EXECUTE AS 'dbo'     AS     TRUNCATE TABLE dbo.ErrorLog; GO -- Grant permissions to execute this procedure to Peteruse. GRANT EXECUTE ON dbo.usp_TruncateErrorLog TO Peteruse -- Change the execution context of this batch to Peteruse. EXECUTE AS [USER=]'Peteruse' -- Execute the stored procedure. EXECUTE dbo.usp_TruncateErrorLog 由此可見，在不能通過授權來使使用者執行某些操作時，尤其適合使用環境切換。

－首頁－添美科技－友情連結－連絡方式－   Copyright 2010 TianMei Technology　All rights reserved. To comment on this site 　 遼B-2-4-20100065