0

I have situation where I need to create a view which will be used by end user who may have PII and NON-PII permission clearance controlled by AD groups.

Say i have dim_customer which contains four columns ID, Name, DoB, Country. When PII user runs 

Select ID, Name, DoB, Country FROM dim_customer

the pii user should get 

ID  NAME  DoB         Country
1   John  1999-10-10  US

If the same query is run by NON-PII user then they should get

ID  NAME     DoB         Country
1   PII DATA PII DATA    US

So basically same view object is used but data is displayed according to the pii clearance.

  1. I dont want to create two views with pii and non-pii suffix.
  2. I tried column level permission but that means when end user try the above query they get error "no select permission on Name and DoB Columns"
  3. I have tried Data Masking but that shows "XXXX" and i would prefer "PII Data".

I am looking for a solution where the query runs successfully and show results as above.

Is this possible in SQL Server ?

thanks in advance

Anbu Dhan
  • 73
  • 2
  • 10
  • It is possible to GRANT or DENY access at a column level, and you should probably implement this if security is paramount. But this won't achieve the masking you want - users will have to avoid selecting columns they are not entitled to see – Stewart Ritchie Jun 15 '20 at 11:21
  • You mentioned you've tried Data Masking and it shows "XXXX". You can specify a custom string like "PII DATA". – Dan Guzman Jun 15 '20 at 11:33
  • @DanGuzman you should post that as an actual answer. It seems better than other answers to the question. – julealgon Jan 12 '23 at 20:58

1 Answers1

0

Create two roles, one that allows PII data, and one that doesn't. Put each user into one role or the other. Than write your view

Select 
    ID, 
    Name, 
    CASE 
       WHEN IS_ROLEMEMBER ('pii_role') = 1 THEN DoB
       ELSE 'PII Data' 
    END as DoB, 
    Country 
FROM dim_customer
Robert Sievers
  • 1,277
  • 10
  • 15