Let's say I have a following table that lists all member of my application.
CREATE TABLE dbo.Member ( MemberId BIGINT, LoginName NVARCHAR(64), SecurityRole NVARCHAR(64) )
I assume that LoginName would be maintained in such a way that it will map to result of SYSTEM_USER function. Also, for the sake of example, I assume only 'Product' and 'Territory' values are possible for SecurityRole attribute and LoginName would be unique. I have also following tables:
CREATE TABLE dbo.MemberToProduct ( MemberId BIGINT, ProductId BIGINT ) CREATE TABLE dbo.MemberToTerritory ( MemberId BIGINT, TerritoryId BIGINT )
Those tables would handle relation of a given member to a product or territory given that they are assigned to 'Product' or 'Territory' security role.
Let's set up table that binds those tables together:
CREATE TABLE dbo.Contact ( ContactId BIGINT, ContactName NVARCHAR(64), TerritoryId BIGINT, ProductId BIGINT )
and a view, which is the core part of my question:
CREATE VIEW dbo.ContactView AS WITH CurrentMember AS ( SELECT TOP (1) MemberId, SecurityRole FROM dbo.Member WHERE LoginName = SYSTEM_USeR ) SELECT ContactId FROM dbo.Contact CROSS JOIN CurrentMember INNER JOIN dbo.MemberToProduct ON MemberToProduct.ProductId = Contact.ProductId AND MemberToProduct.MemberId = CurrentMember.MemberId WHERE CurrentMember.SecurityRole = 'Product' UNION ALL SELECT ContactId FROM dbo.Contact CROSS JOIN CurrentMember INNER JOIN dbo.MemberToTerritory ON MemberToTerritory.TerritoryId = Contact.TerritoryId AND MemberToTerritory.MemberId = CurrentMember.MemberId WHERE CurrentMember.SecurityRole = 'Territory'
This view will be used by users, who should have access to Contacts assigned either to Product or Territory they are allowed to see. This is to be decided by Member assignment to SecurityRole.
My question is: current design of a view is inefficient in my opinion as it needs to process data set that is not necessary (f.e. 'Territory' for 'Product' Member). For the sake of example I have simplified those tables but in reality I have a Contact table that have milions of rows and much more complicated security relations and doing an f.e. an union for each SecurityRole is very slow.
I have tried an approach based on creating a view that would call a stored procedure via OPENQUERY. This stored procedure would then have an IF statement and check for each security role and process Contacts separately for each security role. However this is not feasible to do, because if a view is then joined to another result set, the view returns all data from itself when in most situations it is not necessary.
I would like to know what are the best practices for this kind of problem and what are the options to have a view-like object that could return the same metadata but different data and have that advantage of stored procedure so it could decide what result set it is going to present.