Granular permissions on a database entry (for example, group "X" and "Smith" can view entry Z)

Question

I have records (Contacts, Addresses, etc.) that should be available to any of the following ( including combinations , for example, 2 groups and 4 people):

• Everything
• Members of several groups / departments
• Members of one group / department
• Few people
• Single person

What is a good database structure to implement this? Basically, in my application, I should be able to restrict when an XYZ user logs in to show him records that are “visible” to him as individuals, group members, or because they are visible to everyone.

Basically, I am looking for a way to effectively keep this relationship. This should be quick, as I look at relationships every time someone looks at something.

I am very grateful for the tips on how to do this!

Thanks!

Change I am using SQL Server 2008 Web Edition.

Answer 1

This deviates toward RBAC, role-based access control. You may also wonder whether to use tag-based LBAC access control. And, depending on your DBMS, there may be other ways to achieve it (for example, Oracle VPD is a virtual private database). All this is either enough or very specific to the DBMS - different solutions for different DBMSs.

You seem to be talking about row level management. That is, one row in the contact table may be available to everyone, and the other is available only for one set of departments, the other is available only for one group of people, etc.

Remember that a relational DBMS works best with collections. One group is a set of groups with one group of participants; one user is a set of groups with one member user. This means that we have less work to do.

If you want to implement it in standard SQL, I think you will need to use a combination of views that use joins with control tables, etc. Hard parts with such a system populate management tables and are held back by administrative users (in fact, restraining administrators are always one of the hard parts).

The main method:

• Create a base table with an appropriate column to determine the privilege set that applies to each row in the table.
• Unshare the table.
• Create a view in the base table that displays all the columns from the base table that are allowed. This will be a view of the connection to the checklist, which will be determined instantly. View request conditions will also be subject to the current user.
• Grant appropriate viewing access.
• Create appropriate INSTEAD OF triggers in the view to handle insert, delete, and update operations in the view, pushing the changes to the base table.
• Create a management table to join the base table.
• Fill it with the appropriate data.
• Light blue paper and fit well.

Now that the connecting column and the management table ...

Someone should indicate what permissions apply to newly inserted rows in a table - what is default access. And someone must determine how the default access can be overridden. Both of them can be dirty.

There are several ways to structure a management table:

• One mechanism relies on each row in the base table that has a unique identifier (which may be an automatic generated identifier or only a primary key value). The management table then includes a copy of this unique identifier and determines which users or groups can access it. This means that there can be several records in a management table for a given row, one for each user or group that can access the row. In this diagram, the management table has a foreign key that refers to the base table.

• Another mechanism includes an identification number in the base table, which is the foreign key for the management table (s). It basically identifies a set of privileges, and a link in the base table means that the row has access permission associated with the access control identifier. The structure of the management table may consist in the fact that identifier 0 does not have access to anyone (through the presentation), identifier 1 has access to everyone, and other values ​​indicate combinations of users and groups - each other combination has a different identifier. In this case, there can be several tables in the set of control tables, and we also discuss the availability of a set of these control tables for each protected table.

Obviously, access to control tables is strictly limited, but also crucial for managing those who can see that.

Both of these are administrative nightmares - which is why you are likely to end up with an access control mechanism based on the DBMS rather than a general SQL solution.

Answer 2

I agree with Jonathon regarding technology, but not necessarily a nightmare. I implemented this with a unified view of the union of rights based on:

• who created the base record
• business unit indicated on base record
• special user group pointed to the base record
• internal department specified in the base record
• special discounts for individual users
• administrative roles

The performance was excellent, believe it or not, although the base table has never been more than about 250 thousand records ... it is obvious that a larger base table may require a more complex design. But in our case, it worked, and the administration was not a big problem. The designation of the "created" and "ad hoc" groups were the only rules that were actually used at any scale. Assigning / revoking access to groups is an ongoing task, but one that comes with the territory.