There are a number of different ways you can manage this. One popular approach is to use the Apartment gem to create what is known as the "multi-tenant" database pattern. See if that term gets you closer to what your are looking for.
One database (or at least one schema) per site is the usual method, so maybe say a little more about why you feel that you need to have the same database for multiple sites. What makes these sites special, or what makes the number of databases constrained?
Walter
I want each app/site to have it’s own database but i also wanna let them update a commonly shared database in addition to each apps own database
table has
The users “role” is the condition that determines which database the user will access although in this case it’s not roles it’s just a field in the users table that says which database the user will be using
Why use multiple databases? Why not put it all in one database?
Colin
The way I architect such Apps is by using centralized scope based authorization. It keeps the App clean and avoid bugs like dev forgot to scope the records in controller - controller can ONLY operate on records that are permitted for the current user. If somebody tries to access resource outside of what they are permitted, :not_found is returned rather than :unauthorized - works well for security and user experience.
It has worked well for most of my Apps including complex ERP solutions. Based on project requirements, you may want to use independent schemas (pg), though it might be overkill in lot of use cases.
Keep up to date with Rails on Twitter and This Week in Rails
Policies: Conduct, License, Maintenance, Security, Trademarks