How to store permissions in a database?

Question

SQL

Suppose there are data units "content" and there are users "user".
Users can create content and set access rights for other users according to several rules:
- available to everyone
- available to friends
- available to a specific friend / list of friends
The rules are added through "or". Those. available if available by at least one of the rules.
content [id, user_id, name]
user [id, name]
user_friend [user_id, friend_user_id]
How to organize the storage of such rules in the database so that it is possible to compose a non-fulscan query to select all (via pagination) "content" available to a particular user?
The simplest normalized storage scheme:

content_share_wide [content_id, type] // всем или друзьям, где type = 1 - всем, type = 2 - друзьям

content_share_user [content_id, user_id] // контент, расшаренный на user_id

With such a scheme, it turns out to be impossible to compose a query except with the help of UNION, which has a very negative effect on performance.
How to do better? (how to do it at all, because I don’t want to consider such a union as a working option)

Answer 1

select count(content_id) from content_share where
user_id=-1 /* friends */ 
or
user_id=0 /* anyone */ 
or
user_id=?

Answer 2

I will add that if you apply your hint and glue the share tables into one according to the proposed method, it will turn out like this:

select c.* from content c
join content_share cs on cs.content_id = c.id
where cs.user_id = 0 or cs.user_id = ? /* anyone or me */

union

select c.* from content c
join content_share cs on cs.content_id = c.id
join user_friend uf on c.user_id = uf.user_id
where cs.user_id = -1  /* friends */
  and uf.friend_user_id = ?

Answer 3

'Available to all' without options should be stored as a bolean in content, even if only as a copy filled by a trigger in the content_share table.
'Shared with friends' and 'Shared with a specific user'... is it really that important to separate these concepts. this would make sense if the number of actions to create a new user and add rights would be comparable to the number of requests for access rights, and this is unlikely, for sure in your task the number of read requests is an order of magnitude (or usually it is a logarithm) more changes.
The rule 'Available to a specific user' may be enough, which means you will get by with the table content_share_user {user_id,content_id}
Further, one should never rely on a pure relational model. Make an extra copy of everything that is read more than written in a convenient place for this. A serialized list of user_id identifiers in content.authorised_list (if they are numbers, then for example through ',' with a mandatory ',' at the end), if their number is less than a certain one, it is convenient for requests like '%12345,%', and after all, it can be filled not immediately, but periodically by a separate process and cleared by a trigger on a change. Then the main load will fall not on the execution of the trigger, but on requests only for the last modified data, and there are usually not so many of them.
content
.authorised_list varchar = '123,234,345,' or null for data to be requested from content_share_user
.authorised_all boolean
content_share_user {user_id,content_id}

Answer 4

Hmm, a few considerations:
1. - available to friends
- available to a specific friend / list of friends
is essentially the same thing. "available to friends" = "Available to the list of friends that matches the friend list." it may be cheaper to store two copies of the list of friends, one in the friend list, one in such, denormalization is not always bad at all.
2. OR in the query - goodbye index. Why not use union and two times access on different indexes? Once all public records, the second - for the list of friends. Keeping publicity as a separate column in content is not the most elegant, but the simplest.
OR
create the following structure:
table groups
(id_group number,
id_user number,
id_friend number);
table permissions
(id_permission number,
id_content number,
id_group number);
In the user table, specify the "default" group that matches the friend list, and update it accordingly.
Each new “friend or list” permission generates a new group and a new permission, the “friends” permission is an entry in permissions indicating the default group.
An entry in permissions without a group means "available to everyone".
And we do this:
SELECT * FROM content WHERE id IN (SELECT p.id_content FROM permissions p LEFT JOIN groups g ON p.group = g.id WHERE g.friend = #me#);