I am redesigning an existing SQL Server-backed web application. This application is used for tracking subjects (names, addresses, phone numbers, etc…) and related data (assessment information, court information, school information, etc…). One of the requirements is that certain users should not be allowed to see certain columns of data. The most common example of this is an Intervention/Control column (users collecting data should not be able to see which group a subject belongs to).

I want to enable supervisors to manage these column-level permissions for their own users. To that end, I am considering a data schema where I have a Permission table that would have a row for every user/column combination, and whether they are allowed to view that column.

+----------+-----------+------------+---------+
| Username | TableName | ColumnName | CanRead |
+----------+-----------+------------+---------+
| lukes    | Person    | Name       |       1 |
| lukes    | Person    | Email      |       0 |
| lukes    | Person    | Phone      |       1 |
+----------+-----------+------------+---------+

Users would not have direct access to the tables, they would only have access to views. I could create a Permission view like this, that tells me which columns the user can see for every table:

create view viewPermission as
select x.tablename, x.name canReadName, x.email canReadEmail, x.phone canReadPhone
    from (
        select username, tablename, columnname, cast(canread as tinyint) canread
        from Permission
        where username = SYSTEM_USER        
    ) p
    pivot(
        max(canread)
        for columnname in ([name],[email],[phone])
    ) x

Then in the view for the Person table (for example):

create view viewPerson as
select case when p2.canreadName = 1 then t.name else '<not allowed>' end name,
case when p2.canreademail = 1 then t.email else '<not allowed>' end email,
case when p2.canreadPhone = 1 then t.phone else '<not allowed>' end phone
from Person t
cross join viewPermission p2
where p2.tablename = 'Person'

This works in my initial tests. My question is, is this a horrible way to go about this? What are the alternatives? I know I could handle column visibility inside my app, but users also have access directly to the database for creating ad-hoc queries in Excel and Crystal. I don’t feel like incorporating these permissions checks into the views should degrade performance too terribly, but I’m not sure.