0

I am using Ruby on Rails 3.2.2 and MySQL. After my previous question on "how to handle massive storage of records in database for user authorization purposes", since related answers (on how to solve the issue or how to accomplish to that I am looking for) aren't sufficiently detailed or require to much resources (at least for me), I would like to know what are valid and reasonable alternatives to that approach.

In few words, this question could be phrase as: how to handle "complex" (at level of SQL querying) user authorizations when you have to fetch more than one "authorized" records? That is, for example, given I have a readable_by_user? method in my model class, how to retrieve more than one record (records could be 10 or billions of billions) by executing as few as possible SQL queries when you would use code like the following (note: the following code would be used mostly in index controller actions):

Article.readable_by_user(@current_user)
# => Returns all articles readable by the current user.
Community
  • 1
  • 1
Backo
  • 18,291
  • 27
  • 103
  • 170
  • The question is a bit vague. Are you reinventing the wheel? Surely there are libraries which can accomplish the AAA stage (login). If the connection becomes encrypted, then a session_id can be useful. The complexity of your SQL Schema should not be related to login or access privileges...unless you elaborate enough to show a design error (like unnecessary constraints). – mda Jun 24 '12 at 03:20
  • @mda - It could be that my question is "a bit vague", but *I am not referring to "login" at all*. However, it could be (maybe) that I do not understand what exactly you are referring to... can you be more explicit on how do you are referring user authorizations to `session_id` so that (as you say) the latter "can be useful" in my case? and what about "[...] ... unless you elaborate enough to show a design error (like unnecessary constraints)"? – Backo Jun 24 '12 at 03:32
  • 2
    Continuation of [How to handle massive storage of records in database for user authorization purposes?](http://stackoverflow.com/questions/11114261/how-to-handle-massive-storage-of-records-in-database-for-user-authorization-purp) – user207421 Jun 24 '12 at 03:52
  • 2
    The idea is not to *minimize* the *number* of SQL queries but surely to minimize their total time and space cost? – user207421 Jun 24 '12 at 03:56
  • Last workflow product that I was involved in(http://www.tibco.com/company/news/releases/2003/press588.jsp) had similar requirement. Individual documents had privileges. While calculating the worker queue every document had to be authorized. Documents used to have implicit privileges like owner, admin and workflow owner. Even though each document needs authorization, the privileges didn't require a massive table. – Harish Shetty Jun 24 '12 at 04:04
  • 1
    Your description does not contain the relevant parts of your SQL Schema (An Entity - Relationship Diagram for example). If you have a "huge table", and you don't think you need one, then add table(s) that store the permission(s) attributes where necessary. There will be a redundancy of uids/unique keys/pointers, but that's always the balance you're looking for between time and space complexity. A production db should not be "fully normalized", but rather customized to seek said balance. – mda Jun 24 '12 at 06:45
  • @EJP - "The idea is not to minimize the number of SQL queries but surely to minimize their total time and space cost?" I think both "to minimize the number of SQL queries" (because a candidate solution could be to run a `readable_by_user` method on each retrieved object / record - it is much less performant but works) and "to minimize their total time and space cost" (because another candidate solution is to store many authorization records / objects in a database table - it is less performant for others reasons but works). – Backo Jun 24 '12 at 11:37
  • @KandadaBoggu - Maybe the "last workflow product that you was involved in" didn't require "complex" authorization checks as in my application (with "complex" I mean that, in order to make the user authorization related to a single object to pass or not, it must be fetched records from other database tables "internally" to the user authorization method itself). – Backo Jun 24 '12 at 11:45
  • @mda - More, what do you exactly mean with "AAA stage" in your first comment? – Backo Jun 24 '12 at 23:30
  • Why are you asking a duplicate question? This whole thread should be flagged or voted down. I'm done with this question. – mda Jun 25 '12 at 06:33
  • @mda - Why do you think my question is a duplicate since I am asking for alternatives? – Backo Jun 25 '12 at 09:24
  • @Backo I'm visiting this old question as part of the "close vote review" cleanup. I agree that this question is different, but in practical terms, the linked-to question has many answers which present alternatives. For that practical reason, I'm voting to close this one. – Wayne Conrad Mar 16 '14 at 17:12

0 Answers0