What Sentry permissions are required to create Analytical Views in Arcadia?

Example:

userA wants to create an analytical view av1 in target database td is based on table t in source database sd.*

Creation of Analytical Views

  • userA would need to have SELECT privilege on the base table sd.t and ALL privilege on target database td.

Example:

CREATE ANALYTICAL VIEW td.av1 STORED AS PARQUET AS SELECT y,m,d,max(v) FROM sd.t GROUP BY y,m,d;

userA would require SELECT privilege on base table sd.t and the ALL privilege on target td.

Refresh of Analytical Views

  • userA would need to have ALL privilege on base table sd.t.

Example:

REFRESH ANALYTICAL VIEW td.av1;

Only the privileges of sd.t are considered, userA does not need privileges on td.av1.

Deletion of Analytical Views

  • userA would need to have ALL privilege on the base table sd.t.

Example:

DROP ANALYTICAL VIEW td.av1;

Only the privileges of sd.t are considered, userA does not need privileges on td.av1.

Query routing to Analytical Views

  • userA would need SELECT privilege on the base table sd.t.

Example:

SELECT max(v),y,m,d FROM sd.t GROUP BY y,m,d;

Would get rewritten to to use the analytical view if userA has SELECT privilege on the base table sd.t, userA does not need SELECT privilege on td.av1.

1 Like

@taddwood , this is really good topic for me, but bit confused with refresh and deletion of AV’s. Not sure why user must need to have all(read/write) access to source database while he was doing the action(refresh/deletion) on the target database table(AV). Could you please explain with some more detailed.

Thank you,
Ganesh

Would like to share that Write access to the source database object can be bypassed by creating view in target database for source tables & then create AV on top of it.

@Ganesh By design all operations on Analytical Views are checked against the source table (or logical view).

So yes if you build analytical view on top of a logical view, security checks are performed on the source, which in this case would be the logical view.