I'm wanting to implement something that's basically an ACL in SQL Server (2008, we'll be moving to 2012). We're working with farm production information. We will have users who need to be authorized to view production records, but only at certain vertical levels in a hierarchy.
Our data hierarchy looks like this:
System - Farm - Group - Animal
The idea is that some users will have access at the System level, and can see records for all Farms, Groups, and Animals within that System. Likewise, some users will have permission starting at the Farm level, and need access only linked to that Farm and all Groups (and animals) within it.
Each table contains a primary key column, and a foreign key column linking it to the parent record (along with whatever other attributes each entity requires).
What I've implemented in the past is two-table system for linking users to the appropriate items they're allowed to see. Implemented here, it would look like this:
Table: Authorizations Table: FullAuthorizations Columns: Id (PK) Columns: Id (PK) UserId UserId ObjectId SystemId ObjectType FarmId GroupId AnimalId
The application inserts a record into Authorizations, with the user to authorize, the record id (System id, Farm id, etc), and the type of record (System, Farm, etc). The FullAuthorizations table is used to denormalize the farm hierarchy for easier/faster filtering of data. A trigger is used on the Authorizations table (and each of the farm, etc, tables) to update FullAuthorizations. I considered using a View here, in a previous project with additional levels of entities, and the performance was quite poor once we began getting several hundred thousand records.
The queries would look something like:
SELECT * FROM dbo.Animals a WHERE EXISTS ( SELECT 1 FROM dbo.FullAuthorizations fa WHERE fa.UserId = 1 AND fa.AnimalId = a.Id )
In the other project where we're doing this, the solution is performant, but feels like a bit of a hack, and I especially don't like that we can't maintain referential integrity on Authorizations with the associated objects. I'd appreciate feedback on some other possible solutions. I've been looking at things like Nested Sets, but not sure something like that fits this particular problem.