Why dose MATERIALIZED VIEW have no SECURITY INVOKER option, unlike normal VIEW..?

[okayhooni]

I tested MATERIALIZED VIEW recently and found that reading MATERIALIZED VIEW is performed with permission of VIEW DEFINER (as like DEFAULT behavior of normal Trino view)

I want to define and use MATERIALIZED VIEW with permission of VIEW INVOKER (query-submitting user), but there is no SECURITY INVOKER option, unlike normal VIEW..

Is there any technical(or other special) reason not to implement SECURITY option on the MATERIALIZED VIEW?

• https://trino.io/docs/current/sql/create-view.html
• https://trino.io/docs/current/sql/create-materialized-view.html

Related PR) #19160

Related comment) https://github.com/trinodb/trino/pull/19160/files#r1756250563

cc/ @dain @raunaqmorarka

[martint]

INVOKER security doesn't make sense for materialized views, as there is no "invoker" for the materialized view other than whoever creates it in the first place. For all intents and purposes, the materialized view is computed when it's created (and then maintained by the system in the background).

[okayhooni]

@martint , Thank you for quick response!

We are using some custom functions, which are applicable for the de-identification, encryption, and decryption of sensitive information, as plugins.

When there is a materialized view created through the encryption and decryption functions, we want to prevent the invoker from accessing the view if they do not have the necessary permissions for the custom functions.

CREATE MATERIALIZED VIEW iceberg.temp.credential_table
-- SECURITY INVOKER
AS
SELECT decrypt_function('encrypted_value') as sensitive_value

We have confirmed that every time an attempt is made to query the materialized view, the internal permission-checking logic in Trino verifies the access rights for all the functions included in the DDL of materialized view.

Therefore, in our use case (even though a materialized view is physically stored like a regular table), having the SECURITY INVOKER option like in a regular view is meaningful.

I think it also makes sense when a materialized view is generated from a sensitive information table, to allow only users who have access to the source sensitive information table to query the materialized view.

CREATE MATERIALIZED VIEW iceberg.temp.credential_table
-- SECURITY INVOKER
AS
SELECT sensitive_info_value FROM sensitive_info_table

cc/ @dain

[martint]

We have confirmed that every time an attempt is made to query the materialized view, the internal permission-checking logic in Trino verifies the access rights for all the functions included in the DDL of materialized view.

That should not be happening unless the underlying query is being executed to refresh the materialized view.

[okayhooni]

That should not be happening unless the underlying query is being executed to refresh the materialized view.

It actually did check permissions of functions on DDL, even with SELECT query.

[WaseeA]

Hi @okayhooni, this seems like interesting stuff! I was wondering what you did for your encryption/decryption functions, would you be willing to share? Also, did you implement a custom function for integrity or is that handled by --SECURITY INVOKER?