У меня есть таблицы с отношениями внешнего ключа:
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, но общее решение также работает.
В этой ситуации мы будем использовать Oauth, а электронная почта, используемая для Oauth, хранится в таблице User.
И это дает пользователю SQL-доступ к базе данных или вы будете контролировать выполняемые запросы? Потому что, если всегда есть связь между данными и записью пользователя, вы могли бы просто указать идентификатор пользователя в предложении where запроса, верно?
У пользователя никогда не будет прямого доступа к базе данных, у нас есть сервер, который взаимодействует с базой данных. Но пользователь может изменить запросы API, чтобы запросить ресурсы, к которым у него не должно быть доступа. Как вы сказали, мы можем использовать предложение WHERE для предоставления идентификатора пользователя, но в некоторых случаях это нужно делать с несколькими JOIN и с разными JOIN в зависимости от запрашиваемого ресурса. Это просто кажется раздражающим для поддержания.
Возможно, вы захотите использовать ORM для решения сложных структур данных. Я люблю SQLAlchemy (Python), но с этим вам может помочь любой.
Прежде чем ответить, мы должны прояснить одну вещь: 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-запроса. Если это единственный способ, думаю, я так и сделаю. Однако эта хранимая процедура кажется полезной, или, может быть, я могу написать функции, которые выполняют эту проверку перед выполнением каких-либо операций чтения или записи.
Кстати, как это можно обработать на стороне приложения? Если клиент отправляет запрос API на ваш сервер, единственный способ проверить, что имя пользователя/адрес электронной почты пользователя/идентификатор пользователя, авторизованный клиентом (через oauth или что-то еще), связанный с данным сообщением, - это проверить базу данных, чтобы убедиться, что пользователь создал данный пост. Я что-то упускаю?
Ну, идея обработки этого на стороне приложения заключается в том, что вы всегда читаете обе части данных из базы данных и просто не возвращаете результаты обратно. Различные фреймворки/промежуточное программное обеспечение приложений, чтобы сделать его более автоматизированным/гибким (например, заставить приложение проверять каждый вывод, независимо от конечной точки, искать набор общих полей (например, идентификатор владельца), сравнивать его с некоторым значением контекста и изменять ответ, если необходимо, до возврата. Таким образом, вы всегда читаете, всегда возвращаете и просто убедитесь, что в выводе присутствует поле. Ваш результат перехватывается, проверяется и корректируется
И, справедливости ради, есть вполне конкретные вещи, которые призваны «волшебным образом» решить эту проблему. Например, если вы использовали .NET/.NET Core для API и SQL Server для своей БД, а также Active Directory/встроенную аутентификацию и настроили безопасность на уровне строк в SQL Server... тогда вы могли бы полностью забыть об этом и просто запросите БД. Но в большинстве случаев это нереально.
Только что понял, что касается "перезаписи соединений". Вы можете написать эти соединения один раз, создав представления на стороне БД и вместо этого запрашивая представления.
Это очень полезно, спасибо! Что касается «представлений», чтобы избежать «перезаписи соединений», почему бы нам просто не написать функции, которые проверяют, связан ли данный postID или абзацId с идентификатором пользователя, прежде чем выполнять последующие SQL-запросы? Является ли цель представлений кэшировать ассоциации между этими идентификаторами, чтобы эти «проверочные SQL-запросы на соединение» не добавляли много дополнительной задержки?
Как узнать, какой запрос исходит от какого пользователя? У Sqlite нет пользователя/логина/разрешений.