In SQL Server 2016, can we configure MASKing rules that applies for application level users ?

Think that you need a database logic layer to first determine the user's role/permission (like: User 'X' , User 'Y', etc) and then from there, return the data whether it should be masked or unmasked.

I would do that in a Stored Procedure to do the verification and return data accordingly.

'AppUserA' and 'AppUserB' to see UNMASKed data as database user 'X' can see
Other users should see only MASKed data as database user 'Y' can see

You need to have a mapping methodology at your database end, perhaps a mapping table, etc to determine the user's role/permission.

Thanks Ryan

Do you mean IMPERSONATE to the respective database user inside Stored Procedure and return appropriate resultset ?

EXECUTE AS USER = 'UserX' -- Impersonate AS TestUser...
SELECT * FROM Employee -- the data is masked!!
REVERT

Select allOpen in new window

'

Raj

I have another related question, could you please help me on that as well ?
https://www.experts-exchange.com/questions/28994736/How-to-set-UNMASK-permission-only-to-only-one-table-in-SQL-Server-2016.html

What are you using to mask and unmask the data?

Thanks for your comment Vitor. I was sick and just back. Will reply soon

Hi Rajkumar Gs,

Please use below. You just have to provide the access(UNMASK) to the user whom you want to. By default it will be masked for remaining.

--

GRANT SELECT ON YourTableName TO AppUserA
GO
GRANT UNMASK TO AppUserA
GO

GRANT SELECT ON YourTableName TO AppUserB
GO
GRANT UNMASK TO AppUserB
GO

--

Select allOpen in new window

Hope it helps!

Hi Vitor

What are you using to mask and unmask the data?

To do masking, I am using the Dynamic Data Masking feature of SQL2016

Raj

Hi Pawan & Vitor

I guess my question is not clear to you.

For a database user, the query may work.
But I am wondering how the users in application, whose user information would be in database table like 'Users' , some can view only masked data and some others can view real data ?

Since Dynamic data masking applies to database user, how this feature can be applied to application level users ?

Hope you understand what I am looking for

Thanks
Raj

Thanks Vitor.
Please explain a little bit more on the above query and on implementing it in my asp.net web application.

For eg:- I have logged into my application as 'AppUserA' and trying to view the full data and on another instance 'AppUserB' logs in and tries to view same data, but it would be masked data.

Regards
Raj

Thanks Vitor