Как запретить пользователям изменять внешние ключи в запросах?

У меня есть таблицы с отношениями внешнего ключа:

Users:
id,
has_many posts

Posts:
id,
has_many paragraphs

Paragraph:
id,
String text

У меня есть запросы на просмотр сообщения по идентификатору сообщения и просмотр абзаца по идентификатору абзаца. Моя схема сложнее, чем эта, но по-прежнему ясно, что ресурсы принадлежат пользователю с определенным идентификатором. Учитывая идентификатор пользователя, есть ли в SQL какой-либо способ проверки запросов, которые можно выполнять только для ресурсов, принадлежащих этому пользователю?

validateUserForPostId(postID, userID):
IF CreateSQLQuery("SELECT parent.ID FROM posts child JOIN on users on parent.ID = child.userID WHERE child.ID = ?", postID) == userID:
return true; // Valid, so we should be able to read or write post with ID = postID
validateUserForParagraphId(paragraphID):
// Similar join logic

Схема может усложниться и стать раздражающей для написания. Есть ли для этого встроенное SQL-решение? Я использую SQLite, но общее решение также работает.

Как узнать, какой запрос исходит от какого пользователя? У Sqlite нет пользователя/логина/разрешений.

Gerballi 16.11.2022 23:50

В этой ситуации мы будем использовать Oauth, а электронная почта, используемая для Oauth, хранится в таблице User.

Aloysius 17.11.2022 00:30

И это дает пользователю SQL-доступ к базе данных или вы будете контролировать выполняемые запросы? Потому что, если всегда есть связь между данными и записью пользователя, вы могли бы просто указать идентификатор пользователя в предложении where запроса, верно?

Gerballi 17.11.2022 00:38

У пользователя никогда не будет прямого доступа к базе данных, у нас есть сервер, который взаимодействует с базой данных. Но пользователь может изменить запросы API, чтобы запросить ресурсы, к которым у него не должно быть доступа. Как вы сказали, мы можем использовать предложение WHERE для предоставления идентификатора пользователя, но в некоторых случаях это нужно делать с несколькими JOIN и с разными JOIN в зависимости от запрашиваемого ресурса. Это просто кажется раздражающим для поддержания.

Aloysius 17.11.2022 02:02

Возможно, вы захотите использовать ORM для решения сложных структур данных. Я люблю SQLAlchemy (Python), но с этим вам может помочь любой.

Gerballi 17.11.2022 02:09
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
0
5
50
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

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

Если вы пытаетесь решить эту проблему на стороне движка БД, единственный способ — JOIN обращаться к таблицам владельцев по каждому запросу и отфильтровывать данные. Например, если вы пытаетесь прочитать сообщение, вместо того, чтобы делать:

select
    content, timestamp, userID
from
    posts

вам нужно будет передать параметр с текущим идентификатором пользователя в запрос, чтобы минимизировать проверки:

select
    posts.content,
    posts.timestamp,
    posts.userID
from
    posts child
    join users on parent.ID = child.userID
where
    parent.ID = '?'
    and child.ID = '?'

Популярным решением для того, чтобы сделать его немного более управляемым, является использование таких вещей, как хранимые процедуры (их поддерживают многие механизмы баз данных). Получается что-то вроде:

EXEC sp_getPost “postID”, “userID”

Другим подходом может быть создание представлений на стороне механизма базы данных, которые выполняют необходимые соединения и всегда предоставляют согласованное имя столбца (например, authorUserId) для фильтрации.

Однако для крупных современных приложений или сервисов принято обращаться к коду приложения, а не к БД.

Решение для выполнения соединений - это то, что я пытался описать в вопросе, но кажется диким переписывать соединения для каждого выполненного SQL-запроса. Если это единственный способ, думаю, я так и сделаю. Однако эта хранимая процедура кажется полезной, или, может быть, я могу написать функции, которые выполняют эту проверку перед выполнением каких-либо операций чтения или записи.

Aloysius 17.11.2022 00:36

Кстати, как это можно обработать на стороне приложения? Если клиент отправляет запрос API на ваш сервер, единственный способ проверить, что имя пользователя/адрес электронной почты пользователя/идентификатор пользователя, авторизованный клиентом (через oauth или что-то еще), связанный с данным сообщением, - это проверить базу данных, чтобы убедиться, что пользователь создал данный пост. Я что-то упускаю?

Aloysius 17.11.2022 00:37

Ну, идея обработки этого на стороне приложения заключается в том, что вы всегда читаете обе части данных из базы данных и просто не возвращаете результаты обратно. Различные фреймворки/промежуточное программное обеспечение приложений, чтобы сделать его более автоматизированным/гибким (например, заставить приложение проверять каждый вывод, независимо от конечной точки, искать набор общих полей (например, идентификатор владельца), сравнивать его с некоторым значением контекста и изменять ответ, если необходимо, до возврата. Таким образом, вы всегда читаете, всегда возвращаете и просто убедитесь, что в выводе присутствует поле. Ваш результат перехватывается, проверяется и корректируется

Philip P. 17.11.2022 00:41

И, справедливости ради, есть вполне конкретные вещи, которые призваны «волшебным образом» решить эту проблему. Например, если вы использовали .NET/.NET Core для API и SQL Server для своей БД, а также Active Directory/встроенную аутентификацию и настроили безопасность на уровне строк в SQL Server... тогда вы могли бы полностью забыть об этом и просто запросите БД. Но в большинстве случаев это нереально.

Philip P. 17.11.2022 00:58

Только что понял, что касается "перезаписи соединений". Вы можете написать эти соединения один раз, создав представления на стороне БД и вместо этого запрашивая представления.

Philip P. 17.11.2022 01:31

Это очень полезно, спасибо! Что касается «представлений», чтобы избежать «перезаписи соединений», почему бы нам просто не написать функции, которые проверяют, связан ли данный postID или абзацId с идентификатором пользователя, прежде чем выполнять последующие SQL-запросы? Является ли цель представлений кэшировать ассоциации между этими идентификаторами, чтобы эти «проверочные SQL-запросы на соединение» не добавляли много дополнительной задержки?

Aloysius 17.11.2022 02:20

Другие вопросы по теме