Multiple Database Users per function

Question

We are exploring how SQL inject risks can be limited by only allowing key functions to perform duties within our rails application. I am the security officier, not a full time rails person.

The way I see it, only writing to the database should occur when an account is created (registration) or the account is modified (user profile update). For simplification purposes, let's assume that no other updates happen within the application from users.

If someone does not block a SQL injection attack on a read only request, we would be protected better with two separate calls.

Can rails support the following configuration? -

One database connection for all "read" requests
One database connection (same exact database) for all "write" requests

If so, how would that look in the database file and code requests?

You should be able to use a variant of the answer given the following question. Its just instead of declaring a separate database for the model you will be connecting to the same DB just with different settings. http://stackoverflow.com/questions/6122508/connecting-rails-3-1-with-multiple-databases — rovermicrover, Apr 08 '13 at 22:22
@rovermicrover While interesting, that question does not seem to address accessing the same database with different permissions in db.yml file (user1 = "insert, update" user2 = "select") — EagleEye208, Apr 09 '13 at 00:05
Ok, two models, using the same table. Each with their own DB user, that has different permissions. One can even inherit from the other, with the only difference being the DB setting/User declared through method in the link provided. — rovermicrover, Apr 09 '13 at 00:28
Sorry Mobile, so the two models would work like this use UserCU on all related actions and UserR on all reads. CU being create update and R being read. — rovermicrover, Apr 09 '13 at 00:31

score 0 · Accepted Answer · edited May 23 '17 at 10:32

I thought I might as well post this out.

class User < ActiveRecord::Base
  self.abstract_class = true

  ...

end

CUR = Create Update Read

R = Read only

class UserCUR < User
   establish_connection "user_cur_database_#{Rails.env}"
end

class UserR < User
   establish_connection "user_r_database_#{Rails.env}"
end

database.yml

user_cur_database_production:
  adapter: w.e.sql
  host: yourhost
  username: user_cur
  password: ********
  database: yourdb

user_r_database_production:
  adapter: w.e.sql
  host: yourhost
  username: user_r
  password: ********
  database: yourdb

Where the users would have different access permissions

You could do the same thing but on the actually rails app instead of in the db, I know this isn't what you are looking for exactly but can provide some more help.

Is there an easy way to make a Rails ActiveRecord model read-only?

Yes, this is what I was looking for. Also, help settle a bet: Is it common for rails - or any 3-tier web platform - to get granular with db permissions? — EagleEye208, Apr 09 '13 at 16:38

Multiple Database Users per function

1 Answers1