Is it possible to use different users for different operations on the same DB with Rails? I am a strong believer in using the most restrictive access for a given operation, and having all actions operate with a user with full CRUD privs doesn''t fit the security policies of many of my client projects.
Bump. Anyone have any ideas about this? On Jul 16, 2005, at 11:19 AM, Toby Boudreaux wrote:> Is it possible to use different users for different operations on > the same DB with Rails? > > I am a strong believer in using the most restrictive access for a > given operation, and having all actions operate with a user with > full CRUD privs doesn''t fit the security policies of many of my > client projects. > > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
I just posted a solution for something like this in the Oracle environment. We also like the idea of having the app run as a less privileged user -- running as the schema owner, the app would have full privs, plus the ability to drop tables, etc. So we grant the necessary privs to an application user in the db (distinct from the data schema owner), and then create synonyms. I posted a change the Oracle OCI adapter such that models mapping to synonyms worked. This may not go as far as you''d like, but I''d have trouble keeping track of different users for different pages within a single app. Toby Boudreaux wrote:> Bump. > Anyone have any ideas about this? > > > On Jul 16, 2005, at 11:19 AM, Toby Boudreaux wrote: > >> Is it possible to use different users for different operations on the >> same DB with Rails? >> >> I am a strong believer in using the most restrictive access for a >> given operation, and having all actions operate with a user with full >> CRUD privs doesn''t fit the security policies of many of my client >> projects. >> >> >> _______________________________________________ >> Rails mailing list >> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >> http://lists.rubyonrails.org/mailman/listinfo/rails >> > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
> I am a strong believer in using the most restrictive access for a > given operation, and having all actions operate with a user with full > CRUD privs doesn''t fit the security policies of many of my client > projects.I''m curious to source of this requirement. If the model would know of and pick the right db user for a given operation automatically, what security would having separate users give? I mean, you''d then have an all powerful model in any case and you''d have the details for both users in database.yml. Is this helpful if you have consumers of the database that are not the Rails app? -- David Heinemeier Hansson http://www.loudthinking.com -- Broadcasting Brain http://www.basecamphq.com -- Online project management http://www.backpackit.com -- Personal information manager http://www.rubyonrails.com -- Web-application framework
On Tue, 19 Jul 2005, David Heinemeier Hansson wrote:> > I am a strong believer in using the most restrictive access for a > > given operation, and having all actions operate with a user with full > > CRUD privs doesn''t fit the security policies of many of my client > > projects. > > I''m curious to source of this requirement. If the model would know of > and pick the right db user for a given operation automatically, what > security would having separate users give?Some projects I''ve worked on have had the user who logs in use that login against the db. IOW, there could be bazillions of possible combinations of access privileges depending on a user''s roles, etc. IOW, there''s no "salted hash" or users table per app -- they don''t get in unless they have CRUD privs on the db itself with that user/pass. -- _Deirdre web / blog: http://deirdre.net/ yarn: http://fuzzyorange.com cat''s blog: http://fuzzyorange.com/vsd/ "Memes are a hoax! Pass it on!"
The concern isn''t that the entire model is somehow compromised, but that it''s naive to use a full-access CRUD user for every operation on a public-facing app (and using separate apps is not always sufficient). Validation fails in one common function -- especially something complex such as a search, and... If a search script has a possible exploit -- no matter if the bug lies in user code, in a framework, in a language -- you''ve made yourself needlessly vulnerable. Profile pages in community sites are often run by a DB user with access to password columns. I''ve done a lot of work for the government/NGOs and in many cases this was a hard requirement -- that a given action is taken using the user with the least necessary privilege. Trusting a framework or language unconditionally -- or, rather, purposely omitting a step in securing your app when it''s quite easy to do otherwise -- is how a possibly small vulnerability can be escalated into something more devastating and leave a developer in a world of regret and clients out millions of dollars. SQL Injection is the most obvious culprit, but there is no telling what exploits lie in every day applications. Databases have users and privilege levels for a reason. Not using them seems negligent. On Jul 18, 2005, at 7:58 PM, David Heinemeier Hansson wrote:>> I am a strong believer in using the most restrictive access for a >> given operation, and having all actions operate with a user with full >> CRUD privs doesn''t fit the security policies of many of my client >> projects. >> > > I''m curious to source of this requirement. If the model would know of > and pick the right db user for a given operation automatically, what > security would having separate users give? I mean, you''d then have an > all powerful model in any case and you''d have the details for both > users in database.yml. > > Is this helpful if you have consumers of the database that are not the > Rails app? > -- > David Heinemeier Hansson > http://www.loudthinking.com -- Broadcasting Brain > http://www.basecamphq.com -- Online project management > http://www.backpackit.com -- Personal information manager > http://www.rubyonrails.com -- Web-application framework > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
Toby''s points are good ones. It''s similar to declarative versus procedural referential integrity. Declarative (i.e. database defined and enforced) referential integrity is obviously much more desirable because the application does not need to write code to handle integrity issues, making it much more simple and reliable and making orphans impossible. Relying on the application code to enforce referential integrity can never be as robust. Database security is ideally fine grained, with row-level user and group security defined and enforced at the database level. This removes the need for including much security code in the application and ensures that coding mishaps won''t compromise the security of the data. MySQL has (or will have fine grained row level user security). It''s a great pity that Postgres does not. Andrew ----- Original Message ----- From: "Toby Boudreaux" <rails-lb8SQxIZKShBDgjK7y7TUQ@public.gmane.org> To: "David Heinemeier Hansson" <david.heinemeier-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>; <rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> Sent: Tuesday, July 19, 2005 12:02 PM Subject: Re: [Rails] Multiple DB Users? The concern isn''t that the entire model is somehow compromised, but that it''s naive to use a full-access CRUD user for every operation on a public-facing app (and using separate apps is not always sufficient). Validation fails in one common function -- especially something complex such as a search, and... If a search script has a possible exploit -- no matter if the bug lies in user code, in a framework, in a language -- you''ve made yourself needlessly vulnerable. Profile pages in community sites are often run by a DB user with access to password columns. I''ve done a lot of work for the government/NGOs and in many cases this was a hard requirement -- that a given action is taken using the user with the least necessary privilege. Trusting a framework or language unconditionally -- or, rather, purposely omitting a step in securing your app when it''s quite easy to do otherwise -- is how a possibly small vulnerability can be escalated into something more devastating and leave a developer in a world of regret and clients out millions of dollars. SQL Injection is the most obvious culprit, but there is no telling what exploits lie in every day applications. Databases have users and privilege levels for a reason. Not using them seems negligent. On Jul 18, 2005, at 7:58 PM, David Heinemeier Hansson wrote:>> I am a strong believer in using the most restrictive access for a >> given operation, and having all actions operate with a user with full >> CRUD privs doesn''t fit the security policies of many of my client >> projects. >> > > I''m curious to source of this requirement. If the model would know of > and pick the right db user for a given operation automatically, what > security would having separate users give? I mean, you''d then have an > all powerful model in any case and you''d have the details for both > users in database.yml. > > Is this helpful if you have consumers of the database that are not the > Rails app? > -- > David Heinemeier Hansson > http://www.loudthinking.com -- Broadcasting Brain > http://www.basecamphq.com -- Online project management > http://www.backpackit.com -- Personal information manager > http://www.rubyonrails.com -- Web-application framework > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >_______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Out of curiosity, how is row-level security handled in MySQL? I have implemented row-level security using views in PostgreSQL before, is it done the same way in MySQL (5.0+, obviously), or are there actual row-level permissions that you can enforce? Cheers! -DF On 7/19/05, Andrew Stuart <andrew.stuart-TWq/lk6o2i4FZYWR8B6FNDvXrrhHudLZEK66K81epY8@public.gmane.org> wrote:> Toby''s points are good ones. > > It''s similar to declarative versus procedural referential integrity. > Declarative (i.e. database defined and enforced) referential integrity is > obviously much more desirable because the application does not need to write > code to handle integrity issues, making it much more simple and reliable and > making orphans impossible. Relying on the application code to enforce > referential integrity can never be as robust. > > Database security is ideally fine grained, with row-level user and group > security defined and enforced at the database level. This removes the need > for including much security code in the application and ensures that coding > mishaps won''t compromise the security of the data. > > MySQL has (or will have fine grained row level user security). It''s a great > pity that Postgres does not. > > Andrew > > > ----- Original Message ----- > From: "Toby Boudreaux" <rails-lb8SQxIZKShBDgjK7y7TUQ@public.gmane.org> > To: "David Heinemeier Hansson" <david.heinemeier-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>; > <rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> > Sent: Tuesday, July 19, 2005 12:02 PM > Subject: Re: [Rails] Multiple DB Users? > > > The concern isn''t that the entire model is somehow compromised, but > that it''s naive to use a full-access CRUD user for every operation on > a public-facing app (and using separate apps is not always > sufficient). Validation fails in one common function -- especially > something complex such as a search, and... > > If a search script has a possible exploit -- no matter if the bug > lies in user code, in a framework, in a language -- you''ve made > yourself needlessly vulnerable. Profile pages in community sites are > often run by a DB user with access to password columns. > > I''ve done a lot of work for the government/NGOs and in many cases > this was a hard requirement -- that a given action is taken using the > user with the least necessary privilege. > > Trusting a framework or language unconditionally -- or, rather, > purposely omitting a step in securing your app when it''s quite easy > to do otherwise -- is how a possibly small vulnerability can be > escalated into something more devastating and leave a developer in a > world of regret and clients out millions of dollars. SQL Injection is > the most obvious culprit, but there is no telling what exploits lie > in every day applications. Databases have users and privilege levels > for a reason. Not using them seems negligent. > > > > > On Jul 18, 2005, at 7:58 PM, David Heinemeier Hansson wrote: > > >> I am a strong believer in using the most restrictive access for a > >> given operation, and having all actions operate with a user with full > >> CRUD privs doesn''t fit the security policies of many of my client > >> projects. > >> > > > > I''m curious to source of this requirement. If the model would know of > > and pick the right db user for a given operation automatically, what > > security would having separate users give? I mean, you''d then have an > > all powerful model in any case and you''d have the details for both > > users in database.yml. > > > > Is this helpful if you have consumers of the database that are not the > > Rails app? > > -- > > David Heinemeier Hansson > > http://www.loudthinking.com -- Broadcasting Brain > > http://www.basecamphq.com -- Online project management > > http://www.backpackit.com -- Personal information manager > > http://www.rubyonrails.com -- Web-application framework > > _______________________________________________ > > Rails mailing list > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
I don''t know the answer to that. I''m a postgres user. This document refers to views in mysql 5.0 allowing row level security via views. http://www.sourcekeg.co.uk/www.mysql.com/tech-resources/articles/evaluating-mysql-5.0.html I would most interested to hear more about how you implement row level security in postgres - can you send me something direct? It appears that both postgres and mysql use views to get row level security. andrew ----- Original Message ----- From: "David Felstead" <david.felstead-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> To: "Andrew Stuart" <andrew.stuart-TWq/lk6o2i4FZYWR8B6FNDvXrrhHudLZEK66K81epY8@public.gmane.org>; <rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> Sent: Tuesday, July 19, 2005 12:57 PM Subject: Re: [Rails] Multiple DB Users? Out of curiosity, how is row-level security handled in MySQL? I have implemented row-level security using views in PostgreSQL before, is it done the same way in MySQL (5.0+, obviously), or are there actual row-level permissions that you can enforce? Cheers! -DF On 7/19/05, Andrew Stuart <andrew.stuart-TWq/lk6o2i4FZYWR8B6FNDvXrrhHudLZEK66K81epY8@public.gmane.org> wrote:> Toby''s points are good ones. > > It''s similar to declarative versus procedural referential integrity. > Declarative (i.e. database defined and enforced) referential integrity is > obviously much more desirable because the application does not need towrite> code to handle integrity issues, making it much more simple and reliableand> making orphans impossible. Relying on the application code to enforce > referential integrity can never be as robust. > > Database security is ideally fine grained, with row-level user and group > security defined and enforced at the database level. This removes theneed> for including much security code in the application and ensures thatcoding> mishaps won''t compromise the security of the data. > > MySQL has (or will have fine grained row level user security). It''s agreat> pity that Postgres does not. > > Andrew > > > ----- Original Message ----- > From: "Toby Boudreaux" <rails-lb8SQxIZKShBDgjK7y7TUQ@public.gmane.org> > To: "David Heinemeier Hansson" <david.heinemeier-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>; > <rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> > Sent: Tuesday, July 19, 2005 12:02 PM > Subject: Re: [Rails] Multiple DB Users? > > > The concern isn''t that the entire model is somehow compromised, but > that it''s naive to use a full-access CRUD user for every operation on > a public-facing app (and using separate apps is not always > sufficient). Validation fails in one common function -- especially > something complex such as a search, and... > > If a search script has a possible exploit -- no matter if the bug > lies in user code, in a framework, in a language -- you''ve made > yourself needlessly vulnerable. Profile pages in community sites are > often run by a DB user with access to password columns. > > I''ve done a lot of work for the government/NGOs and in many cases > this was a hard requirement -- that a given action is taken using the > user with the least necessary privilege. > > Trusting a framework or language unconditionally -- or, rather, > purposely omitting a step in securing your app when it''s quite easy > to do otherwise -- is how a possibly small vulnerability can be > escalated into something more devastating and leave a developer in a > world of regret and clients out millions of dollars. SQL Injection is > the most obvious culprit, but there is no telling what exploits lie > in every day applications. Databases have users and privilege levels > for a reason. Not using them seems negligent. > > > > > On Jul 18, 2005, at 7:58 PM, David Heinemeier Hansson wrote: > > >> I am a strong believer in using the most restrictive access for a > >> given operation, and having all actions operate with a user with full > >> CRUD privs doesn''t fit the security policies of many of my client > >> projects. > >> > > > > I''m curious to source of this requirement. If the model would know of > > and pick the right db user for a given operation automatically, what > > security would having separate users give? I mean, you''d then have an > > all powerful model in any case and you''d have the details for both > > users in database.yml. > > > > Is this helpful if you have consumers of the database that are not the > > Rails app? > > -- > > David Heinemeier Hansson > > http://www.loudthinking.com -- Broadcasting Brain > > http://www.basecamphq.com -- Online project management > > http://www.backpackit.com -- Personal information manager > > http://www.rubyonrails.com -- Web-application framework > > _______________________________________________ > > Rails mailing list > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
Out of curiosity (as it''s quite important to some of my upcoming decisions for projects), is following the Principle of Least Privilege impossible in a Rails app? Is there an AR hack/patch/config angle that might allow for switching DB users per action? Maybe a :user=>:write_user param or something ;) -tj On Jul 18, 2005, at 10:31 PM, Andrew Stuart wrote:> Toby''s points are good ones. > > It''s similar to declarative versus procedural referential integrity. > Declarative (i.e. database defined and enforced) referential > integrity is > obviously much more desirable because the application does not need > to write > code to handle integrity issues, making it much more simple and > reliable and > making orphans impossible. Relying on the application code to enforce > referential integrity can never be as robust. > > Database security is ideally fine grained, with row-level user and > group > security defined and enforced at the database level. This removes > the need > for including much security code in the application and ensures > that coding > mishaps won''t compromise the security of the data. > > MySQL has (or will have fine grained row level user security). > It''s a great > pity that Postgres does not. > > Andrew > > > ----- Original Message ----- > From: "Toby Boudreaux" <rails-lb8SQxIZKShBDgjK7y7TUQ@public.gmane.org> > To: "David Heinemeier Hansson" <david.heinemeier-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>; > <rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> > Sent: Tuesday, July 19, 2005 12:02 PM > Subject: Re: [Rails] Multiple DB Users? > > > The concern isn''t that the entire model is somehow compromised, but > that it''s naive to use a full-access CRUD user for every operation on > a public-facing app (and using separate apps is not always > sufficient). Validation fails in one common function -- especially > something complex such as a search, and... > > If a search script has a possible exploit -- no matter if the bug > lies in user code, in a framework, in a language -- you''ve made > yourself needlessly vulnerable. Profile pages in community sites are > often run by a DB user with access to password columns. > > I''ve done a lot of work for the government/NGOs and in many cases > this was a hard requirement -- that a given action is taken using the > user with the least necessary privilege. > > Trusting a framework or language unconditionally -- or, rather, > purposely omitting a step in securing your app when it''s quite easy > to do otherwise -- is how a possibly small vulnerability can be > escalated into something more devastating and leave a developer in a > world of regret and clients out millions of dollars. SQL Injection is > the most obvious culprit, but there is no telling what exploits lie > in every day applications. Databases have users and privilege levels > for a reason. Not using them seems negligent. > > > > > On Jul 18, 2005, at 7:58 PM, David Heinemeier Hansson wrote: > > >>> I am a strong believer in using the most restrictive access for a >>> given operation, and having all actions operate with a user with >>> full >>> CRUD privs doesn''t fit the security policies of many of my client >>> projects. >>> >>> >> >> I''m curious to source of this requirement. If the model would know of >> and pick the right db user for a given operation automatically, what >> security would having separate users give? I mean, you''d then have an >> all powerful model in any case and you''d have the details for both >> users in database.yml. >> >> Is this helpful if you have consumers of the database that are not >> the >> Rails app? >> -- >> David Heinemeier Hansson >> http://www.loudthinking.com -- Broadcasting Brain >> http://www.basecamphq.com -- Online project management >> http://www.backpackit.com -- Personal information manager >> http://www.rubyonrails.com -- Web-application framework >> _______________________________________________ >> Rails mailing list >> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >> http://lists.rubyonrails.org/mailman/listinfo/rails >> >> > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >