What is a common approach to scope records by those that an user can "read"?

Question

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)

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)

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

UPDATED after @Matzi answer

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

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

So, there is some way to accomplish what I would like to make in a performant and "really" correct way (maybe, by changing the above approach at all)?

Answer 1

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.

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:

• The user's own article (SELECT a.user == ? FROM Articles a WHERE a.id = ?)
• The article is open to everyone (SELECT a.state == 0 FROM Articles a WHERE a.user = ?)
• The user is member of a group with access to articles

sql:

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

• The user is assigned to the given article

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

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.

Answer 2

I think you sholud look for declarative_authorization gem. With its with_permissions_to method you can easily perfom such database queries. For example: Article.with_permissions_to(:read).limit(10).offset(20)

Answer 3

The cancan gem has this functionality. In fact, as of version 1.4 it will automatically scope your queries to return objects that are accessible to the current user.

See this page for more info: https://github.com/ryanb/cancan/wiki/Fetching-Records

Answer 4

I had the same issue on a system I'm currently worked on.

The most efficient way I found was to implement a batch job that pre-calculates the authorization state of each record. I went with something like accessible_by_companies and stored an array with all the company codes that could access those records, but you might as well work with accessible_by_users if that's your case.

On the "show" action, I recalculate the list of authorized companies for the record, use it to perform the authorization check, and store it again.

I used ElasticSearch to store the pre-calculated values and all the data I needed to perform queries and listings. The database is only touched when viewing a record or by the batch job. There's a big performance gain on this approach, give it a try.

Answer 5

For best performance I would suggest storing list of user readable articles in the session - the user is not going to change within the session and you may consider refresh frequency and/or conditions separately. Assuming that your Articles.list() can be filtered by ids all you will need to do will be to pass the list of user readable ids to Articles.list() functionality. Re user readable list update: you should really update it relatively infrequently - at most once per search, you don' t want to refresh the complete list on every page load for the simple reason that the new results may appear in the pages that user already scrolled through anyway.