Data sharing in multi-tenant database systems

Patent metadata

Field	Value
Publication number	US-11100103-B2
Application number	US-202017120546-A
Country	US
Kind code	B2
Filing date	Dec 14, 2020
Priority date	May 31, 2019
Publication date	Aug 24, 2021
Grant date	Aug 24, 2021

Abstract

The subject technology generates a materialized view over a share object, the share object including data associated with a first account. The subject technology merges the materialized view and the share object. The subject technology determines that the materialized view is stale with respect to the share object based at least in part on the merging. The subject technology generates a refreshed materialized view with respect to the share object in response to the determining. The subject technology shares the refreshed materialized view to a second account in response to a request from the second account.

First claim

What is claimed is:

1. A method comprising:
generating a materialized view over a share object, the share object including data associated with a first account;
merging the materialized view and the share object;
determining that the materialized view is stale with respect to the share object based at least in part on the merging;
generating a refreshed materialized view with respect to the share object in response to the determining; and
sharing the refreshed materialized view to a second account in response to a request from the second account.

2. The method of claim 1 , further comprising:
updating the data associated with the first account subsequent to generating the materialized view and prior to sharing the refreshed materialized view to the second account.

3. The method of claim 2 , further comprising:
receiving a request from the second account to generate a second materialized view over particular data associated with the first account;
determining that the particular data is included in the share object; and
granting, to the second account, authorization to generate the second materialized view based on the particular data.

4. The method of claim 3 , further comprising:
providing a notification to the first account indicating that the second account was granted authorization to generate the materialized view over the particular data.

5. The method of claim 1 , further comprising:
granting cross-account access rights to the share object to the second account such that the second account has access to the share object without copying the share object.

6. The method of claim 5 , wherein the granted cross-account access rights are based on a first role associated with the first account, and the granted cross-account access rights are assigned to a second role associated with the second account.

7. The method of claim 6 , wherein the first role has a select grant to data in a first table, the second role has a usage grant of the first role, and the second role is allowed to access data in the first account based at least in part on the usage grant, wherein the usage grant allows the second role to inherit access privileges of the first role.

8. The method of claim 7 , further comprising:
executing, by a user with the second role, a database query on the first table.

9. The method of claim 1 , further comprising:
generating an alias object in the second account;
linking the alias object to a database associated with the first account;
granting, to a first role in the share object, usage privileges to the alias object; and
granting, to a second role in the second account, usage privileges to the first role contained in the share object.

10. The method of claim 1 , further comprising:
defining a secure view definition for the materialized view, the defining the secure view definition including:
granting the second account read access and write access to the materialized view, granting the first account read access to the materialized view, or hiding the materialized view from the first account such that the first account does not have visibility into whether the materialized view was generated.

11. A system comprising:
at least one processor; and
a memory device including instructions, which when executed by the at least one processor, cause the at least one processor to perform operations comprising:
generating a materialized view over a share object, the share object including data associated with a first account;
merging the materialized view and the share object;
determining that the materialized view is stale with respect to the share object based at least in part on the merging;
generating a refreshed materialized view with respect to the share object in response to the determining; and
sharing the refreshed materialized view to a second account in response to a request from the second account.

12. The system of claim 11 , wherein the operations further comprise:
updating the data associated with the first account subsequent to generating the materialized view and prior to sharing the refreshed materialized view to the second account.

13. The system of claim 12 , wherein the operations further comprise:
receiving a request from the second account to generate a second materialized view over particular data associated with the first account;
determining that the particular data is included in the share object; and
granting, to the second account, authorization to generate the second materialized view based on the particular data.

14. The system of claim 13 , wherein the operations further comprise:
providing a notification to the first account indicating that the second account was granted authorization to generate the materialized view over the particular data.

15. The system of claim 11 , wherein the operations further comprise:
granting cross-account access rights to the share object to the second account such that the second account has access to the share object without copying the share object.

16. The system of claim 15 , wherein the granted cross-account access rights are based on a first role associated with the first account, and the granted cross-account access rights are assigned to a second role associated with the second account.

17. The system of claim 16 , wherein the first role has a select grant to data in a first table, the second role has a usage grant of the first role, and the second role is allowed to access data in the first account based at least in part on the usage grant, wherein the usage grant allows the second role to inherit access privileges of the first role.

18. The system of claim 17 , wherein the operations further comprise:
executing, by a user with the second role, a database query on the first table.

19. The system of claim 11 , wherein the operations further comprise:
generating an alias object in the second account;
linking the alias object to a database associated with the first account;
granting, to a first role in the share object, usage privileges to the alias object; and
granting, to a second role in the second account, usage privileges to the first role contained in the share object.

20. The system of claim 11 , wherein the operations further comprise:
defining a secure view definition for the materialized view, the defining the secure view definition including:
granting the second account read access and write access to the materialized view, granting the first account read access to the materialized view, or hiding the materialized view from the first account such that the first account does not have visibility into whether the materialized view was generated.

21. A non-transitory computer-readable medium comprising instructions, which when executed by at least one processor, cause the at least one processor to perform operations comprising:
generating a materialized view over a share object, the share object including data associated with a first account;
merging the materialized view and the share object;
determining that the materialized view is stale with respect to the share object based at least in part on the merging;
generating a refreshed materialized view with respect to the share object in response to the determining; and
sharing the refreshed materialized view to a second account in response to a request from the second account.

22. The non-transitory computer-readable medium of claim 21 , wherein the operations further comprise:
upda

Assignees

• Snowflake Inc

Inventors

• Rajaperumal Prasanna
• Cruanes Thierry
• Lee Allison Waingold
• Demura Igor
• Yan Jiaqi
• Dageville Benoit

Classifications

• G06F16/278

  Data partitioning, e.g. horizontal or vertical partitioning · CPC title

• G06F16/256

  in federated or virtual databases · CPC title

• G06F16/24552

  Database cache management · CPC title

• G06F16/24539 Primary

  using cached or materialised query results · CPC title

• G06F16/2393 Primary

  Updating materialised views · CPC title