SQL: Support Run_as parameter in X-pack in SQL statement

[elasticmachine]

Original comment by @gingerwizard:

This request has BI tools in mind. Most of the ones we have looked at support being able to inject this current BI user into the SQL statement. @elasticjeff has further details here. The driver connects through a user. In order to allow document permissions to be reflected in the BI tool, the proposal is to allow a run_as parameter to be added to the sql statement. This would in turn exploit the x-pack functionality. This would require the administrator to inturn reflect the users access permissions in the data store i.e. Elasticsearch.

@eskibars for thoughts. @costin

[elasticmachine]

Original comment by @costin:

Sounds like a useful function and implementation wise it's not a big deal - just another parameter on the configuration which gets set/reset across queries. However I'm concerned on the amount of time this takes for testing it - and I don't know - @nik9000. SSL proved to be quite the hurdle.

If we are to do it for alpha, it's going to be post merge to xpack.

[elasticmachine]

Original comment by @nik9000:

So you'd give the BI tool user the ability to run as some other user and have that user run the queries? That doesn't sound useful unless you want to be able to have the BI tool run as multiple users or something. Otherwise why not just give the permissions to the BI tool user.

[elasticmachine]

Original comment by @gingerwizard:

@nik9000 individuals access the BI tool with their own login credentials. As they are clicking around its executing queries via SQL, populating the queries from values in the visuals. As well as the visual values, their usernames are available. Currently all queries are executed as one admin - that used to make the JDBC connection. The idea here is to pass the current BI user into the SQL statement to indicate the running user. The tool would then execute the queries as this user, thus ensuring data access privileges are respected. This relies on us reflecting permissions for the data into Elasticsearch - which is a reasonable expectation.

[elasticmachine]

Original comment by @jasontedor:

We discussed this in Fix-it-Friday and agree that it makes sense to add this functionality. We support this functionality elsewhere and should support it on this layer as well.

[elasticmachine]

Original comment by @nik9000:

We discussed this in Fix-it-Friday and agree that it makes sense to add this functionality. We support this functionality elsewhere and should support it on this layer as well.

This isn't about supporting run-as for SQL in general, it is about embedding support for run-as in the SQL grammar itself. So the user could do something like RUN AS bob SELECT * FROM bobs_index. I don't think this is the same thing.

[elasticmachine]

Original comment by @jasontedor:

This is how I understood the proposal and I think this is equivalent to submitting the es-security-runas-user header? That is, I think it's the same thing?

@s1monw You also commented during the discussion. Did you interpret this the same as me and the same as @nik9000 is clarifying?

[elasticmachine]

Original comment by @costin:

@gingerwizard can you clarify what is it you are looking for? Do you need run as inside SQL itself or it is enough to just configure the client (cli/odbc,jdbc driver) appropriately (which was my assumption)?

[elasticmachine]

Original comment by @nik9000:

Yeah, if you just want it in the CLI and JDBC driver we can totally get that working.

[elasticmachine]

Original comment by @gingerwizard:

We ideally need to expose it as a SQL operator e.g.

SELECT * FROM mortgages RUN AS bob

Id like to confirm with @elasticjeff that is the easiest way to integrate with most BI tools to ensure permissions are pushed down to the datastore.

[elasticmachine]

Original comment by @gingerwizard:

I think we need to look a number of toolsto see how the runtime user is passed to the driver. I'll do some research and detail the approaches the main BI vendors use. This is low priority and defn not a req for alpha or even the first release.

[elasticsearchmachine]

Pinging @elastic/es-analytical-engine (Team:Analytics)

[wchaparro]

Closing as not planned, superceded by our work in ES|QL. Please reopen if you have concerns.