Is it feasible to somehow have a Rails app defer connecting to the database until after the first page is loaded, that somehow then uses either some ADSI or WMI scriptery to get the client''s current logged in username, pass that to the server for the session, and then login to the database after that has been retrieved? I have a SQL Server database that has been set up with Windows-based security, and just thought that perhaps writing a nice Rails front-end on the data, instead of the current Access ADP/ADE variation, might be a fun project to look into, which of course would require some way to get the user''s current security information to establish the connection. Probably not possible, though.
David Heinemeier Hansson
2005-Jul-21 10:15 UTC
Re: SQL Server, Rails and trusted security...
> I have a SQL Server database that has been set up with Windows-based > security, and just thought that perhaps writing a nice Rails > front-end on the data, instead of the current Access ADP/ADE > variation, might be a fun project to look into, which of course would > require some way to get the user''s current security information to > establish the connection. Probably not possible, though.Sure it is. No connection to the database is established before the first model asks for it. You can preempt that by establishing your own connection first that uses whatever credentials you''d like. In a before_filter or similar, you just do ActiveRecord::Base.establish_connection yourself. Then all models from there on will use that connection. -- 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
Sorry to highjack this discussion somewhat, but does this same approach allow for DB user switching per action? For instance, could I have my user model change to a DB user with rights to read the password table during authorization? On Jul 21, 2005, at 6:15 AM, David Heinemeier Hansson wrote:>> I have a SQL Server database that has been set up with Windows-based >> security, and just thought that perhaps writing a nice Rails >> front-end on the data, instead of the current Access ADP/ADE >> variation, might be a fun project to look into, which of course would >> require some way to get the user''s current security information to >> establish the connection. Probably not possible, though. >> > > Sure it is. No connection to the database is established before the > first model asks for it. You can preempt that by establishing your own > connection first that uses whatever credentials you''d like. > > In a before_filter or similar, you just do > ActiveRecord::Base.establish_connection yourself. Then all models from > there on will use that connection. > -- > 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 >
On 7/21/05, Toby Boudreaux <rails-lb8SQxIZKShBDgjK7y7TUQ@public.gmane.org> wrote:> Sorry to highjack this discussion somewhat, but does this same > approach allow for DB user switching per action? For instance, could > I have my user model change to a DB user with rights to read the > password table during authorization?I imagine so. Just follow David''s advice and do ActiveRecord::Base.establish_connection in a before_filter. Look in config/environment.rb for an example of how Rails does it. You could use multiple database.yml files for whatever users you need. Corey''s inquiry is a little more interesting, however. I imagine you''d have to run in IIS because of the way it grabs your authentication info. It doesn''t actually send your password, it uses some NT LanManager protocol to establish your login. Perhaps if this is all working in IIS you just have to pass the right SQL Server connection string and that''s it?
On 7/21/05, Rick Olson <technoweenie-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Corey''s inquiry is a little more interesting, however. I imagine > you''d have to run in IIS because of the way it grabs your > authentication info. It doesn''t actually send your password, it uses > some NT LanManager protocol to establish your login. Perhaps if this > is all working in IIS you just have to pass the right SQL Server > connection string and that''s it?Well...I don''t know. From the command line, using trusted security looks like this: osql -S somedbserver -d somerandomdb -E osql/isqlw/etc then uses the current user''s credentials to establish the connection, and the security into the DB is managed via NT/AD security. If you start the Command window with <i>runas</i> to login as a different user, then doing the above in that session would of course invoke the connection using the credentials of the user logged in via runas. The problem for me is that for potential users of this kind of setup, I can query the database and get its NT roles (need to add some trickery to the sqlserver_adapter.rb to let it execute procs), and probably identify the users in those roles as well, but the users do not have SQL Server logins and connect only via Trusted Security, which metes out their access restrictions. It won''t be feasible to push Ruby to user computers to run the apps via rails on their computers, nor to set up application-level security. (for non-Windows users, runas is equivalent in spirit, but not implementation, as su or sudo on *nix, but it''s not scriptable because it will always interactively prompt you to enter a password. However, a cool little dll called ''tqcrunas.dll'' can get you around this (which is great for having login scripts invoke things on logging in computers as the local administrator during users login...), as well I think it doesn''t require the Runas service to be running, either. MKS'' su command, at the time (3 yrs ago) is just really a unix-style wrapper around runas... ). The ideal would be to somehow use WMI and/or ADSI calls on the server to slurp the client''s security tokens and then somehow push these to the sql server in question to set up the connection. And, of course, it''s not something I set up initially (I would have used SQL Server security...). And the SQL Server adapter does not even try to invoke Trusted Connections, which would be trying to invoke the SQL Server connection using the containing app''s security (for IIS, that would be IIS_machine; for apache/fastcgi/rails, the user starting the server?), in which case you would either need to figure out how to work around this or implement application-level security, just like every other web app. Thanks for the ideas.> _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >