Utilizing cross-database capabilities

Heavydb supports cross-database SQL, by qualifying tables with their associated db name.

Beginning in v6.4, users with proper permissions can query across databases within a HEAVY.AI instance. 

For the remote database, the user would need to have database ACCESS and table SELECT permissions, at minimum.

Example using the default heavyai and information_schema databases:

select * from information_schema.roles

This would be valid SQL if run as a user with adequate permissions, from either the heavyai or  information_schema database on an instance.

Example of a join between tables from databases heavyai and information_schema:

select object_name, name 
from heavyai.heavyai_us_states as s, information_schema.permissions as p
where p.object_name = s.name

What if some users have no access nor visibility to the other remote database?

A user such as an admin-level user in database 'A' who also has database ACCESS and table SELECT permissions for a remote database 'B' can create a new view 'V' in database 'A' which selects from a table from database 'B'. 

Other users in database 'A' who normally have no ACCESS or SELECT permissions for database 'B' can then be granted SELECT permission to 'V'.  That allows them to query the data from 'B' while remaining inside database 'A' while having no knowledge or direct permissions of 'B'.

Extra care must be observed when opening this type of conduit between databases, and proper roles/permissions should be created for this purpose.

Use cross-db capability to organize shared data between multi-tenanted users.

Scenario: There are users divided into 2 or more organizations, each residing in their own databases.  Org1 and Org2 users should not be aware of each other nor see each other or their dashboards.  But both orgs do consume various types of common data in their own dashboards.

To avoid storage redundancy of having the common data exist as duplicated tables in both org's databases, a single common data table can be placed in a 3rd database meant to be shared by both orgs. 

ACCESS and SELECT permissions can be granted to users in both Org1 and Org2, to support ad hoc SQL queries.  Local views could be created that SELECT from the shared database's table, in order to be available for new chart creation in the Immerse UI.