In our organization we have been using MDM feature of MS SQL 2012, to maintain product data in central location. and all other system subscribe to this information.
This product (same entity) belongs to two different business unit with in our organization and we had to implement security model to achieve this.
In MDM 2012 there is out of box functionality for this. the closest feature that come close is Hierarchy segmentation.
Process starts with Declaring Explicit hierarchy for the entity in scope.
and than level entities are assigned to this Hierarchy, There is another way of creating hierarchy which is derived hierarchy and I am sure that would potentially work the same way.
once this assignment is completed next step is to assign permission on this Hierarchy rather than the entity itself.
In my experience I noticed that permission on entity does not readily get applied to leaf level. Sometimes there is small lag while other it does not even get applied at all.
The work around that to get implemented is running following code
DECLARE @Model_ID INT;
SELECT @Model_ID = ID FROM mdm.tblModel WHERE [Name] = N'ModelName';
EXEC [mdm].[udpSecurityMemberProcessRebuildModel] @Model_ID=@Model_ID, @ProcessNow=1;
GO
