+1 what Askar said Also you could give users access to views. A particular user might have a view with “WHERE tenant = 100” so if they ask for any any other tenant is they would get no results.
It’s also possible to use Calcite to check grants (table and column access) and fail a query if they access objects they are not allowed to see or use. We should add features to support this use case better. Julian > On Apr 2, 2023, at 11:09, Askar Bozcan <askar.mu...@gmail.com> wrote: > > (EDIT) > ... *For this case *there's also a Druid adapter which uses the native JSON > intf. > >> On Sun, 2 Apr 2023 at 21:08, Askar Bozcan <askar.mu...@gmail.com> wrote: >> >> What's your use case? Do you want to: >> a) Use Calcite as a "frontend" DB of sorts, to accept all queries and send >> the processed SQL query (from Calcite) only the accepted queries? For this >> case >> b) Just use Calcite as a query processor, and send the processed query >> yourself? >> >> - Askar >> >>> On Sun, 2 Apr 2023 at 20:38, Sebastien Rosset <sros...@gmail.com> wrote: >>> >>> Thank you for the quick response. I am new to Calcite, it's good to hear >>> there might be a possibility. I will investigate. Regarding the tenant_id, >>> if the input SQL statement is: >>> SELECT a, b, c >>> FROM datasource >>> WHERE input_expression >>> >>> Then the tool should add a "security filter" as shown below: >>> >>> SELECT a, b, c >>> FROM datasource >>> WHERE tenant_id = 'abcd123' AND input_expression >>> >>> >>> On Sun, Apr 2, 2023 at 9:56 AM Askar Bozcan <askar.mu...@gmail.com> >>> wrote: >>> >>>> Hello Sebastien, >>>> I'd say it's quite feasible for this purpose by having Calcite push-down >>>> pre-processed queries to Druid and being a kind of a "front" to >>> underlying >>>> Druid DB. >>>> >>>> Regarding rejecting queries other than SELECT: >>>> 1) Parse the query and get the SqlNode representing the root of the >>> syntax >>>> tree. >>>> 2) Extend SqlShuttle >>>> < >>>> >>> https://calcite.apache.org/javadocAggregate/org/apache/calcite/sql/util/SqlShuttle.html >>>>> >>>> to check & manipulate the syntax tree, returning null for rejected >>> nodes & >>>> recursively iterating for SELECT sub-queries. >>>> Note that you can use SqlSelect's getFrom, getGroup, etc to get all of >>> the >>>> possible nodes which can contain subqueries. Also note that SqlSelect >>>> itself is a subtype of SqlCall. >>>> >>>> Regarding "tenant_id" in WHERE case, I'm not too sure as I'm neither >>>> familiar with Druid nor with Druid adapter in Calcite. Regardless, it >>>> should be doable on the adapter level AFAIK. >>>> >>>> Hope that was helpful! >>>> >>>> Regards, >>>> Askar Bozcan >>>> >>>> On Sun, 2 Apr 2023 at 18:07, Sebastien Rosset <sros...@gmail.com> >>> wrote: >>>> >>>>> Would it make sense to use Apache Calcite as a security mediator? Has >>>>> calcite already been used for that purpose? The mediator would parse >>>>> untrusted SQL queries, reject queries other than SELECT (including any >>>>> sub-queries), and inject multi-tenancy WHERE filters in every SELECT >>>> query, >>>>> including sub-queries? >>>>> >>>>> More specifically, consider a Druid database which is configured to >>>>> implement multi-tenancy with shared datasources: >>>>> >>>>> >>>> >>> https://druid.apache.org/docs/latest/querying/multitenancy.html#partitioning-shared-datasources >>>>> . >>>>> Every Druid datasource would have a "tenant_id" attribute that can be >>>> used >>>>> in a Druid-SQL WHERE clause. >>>>> >>>>> Thank you. Sebastien >>>>> >>>> >>> >>
