Как хранить в БД права доступа?

Question

[dmitry_dvp]

Положим есть единицы данных «content» и есть пользователи «user».


Пользователи могут создавать content и настраивать права доступа для других пользователей по нескольким правилам:

— доступно всем

— доступно друзьям

— доступно конкретному другу/списку друзей


Правила складываются через «или». Т.е. доступно, если доступно хотя бы по одному из правил.

content [id, user_id, name]
user [id, name]
user_friend [user_id, friend_user_id]


Как организовать хранение таких правил в БД с тем, чтобы можно было составить не фулскановый запрос на выборку всех (via pagination) «content», доступных для конкретного пользователя?


Простейшая нормализованная схема хранения:

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

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

При такой схеме получается невозможным составить запрос кроме как с помощью UNION, что очень негативно сказывается на производительности.


Как сделать лучше? (как вообще сделать, потому что такой union и за работающий вариант считать не хочется)

Answer 1

[Vampiro]

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

Comments

[dmitry_dvp]

С "-1" не понятно: тут нет проверки, что текущий пользователь является другом

[Vampiro]

Вы же как-то планировали решать этот вопрос, создавая таблицу content_share_wide? Мне сложно телепатить не видя всей схемы. Но проблема с union решается банальным объединением в одну таблицу.

[dmitry_dvp]

Не то, чтобы планировал решать именно так, но в варианте с union это бы выглядело примерно следующим образом:

select c.* from content c
join content_share_wide csw on csw.content_id = c.id
where csw.type = 1

union

select c.* from content c
join content_share_wide csw on csw.content_id = c.id
join user_friend uf on c.user_id = uf.user_id
where csw.type = 2 and uf.friend_user_id = ?

union

select c.* from content c
join content_share_user csu on csu.content_id = c.id
where csu.user_id = ?

Вся схема изложена в вопросе. Это таблицы content, user и user_friend + потенциальные таблицы, в которых бы хранились права доступа, о которых собственно и вопрос.

Answer 2

[dmitry_dvp]

Добавлю, что если применить вашу подсказку и склеить таблицы share в одну по предложенному методу, получится вот так:

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 = ?

Comments

[Vampiro]

ок, мы «свинтили» один юнион из трех.
Вообще-то вы сами уже смогли бы догадаться, что можно заменить условие «join user_friend uf on c.user_id = uf.user_id» на «втыкивание» записей с id каждого друга. Да, это доставит определенный головняк на этапе «удалить/добавить» как друзей, так и контент, но что поделать. Скорость выборки должна быть приоритетной задачей. Не забывайте про insert ignore.

Answer 3

[rPman]

'Доступно всем' без вариантов нужно хранить в виде bolean у content, даже хотя бы в виде копии, заполняемой тригером у таблицы content_share.

'Доступно друзьям' и 'Доступно конкретному пользователю'… так ли важно разделять эти понятия. это бы имело смысл, если бы количество действий по созданию нового пользователя и добавлению прав было бы сравнимо с количеством запросов на права доступа, а это маловероятно, наверняка в вашей задаче количество запросов на чтение на порядок (или обычно это логарифм) больше изменений.
Может быть достаточно правила 'Доступно конкретному пользователю', а значит обойдетесь таблицей content_share_user {user_id,content_id}

Дальше, никогда не нужно надеяться на чистую реляционную модель. Делайте дополнительную копию на все, что читается чаще чем пишется в удобном для этого месте. Сериализованный список идентификаторов user_id в content.authorised_list (если это числа, то к примеру через ',' с обязательным ',' в конце), если их количество меньше определенного, удобен для запросов вида like '%12345,%', и ведь его можно заполнять не сразу, а периодически отдельным процессом и очищать по триггеру на изменении. Тогда основная нагрузка ляжет не на выполнение тригера, а на запросы только последних измененных данных, а их обычно не так много.
content
.authorised_list varchar = '123,234,345,' или null — для данных, которые нужно запросить из content_share_user
.authorised_all boolean
content_share_user {user_id,content_id}

Comments

[dmitry_dvp]

>> нужно хранить в виде bolean у content
Сомнительно. Флаги плохо или вообще не поддаются индексированию. Гораздо быстрее в качестве флага использовать «наличие строки в приjoinенной таблице».
С помощью этого можно разве что незначительно ускорить проверку прав конкретной строки

>> так ли важно разделять эти понятия
Да, конечно. Правильно ли я понял, вы предложили вместо флага «все» напрямую перечислять всех пользователей в таблице? Это логически не подходит, потому как вновь регистрирующиеся пользователи должны видеть такой контент. Но главное, что «доступно всем» это самое популярный вариент выбора.

>> Дальше, никогда не нужно надеяться на чистую реляционную модель. Делайте дополнительную копию на все, что читается чаще чем пишется в удобном для этого месте.
Абсолютно не зацикливаюсь на нормализации в этом вопросе, но даже денормализованного качественного решения найти пока не могу

>> запросов вида like '%12345,%',
Не представляю, где бы я мог воспользоваться таким заведомо неиндексируемым (MySQL) выражением. Опять же, разве что для того, чтобы незначительно ускорить проверку прав конкретной строки

