Sql-Server

儲存過程可以選擇和更新其他數據庫中的表 - 授予最小權限

  • March 3, 2016

我最近不得不製作儲存過程spPartyOrderAllocation_AllocateItems來選擇和更新其他數據庫中的表，同時將權限級別保持在最低水平。

這是必要的，因為特定應用程序Stock Allocation將連接到數據庫並僅執行此儲存過程。

我這樣做的方法是：

**1）**創建一個登錄名，用於在不同數據庫中執行儲存過程

USE [master]
GO
CREATE LOGIN [PartyOrderAlloc_HiddenLogin] WITH PASSWORD=N'R4dh3R4dh3' 
, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO

**2)**將上述登錄的模擬授予將執行該過程的使用者組

GRANT IMPERSONATE ON LOGIN::[PartyOrderAlloc_HiddenLogin] TO [mycompany\it testing team]
GO

**3）**根據上面（1）的登錄創建使用者並授予所需的所有權限

USE [SAOrder]
GO
CREATE USER [PartyOrderAlloc_HiddenUser] FOR LOGIN [PartyOrderAlloc_HiddenLogin]
GO
--The SELECT permission was denied on the object 'tblSAOrderItemStatus', database 'SAOrder', schema 'dbo'.
GRANT SELECT ON dbo.tblSAOrderItemStatus TO [PartyOrderAlloc_HiddenUser]  
GO

-- The UPDATE permission was denied on the object 'tblSAOrderItemStatus', database 'SAOrder', schema 'dbo'.
GRANT UPDATE  ON dbo.tblSAOrderItemStatus TO [PartyOrderAlloc_HiddenUser]  
GO


USE [SAStockData]
GO
CREATE USER [PartyOrderAlloc_HiddenUser] FOR LOGIN [PartyOrderAlloc_HiddenLogin]
GO
GRANT SELECT ON dbo.xtblOrgUnt     TO [PartyOrderAlloc_HiddenUser]  
GO
GRANT INSERT ON dbo.tblAudit       TO [PartyOrderAlloc_HiddenUser]                          
GO


USE [SAStockLevel]
GO
CREATE USER [PartyOrderAlloc_HiddenUser] FOR LOGIN [PartyOrderAlloc_HiddenLogin]
GO
GRANT SELECT ON dbo.tblOrgGrpStockLevel  TO [PartyOrderAlloc_HiddenUser]  
GO
GRANT UPDATE ON dbo.tblOrgGrpStockLevel  TO [PartyOrderAlloc_HiddenUser]  
GO
GRANT SELECT ON dbo.tblOrgUntStockLevel  TO [PartyOrderAlloc_HiddenUser]  
GO
GRANT UPDATE ON dbo.tblOrgUntStockLevel  TO [PartyOrderAlloc_HiddenUser]  
GO
GRANT SELECT ON dbo.ztblAllocationBlockItems  TO [PartyOrderAlloc_HiddenUser]  
GO

**4）**在數據庫上創建儲存過程

$$ SAOrder $$

use SAOrder 
go

alter PROCEDURE spPartyOrderAllocation_AllocateItems(@strBxOrderNo varchar(20))
--with EXECUTE AS 'PartyOrderAlloc_HiddenUser'
AS
BEGIN

-- 18-Feb-2016 BAC Created spPartyOrderAllocation_AllocateItems
--

SET NOCOUNT ON;

EXECUTE AS LOGIN='PartyOrderAlloc_HiddenLogin'

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#YTAB')) DROP TABLE #YTAB

Declare @sintOrderSeqNo smallint  
Declare @strItemNo  varchar(20)  
Declare @tintOrgGrpId tinyint  
Declare @tintOrgUntId tinyint

--Select oders items that are on allocation hold and have stock in the W/H
Select OIS.strBxOrderNo, OIS.sintOrderSeqNo, OIS.strItemNo, SL.tintOrgGrpId, Null tintOrgUntId_Target
Into #YTAB
From SAOrder.dbo.tblSAOrderItemStatus OIS
Join SAStockLevel.dbo.tblOrgGrpStockLevel SL on SL.bigOrgGrpStkLvlId = OIS.bigOrgGrpStkLvlId
Join SAStockLevel.dbo.ztblAllocationBlockItems ABI on ABI.strItemNo = OIS.strItemno and ABI.tintOrgGrpId = SL.tintOrgGrpId
Where @strBxOrderNo = OIS.strBxOrderNo
And   IsNull(OIS.sintAllocToOrgUntId,0) = 0
And   OIS.sintSAItemStatus = 1
And   SL.tintStockTypeId = 1

If @@ROWCOUNT = 0
Begin
   Select '1'
   Return
End

Declare cur_ytbIED_SAOrderAllocationReplenishment cursor forward_only for  
Select  
 strBxOrderNo  
