Variable datasource based on user

问题:

I'm currently developing a back end and having my first run in with security laws etc. and it has complicated the design of my DB slightly:

Specification

Central server for app with DB containing limited user information (user_id, email, password (hashed and salted)) can be anywhere.

Organisations making use of our service require that all other information be stored in-house, so the database for that particular organisation is in their building.

The user IDs in our central database are used by multiple types of users in these organisations databases, where more info about that user is stored (phone number, name, address...)

Problem

With Spring Boot, I need to make it so the datasource used is determined by which user makes the request. I map users to their corresponding organisation's database within the central server so the information is there, but I'm not sure how to make this variable.

I understand there are methods involving adding another database config in the application.properties file. But as far as I'm aware this can't be changed (easily) once the server is deployed and running without a full redeploy, and I'm hoping to build this in such a way that adding another organisation only involves setting up their db, and adding another database details to the central server.

Extra detail

I'd like to use CrudRepository with hibernate entities for this. I plan on only generating user IDs on the central server.

Any pointers would be awesome.

Thanks!

回答1:

The terminology for this is database multi-tenancy. There are multiple strategies for multi-tenancy: different databases, different schemas in the same database, and the same schema on one database with a defined discriminator.

You basically create a DataSourceBasedMultiTenantConnectionProviderImpl class which provides the connection to a datasource based on which tenant is requesting it, and a CurrentTenantIdentifierResolverImpl class which identifies who is the requesting tenant.

You can read more about it here. Since your tenants each have their own database, you would probably want to focus on the multi-tenancy separate database approach. It worked fine with CrudRepository when I implemented it. You also might want to find your own way of creating the tenant map, since I had 2 tenants and no need to add more at any point.

Heres a sample of the connection provider from when I implemented this:

public class DataSourceBasedMultiTenantConnectionProviderImpl extends AbstractDataSourceBasedMultiTenantConnectionProviderImpl {

    private static final String DEFAULT_TENANT_ID = "A";

    @Autowired
    private DataSource datasourceA;

    @Autowired
    private DataSource datasourceB;

    private Map<String, DataSource> map;

    @PostConstruct
    public void load() {
        map = new HashMap<>();
        map.put("A", datasourceA);
        map.put("B", datasourceB);
    }

    @Override
    protected DataSource selectAnyDataSource() {
        return map.get(DEFAULT_TENANT_ID);
    }

    @Override
    protected DataSource selectDataSource(String tenantIdentifier) {
        return map.get(tenantIdentifier);
    }
}