Restricted queries in a database clean room
Embodiments of the present disclosure may provide a data clean room architecture that restricts data included in the clean room. The data clean room architecture can implement a policy to enable data restrictions for data shared between multiple parties via a distributed database. Multiple database accounts can implement validation instances to validate queries when received from other database accounts. One or more of the database accounts can provide a query template that is congruent with the validation instance for use by the other database accounts to generate queries against the data shared in the data clean room.
1. A method comprising:
accessing, from a first database of a distributed database, a first shared source dataset from a second database of the distributed database and a second shared source dataset from a third database of the distributed database;
storing an approved statements table comprising database statements that are executable via the distributed database against the first shared source dataset, the second shared source dataset, and a source dataset, the source dataset being managed on the distributed database by the first database;
determining that one or more elements in the database statements are in the approved statements table;
in response to the one or more elements of the database statements being in the approved statements table, generating a results dataset by executing the database statements against the first shared source dataset, the second shared source dataset, and the source dataset; and
storing, by one or more processors of a machine, the results dataset in the first database.
2. The method of claim 1 , wherein the database statements in the approved statements table comprise a query request from the first database.
3. The method of claim 2 , wherein the query request is validated by a first validation database execution object on the second database.
4. The method of claim 2 , wherein the query request is validated by a second validation database execution object on the third database.
5. The method of claim 4 , wherein the first validation database execution object and the second validation database execution object comprise stored procedures of the distributed database.
6. The method of claim 4 , wherein the first validation database execution object and the second validation database execution object comprise user-defined functions (UDFs) of the distributed database.
7. The method of claim 3 , further comprising:
receiving, from the second database, a query template comprising query language for execution in accordance with the first validation database execution object.
8. The method of claim 7 , wherein the query request that is generated by the first database comprises a modified version of the query template that is received from the second database.
9. The method of claim 1 , wherein the results dataset is generated by a stored procedure that is executed on the first database using an available values data that is received from the second database, the available values data comprising a schema of allowed columns of the second shared source dataset that are useable by the stored procedure to generate the results dataset.
10. The method of claim 2 , wherein the query request is against a combination of portions of the first shared source dataset, the second shared source dataset, and the source dataset.
11. The method of claim 10 , wherein the query request comprises a join database statement to join matching entries in one or more of: the first shared source dataset, the second shared source dataset, and the source dataset.
12. A system comprising:
one or more processors of a machine; and
at least one memory storing instructions that, when executed by the one or more processors, cause the machine to perform operations comprising:
accessing, from a first database of a distributed database, a first shared source dataset from a second database of the distributed database and a second shared source dataset from a third database of the distributed database;
storing an approved statements table comprising database statements that are executable via the distributed database against the first shared source dataset, the second shared source dataset, and a source dataset, the source dataset being managed on the distributed database by the first database;
determining that one or more elements in the database statements are in the approved statements table;
in response to the one or more elements of the database statements being in the approved statements table, generating a results dataset by executing the database statements against the first shared source dataset, the second shared source dataset, and the source dataset; and
storing the results dataset in the first database.
13. The system of claim 12 , wherein the database statements in the approved statements table comprise a query request from the first database.
14. The system of claim 13 , wherein the query request is validated by a first validation database execution object on the second database.
15. The system of claim 14 , wherein the query request is validated by a second validation database execution object on the third database.
16. The system of claim 15 , wherein the first validation database execution object and the second validation database execution object comprise stored procedures of the distributed database.
17. The system of claim 15 , wherein the first validation database execution object and the second validation database execution object comprise user-defined functions (UDFs) of the distributed database.
18. The system of claim 14 , further comprising:
receiving, from the second database, a query template comprising query language for execution in accordance with the first validation database execution object.
19. The system of claim 18 , wherein the query request that is generated by the first database is a modified version of the query template that is received from the second database.
20. One or more machine-storage media containing instructions that, when executed by a machine, cause the machine to perform operations comprising:
accessing, from a first database of a distributed database, a first shared source dataset from a second database of the distributed database and a second shared source dataset from a third database of the distributed database;
storing an approved statements table comprising database statements that are executable via the distributed database against the first shared source dataset, the second shared source dataset, and a source dataset, the source dataset being managed on the distributed database by the first database;
determining that one or more elements in the database statements are in the approved statements table;
in response to the one or more elements of the database statements being in the approved statements table, generating a results dataset by executing the database statements against the first shared source dataset, the second shared source dataset, and the source dataset; and
storing the results dataset in the first database.
21. The one or more machine-storage media of claim 20 , wherein the database statements in the approved statements table comprise a query request from the first database.
22. The one or more machine-storage media of claim 21 , wherein the query request is validated by a first validation database execution object on the second database.
23. The one or more machine-storage media of claim 21 , wherein the query request is validated by a second validation database execution object on the third database.
24. The one or more machine-storage media of claim 23 , wherein the first validation database execution object and the second validation database execution object comprise stored procedures of the distributed database.
25. The one or more machine-storage media of claim 23 , wherein the first validation database execution object and the second validation database execution object comprise user-defined functions (UDFs) of the distributed database.
26. The one or more machine-storage media of claim 22 , the operations further comprising:
receiving, from the second database, a query template comprising query language for execution in accordance with the first validation database execution object.
27. The one or more machine-storage media of claim 26 , wherein the query request that is generated by the first database comprises a modified version of the query template that is received from the second database.
28. The one or more machine-storage media of claim 20 , wherein the results dataset is generated by a stored procedure that is executed on the first database using an available values data that is received from the second database, the available values data comprising a schema of allowed columns of the second shared source dataset that are useable by the stored procedure to generate the results dataset.
29. The one or more machine-storage media of claim 21 , wherein the query request is against a combination of portions of the first shared source dataset, the second shared source dataset, and the source dataset.
30. The one or more machine-storage media of claim 29 , wherein the query request comprises a join database statement to join matching entries in one or more of: the first shared source dataset, the second shared source dataset, and the source dataset.