, sintOrderSeqNo  
, strItemNo  
, tintOrgGrpId  
From  
#YTAB  

Begin Transaction

Begin Try  

   Open cur_ytbIED_SAOrderAllocationReplenishment  

   Fetch Next From cur_ytbIED_SAOrderAllocationReplenishment  
   Into  
         @strBxOrderNo  
       , @sintOrderSeqNo  
       , @strItemNo  
       , @tintOrgGrpId  

   While @@FETCH_STATUS = 0  
   Begin  

       Begin

           Select Top 1
               @tintOrgUntId = tintOrgUntId
           From
               SAStockLevel.dbo.tblOrgUntStockLevel OUSL
           Where
               strItemNo = @strItemNo
           And
               tintOrgUntId In (Select tintOrgUntId from SAStockData.dbo.xtblOrgUnt Where tintOrgGrpId = @tintOrgGrpId and tintOrgUntId<>1)
           And
               lngTotal > (Case When lngAllocated > 0 Then lngAllocated Else 0 End)
           Order By
               lngTotal - (Case When lngAllocated > 0 Then lngAllocated Else 0 End) Desc   

           IF @@ROWCOUNT = 0
               Begin
                   Delete #YTAB
                   Where strBxOrderNo = @strBxOrderNo and sintOrderSeqNo = @sintOrderSeqNo
               End
           Else
               Begin
                   -- Reserve stock in the W/H by updating allocated stock on w/h row

                   print 'tintOrgUntId = ' + convert(varchar(3),@tintOrgUntId)

                   Update  SAStockLevel.dbo.tblOrgUntStockLevel 
                   Set     lngAllocated = lngAllocated + 1
                   Where   strItemNo = @strItemNo
                   And     tintOrgUntId = @tintOrgUntId

                   -- Update y-table if w/h allocated to

                   Update  #YTAB
                   Set     tintOrgUntId_Target = @tintOrgUntId
                   Where   strBxOrderNo = @strBxOrderNo
                   AND     sintOrderSeqNo = @sintOrderSeqNo

                   -- Allocated to W/H so now insert to log indicating this has happened, In case we require to roll it back later

                   INSERT INTO tblSAOrderAllocationItemAllocateLog
                                                       ([strItemNo]
                                                       ,[tintOrgUntId]
                                                       ,[sintQuantity])
                   VALUES  (@strItemNo
                           ,@tintOrgUntId
                           ,1)
               End

       End


       Fetch Next from cur_ytbIED_SAOrderAllocationReplenishment  
       Into  
             @strBxOrderNo  
           , @sintOrderSeqNo  
           , @strItemNo  
           , @tintOrgGrpId  

   End  

   Close cur_ytbIED_SAOrderAllocationReplenishment  

   Deallocate cur_ytbIED_SAOrderAllocationReplenishment  


    --Move the stock allocated from reserved to allocated    

   UPDATE SAStockLevel.dbo.tblOrgGrpStockLevel    
   SET lngReserved = lngReserved - sintQuantity    
       ,lngAllocated = lngAllocated + sintQuantity    
   FROM SAStockLevel.dbo.tblOrgGrpStockLevel GSL    
   JOIN    
       (    
          SELECT     
                yOAR.strItemNo    
              , OU.tintOrgGrpId    
              , COUNT (*) AS sintQuantity    
          FROM #YTAB yOAR    
          JOIN SAStockData.dbo.xtblOrgUnt OU on OU.tintOrgUntId = yOAR.tintOrgUntId_Target    
          GROUP BY yOAR.strItemNo,OU.tintOrgGrpId    
       ) ALog     
             ON  ALog.strItemNo = GSL.strItemNo     
             AND ALog.tintOrgGrpId = GSL.tintOrgGrpId    
             AND GSL.tintStockTypeId = 1    


   UPDATE tblSAOrderItemStatus    
   SET sintAllocToOrgUntId = tintOrgUntId_Target  
   FROM tblSAOrderItemStatus OIS    
   JOIN #YTAB OA    
      ON  OA.strBxOrderNo = OIS.strBxOrderNo    
      AND OA.sintOrderSeqNo = OIS.sintOrderSeqNo    


    INSERT INTO SAStockData.dbo.tblAudit(dtmDateTime,strMessage)    
    VALUES (Getdate(),'Manual Allocation Completed Successful')    

End Try  


Begin Catch  

   Select Error_Message()  
   IF @@TRANCOUNT > 0 Rollback
   Return

End Catch

commit

Select '0'





END
GO

這一直工作得很好。結果和行為符合預期。

題：

**1）**我怎樣才能以不同的方式更有效地做到這一點？

**2）**你有沒有註意到，而不是用 EXECUTE 編寫程序，因為我不得不添加這一行

  EXECUTE AS LOGIN='PartyOrderAlloc_HiddenLogin'

