> 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.
I'm not sure if you are referring to the Druid View manager https://docs.imply.io/latest/druid/operations/views/view-manager/, which is an alpha feature. It would elegantly solve the problem, but it's still an alpha feature. On Sun, Apr 2, 2023 at 11:35 AM Julian Hyde <[email protected]> wrote: > +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 <[email protected]> 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 <[email protected]> > 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 <[email protected]> > 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 <[email protected]> > >>> 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 <[email protected]> > >>> 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 > >>>>> > >>>> > >>> > >> >
