Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-5132

Context based dynamic parameterization of views

    XMLWordPrintableJSON

    Details

      Description

      Requirement

      Its known that Views in SQL cannot have custom dynamic parameters/variables. Please refer to Justin Swanhart's response to this SO question in handling dynamic parameterization of views.

      The PR #685 DRILL-5043 originated based on this requirement so that we could build views that can dynamically filter records based on some dynamic values (like current tenant-id, user role etc.)

      Since Drill's basic unit is a View... having such built-in support can bring in dynamism into the whole game.

      This feature can be utilized for:

      • Data Isolation in Shared Multi-Tenant environments based on Custom Tenant Discriminator Column
      • Data Protection in building Chained Views with Custom Dynamic Filters

      To explain this further, If we assume that:

      1. As and when the user connection is established, we populate session context with session parameters such as:
        • Tenant ID of the currently logged in user
        • Roles of the currently logged in user
      1. We expose the session context information through context-based-functions such as:
        • session_id – that returns unique id of the session
        • session_parameter('<parameter-name>') - that returns the value of the session parameter

      then a view created with the following kind of query:

      create or replace view dynamic_filter_view as select
         a.field as a_field
         b.field as b_field
      from
         a_table as a
      left join
         b_table as b
      on
         a.bId = b.Id
      where
         session_parameter('tenantId')=a.tenantId
      

      becomes a query that has built-in support for dynamic parameterization that only returns records of the tenant of the currently logged in user. This is a very useful feature in a shared-multi-tenant environment where data is isolated using multi-tenant-descriminator column 'tenantId'.

      When building chained views this feature will be useful in filtering records based on context based parameters.

      This feature will particularly be useful for data isolation / data protection with jdbc storage plugins where drill-authenticated-credentials are not passed to jdbc connection authentication. A jdbc storage has hard-coded, shared credentials. Hence the the responsibility of data isolation / data protection lies with Views themselves. Hence, the need for built-in support of context based dynamic parameters in Views.

      Design/Implementation Considerations:

      • Session parameters can be obtained through authenticators so that custom authenticators can return a HashMap of parameters obtained from external systems.
      • Introduce SessionContext to hold sessionId and sessionParameters
      • Implement context based functions session_id and session_parameter()

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                nagarajanchinnasamy Nagarajan Chinnasamy
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated: