CODEWITHSUNDEEP
sql-serversql-viewdmldata-maskingpii
Anbu Dhan
Anbu Dhan

Reputation: 73

SQL Server view for PII and Non PII end user

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, and 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 don't 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 the following error:

no select permission on Name and DoB Columns

  1. 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.

Upvotes: 0

Views: 595

Answers (1)

Robert Sievers
Robert Sievers

Reputation: 1353

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

Upvotes: 0

Related Questions