1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.