[rPman]

Я дико извиняюсь, хотел подать полезную идею и сам же подсунул фигню… правда, если сделать fulltext index и использовать match…

Я просто хотел чтобы на задачу взглянули с другой стороны, в одном моем очень старом говнопроекте, где количество пользователей было ограничено и изменялось очень редко, и поэтому таблица раздачи прав представляла из себя булеву матрицу с колонками — пользователями, строками — объектами (вот их как раз могло быть очень много). Это не так уж и страшно, таблица с сотней колонок и кучи нулей, зато проверка прав доступа была действительно быстрой (в том проекте многие запросы генерировались перед исполнением — это плохая практика, но давала невероятную гибкость принципов метапрограммирования).

Да, я действительно предложил задуматься о том, чтобы убрать такое понятие как 'доступно друзьям', а триггерами на добавление/удаление друзей заполнять таблицу прав, чтобы избавиться от content_share_wide,… и в конце концов, есть же enum (правда его недостаток — при добавлении нового типа потребуется изменение типа поля таблицы, что обычно не шустро для очень больших таблиц).

Answer 4

[agathis]

Хм, несколько соображений:
1. — доступно друзьям
— доступно конкретному другу/списку друзей
по сути одно и то же. «доступно друзьям» = «Доступно списку друзей, совпадающему с френд-листом». возможно дешевле будет хранить две копии списка друзей, одна во френд-листе, одна в таких, денормализация — это вовсе не всегда плохо.

2. OR в запросе — прощай индекс. Почему не использовать юнион и два раза доступ по разным индексам? Один раз все публичные записи, второй — для списка друзей. Публичность хранить отдельным столбцом в content — не самое элегантное, но самое простое.

ИЛИ

создаем такую структуру:

table groups
(id_group number,
id_user number,
id_friend number);

table permissions
(id_permission number,
id_content number,
id_group number);

В таблице user указать «дефолтную» группу, совпадающую с френд-листом, соответственно ее обновлять.
Каждое новое разрешение «другу или списку» порождает новую группу и новый пермишен, разрешение «друзьям» — запись в permissions с указанием на дефолтную группу.

Запись в permissions без группы значит «доступно всем».
И делаем так:
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#);

Comments

[agathis]

то есть, запрос конечно такой

SELECT *
FROM   content
WHERE  id IN (SELECT content
              FROM   permissions p
              LEFT   JOIN groups g ON p.group = g.id
                               AND    g.friend = #me#);

[dmitry_dvp]

1. Впринципе да. Об этом уже выше упоминали. Ребилдить такие списки по доавлению/удалению друзей.
2. Понятно, что OR и union — это, здесь, альтернатива друг другу. Union плох по следующей причине: Самый популярный вариант выбора прав доступа будет «доступно всем». Т.е. с одной стороны от union'а будет стоять select чуть ли не всей таблицы content. Пагинировать select с таким union'м внутри — это temporary table + filesort практически по всем rows

ИЛИ. Идею понял. Она сродни предложенному Vampiro: использование особого признака для «для друзей»

Составляя вопрос, я намеренно откинул все, что показалось мне не обязательным, чтобы увеличить шансы на ответ (простыню никто читать бы не стал).

В реальности к моим правилам добавляются ещё 2 вида правил:
— доступно всем
— доступно зарегистрированным
— доступно друзьям
— доступно конкретному другу/списку друзей
— доступно кругу(кругам) друзей

(круг — подмножество друзей пользователя, организованых в одну группу, for example «коллеги»)

а может случиться так, что эти правила пополнятся ещё какими-то

Именно поэтому я всё ещё не могу сказать, что нашёл подходящее решение

[agathis]

Круг друзей во вторую схему ложится прекрасно. Для зарегистрированных можно завести ещё одну группу — уже не так элегантно, но тоже работает.

Кстати, если «для всех» доступно хотя бы половина контента, фуллскан — самый дешёвый способ доступа.

Пагинация как реализуется? Сортировка (причём наверняка по дате контента), потом чтение пачками?

[dmitry_dvp]

Да, пагинация по характеристикам, хранящимся в колонках таблицы content.
Пагинация вполне обычная — N штука на страницу. Если всё уложится в качественный запрос, то будет через limit offset конечно.

[agathis]

ага. я пользуюсь единственной, кажется, СУБД, которая не умеет limit offset (только limit) :)

но я боюсь фуллскана все равно не избежать.

Answer 5

[librarian]

Права доступа для конкретного пользователя лучше всего хранить в виде 11000100101, то есть в int и делать проверку на право доступа тупо сдвигом на два.

Comments

[librarian]

Например, есть права: читать, писать, править, загружать.

Тогда права типа 0101 будут выглядеть в таблице как число 5 = 1 + 4
А права типа 1111 будут выглядеть как: 1+2+4+8 = 15

[librarian]

Проверка на принадлежность конкретной группе пользователей реализуется просто выборкой из нужной таблицы.

[dmitry_dvp]

Но вопрос-то состоит не в том, как впринципе проверить права — это не представляет проблемы. Вопрос в том, как быстро выбирать из БД строки, права на которые у тебя есть