In a system with multi-tenant database containers, there is a clear rule:
Each tenant database has its own database administrator accounts and end users. Privileges granted to users in a particular multi-tenant database container authorize operations in that database only.
An exception to this rule is given in the system database:
In case a user gets assigned the system privilege DATABASE ADMIN, this authorization allows them to execute operations on individual tenant databases, for example, to create or drop tenant databases, to change database-specific properties in configuration (*.ini) files, and perform database-specific backups.
The system database, as well as all tenant databases, has their own SYSTEM user. The SYSTEM user of the system database has already assigned the above mentioned system privilege DATABASE ADMIN.
In a multiple-container system, privileges granted to users in a particular database authorize access to and modification of database objects in that database only.
Cross Database Queries Between Multi-Tenant Database Containers
Every tenant database in a multiple-container system is self-contained with its own isolated set of database users and isolated database catalog. However, to support cross-application reporting, cross-database SELECT queries are possible. This means that database objects such as tables and views can be local to one database, but be read by users from other databases in the same system.
A user in one database can run a query that references objects in another database, if the user is associated with a sufficiently privileged user in the remote database. This associated user is called a remote identity. This is the user who executes the query (or part of the query) in the remote database, and therefore the user whose authorization is checked.
Cross-database access is not enabled by default and must be configured before such user mappings can be set up.
By default, cross database access between tenants is inactive. To run queries spanning multiple tenant databases, the global cross database access switch has to be turned on. An allowlist of databases that are permitted to communicate with each other also has to be set up.
Read-only queries between multi-tenant database containers are possible through the association of the requesting user with a remote identity on the remote database or databases. Cross-database queries (federation) are supported in the SQL engine and calculation engine.