用户可以“阅读"的范围记录的常用方法是什么? [英] What is a common approach to scope records by those that an user can "read"?

问题描述

我正在使用 Ruby on Rails 3.2.2，我想知道在必须检查用户是否具有读取"记录列表"中存在的记录的适当授权时，什么是常用方法.也就是这个时候我有以下几点:

I am using Ruby on Rails 3.2.2 and I would like to know what is a common approach when it must be checked if an user has proper authorizations to "read" records present in a "list" of records. That is, at this time I have the following:

class Article < ActiveRecord::Base
  def readable_by_user?(user)
    # Implementation of multiple authorization checks that are not easy to
    # translate into an SQL query (at database level, it executes a bunch of
    # "separate" / "different" SQL queries).

    ... # return 'true' or 'false'
  end
end

通过使用上面的代码，我可以对单个文章对象执行授权检查:

By using the above code I can perform authorization checks on a single article object:

@article.readable_by_user?(@current_user)

但是，当我想通过准确检索 10 个对象来制作(通常在我的控制器 index 操作中)如下所示时

However, when I would like to make (usually, in my controller index action) something like the following by retrieving exactly 10 objects

Article.readable_by_user(@current_user).search(...).paginate(..., :per_page => 10)

我仍然必须对每个对象执行授权检查.所以，我可以做什么来对该记录列表"执行授权检查(Article 对象的数组)以智能"/高性能"的方式?也就是说，例如，我应该加载 Article.all(可能按创建的数据排序，限制 SQL查询 10 条记录，...)然后迭代这些对象中的每一个以执行授权检查?或者我应该做一些不同的事情(也许使用一些 SQL 查询技巧、一些 Ruby on Rails 工具或其他东西)?

I must still to perform authorization checks on each object. So, what I can make to perform authorization checks on that "list" of records (an array of Article objects) in a "smart" / "performant" way? That is, for example, should I load Article.all (maybe ordering those by created data, limiting the SQL query to 10 records, ...) and then to iterate on each of those objects so to perform authorization checks? or should I make something different (maybe with some SQL query trick, some Ruby on Rails facility or something else)?

我尝试手动"检索用户可读的文章，例如使用 find_each 方法:

I tried to retrieve articles readable by an user "manually", for example by using the find_each method:

# Note: This method is intended to be used as a "scope" method
#
#   Article.readable_by_user(@current_user).search(...).paginate(..., :per_page => 10)
#
def self.readable_by_user(user, n = 10)
  readable_article_ids = []

  Article.find_each(:batch_size => 1000) do |article|
    readable_article_ids << article.id if article.readable_by_user?(user)

    # Breaks the block when 10 articles have passed the readable authorization 
    # check.
    break if readable_article_ids.size == n
  end

  where("articles.id IN (?)", readable_article_ids)
end

此时，上面的代码是我能想到的最高性能的妥协"，即使它有一些陷阱:它限制"检索对象的数量为给定的数量具有给定 ids 的记录(在上例中默认为 10 条记录)；实际上，它真的"不会检索用户可读的所有对象，因为当您尝试进一步限定相关的 ActiveRecord::Relation "where"/"withwhich" 使用了 readable_by_user 范围方法(例如，当您还按 title 添加进一步的 SQL 查询子句搜索文章时)，它会限制 记录到那些 where("articles.id IN (?)", readable_article_ids)(也就是说，它限制"/限制"检索和可读对象的数量为前 10 个和所有当按title搜索时，用户可读的其他文章将被忽略).为了使 readable_by_user 方法与其他作用域的方法正常工作，该问题的解决方案可能是 not break 块，以便加载所有可读文章，但是当有很多记录时，出于性能原因，这并不好(也许，另一种解决方案是将所有文章id可读的存储在某处)用户，但我认为这不是解决问题的常见/简单解决方案).

At this time, the above code is the most "performant compromise" that I can think of, even if it has some pitfall: it "restricts" the amount of retrieved objects to a given amount of records with given ids (10 records by default in the above example); practically speaking, it "really" doesn't retrieve all objects readable by an user since when you try to further scope the related ActiveRecord::Relation "where" / "with which" the readable_by_user scope method is used (for example, when you would also search articles by title adding a further SQL query clause), it would restrict records to those where("articles.id IN (?)", readable_article_ids) (that is, it "limits" / "restricts" the amount of retrieved and readable objects to first 10 and all others articles readable by the user will be ignored when searching by title). A solution to the issue in order to make the readable_by_user method to properly work with further scope methods could be to do not break the block so to load all readable articles, but it is no good for performance reasons when there are a lot of records (maybe, another solution could be to store somewhere all article ids readable by an user, but I think it is not a common/easy solution to solve the issue).

那么，有什么方法可以以一种高效且真正"正确的方式完成我想做的事情(也许，完全改变上述方法)?强>

推荐答案

这取决于您的 readable_by_user 功能.如果它很容易翻译成 SQL，那么它就是前进的道路.如果比这更复杂，那么您很可能必须手动进行检查.

It depends on your readable_by_user function. If it is easy to translate into an SQL, than it is the way forward. If it is more complicated than that then you most probably have to do the check manually.

更新:为了阐明为可读列表创建 SQL 查询的要点，我提供了一个示例.假设文章对给定用户的可读性取决于以下因素:

UPDATE: To clarify the point of creating an SQL query for the readable list I present an example. Assume, that a readability of an article to a given user is dependent of the following:

• 用户自己的文章(SELECT a.user == ? FROM Articles a WHERE a.id = ?)
• 文章对所有人开放(SELECT a.state == 0 FROM Articles a WHERE a.user = ?)
• 该用户是可以访问文章的组的成员

sql:

SELECT max(g.rights) > 64
FROM Groups g 
JOIN Groups_users gu on g.id = ug.group_id
WHERE gu.id = ?

• 用户被分配到给定的文章

sql:

SELECT 1
FROM Articles_users au
WHERE au.article_id = ? AND au.user_id = ?

这些可以总结为以下查询:

These can be summarized in the following query:

def articles_for_user(user) 
  Articles.find_by_sql(["
    SELECT a.*
    FROM Articles a
    LEFT OUTER JOIN Articles_users au on au.article_id = a.id and au.user_id = ?
    WHERE a.user_id = ? 
       OR au.user_id = ?
       OR 64 <= (SELECT max(g.rights) 
                 FROM Groups g 
                 JOIN Groups_users gu on g.id = ug.group_id
                 WHERE gu.id = ?)
  ", user.id, user.id, user.id, user.id])
end

这肯定是一个复杂的查询，但最有效的解决方案.数据库应该做数据库的事情，如果你只使用 SQL 查询和一些逻辑来评估你的 readable_bu_user 那么你可以将它转换成一个纯 SQL 查询.

This is sure a complicated query, but the most efficient solution. The database should do database stuff, if you only use SQL queries and some logic to evaluate your readable_bu_user then you can translate it into one pure SQL query.

这篇关于用户可以“阅读"的范围记录的常用方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！