This is the classic multi-tenant question. It depends on which of the isolation models you are using and where you will host. Each model has advantages and disadvantages.
DB per tenant Pros: Your code does not have to take tenant into account. As long as you can route your queries to the appropriate tenant’s DB. Load balancing across DBs are possible if you deploy to diff servers. Think of a few very busy tenants and some not-so-busy. Migrating tenants that grow over time to other servers are easy Cons: With lots of tenants/DBs, when you do an app upgrade you will have to upgrade all DBs at the same time as you will likely update your webapi as well. If not, you will need to maintain different versions of the API as appropriate for the DB version. Truly a nightmare Also no cross tenant/DB queries for your analytics All tenants in one DB Pros: When update time comes, upgrade one DB and your WebAPI and you are done Cross-tenant queries for analytics Load balancing can be done on groups of tenants by using several multi-tenant DBs on different servers Cons: Your application code and table structure have to have TenantId everywhere. Easy for a bug to creep in and leak data across tenants. Some tenants don’t like this model. You cannot migrate a tenant from one DB to another Regarding the question re user, your end users will never log into the DB so you don’t need multiple users set up in FB. It is good practice for you to set up a single non-admin user that is used by your webapi and grant accordingly. Another WebAPI that is subset or different end user use cases may require one additional FB user, again with grants according to what they need From: [email protected] [mailto:[email protected]] Sent: Wednesday, 05 June, 2019 10:58 To: [email protected] Subject: Re: {Disarmed} [firebird-support] Web application with Firebird 3 and SaaS > I am planning the infrastructure and security and I have several > doubts. My initial idea would be to have a database where users can > save their emails and create a separate database for each client. For an Web-Application this is not really common practice as you prevent e.g. usage of "connection pooling". An alternative would be to use an single database and an single database user. If the machine is dedicated toi that web application it should be ok to use default SYSDBA/masterkey login as nobody besides the application can connect to database. It's than up to the application to manage user login with information stored in an normal table. Also the application has to manage accessing the correct data - you will pass Company ID to most SQL statements. As number of clients grows using an single database will consume much less resources on your server. > I think it is best to isolate each database with its own security*.fdb > to prevent any client from accessing another database You application is the only one connecting to database. The clients are connecting to your application. > and in this point is what I am not clear about, how to manage users > and security by database, permissions... read documentation for "CREATE USER" and "GRAND" SQL-Statements. > I would also need to be able to create a database on the fly, when a > new client registers, for that I need to stop the Firebird service? > You can create databases without stopping server of course - it is up to the server to create database. But, creating database and tables does take some time. [Non-text portions of this message have been removed]
