API and Database Access Control Architecture

Question

I am trying to implement access based control for a Survey app but having trouble designing a functional architecture. It seems like DAC would be a good fit for what I am trying to do, but am unsure.

Hierarchy

Survey Series -> Multiple Collection's of Survey's (1 survey that multiple people have to complete) -> Survey.

Examples

I have Survey Managers who need to assign collection creators. Collection creators assign users to a survey belonging to a collection. Each User can have different access controls based on each Series, Collection or Survey.

• User 1 can only view surveys assigned to him, but can also assign surveys belonging to a specific collection to other users since he was given access by another Collection Creator.

• User 2 can only view collections he has created and has been given access to and view the assigned surveys in each collection.

• User 3 can create series but cannot view any collections to users he did not assign as collection creators.

Summary The access should be hieracle. Every record (Series, Collection, Survey) would have its own set of access controls [Create, Read, View, Update] that users delegate. Could I get some help with designing a model?

Answer 1

Unfortunately, implementing a complex and hierarchical authorization model like this within a database is... not simple. Given the requirements presented (similar roles across multiple resource types), and the need for “inheritance” of those roles, I’ve outlined a possible model to store the roles:

Name	Kind	Description
user_id	Foreign Key	The user that has this role
role	Enum	The role
survey_id	FK or NULL	The survey to which the role applies
collection_id	FK or NULL	The collection to which the role applies
series_id	FK or NULL	The series to which the role applies

Each row in the table would contain a user, their role, and one of the {survey, collection, series} to which the role applies. This would allow your application to easily lookup the role (which are standardized) for the given resource you are checking. This is a pattern similar to Generic Foreign Key in Django. However, it still leaves one key problem: the heirarchy.

One option would be define a function in your code that knows which roles to lookup for each level of your heirarchy, so if it is given a survey, it looks up the roles for the user in all levels (survey, collection, series) and returns the role with the greatest access to check against. Otherwise, you'd likely need to make use of a series of joins to select all the roles for the user and the incoming resource, which could become very slow as your tables grow larger. This also only works if your roles remain the same at each level; otherwise, you'll find yourself quickly having to compare different enumerations of roles.

Hierarchical questions of these sorts appear in permissions checking often, and your unease is a recognition of that fact that its tough to map them in traditional relational databases: permission relationships are, at their core, a graph, and relational databases were not designed for easy traversal of graphs.

As a result of this complexity, there are services (this one is mine) that abstract away this complexity for you, so you can define your roles and relationships and not have to worry about the traversals. As a demonstration, I took the requirements you listed above and wrote an example in our playground, which might help you visualize and test these relationships.