Sign up ×
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:

- 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

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:

FROM dbo.Animals a
    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 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

3 Answers 3

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!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 '14 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 '14 at 13:52

Is hierarchyid and possibly dynamicSQL an option?

The idea is both users and tables are assigned with corresponding hierchyid. User's hierarchyid represents the maximum level of authorization, and the table side to be the minimum required.

You can easily put in a check / comparison routine and produce appropriate error message if the user hierchyid doesn't qualify for the targeted table, and proceed if qualifies.

For example, user at /1/ can access all, but those at /1/1/ or /1/3/ can access only Farm level and below. Likewise 1/1/1 and such can access Group and below.

The benefit I see in this is that you can further restrict access within the same level, such as 1/1/1 can access all Group level tables, but 1/1/10 can access only lImited number of tables, of course if that's important to your structure...

share|improve this answer

I believe this could be done more simply by using globally unique identities for all entities. Avoiding any need to manage heirarchies while maintaining referential integrity between authorisation and entities.

Create a table for globally unique id's of all entities

CREATE TABLE dbo.entity ( 
    entity_id INT IDENTITY(1,1) NOT NULL,
    entity_type VARCHAR(20) NOT NULL,
    /* ... */

ALTER TABLE dbo.entity

Have all entities reference this table

CREATE TABLE dbo.animal ( 
    animal_id INT NOT NULL, 
    /* ... */ 

ALTER TABLE dbo.animal
ADD CONSTRAINT PK_animal PRIMARY KEY ( animal_id );

ALTER TABLE dbo.entity
ADD CONSTRAINT FK_animal_entity FOREIGN KEY ( animal_id )
REFERENCES dbo.entity ( entity_id )

Reference the entity (and user) table from your authorisation table

CREATE TABLE dbo.authorisation ( 
    authorisation_id INT IDENTITY(1,1) NOT NULL, 
    entity_id INT NOT NULL, 
    user_id INT NOT NULL, 
    /* ... */ 

ALTER TABLE dbo.authorisation
ADD CONSTRAINT PK_animal PRIMARY KEY ( animal_id );

ALTER TABLE dbo.authorisation
ADD CONSTRAINT FK_authorisation_entity FOREIGN KEY ( entity_id )
REFERENCES dbo.entity ( entity_id )

ALTER TABLE dbo.authorisation
ADD CONSTRAINT FK_authorisation_user FOREIGN KEY ( user_id )
REFERENCES dbo.user ( user_id )

You then simply apply the authorisation much as you have been doing

FROM dbo.animal
    SELECT 1
    FROM dbo.authorisation
    WHERE authorisation.user_id = @user_id 
    AND auth.entity_id = animal.animal_id

Obviously there's the pain point of 'getting' the identity when inserting

DECLARE @entity TABLE ( entity_id INT )

INSERT dbo.entity (entity_type)
OUTPUT inserted.entity_id into @entity (entity_id)
VALUES ('animal')

INSERT INTO dbo.animal ( animal_id, /*...*/)
SELECT TOP 1 id, /* ... */
FROM @entity

But this seems more simple then managing hierarchies. It could also be redundant if you chose to use GUIDs rather than an 'entity id' table.

n.b. I have used the term 'entity' as what you have refered to as an 'object'

share|improve this answer

Your Answer


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.