Как я могу управлять доступом через таблицу SQL?

Я пытаюсь создать систему контроля доступа.

Вот урезанный пример того, как выглядит таблица, доступ к которой я пытаюсь контролировать:

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

А таблица контроля доступа выглядит так:

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

Доступ может быть предоставлен либо путем указания идентификатора «вещи» напрямую, либо для всей группы вещей путем указания идентификатора группы. В приведенном выше примере пользователю 1 был предоставлен уровень доступа 50 к группе 1, который должен применяться, если нет каких-либо других правил, предоставляющих более конкретный доступ к отдельной вещи.

Мне нужен запрос, который возвращает список вещей (только идентификаторы в порядке) вместе с уровнем доступа для конкретного пользователя. Итак, используя приведенный выше пример, я бы хотел что-то подобное для идентификатора пользователя 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's no access
        level for it)

Самое близкое, что я могу придумать, это:

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)
    )

Но это возвращает несколько строк, когда мне нужна только одна для каждой строки в таблице «вещи». Я не уверен, как перейти к одной строке для каждой «вещи» или как установить приоритет правил «вещи» над правилами «группы».

Если это поможет, я использую базу данных PostgreSQL.

Пожалуйста, не стесняйтесь оставлять комментарии, если есть какая-либо информация, которую я пропустил.

Заранее спасибо!


postgresql sql access-control
person Dan    schedule 14.10.2008    source источник

Ответы (4)


arrow_upward
1
arrow_downward

Я не знаю диалект Postgres SQL, но, может быть, что-то вроде:

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

Кстати, дизайн мне не нравится. Я бы предпочел, чтобы таблица доступа была разделена на две части:

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

Затем мой запрос становится:

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

Я предпочитаю это, потому что внешние ключи теперь можно использовать в таблицах доступа.

person Tony Andrews    schedule 14.10.2008
comment
О, интересное решение. Кажется, работает с моими ограниченными примерными данными, я попробую некоторые реальные данные и посмотрю, как они поживают. Для справки, я согласен, что дизайн таблицы немного неприятный. Я, к сожалению, застрял с этим. - person Dan; 14.10.2008

arrow_upward
1
arrow_downward

Я только вчера вечером читал статью об этом. У него есть некоторые идеи, как это сделать. Если вы не можете использовать ссылку в заголовке, попробуйте использовать Академию Google на Ограничение раскрытия информации по Гиппократу. Базы данных.

person tvanfosson    schedule 14.10.2008

arrow_upward
1
arrow_downward

Хотя есть несколько хороших ответов, наиболее эффективным, вероятно, будет что-то вроде этого:

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

Который просто использует обобщение, добавленное к вашему запросу, чтобы получить то, что вы ищете.

person Josef    schedule 14.10.2008

arrow_upward
0
arrow_downward

Тони:

Неплохое решение, мне нравится, вроде работает. Вот ваш запрос после незначительной настройки:

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;

И результаты выглядят правильно:

 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 |

Я полностью понимаю, что это не идеальная схема. Тем не менее, я застрял с ним в некоторой степени.

Йозеф:

Ваше решение очень похоже на то, с чем я играл, и мои инстинкты (какие они есть) говорят мне, что это должно быть возможно сделать таким образом. К сожалению, это не дает полностью правильных результатов:

 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 |

Уровень доступа для «вещи 1» имеет более высокое значение доступа «группа», а не более конкретное значение доступа «вещь» 10, что мне и нужно. Я не думаю, что есть способ исправить это в GROUP BY, но если у кого-то есть какие-либо предложения, я буду более чем счастлив оказаться неправым в этом вопросе.

person Dan    schedule 14.10.2008