The multi-tenancy is an architecture in which different tenants share a single software instance. CrateDB does not support the creation of multiple databases and catalogs as some other solutions (e.g., PostgreSQL). However, there are several ways to implement multi-tenancy in CrateDB, and, as is often the case, which one works the best depends on a variety of options and trade-offs. In this article, we will illustrate two methods for sharing a single CrateDB instance between multiple tenants. # Schema-based multi-tenancy In schema-based multi-tenancy, every tenant has its own database schema. CrateDB supports the creation of tables in different schemas ([Schemas - CrateDB Reference](https://crate.io/docs/crate/reference/en/latest/general/ddl/create-table.html#schemas)). The following statements illustrate the creation of two tables with different schemas: ```sql CREATE TABLE "tenantA"."table1" ( id int, name text, ); CREATE TABLE "tenantB"."table2" ( id int, address text, ); ``` In this example, we created the first table inside schema ``tenantA`` and the second table inside schema ``tenantB``. Furthermore, access privileges can be administrated on the `SCHEMA` level to restrict access for tenant users only on their schema. The schema-based multi-tenancy has a couple of benefits: * Schema changes are independent of other tenants inside CrateDB * Less risk of data leakage due to data isolation * Application code does not have to be tenant-aware However, there are some drawbacks: * More complexity, as this approach requires the creation of different schemas for different tenants * Performance considerations, such as sharding and partitioning, need to be done for every tenant individually (depending on the expected data volume) * Higher risk of querying incorrect schema * Risk of getting close to the maximum number of shards ([cluster.max_shards_per_node](https://crate.io/docs/crate/reference/en/5.3/config/cluster.html#shard-limits)) if there is a significant number of tenants # Table-based multi-tenancy In table-based multi-tenancy, all data resides in the same table, but it’s separated by a discriminator column. In this case, each query needs a `WHERE` statement to select data based on the tenant context. The following example illustrates table creation with a separate `tenant` column. ```sql CREATE TABLE "doc"."name" ( id int, name text, price int, tenant name ); ``` Record-based access control is not possible in this scenario. However, you can create a `VIEW` that is restricted to a single tenant. Without the usage of views, data isolation must be guaranteed on the application level. Table-based multi-tenancy has some benefits: * The application doesn't need to worry about which schema it is connecting to * There is only one schema to maintain * Performance considerations are easier to make, as you don't need to differentiate between tenants with high and low data volume in your sharding and partitioning strategy * Data is shared across all tenants Drawbacks are: * Application code needs to be tenant-aware * Schema changes affect all tenants * Possible data leaks as record-based access control are not possible Finally, if you need full data isolation between different tenants, then you must run a separate CrateDB cluster for each tenant. # Configuring access privileges with CrateDB The privileges of CrateDB users have to be managed using the `GRANT`, `DENY` or `REVOKE` statements ([Privileges](https://crate.io/docs/crate/reference/en/4.8/admin/privileges.html)). CrateDB supports four different privilege types: * Data Query Language (DQL) * Data Manipulation Language (DML) * Data Definition Language (DDL) * Administration Language (AL) These privileges can be granted on `CLUSTER`, `SCHEMA`, `TABLE`, and `VIEW` levels. In schema-based multi-tenancy, you can grant a user full privileges on schema `tenantA` using the following statement: ```sql GRANT ALL PRIVILEGES ON SCHEMA tenantA TO tenantA_user1; ``` Similarly, in table-based multi-tenancy you can grant `DQL` privilege for a specific tenant view: ```sql GRANT DQL ON VIEW tenantA_view TO tenantA_user1; ``` # Summary This short article covers the main approaches to multi-tenancy with CrateDB: schema-based and table-based multi-tenancy. We also outlined the benefits and drawbacks of each approach, and which one works best for you depends on your use case and goals. If you find this article interesting and want to learn more about CrateDB, visit our [official documentation](https://crate.io/docs/crate/reference/en/4.8/) and check our tutorials on [CrateDB Community](https://community.cratedb.com/).