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: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Sent: Wednesday, 05 June, 2019 10:58
To: firebird-support@yahoogroups.com
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]