我知道…我也不喜歡它，但它不適用於我使用 EXECUTE AS。

**3）**也許如果我的登錄名和使用者都是域帳戶，這會有所不同嗎？

我不想添加任何數據庫所有權鏈，或者為了這項工作值得信賴，除非真的有必要。

還：

我們仍然在這些測試機器上使用 sql-2005：

Microsoft SQL Server 2005 - 9.00.5000.00 (X64) 2010 年 12 月 10 日 10:38:40 版權所有 (c) 1988-2005 Microsoft Corporation Developer Edition (64-bit) o​​n Windows NT 6.0 (Build 6002: Service Pack 2)

1）我怎樣才能以不同的方式更有效地做到這一點？

這可以通過使用我稱之為“基於程式碼”的安全性而不是正常的“基於登錄/使用者”的安全性來更好地處理。這個想法本質上是授予一個或多個模組（儲存過程、觸發器、大多數類型的函式等）而不是登錄或使用者的權限。然後，您EXECUTE將模組的權限授予任何合適的使用者和/或角色。

這是一種更簡潔的方法，因為它更加精細/可控，並且不需要IMPERSONATE權限EXECUTE AS、跨數據庫所有權連結或TRUSTWORTHY ON. 您現有的登錄名/使用者只能執行編碼到這些模組中的非常具體的事情，而授予他們IMPERSONATE為在此預期目的之外使用該權限打開了大門。而且這也更安全，因為 usingEXECUTE AS也可以在此預期目的之外使用，並且如果有人要更改儲存過程的定義，它仍然有效。相反，當您對模組進行簽名時，簽名是根據該模組的目前定義生成的，包括語句的WITH EXECUTE AS子句CREATE。意思是，任何更改模組定義或principal_id指定的 forWITH EXECUTE AS將使簽名無效，因此將自動刪除簽名，導致不再授予隱含權限。

您可以通過創建非對稱密鑰或證書**來執行此操作，然後根據需要根據該密鑰或證書創建登錄名和/或使用者，然後使用相同的密鑰或證書對應該是的模組進行簽名能夠做登錄的使用者不應該做的事情。最後，您根據需要將所需的權限分配給登錄和/或使用者。

我在以下答案中提供了幾個範例和解釋：

2）你有沒有註意到，而不是用 EXECUTE 編寫程序，因為我不得不添加這一行

這是因為, , etc 語句的EXECUTE AS子句僅適用於每個數據庫本地的使用者。登錄是伺服器級別的。這與實施我在#1 中的建議無關。CREATE PROCEDURE``CREATE FUNCTION

3）也許如果我的登錄名和使用者都是域帳戶，這會有所不同嗎？

那不應該有什麼不同。但同樣，這與實施我在 #1 中的建議無關。

我不想添加任何數據庫所有權鏈，或者為了這項工作值得信賴，除非真的有必要。

當使用 #1 中建議的基於簽名/基於程式碼的安全性時，這些都不是必需的。

** 證書（包含密鑰）和非對稱密鑰之間的主要區別在於將該密鑰複製到其他數據庫的難易程度。如果需要非常本地化到單個數據庫，那麼非對稱密鑰就可以了。但是，如果您需要跨多個數據庫進行管理，或者需要伺服器級別的權限，那麼使用證書通常更容易，因為它們允許將其密鑰資訊備份到一個文件中，然後可以使用該文件重新創建該文件其他數據庫中的相同證書，即使在其他實例上也是如此。從 SQL Server 2012 開始，您可以提取可用於重新創建它們的十六進製字節。對於非對稱密鑰，我發現在所有數據庫中使用一緻密鑰創建它們的唯一方法是將密鑰資訊嵌入到程序集中，然後將該程序集載入到數據庫中，從程序集中創建非對稱密鑰，然後如果不再需要程序集，請將其刪除。這是使用 SQLCLR 時的一種自然方法，因為您已經有一個程序集並且應該對其進行簽名（使用密碼 -.pfx文件代替.snk）。但是當不使用 SQLCLR 時，只需使用證書即可。

引用自：https://dba.stackexchange.com/questions/131005

相關問答

Sql-Server

SQL Server 允許儲存過程連結

  • March 1, 2022
檢視問答
Sql-Server

SQL Server - 通過證書向儲存過程添加簽名不起作用

  • January 28, 2022
檢視問答
Sql-Server

建議創建表或設計儲存過程以刪除 while 循環

  • October 21, 2021
檢視問答
Sql-Server

向Microsoft sql server中的所有使用者授予權限是否安全

  • March 5, 2021
檢視問答
Sql-Server

為過程引用的所有表生成 GRANT

  • April 23, 2020
檢視問答
Sql-Server

SQL 語句層過濾查詢限制訪問

  • February 10, 2020
檢視問答