Search code examples
javamysqldatabasespring-bootdatabase-administration

How can I achieve multitenancy in MySql by using Java Spring

How can I achieve multitenancy with MySql Java Spring Best Practices and Suggest to use any other DataBase in stead of MySql for Multitenancy Write and Read for Reporting Purpose.

Solution
    1. Give each tenant their own MySQL username and password.
    2. Then give each tenant one or more databases of their own.
    3. Then grant each tenant's username access only to the databases they own.

    This kind of multitenant approach is used by millions upon millions of WordPress installations on hosting providers, so it is field tested and proven.

    To create the user, do this. Give the user's machine address

      CREATE USER 'userid'@'localhost' IDENTIFIED BY 'password';
  CREATE USER 'userid'@'%' IDENTIFIED BY 'password';

    Then create the database (this gives the database the same name as the user; that can be convenient for multiple tenancy.

      CREATE DATABASE userid;

    Then grant the user privileges on that database only.

      GRANT ALL PRIVILEGES ON userid.* TO 'userid'@'localhost';
  GRANT ALL PRIVILEGES ON userid.* TO 'userid'@'%';

    Then flush the privileges to make them 'take.' 

      FLUSH PRIVILEGES;

    If you want a global reporting user with readonly access to all databases, you can set that up like this.

      GRANT SELECT ON *.* TO 'globalreportid'@'localhost';
  GRANT SELECT ON *.* TO 'globalreportid'@'%';
  GRANT SHOW DATABASES TO 'globalreportid'@'localhost';
  GRANT SHOW DATABASES TO 'globalreportid'@'%';

    Notice you can design a multitenant application that uses just one database, and has columns in various tables to identify the relevant tenant for each row. That's the way most multi-user web applications work. Enforcing the rule that various tenants should not see each others' data is, in that case, up to your application. That is not multi-tenancy at the database level, however.

    Creating a separate database for each tenant in a multitenant application is generally not a good idea if you expect more than a dozen tenants or so to use your system.

    Database security is a complex topic. You can read about MySQL security here. https://dev.mysql.com/doc/refman/5.7/en/security.html