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)
Best Answer
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
EXECUTEpermission 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
IMPERSONATEpermissions,EXECUTE AS, Cross-Database Ownership Chaining, orTRUSTWORTHY ON. Your existing Logins / Users can only do the very specific things that are coded into those modules, whereas granting themIMPERSONATEopens the door for that permission to be used outside of this intended purpose. And this is also more secure since usingEXECUTE AScan 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 theWITH EXECUTE ASclause of theCREATEstatement. Meaning, any change to either the module definition or theprincipal_idspecified forWITH EXECUTE ASwill 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:
This is because the
EXECUTE ASclause of theCREATE 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.That shouldn't make a difference. But again, this will become irrelevant upon implementing what I am suggesting in #1.
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 --
.pfxfile instead.snk). But when not working with SQLCLR, then just go with a Certificate.