MySQL how to store data + access permissions performance-wise

Question

I've recently came across a problem for which I can only think of muddy solutions.

Imagine a table has several objects (lets say photos to make it easier). Every single photo/object must have it's own set of permissions.

1 - photo must always be seen by uploader
2 - photo belongs to a group of photos and all users that have access to that group can see it
3 - photo can be shut-out and made private
4 - photo can be "authorized" user-specific
5 - photo can be "denied" user-specific

Both the users and the groups of photos will grow to a moderate size.

I'm not looking for a SQL-detailed query here. Just hoping that someone could point me in the right direction.

Answer 1

I'd like to note I do not have much experience with optimizing SQl at this point, so it's possible my answer would impose too much load on the server.

I would opt for a table, say photo_permission who would contain a mapping between the user_id's and the authorized photo_id's for each user id. Something like:

| user_id | photo_id |
| 1       | 1        |
| 1       | 3        |
| 2       | 1        |
| 2       | 2        |

If a record exists for a certain user with id user_id and a certain photo with id photo_id, the user has permission to view the photo.

If you cannot block the photo from a user who has access to the group containing the photo, you could even simplify the above table

| authority_id | authority_type | photo_id |
| 1            | group          | 1        |
| 1            | user           | 3        |
| 2            | user           | 1        |
| 2            | group          | 2        |

Now you can check whether the user's id is listed on rows with authority_type user OR check whether his id is IN (SELECT id FROM user where group_id=authority_id) on rows with authority_type group. Note that the above code is not tested.