What's a good way to model user authorization to hierarchical data?

Question

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.

Answer 1

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.

Answer 2

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...

Answer 3

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
ADD CONSTRAINT PK_entity PRIMARY KEY (entity_id);

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 )
ON DELETE CASCADE ON UPDATE CASCADE;

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 )
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE dbo.authorisation
ADD CONSTRAINT FK_authorisation_user FOREIGN KEY ( user_id )
REFERENCES dbo.user ( user_id )
ON DELETE CASCADE ON UPDATE CASCADE;

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

SELECT *
FROM dbo.animal
WHERE EXISTS (
    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'