[DRILL-5132] Context based dynamic parameterization of views

• Type: Wish
• Status: Open
• Priority: Critical
• Resolution: Unresolved
• Component/s: Server
• Labels: authentication context isolation jdbcstorage multi-tenancy session-context session-parameter
• Assignee: Unassigned
• Reporter: Nagarajan Chinnasamy
• Created: 15/Dec/16 20:12
• Updated: 04/Jan/17 20:10

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()

Issue Links

• links to: GitHub Pull Request #685