How to restrict users to see only records related to their client where in table mulitple clients data stored AKA Row Level Security

Last Post 24 Aug 2011 08:06 AM by Scott Rouse. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages Not Resolved
Imran Khan
New Member
New Member

--
22 Aug 2011 02:45 PM
Hi,

I have requirement to implement row level security on database tables in sql server 2008 R2 where data from different clients is stored and I want to restrict all client users to access only data related to their respected clients. Can you please suggest different scenarios to implement this requirement as using sql server roles, logins and users I could not implement row level security.

Tried to use SUSER_SNAME(), CURRENT_USER() to workout the solution but did not work.

Can you please advise how to sort out this requirement.

Many Thanks.
rm
New Member
New Member

--
23 Aug 2011 05:36 AM
One option, create view for each client which contains rows for that client only.
Scott Rouse
New Member
New Member

--
24 Aug 2011 08:03 AM
I have a solution that uses roles, views and the is_member () function.

Add a column to your data tables, required_role, that contains the required role or is NULL for open access.

Create a view selecting rows from the data table “WHERE (isnull(is_member(required_role),(1))) = 1”
This is a somewhat limited solution, but it works for me.

Proof of concept example:


CREATE TABLE #test(
    tid NUMERIC IDENTITY,
    text1    VARCHAR(50),
    required_role    VARCHAR(50) NULL
    )
go
    INSERT INTO #test(text1, required_role )
    VALUES  ( 'Text viewable by public role', 'public')
    INSERT INTO #test(text1, required_role )
    VALUES  ( 'Text for db_owner','db_owner')
    INSERT INTO #test(text1)
    VALUES  ( 'Text for anyone' )
       
    --EXECUTE AS USER = 'NONdboUser' -- test using a user not in the db_owner role   
    
     -- source for view  
     SELECT USER_NAME(),* FROM #test
        WHERE (isnull(is_member(required_role),(1))) = 1             
        
    --REVERT;  -- back to previous user

 
Scott Rouse
New Member
New Member

--
24 Aug 2011 08:06 AM


Proof of concept example, clear text

CREATE TABLE #test(
tid NUMERIC IDENTITY,
text1 VARCHAR(50),
required_role VARCHAR(50) NULL
)
go
INSERT INTO #test(text1, required_role )
VALUES ( 'Text viewable by public role', 'public')
INSERT INTO #test(text1, required_role )
VALUES ( 'Text for db_owner','db_owner')
INSERT INTO #test(text1)
VALUES ( 'Text for anyone' )

--EXECUTE AS USER = 'NONdboUser' -- test using a user not in the db_owner role

-- source for view
SELECT USER_NAME(),* FROM #test
WHERE (isnull(is_member(required_role),(1))) = 1

You are not authorized to post a reply.

Acceptable Use Policy