I recently had to make the stored procedure spPartyOrderAllocation_AllocateItems to select and update tables in other databases, while keeping the pemissions levels to the minimum.

This was necessary because an specific application Stock Allocation would connect to the databases and only run this stored procedure.

The way I go this done is:

1) create a login that will be used to run the stored procedure in different databases

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 the above login to the group of users that will run that procedure

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

3) create the user based on the login above (1) and grant all the permissions required

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) Create the stored procedure on database [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

this has been working very well as it is. the results and behaviour is as expected.

question:

1) How could I have done this differently and more effectively?

2) have you noticed that instead of writing the procedure with EXECUTE AS I had to add this line

   EXECUTE AS LOGIN='PartyOrderAlloc_HiddenLogin'

I know... I did not like it either, but it would not work with me using EXECUTE AS.

3) MAYBE if both my login and user were domain accounts, would this make a difference?

I don't want to add any database ownership chain, or trustworthy in order for this work, unless really necessary.

also:

we are still on sql-2005 on these test machines:

Microsoft SQL Server 2005 - 9.00.5000.00 (X64) Dec 10 2010 10:38:40 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.0 (Build 6002: Service Pack 2)

up vote 3 down vote accepted

1) How could I have done this differently and more effectively?

This can be handled better by using what I call "code-based" security, instead of regular "Login/User-based" security. The idea is to essentially grant permissions to one or more modules (Stored Procedures, Triggers, most types of Functions, etc) instead of to Logins or Users. Then you grant EXECUTE permission to the module(s) to whatever User(s) and/or Role(s) are appropriate.

This is a much cleaner approach as it is far more granular/controllable and does not require IMPERSONATE permissions, EXECUTE AS, Cross-Database Ownership Chaining, or TRUSTWORTHY ON. Your existing Logins / Users can only do the very specific things that are coded into those modules, whereas granting them IMPERSONATE opens the door for that permission to be used outside of this intended purpose. And this is also more secure since using EXECUTE AS can also be used outside of this intended purpose, and will still be in effect if someone were to change the definition of the Stored Procedure. In contrast, when you sign a module, the signature is generated from the current definition of that module, including the WITH EXECUTE AS clause of the CREATE statement. Meaning, any change to either the module definition or the principal_id specified for WITH EXECUTE AS will invalidate the signature and the signature will thus be automatically dropped, resulting in the implied permissions no longer being granted.

You do this by creating either an Asymmetric Key or Certificate** , then create a Login and/or Users, depending on the need, based on that Key or Certificate, and then use that same Key or Certificate to sign the modules that should be able to do things that the users logging in shouldn't be able to do. Finally, you assign the desired permission(s) to the Login and/or User(s) as appropriate.

I have provided several examples and explanation across the following answers:

2) have you noticed that instead of writing the procedure with EXECUTE AS I had to add this line

This is because the EXECUTE AS clause of the CREATE PROCEDURE, CREATE FUNCTION, etc statements works on Users only, which are local to each DB. Logins are server-level. This will become irrelevant upon implementing what I am suggesting in #1.

3) MAYBE if both my login and user were domain accounts, would this make a difference?

That shouldn't make a difference. But again, this will become irrelevant upon implementing what I am suggesting in #1.

I don't want to add any database ownership chain, or trustworthy in order for this work, unless really necessary.

Neither of these are necessary when using signature-based / code-based security as suggested in #1.


** The main difference between Certificate (which contains a key) and Asymmetric Key is how easy is it to duplicate that key into other databases. If the need is very localized to a single database then an Asymmetric Key is fine. But if you need to manage this across multiple databases, or need a server-level permission, then it is usually easier to use a Certificate since they allow for backing up their key info into a file that can then be used to re-create that same Certificate in other databases, even on other instances. And starting in SQL Server 2012, you can extract the hex bytes that can then be used to recreate them. For Asymmetric Keys, the only way that I have found to create them with a consistent key across all databases is to embed that key info into an Assembly, then load that Assembly into a database, create the Asymmetric Key from the Assembly, and then if the Assembly is no longer needed, drop it. This is a natural approach when working with SQLCLR since you already have an Assembly and it should be signed (with a password -- .pfx file instead .snk). But when not working with SQLCLR, then just go with a Certificate.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.