2

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.

  • So all the other folks. Do they all have to have direct database access? Edit: What about providing an API or some kind of XML feed? – johnny Feb 15 '17 at 15:04
  • Yes, everyone has direct access. – Jerrad Feb 15 '17 at 15:05
  • I edited my question. – johnny Feb 15 '17 at 15:05
  • You mean allowing users to query the data via an API instead of direct database access? – Jerrad Feb 15 '17 at 15:06
  • 1
    Will each user have there own set of permissions, or can it be set up via groups/roles? – Jon Raynor Feb 15 '17 at 15:16
1
> My question is, is this a horrible way to go about this?

It is flexible and complicated and i assume that it violates the yagni and the kiss principles.

If you are able to cutdown to less than 10 different permission-roles i would "implement" this with static rolespecific database-views.

Example: the views personLaw personAnonymous personFinace map to table person for the roles Law Anonymous Finace

there is no need to implement a admin-gui to assign the permissions. all you need is a texteditor for database-view-scripts and the existing datebase-permission-gui

0

I would cut down on user administration as much as possible. If the number of reports is small and easily definable, I'd create a View. I personally wouuld rather deal with Views than lots of users. On the other hand, if I had to maintain 50 Views that changed at a whim, I might cave or see what else is going on.

If you know what they need and it requires direct database access to the tables, and that person absolutely must have a table, you have little choice (I do not know the politics involved.) Fix their account. It could be a mix.

I do not know your way is horrible. It looks like it may be unnecessary.

And API could be useful, XML or JSON. It depends on how much work it creates.

  • There are stock reports built into the app. But users also whip up their own reports to solve whatever their current issue it ("I need to know how many teenagers participated last year who also had a participating parent this year", or something like that). Those reports are not easily definable. Also, I'm trying to make this app as hands-off for myself (once it is finished) as possible. I want the supervisors to handle their own users, instead of having to go through me. Also, I don't think an API would work for querying via Excel or Crystal Reports. – Jerrad Feb 15 '17 at 15:29
  • Those programs can consume XML or JSON results, with some work. – johnny Feb 15 '17 at 18:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.