December 22, 2016 at 9:33 am
I have a situation where I need to give a login a restricted view of data in all tables in a database. Every table includes a "MemberID" column. What I need is a login that has full select access to all of the data in every table and then another login that has select access to a "restricted" view of the data based on a list of approved "MemberID's".
The other requirement is that the same query needs to work regardless of the login. So if USERA (who has full permission) does a select against TABLEA they will get all of the records returned - if USERB runs the exact same query they will only get back the records that match the "MemberID" list that they are allowed to access.
My thought was to create a schema and then create a view with the same name as each table that includes the necessary WHERE clause to limit the data returned. This works in theory but I seem to be having a problem with the Permission Chaining. I can't seem to find the right combination to Create the User, Create the Schema, Create the View and then have the logged in user be able to select against the view but not the underlying table directly.
December 22, 2016 at 9:53 am
Once you've got the views created, the rest is easy. All you need to do is grant SELECT on the views to all users, and revoke SELECT on the underlying tables.
John
December 22, 2016 at 10:03 am
I think what is making this a bit trickier is that I want to be able to use ONE query for both users.
So for example:
Table A has a column MEMBERID and another column "DATAVALUE"
MEMBERID's are 1,2,3,4,5
I want USERA to be able to "SELECT MEMBERID, DATAVALUE FROM TableA" and get back 5 records.
I want USERB to be able to "SELECT MEMBERID, DATAVALUE FROM TableA" and get back 2 records. This is because they only have permission to view data for MEMBERIDs 1 and 2.
This is why I was thinking that a view created as SCHEMAB.TableA with a select that includes the WHERE clause MEMBERID in (1,2) would work. Since USERB has access to the SCHEMAB schema and not the dbo schema they would get the SCHEMAB.DOCUMENT view instead of the dbo.DOCUMENT table.
December 22, 2016 at 11:21 am
Would a set of stored procedures to retrieve the data from the underlying tables be acceptable? Then you could have everyone call the 'same query' so to speak, but the SP can handle special processing and filtering on the underlying table structures.
December 23, 2016 at 3:08 am
MFlanagan 91357 (12/22/2016)
This is why I was thinking that a view created as SCHEMAB.TableA with a select that includes the WHERE clause MEMBERID in (1,2) would work. Since USERB has access to the SCHEMAB schema and not the dbo schema they would get the SCHEMAB.DOCUMENT view instead of the dbo.DOCUMENT table.
No, please don't do it like that! Not only would confusion reign, but you'd also need to abandon the best practice of qualifying object names with schemas. And if someone came along and changed someone's default schema, you'd be getting wrong results without necessarily knowing. Instead, how about creating a view something like this:WITH MyData AS (
SELECT
t.MEMBERID AS MEMBERID
,a.MEMBERID AS Approved
,DATAVALUE
FROM TableA t
LEFT JOIN ApprovedList a ON t.MEMBERID = a.MEMBERID
)
SELECT
MEMBERID
,DATAVALUE
FROM MyData
WHERE SUSER_SNAME() = 'USERA'
OR (SUSER_SNAME() = 'USERB' AND Approved IS NOT NULL);
John
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply