Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Can you not use SQL Server's built-in users and groups? –  Neil McGuigan Oct 28 '13 at 1:56
    
I don't think this would work well, as we still have to maintain user and group info in the website; we're using ASP.net membership for user management, roles, profiles, etc. I'd prefer to not have to manage users on two platforms and keep them in sync. –  Michael R Oct 30 '13 at 13:47
add comment

1 Answer

A long time ago, in 2000, I had a similar question, and posted on Google Groups. I received an awesome response from a guy called Joe Celko, and it will fit right into your problem. I've been using this methodology ever since, and it has great performance, and works really well

https://groups.google.com/forum/#!search/pascaljr/microsoft.public.sqlserver.programming/knQ5WXIDfeo/E0vfeC2kbTsJ

It uses two integer values, a left and a right value, to store the hierarchy. I've made some adaption to the table and procs over the years, but the essence remains the same.

share|improve this answer
    
Joe Celko describes the visitation number approach to managing hierarchical data as nested sets in his book SQL for Smarties which is an excellent resource. –  Joel Brown Jul 1 '13 at 11:40
    
@JoelBrown: With modern DBMS the nested set solution can easily be replaced using a recursive query (common table expression) and an adjacency list model (which is much easier to maintain/update) –  a_horse_with_no_name Jan 26 at 10:44
    
@a_horse_with_no_name - True enough, although I think there is room for discussion about how easily anything can be done with CTEs and there's nothing difficult about maintaining nested sets. I think adjacency list vs. visitation number is really more a matter of personal preference. I expect one could argue endlessly about their relative merits, which they surely have, but in the end it will come down to one's particular situation or priorities. –  Joel Brown Jan 26 at 13:52
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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