Granular Database Record Access Rights (e.g. Group "X" & Individual "Smith" can view Record Z)

Question

I have records (Contacts, Addresses etc.) which should be accessible to any of the following (incl. combinations, e.g. 2 groups and 4 individuals):

• Everybody
• Members of multiple Groups/Departments
• Members of a single Group/Department
• Multiple Individuals
• Single Individual

What is a good database structure to realize this? Basically in my application I need to be able to restrict when user XYZ is logged in to only show him the records that are "viewable" to him as an individual, member of a group, or because they are visible to everybody.

Basically I'm looking for a way to efficiently store these relationships. It should be fast as I would look at the relationships every time anybody looks at anything.

I'd really appreciate some hints on how to do this!

Thank you!

Edit: I'm using SQL Server 2008 Web Edition.

Answer 1

This is lurching in the direction of RBAC - role-based access control. You might also wonder about whether to use LBAC - label-based access control. And, depending on your DBMS, there could be other ways to achieve it (consider Oracle VPD - virtual private database - for example). All of this is either rather or very DBMS specific - different solutions for different DBMS.

You seem to be talking about control at the row level. That is, one row in the contact table may be accessible to everybody, while another is accessible only to one set of departments, another is only accessible to one group of people, and so on.

Remember that relational DBMS work best with sets. A single group is a set of groups with one member group; a single user is a set of groups with one member user. This means we have fewer cases to deal with.

If you want to implement it in Standard SQL, then I think you are going to need to use a combination of views exploiting joins with control tables, etc. The hard parts with such a system are populating the control tables and restraining the administrative users (actually, restraining administrators is always one of the hard parts).

The basic technique would be:

• Create the base table with an appropriate column to identify the privilege set that applies to each row in the table.
• Revoke all public access to the table.
• Create a view on the base table which shows all the columns from the base table that are permitted. It will be a join view with a control table, to be defined momentarily. The view query conditions will also be conditioned by the current user.
• Grant appropriate access to the view.
• Create appropriate INSTEAD OF triggers on the view to handle insert, delete and update operations on the view, relaying the changes to the base table.
• Create the control table to join with the base table.
• Populate it with appropriate data.
• Light blue touch paper and stand well back.

Now, about that joining column and the control table...

Someone has to specify what permissions apply to newly inserted rows in the table - what is the default access provided. And someone has to define how the default access can be overridden. Both of those could be messy.

There are several ways to structure the control table:

1. One mechanism relies on a each row in the base table having a unique ID (which might be an automatic generated ID or just the value of the primary key). The control table then includes a copy of that unique ID, and defines which users or groups can access it. This means that there may be multiple entries in the control table for a given row, one for each user or group that can access the row. In this scheme, the control table has a foreign key that references the base table.

2. Another mechanism embeds an ID number into the base table that is a foreign key to the control table(s). It basically identifies a set of privileges, and the reference in the base table means that the row has the access permission associated with the access control ID. The structure behind the control table might be that the ID 0 has no access for anyone (via the view), ID 1 has access for everyone, and other values designate combinations of users and groups - each different combination has a different ID. With this, there might be several tables in the set of control tables - and we're also discussing having a set of these control tables for each table that is protected.

Clearly, access to the control tables is strictly limited - but also crucial to the management of who can see what.

Both of these are administrative nightmares - which is why you probably end up with a DBMS-provided access control mechanism rather than a generic SQL solution.

Answer 2

I agree with Jonathon about the technique but not necessarily about the nightmare. I've implemented this with a single view of a union of rights based on:

• who created the base record
• the business unit indicated on the base record
• the ad hoc user group indicated on the base record
• the internal department indicated on the base record
• ad hoc grants to individual users
• administrative roles

Performance was fine, believe it or not, although the base table was never larger than about 250K records... obviously, a larger base table might require a more elaborate design. But in our case, it worked well and administration was not a big deal at all. The created-by and ad hoc user group assignment were the only rules that actually got used on any sort of wide scale. Assigning / revoking access to groups was an ongoing task, but one which comes with the territory.