How can I control access through an SQL table?

Question

I am trying to create an access control system.

Here is an example of how the table I'm trying to control access to looks like this:

things table: id group_id name 1 1 thing 1 2 1 thing 2 3 1 thing 3 4 1 thing 4 5 2 thing 5 

And the access control table looks like this:

 access table: user_id type object_id access 1 group 1 50 1 thing 1 10 1 thing 2 100 

Access can be granted either by specifying the identifier of the “thing” directly, or granted to the entire group of things by specifying the identifier of the group. In the above example, user 1 was granted access level 50 to group 1, which should be applied if there are no other rules that provide more specific access to a single thing.

I need a query that returns a list of things (ids only in order) along with the access level for a specific user. Therefore, using the example above, I would like something similar for user id 1:

 desired result: thing_id access 1 10 2 100 3 50 (things 3 and 4 have no specific access rule, 4 50 so this '50' is from the group rule) 5 (thing 5 has no rules at all, so although I still want it in the output, there no access level for it) 

Closest I can come up with the following:

 SELECT * FROM things LEFT JOIN access ON user_id = 1 AND ( (access.type = 'group' AND access.object_id = things.group_id) OR (access.type = 'thing' AND access.object_id = things.id) ) 

But this returns multiple rows when I want only one for each row in the “stuff” table. I’m not sure how to go to one line for each thing, or how to prioritize the rules of the “thing” over the rules of the “group”.

If that helps, the database I'm using is PostgreSQL.

Please feel free to leave a comment if there is any information that I missed.

Thanks in advance!

Answer 1

I don't know the Postgres SQL dialogs, but maybe something like:

 select thing.*, coalesce ( ( select access from access where userid = 1 and type = 'thing' and object_id = thing.id ), ( select access from access where userid = 1 and type = 'group' and object_id = thing.group_id ) ) from things 

By the way, I do not like the design. I would prefer the access table to be divided into two parts:

 thing_access (user_id, thing_id, access) group_access (user_id, group_id, access) 

Then my query will look like this:

 select thing.*, coalesce ( ( select access from thing_access where userid = 1 and thing_id = thing.id ), ( select access from group_access where userid = 1 and group_id = thing.group_id ) ) from things 

I prefer this because now you can use foreign keys in access tables.

Answer 2

I just read an article last night about this. It has some ideas on how to do this. If you cannot use the title link, try using Google Scholar to Limit disclosure in Hippocratic databases.

Answer 3

While there are some good answers, something like this might be most effective:

 SELECT things.id, things.group_id, things.name, max(access) FROM things LEFT JOIN access ON user_id = 1 AND ( (access.type = 'group' AND access.object_id = things.group_id) OR (access.type = 'thing' AND access.object_id = things.id) ) group by things.id, things.group_id, things.name 

That just uses the generalization added to you to get what you are looking for.

Answer 4

Tony:

Nice solution, I like it, it seems to work. Here is your request after a minor tweak:

 SELECT things.*, coalesce ( ( SELECT access FROM access WHERE user_id = 1 AND type = 'thing' AND object_id = things.id ), ( SELECT access FROM access WHERE user_id = 1 AND type = 'group' AND object_id = things.group_id ) ) AS access FROM things; 

And the results look right:

  id | group_id | name | access ----+----------+---------+-------- 1 | 1 | thing 1 | 10 2 | 1 | thing 2 | 100 3 | 1 | thing 3 | 50 4 | 1 | thing 4 | 50 5 | 2 | thing 5 | 

I fully understand that this is not an ideal scheme. However, I am somewhat stuck with this.

---

Josef:

Your decision is very similar to what I was playing with, and my instincts (such as them) tell me that it can be done like this. Unfortunately, it does not give absolutely correct results:

  id | group_id | name | max ----+----------+---------+----- 1 | 1 | thing 1 | 50 2 | 1 | thing 2 | 100 3 | 1 | thing 3 | 50 4 | 1 | thing 4 | 50 5 | 2 | thing 5 | 

The access level for “thing 1” got a higher group access value, and not the more specific “thing” value 10, and this is what I need. I do not think there is a way to fix this in GROUP BY , but if anyone has any suggestions, I am more than happy to be wrong at that time.