Authorization for Analytical Views

Arcadia Engine's Analytical Views implements authorization criteria through Sentry and Ranger to restrict access to specific databases, tables, or columns.

The system translates authorization checks on analytical views into corresponding checks on underlying base tables.

Authorization Concepts

Arcadia Engine checks the user's authorization for analytical views, which makes the system secure.

Arcadia Engine uses the user's system privileges on objects (columns, tables, databases, and servers) to authorize access to analytical views.

For example, consider userA, who belongs to the groupA, which in turn has roleA. The analytical view av1 is based on table t. Then,

  • If role roleA has SELECT privilege on table t, then whenever userA submits a query on t it will be routed to analytical view av1.
  • If role roleA has only SELECT privilege on table t, userA will not be able to alter or insert into any analytical view based on table t. To do that, userA must have ALL and INSERT privileges, respectively.

Examples of Authorization

This section describes operations on analytical views, and the corresponding necessary authorization levels.

For example, consider one of the steps of the analytical view refresh mechanism:

INSERT INTO analytical_view_name SELECT … FROM base_table;

This statement requires INSERT privilege on analytical_view_name, and a SELECT privilege on base_table. Internally, Arcadia Engine verifies that the user has both INSERT and SELECT privileges for base_table.

Create Analytical View

Operation
CREATE ANALYTICAL VIEW td.av1 
  STORED AS PARQUET 
  AS SELECT y,m,d,max(v) 
     FROM sd.t 
     GROUP BY y,m,d;
Authorization
(SELECT privilege on base table sd.t 
  OR 
 SELECT COLUMN privilege on y,m,d,v of sd.t) 
  AND 
ALL privilege on target td

Refresh Analytical View

Operation
REFRESH ANALYTICAL VIEW
Authorization
ALL privilege on sd.t

Drop Analytical View

Operation
DROP ANALYTICAL VIEW td.av1;
Authorization
ALL privilege on sd.t
No privilege on td.av1 needed

Show Analytical Views

Operation
SHOW [EXTENDED] [ALL] ANALYTICAL VIEWS
Authorization
Some TABLE level privilege on the base tables

Describe

Operation
DESCRIBE [FORMATTED] aview_name
Authorization

Shows all the columns in the analytical view if the user has the following privileges:

ANY TABLE privilege 
  OR 
SELECT privilege on ANY COLUMN

Select from Table

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

Query routes to analytical view if the user has the following privileges:

SELECT privilege on sd.t 
  OR 
SELECT COLUMN privilege on y, m, d, v of sd.t

Select from Analytical View

Operation
SELECT y, m, d 
  FROM td.av1;
Authorization
SELECT privilege on sd.

For native access, must have TABLE access, as COLUMN privilege is not enough.

Explain Select

Operation
EXPLAIN SELECT max(v),y,m,d 
  FROM sd.t 
  GROUP BY y,m,d;
Authorization
SELECT privilege on sd.t
  OR
SELECT COLUMN privilege on y, m, d, v of sd.t

Select from Logical View

Operation
SELECT y,m,d,max_v FROM logical_view;

Where the logical view has the following definition:

CREATE VIEW logical_view 
  AS SELECT max(v) 
       AS max_v,y,m,d 
       FROM sd.t 
       GROUP BY y,m,d;)
Authorization
TABLE SELECT privilege on logical_view

No privilege check on matching analytical views required

Explain Select from Logical View

Operation
EXPLAIN SELECT y,m,d,max_v FROM logical_view;
Authorization

If issued internally by Arcadia Visualization Server:

TABLE SELECT privilege on logical_view

If issued in SQL visualization or through command line interface:

TABLE SELECT privilege on logical_view 
  AND 
(TABLE SELECT privilege on sd.t
  OR
 COLUMN SELECT privilege on y, m, d, v on sd.t)